PHP PDOとSQLite：SQLインジェクションを再現してから直す

シリーズ：PHP講座 4/6（中級）。 PHP のクラスが書けるようになり、次はデータベースを扱いたい人向けです。読み終えると、PDO でデータベースを開き、プリペアドステートメントで検索し、同僚のコードで SQL インジェクションが本当に起きることを見せたうえで直せるようになります。

この記事は、データベースにアクセスする PHP を書く人・レビューする人、特に . 演算子で SQL を組み立てている社内ツールや管理画面のコードを抱えている人のためのものです。PHP に同梱されている SQLite を使うので、DB サーバーは要りません。すべての例は PHP 8.5.6 CLI + SQLite 3.53.4、Apple Silicon Mac（M1 Pro）で実行しました。インジェクションは本物で、下のペイロードは、ログイン確認で守るはずだったテーブルの全行を返しました。

本番の事例として、10年物の PHP バックエンドでインジェクションと出力の修正90件を行い、出力がバイト単位で同一であることを証明した レガシー PHP の SQLi/XSS 監査 もあります。この講座はその土台です。テーブルはすべて汎用の users と posts です。

PDO で接続し、ERRMODE_EXCEPTION を設定する

<?php
declare(strict_types=1);

$file = __DIR__ . '/app.sqlite';
$pdo = new PDO('sqlite:' . $file, options: [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$pdo->exec(<<<SQL
    CREATE TABLE users (
        id    INTEGER PRIMARY KEY,
        email TEXT NOT NULL UNIQUE,
        name  TEXT NOT NULL,
        role  TEXT NOT NULL DEFAULT 'member'
    );
    CREATE TABLE posts (
        id      INTEGER PRIMARY KEY,
        user_id INTEGER NOT NULL REFERENCES users(id),
        title   TEXT NOT NULL,
        body    TEXT NOT NULL
    );
SQL);

$ins = $pdo->prepare('INSERT INTO users (email, name, role) VALUES (:email, :name, :role)');
foreach ([
    ['alice@example.com', 'Alice', 'admin'],
    ['bob@example.com',   'Bob',   'member'],
    ['carol@example.com', 'Carol', 'member'],
] as [$email, $name, $role]) {
    $ins->execute(['email' => $email, 'name' => $name, 'role' => $role]);
}
$p = $pdo->prepare('INSERT INTO posts (user_id, title, body) VALUES (?, ?, ?)');
$p->execute([1, '100% sure', 'Discount 50% off_today']);
$p->execute([1, 'Hello', 'first post']);
$p->execute([2, 'under_score', 'a_b']);
$p->execute([3, 'Points', 'you have 500 points']);
echo "users: ", $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn(), PHP_EOL;   // users: 3

ERRMODE_EXCEPTION は PHP 8.0 から既定値です（8.5.6 で getAttribute(PDO::ATTR_ERRMODE) がそれを返すことを確認しました）。それでも明示的に設定してください。コードは古いサーバーにコピーされることがあり、以前の既定値は「黙って失敗する」でした。黙るとこうなります。

ERRMODE_SILENT:  $pdo->query('SELECT * FROM nope') returns bool(false)
                 errorInfo() = [HY000, 1, no such table: nope]
                 then: Fatal error: Uncaught Error: Call to a member function fetchAll() on false

本当の原因（no such table）は errorInfo() の中にあり、目に入るエラーは1行後の false についての話です。例外モードなら原因がメッセージに出ます。

PHP 8.4 ではドライバ別のサブクラスも追加され、PDO::connect('sqlite::memory:') は Pdo\Sqlite オブジェクトを返します。8.5 では PDO クラス上のドライバ固有定数が非推奨になり、MySQL の設定に触れた途端に表示されます。

Deprecated: Constant PDO::MYSQL_ATTR_INIT_COMMAND is deprecated since 8.5, use Pdo\Mysql::ATTR_INIT_COMMAND instead in ~/demo/b8_misc.php on line 4

SQL インジェクションを実際のクエリで再現する

数多くの入門記事に出てくるパターンです。入力を受け取り、そのまま SQL に連結します。

$input = $argv[1] ?? "nobody@example.com' OR '1'='1";   // 実際には $_GET['email'] のつもり

// 脆弱
$sql = "SELECT id, email, role FROM users WHERE email = '" . $input . "'";
echo "SQL: $sql\n";
$rows = $pdo->query($sql)->fetchAll();
echo "concat   -> ", count($rows), " rows\n";
foreach ($rows as $r) echo "  ", implode(' | ', $r), "\n";

// 修正
$st = $pdo->prepare('SELECT id, email, role FROM users WHERE email = ?');
$st->execute([$input]);
echo "prepared -> ", count($st->fetchAll()), " rows\n";

SQL: SELECT id, email, role FROM users WHERE email = 'nobody@example.com' OR '1'='1'
concat   -> 3 rows
  1 | alice@example.com | admin
  2 | bob@example.com | member
  3 | carol@example.com | member
prepared -> 0 rows

入力中のシングルクォートが文字列リテラルを閉じ、OR '1'='1' が条件の一部になりました。ユーザー1人を返すはずのクエリが、admin を含む3人全員を返しています。UNION を使うペイロードなら、クエリが選んでいない列まで読めてしまいます。

SQL: SELECT id, email, role FROM users WHERE email = 'x' UNION SELECT id, name || ':' || email, role FROM users --'
concat   -> 3 rows
  1 | Alice:alice@example.com | admin
  ...
prepared -> 0 rows

prepare() では、値が存在する前に SQL 文が送られて解析されます。値はその後にデータとしてバインドされ、文字列全体として比較されるので、クォートは「誰も持っていないメールアドレスの1文字」にすぎません。

同じ実行から分かったことがあと2つあります。

• O'Brien@example.com のような普通の名前でも連結クエリは壊れ、PDOException: SQLSTATE[HY000]: General error: 1 near "Brien": syntax error になりました。アポストロフィを含む入力でエラーが出るなら、ほぼ確実にインジェクションの穴があります。日本語の名前でこのエラーに出会うことは少ないため、テストデータに必ずシングルクォート入りの値を1つ入れておくのがおすすめです。
• x'; DROP TABLE users; -- を query() に通すと0行が返り、テーブルは残りました。PDO の SQLite の query() は最初の文しか実行しないからです。これに頼ってはいけません。 同じ形のペイロードを exec() に通すと2つの文が両方実行され、UPDATE users SET name = '…' に x' WHERE 1=0; DROP TABLE posts; -- を入れたら posts テーブルが消えました。また MySQL の PDO は、プリペアをエミュレートしている場合、既定で複数の文を許可します。

よくあるミス → 実際に起きること → 直し方

ミス：「addslashes でエスケープしたから安全」。 addslashes は接続の文字コードもデータベースのクォート規則も知りません。SQLite はそもそもバックスラッシュでエスケープしません。O'Brien の正しいクォートは 'O''Brien' で、$pdo->quote("O'Brien") もそれを返しました。ペイロード x' OR 1=1 -- を addslashes に通して実行した結果です。

SELECT id FROM users WHERE email = 'x\' OR 1=1 --'
  rows: 3

SQLite にとって 'x\' は「バックスラッシュを含む完結した文字列」です。addslashes が「エスケープした」はずのクォートがリテラルを閉じ、3行すべてが返りました。直し方： 値にはプレースホルダーを使います。quote() もありますが、構造を変えられないコードのための最後の手段と考えてください。上で紹介した監査記事がまさにその状況です。

ミス：テーブル名や列名をバインドする。 プレースホルダーに入るのは値だけです。ORDER BY ? は定数をバインドするだけなので、その列では並び替わりません。ORDER BY ? DESC に 'id' を渡すと [1,2,3] が返り、エラーも出ませんでした。直し方： 許可リストを使います。$col = in_array($_GET['sort'], ['id', 'email'], true) ? $_GET['sort'] : 'id';

MySQL でエミュレートプリペアが重要な理由（説明のみ、未実行）

この講座では MySQL サーバーを用意していないので、この節は PDO のドキュメントと以前の本番作業に基づく説明で、ここで実行したものではありません。SQLite では setAttribute(PDO::ATTR_EMULATE_PREPARES, true) が単に false を返しました。ドライバは常にネイティブにプリペアします。

PDO_MySQL は既定でプリペアをエミュレートします。 PHP 自身がエスケープした値を SQL 文に埋め込み、完成した1つの文字列をサーバーに送ります。その結果：

1. 文字コード。 エスケープが安全なのは、PHP が本当の接続文字コードを知っている場合だけです。DSN に charset=utf8mb4 を書いてください。後から SET NAMES を実行すると PDO に知らせないままサーバー側の文字コードだけが変わります。これは昔のマルチバイト系エスケープ攻撃の前提条件で、Shift_JIS や EUC-JP の古いシステムを移行するときは特に注意が必要です。
2. 型。 エミュレート時は数値が文字列で返ることが多く、ネイティブプリペアでは INT 列が PHP の int で返ります。つまり JSON が変わります。mysqli 版の話は プリペアドステートメントで JSON の数値型が変わる を参照してください。
3. 複数の文がエミュレート時は既定で許可されます（Pdo\Mysql::ATTR_MULTI_STATEMENTS を参照）。
4. エラーが遅れて出ます。 構文エラーは prepare() ではなく execute() で現れます。

新しい MySQL のコードでは、DSN に charset=utf8mb4、ATTR_EMULATE_PREPARES => false、Pdo\Mysql::ATTR_MULTI_STATEMENTS => false。切り替えたら JSON 出力を再テストしてください。

fetch モード

既定の fetch モードは FETCH_BOTH で、すべての列を2回返します。

FETCH_BOTH: ["id"=>1, 0=>1, "email"=>"alice@example.com", 1=>"alice@example.com", "role"=>"admin", 2=>"admin"]

メモリは2倍、json_encode するとキーが重複します。既定を FETCH_ASSOC にし、用途に合う専用モードを選びましょう。

型付きプロパティを持つクラスへの FETCH_CLASS では、id が int(1) で届きました。PDO の SQLite は整数列を PHP の int で返します。行がないとき fetch() も fetchColumn() も false を返すので、=== false で確認してください。本物の値 0 や空文字列を「行なし」と取り違えてはいけません。

IN 句と LIKE のエスケープ

プレースホルダー1つで済ませるミスです。

$ids = [3, 1, 999];
$st = $pdo->prepare('SELECT id FROM users WHERE id IN (?)');
$st->execute([implode(',', $ids)]);       // -> []

エラーなしで0行でした。データベースは各 id を1つの文字列 '3,1,999' と比較したからです（MySQL ならこの文字列を 3 にキャストして1行返します。別の間違った答えです）。正しくは値ごとにプレースホルダーを1つ作ります。

function inList(PDO $pdo, string $sqlWithIn, array $values): PDOStatement
{
    if ($values === []) {
        throw new InvalidArgumentException('empty IN list: return early (IN () is a syntax error in MySQL)');
    }
    $marks = implode(',', array_fill(0, count($values), '?'));
    $st = $pdo->prepare(str_replace('(:in)', "($marks)", $sqlWithIn));
    $st->execute(array_values($values));
    return $st;
}
$st = inList($pdo, 'SELECT id, email FROM users WHERE id IN (:in) ORDER BY id', $ids);  // -> [1, 3]

array_values() が重要です。array_filter() の後はキーが [0, 2] になることがあり、IN (?,?) に execute([0 => 1, 2 => 3]) を渡すと General error: 25 column index out of range で失敗しました。SQLite は空の IN () を受け付けて0行を返しましたが、MySQL では構文エラーなので [] は PHP 側で処理します。

プリペアドステートメントは LIKE のインジェクションも防ぎますが、ユーザーが入力した % と _ はワイルドカードのままです。

function likeEscape(string $s, string $esc = '\\'): string
{
    return str_replace([$esc, '%', '_'], [$esc . $esc, $esc . '%', $esc . '_'], $s);
}
$safe = $pdo->prepare("SELECT title FROM posts WHERE body LIKE ? ESCAPE '\\'");
$safe->execute(['%' . likeEscape($q) . '%']);

search 50%  naive=["100% sure","Points"]                            escaped=["100% sure"]
search _    naive=["100% sure","Hello","under_score","Points"]      escaped=["100% sure","under_score"]
search %    naive=["100% sure","Hello","under_score","Points"]      escaped=["100% sure"]

_ の検索が全投稿に一致しました。大きなテーブルでユーザーが % を入力すると全行スキャンを強制でき、手軽な DoS になります。エスケープ文字自体を最初にエスケープしてください（str_replace の配列は左から順に処理されます）。

日本語の入力では、さらに2つ確認しました。本文が 本日50%オフ、本日５０％オフ、user_id の話、ＡＢＣ、abc などのテーブルに対する実際の結果です。

LIKE '%％%'  -> [2]              全角の ％ はただの文字（「５０％」の行だけ）
LIKE '%＿%'  -> []               全角の ＿ もただの文字
LIKE '%_%'   -> 全7行            半角の _ は任意の1文字
LIKE 'ABC'   -> [5]  (abc)       ASCII は大文字小文字を区別しない
LIKE 'ＡＢＣ' -> [4]  (ＡＢＣ のみ) 全角英字は abc に一致しない

全角の ％ と ＿ はエスケープ不要ですが、逆に「全角で入力された 50％ を半角の 50% と同じものとして検索したい」なら、mb_convert_kana($q, 'a') などで検索語と保存データの両方を揃える必要があります（本日５０％オフ は 本日50%オフ に、ＡＢＣ＿ は ABC_ になりました）。変換すると全角の ＿ が半角の _ に、つまりワイルドカードに変わるので、正規化してから likeEscape() する順番を守ってください。SQLite の LIKE が大文字小文字を同一視するのは ASCII だけです。

実際の PDOException メッセージ

上のデータベースに対して、1つのスクリプトで出したものです。

[typo in table]    SQLSTATE[HY000]: General error: 1 no such table: user
[typo in column]   SQLSTATE[HY000]: General error: 1 no such column: mail
[UNIQUE violation] SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: users.email
[NOT NULL]         SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: users.name
[too many params]  SQLSTATE[HY000]: General error: 25 column index out of range
[missing :name]    SQLSTATE[HY000]: General error: 25 column index out of range
[bad file path]    SQLSTATE[HY000] [14] unable to open database file
[too few params]   no exception

注目点：

• UNIQUE 違反の getCode() は文字列 '23000'、接続エラーでは int の 14 でした。「メールアドレスが登録済み」の判定は getCode() === 23000 ではなく、$e->errorInfo[0] === '23000'（またはドライバコード errorInfo[1] === 19）で行います。
• パラメータが足りなくても SQLite では例外になりませんでした。 足りない ? は NULL としてバインドされます。SELECT ?, ? に値を1つだけ渡すと "1" と NULL が返ることで確認しました。クエリは黙って何にも一致しません。MySQL ではここで SQLSTATE[HY093]: Invalid parameter number になるので、SQLite でしかテストしていないコードは本番で失敗することがあります。
• 名前付きパラメータのつづり間違い（:id に対して uid）は「パラメータがない」ではなく、分かりにくい column index out of range になりました。このメッセージを見たら、配列のキーとプレースホルダーを見比べてください。

トランザクション：1万件の INSERT を計測

トランザクション外の INSERT は、それぞれが1つのトランザクションです。SQLite はジャーナルを書き、ディスクにフラッシュしてから戻ります。1つのトランザクションで包めば、それが1回で済みます。

$st = $pdo->prepare('INSERT INTO posts (user_id, title) VALUES (?, ?)');
$t = hrtime(true);
if ($mode === 'tx') $pdo->beginTransaction();
for ($i = 0; $i < 10_000; $i++) {
    $st->execute([$i % 100, "title $i"]);
}
if ($mode === 'tx') $pdo->commit();
$ms = (hrtime(true) - $t) / 1e6;

実行ごとに新しい DB ファイル、PHP 8.5.6 CLI、SQLite 3.53.4（既定値：journal_mode=delete、synchronous=2/FULL）、Apple Silicon Mac（M1 Pro）、内蔵SSD、3回実行です。

トランザクション1つで約530倍速くなりました。WAL モードはまとめられない場合（1リクエストで1行書く Web アプリなど）には大きく効きますが、一括処理のトランザクションの代わりにはなりません。トランザクションは正しさ、つまり「全部成功か全部取り消しか」のためのものでもあります。

function transfer(PDO $pdo, callable $work): void
{
    $pdo->beginTransaction();
    try {
        $work($pdo);
        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

before: 4
rolled back: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: posts.title
after: 4
PDOException: There is already an active transaction
PDOException: There is no active transaction

1つ目の INSERT は成功し、2つ目が失敗し、ロールバックで両方が消えました。最後の2行は、PDO にネストしたトランザクションがないことを示しています。2回目の beginTransaction() は例外になり、ロールバック後の commit() も例外になります。外側のトランザクション内で動く可能性があるヘルパーなら、$pdo->inTransaction() を確認するか SAVEPOINT を使ってください。

判断表

正直な限界とバージョンメモ

• PHP 8.5.6 CLI と SQLite 3.53.4 でのみ実行しました。ERRMODE_EXCEPTION が既定になったのは PHP 8.0、ドライバ別サブクラス（Pdo\Sqlite、PDO::connect）は 8.4、PDO::MYSQL_ATTR_* の非推奨は 8.5 からです（Deprecated メッセージを確認しました）。
• MySQL の挙動（エミュレートプリペア、HY093、IN () のエラー、IN での文字列キャスト）はドキュメントと以前の本番作業に基づく説明で、ここでは実行していません。
• トランザクションの時間は macOS・内蔵SSD でのものです。macOS の SQLite は既定で F_FULLFSYNC を使わないので、ここでの自動コミットは完全な耐久性がある場合より速く出ています。レンタルサーバーのネットワークストレージでは、自動コミットはさらに遅くなると考えてください。

演習

findPosts(PDO $pdo, array $userIds, string $q): array を書いてください。投稿者が $userIds に含まれ、本文に $q が文字どおり含まれる（_ はアンダースコアそのもの）投稿の id, title を返します。$userIds が空なら、データベースに触れずに [] を返すこと。

解答

function findPosts(PDO $pdo, array $userIds, string $q): array
{
    $userIds = array_values(array_map('intval', $userIds));
    if ($userIds === []) {
        return [];
    }
    $marks = implode(',', array_fill(0, count($userIds), '?'));
    $like = '%' . str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $q) . '%';
    $st = $pdo->prepare(
        "SELECT id, title FROM posts WHERE user_id IN ($marks) AND body LIKE ? ESCAPE '\\' ORDER BY id"
    );
    $st->execute([...$userIds, $like]);
    return $st->fetchAll(PDO::FETCH_ASSOC);
}

echo json_encode(findPosts($pdo, [1, 2], '_')), PHP_EOL;
echo json_encode(findPosts($pdo, [], '_')), PHP_EOL;
echo json_encode(findPosts($pdo, [2, 3], '50')), PHP_EOL;

[{"id":1,"title":"100% sure"},{"id":3,"title":"under_score"}]
[]
[{"id":4,"title":"Points"}]

SQL の中で IN の ? が先に来るので、配列でも IN の値を先に置きます。この順番を取り違えるのは、エラーにならない典型的なバグです。

前回：PHP講座 3 — クラス、interface、enum · 次回：PHP講座 5 — XSS、CSRF、セッション