PHPのPDO：MySQLへの接続とプリペアドステートメント

PDO（PHP Data Objects）は、データベースとやり取りするための組み込みの方法です。new PDO($dsn, $user, $password)で接続し、値を含むクエリはすべてprepare()とexecute()で実行して、値がSQLとは別に送られるようにします。

<?php
$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'shop_user', 'secret', [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = ?');
$stmt->execute([$email]);
$user = $stmt->fetch();      // an array, or false if no row matched

このページの例はデータベースサーバーが必要なので、普通のコードとして示しています。下のSQLインジェクションの例は実行できます。

PDOでMySQLに接続する

1つ目の引数はDSNで、ドライバー名のあとに接続の詳細が続きます。接続は1か所にまとめ、必要な場所でincludeしましょう。

<?php
// db.php
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // the default since PHP 8.0
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,        // rows as ['id' => 1, ...]
    PDO::ATTR_EMULATE_PREPARES   => false,                   // real prepared statements, real int types
];

try {
    $pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASS'), $options);
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    exit('Database unavailable.');
}

return $pdo;

• charset=utf8mb4はDSNに含めます。これがないと、アクセント付きの文字や絵文字が?や文字化けしたバイトで届くことがあります。
• $e->getMessage()を訪問者に出力してはいけません。ホストやユーザー名が含まれていることがあります。ログに残しましょう。
• パスワードはコードに書かず、環境変数か、Webのルートの外の設定ファイルに置きます。

ほかのデータベースではDSNが変わるだけです。PostgreSQLならpgsql:host=localhost;dbname=shop、SQLiteのファイルならsqlite:/var/data/app.dbです。それぞれにPDOのドライバー（pdo_mysql、pdo_pgsql、pdo_sqlite）のインストールが必要で、php -mで入っているものを一覧できます。

?と:nameのプリペアドステートメント

プレースホルダーは値が入る場所を示します。位置で指定する?のプレースホルダーにはリストを、名前付きのものには連想配列を使います。

<?php
// Positional
$stmt = $pdo->prepare('SELECT * FROM products WHERE category = ? AND price < ?');
$stmt->execute(['books', 20]);

// Named: easier to read when there are many values
$stmt = $pdo->prepare('SELECT * FROM products WHERE category = :cat AND price < :max');
$stmt->execute(['cat' => 'books', 'max' => 20]);

// bindValue when you want to set the type explicitly
$stmt = $pdo->prepare('SELECT * FROM products ORDER BY id LIMIT :limit');
$stmt->bindValue('limit', 10, PDO::PARAM_INT);
$stmt->execute();

プレースホルダーを引用符で囲んではいけません。WHERE name = '?'は文字どおりのテキスト?と比較してしまいます。プリペアドステートメントは違う値で何度でも実行できるので、ループの中ではなく、ループの前で1回だけ準備しましょう。

SQLで文字列を連結するのが危険な理由

このページで唯一実行できるブロックです。同じ入力に対して、間違った方法と正しい方法でクエリを組み立てます。$emailを変えて、もう一度実行してみてください。

<?php
$email = "nobody@example.com' OR '1'='1";

// Wrong: the input becomes part of the SQL
$sql = "SELECT * FROM users WHERE email = '" . $email . "'";
echo "Concatenated:\n$sql\n\n";

// Right: the SQL stays fixed, the value travels separately
$sql = 'SELECT * FROM users WHERE email = ?';
$params = [$email];
echo "Prepared:\n$sql\n";
echo 'Values: ', json_encode($params), "\n";

連結したクエリでは、入力の中の引用符が文字列を閉じ、OR '1'='1'がすべての行で条件をtrueにするので、クエリはすべてのユーザーを返します。ログインフォームなら、パスワードを知らずにログインできてしまうということで、ドライバーが1回の呼び出しで複数の文を受け付けるなら、'; DROP TABLE users; --のような入力で攻撃者の選んだ2つ目の文が実行されます。プリペアドの版では、データベースはSQLと値を別々に受け取るので、引用符は誰にも一致しないメールアドレスの中の1文字にすぎません。

addslashes()のようなエスケープ関数は解決策になりません。接続の文字セットに依存するケースを見逃すからです。数値や「自分のコードから来る」値も含め、すべての値にプレースホルダーを使いましょう。

行を取得する：fetch、fetchAll、fetchColumn

<?php
// One row
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([42]);
$user = $stmt->fetch();                 // ['id' => 42, 'name' => 'Ada', ...] or false
if ($user === false) {
    http_response_code(404);
}

// All rows
$rows = $pdo->query('SELECT id, name FROM users ORDER BY name')->fetchAll();

// Loop row by row without building an array of all rows
$stmt = $pdo->query('SELECT id, name FROM users');
foreach ($stmt as $row) {
    echo $row['name'], "\n";
}

// One value
$count = $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();

// Two columns as key => value
$names = $pdo->query('SELECT id, name FROM users')->fetchAll(PDO::FETCH_KEY_PAIR);  // [42 => 'Ada', ...]

SQLに外部からの値が含まれないならquery()で問題ありません。変数が関わったとたんにprepare()に切り替えましょう。

挿入、更新、削除

<?php
$stmt = $pdo->prepare('INSERT INTO users (email, name, password_hash) VALUES (?, ?, ?)');
$stmt->execute([$email, $name, password_hash($password, PASSWORD_DEFAULT)]);
$id = (int) $pdo->lastInsertId();

$stmt = $pdo->prepare('UPDATE users SET name = ? WHERE id = ?');
$stmt->execute([$newName, $id]);
echo $stmt->rowCount();                 // rows changed

$stmt = $pdo->prepare('DELETE FROM sessions WHERE expires_at < NOW()');
$stmt->execute();

lastInsertId()は文字列を返すので、キャストします。MySQLでは、UPDATEのあとのrowCount()は実際に変わった行を数えるので、名前をすでに持っている値に設定すると0になります。パスワードは平文ではなく、password_hashで保存しましょう。

トランザクション

トランザクションは、複数の文を一緒に成功させるか一緒に失敗させます。何かが例外を投げたらロールバックし、何も保存されません。

<?php
try {
    $pdo->beginTransaction();

    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([100, $from]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([100, $to]);
    $pdo->prepare('INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)')->execute([$from, $to, 100]);

    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}

これは、エラーが例外を投げること（PHP 8.0以降のデフォルト）に頼っているので、失敗した文はcatchに飛びます。MySQLでは、テーブルはInnoDBでなければなりません。MyISAMはトランザクションを無視します。Throwableと再スローについてはtry/catchのページで扱っています。

配列を使ったWHERE IN

1つの?が持てるのは1つの値なので、配列でIN (?)としても動きません。値ごとにプレースホルダーを1つ作ります。

<?php
$ids = [3, 8, 15];

if ($ids) {
    $placeholders = implode(',', array_fill(0, count($ids), '?'));   // "?,?,?"
    $stmt = $pdo->prepare("SELECT * FROM products WHERE id IN ($placeholders)");
    $stmt->execute($ids);
    $products = $stmt->fetchAll();
} else {
    $products = [];                     // IN () would be a syntax error
}

SQLに埋め込まれる文字列は疑問符とカンマだけなので、クエリは今でも安全です。

プレースホルダーのよくあるミス

• テーブル名とカラム名。 ORDER BY ?はカラムではなく一定の文字列で並べ替えてしまいます。識別子は許可リストから選びます：$sort = in_array($_GET['sort'] ?? '', ['name', 'price'], true) ? $_GET['sort'] : 'name';としてから、$sortをSQLに入れます。
• エミュレートされたプリペアでのLIMIT ?。 MySQLでデフォルトのATTR_EMULATE_PREPARESのままexecute(['10'])とすると、構文エラーになるLIMIT '10'が送られます。PDO::PARAM_INTでバインドするか、エミュレーションをオフにしましょう。
• 同じ名前付きプレースホルダーを2回使う。 WHERE first = :q OR last = :qが動くのはエミュレートされたプリペアのときだけです。本物のプリペアではSQLSTATE[HY093]: Invalid parameter numberになるので、:q1と:q2を使います。
• fetch()がfalseを返すことを忘れる。 falseに対する$user['name']は警告とnullになります。先に結果を確認しましょう。