PDOのEMULATE_PREPARESでLIMITのバインドが構文エラーになる理由

Bynori

PDOのエミュレートプリペアとは、プレースホルダをサーバに渡さず、PDO側が値を埋め込んだSQL文字列を組み立てて送る動作のことです。pdo_mysqlでは初期設定で有効で、LIMITに execute() の配列でint を渡すと構文エラーになる原因もここにあります。

「ローカルでは動いていたページング処理が、PDOのオプションをいじった途端にSQLエラーになった」という相談は、何度か見てきました。逆に、LIMIT ? を execute([10]) で素直に書いて、いきなり構文エラーで固まる人もいます。どちらも根っこは同じで、エミュレートの有無です。

LIMIT ? に execute([10]) を渡すとなぜ落ちるのか

execute() に配列で渡した値は、型を指定しない限りすべて PDO::PARAM_STR として扱われます。エミュレートが有効だと、PDOは値を文字列としてクォートしてSQLに埋め込むので、MySQLに届く文は LIMIT ’10’ になります。LIMIT には整数リテラルしか書けないので、構文エラーです。

<?php
$pdo = new PDO('mysql:host=localhost;dbname=app;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

// エミュレート有効(pdo_mysqlの初期状態)だと、LIMIT '10' になって構文エラー
$stmt = $pdo->prepare('SELECT id, name FROM users ORDER BY id LIMIT ?');
$stmt->execute([10]);

直し方は、bindValue() で型を明示することです。PARAM_INT を付ければ、クォートされない整数として埋め込まれます。

<?php
$stmt = $pdo->prepare('SELECT id, name FROM users ORDER BY id LIMIT :limit OFFSET :offset');
$stmt->bindValue(':limit', 10, PDO::PARAM_INT);
$stmt->bindValue(':offset', 20, PDO::PARAM_INT);
$stmt->execute();

ページング系は、値が (int) にキャストされたものか、バリデーション済みかどうかを入口で確認しておくと、後でSQL側を疑わずに済みます。

エミュレートをオフにすると何が変わるのか

ATTR_EMULATE_PREPARES を false にすると、MySQLサーバ側のプリペアドステートメントを使います。SQL文とパラメータが別々にサーバへ届くので、「PDOの埋め込み処理に依存しない」という安心感があります。一方で、挙動の違いがいくつか出ます。

<?php
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

// 同じ名前付きプレースホルダを2回使うと、ネイティブでは SQLSTATE[HY093] になる
$stmt = $pdo->prepare('SELECT * FROM users WHERE name = :q OR kana = :q');
$stmt->execute([':q' => '山田']);

エミュレート有効なら同名の再利用が通りますが、ネイティブでは通りません。切り替えるときは、:q1 と :q2 のように別名にして、同じ値を2回バインドする形に直しておくのが安全です。

もうひとつ、エミュレート有効だとMySQL向けに複数ステートメントを1回で送れてしまう挙動があります。PDO::MYSQL_ATTR_MULTI_STATEMENTS を false にすれば抑えられる(PHP 8.5以降は Pdo\Mysql::ATTR_MULTI_STATEMENTS が推奨)ので、ネイティブへ移さない場合はそちらを検討する価値があります。

PHP 8.1で取得値の型が変わった点に注意

PHP 8.1以降、pdo_mysqlはエミュレート有効でも、整数列と浮動小数点列を文字列ではなくネイティブの int と float で返すようになりました(それ以前はエミュレートだと文字列でした)。DECIMAL 列は引き続き文字列で返ります。

これを知らずに、8.0から8.1以降へ上げたとき「=== ‘1’ で比較していたら全部falseになった」というのは、起こりうるハマりどころだと思います。ID比較などは、型を揃えてから行うほうが無難です。

まとめ

PDOのMySQLでは、execute() の配列は全部文字列扱いで、エミュレート有効だとLIMITがクォートされて落ちる、というのが今回の要点でした。LIMIT と OFFSET は bindValue() に PARAM_INT を付けて渡すのが一番素直です。エミュレートの切り替えは、同名プレースホルダの再利用や取得値の型にも影響するので、環境ごとにテストを通してから変えたほうがよさそうだ、という気がしています。

よくある質問

Q. LIMIT に int を渡しているのに構文エラーになるのはなぜですか?
A. execute() の配列で渡すと型は文字列扱いになり、エミュレート有効だと LIMIT ’10’ のようにクォートされるためです。bindValue() に PDO::PARAM_INT を指定してください。

Q. ATTR_EMULATE_PREPARES は false にしたほうがいいですか?
A. 一概には言えません。ネイティブはSQLとパラメータが分離されますが、同名プレースホルダの再利用ができないなど挙動が変わります。テストを通して判断するのが現実的です。

Q. PHP 8.1で変わったのは何ですか?
A. pdo_mysqlのエミュレート有効時でも、整数列と浮動小数点列がint・floatで返るようになりました。DECIMAL は文字列のままです。

類似投稿

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です