PDOでの数値列の扱いにはワナがいっぱい（2）

PHP Advent Calendar 2013 in Adventarの18日目です。昨日は、takc923さんの「PHPのissetの罠」でした。

「PDOでの数値列の扱いにはワナがいっぱい」を書いたところ、以下のように結構反響がありました。

PDOでの数値列の扱いにはワナがいっぱい — A Day in Serenity (Reloaded) — PHP, FuelPHP, Linux or something http://t.co/kDJAWzdMqr | 先日思いっきりハマったところ。
— K.K. (@kkmym) December 16, 2013

他の方も書いてるけど、バインドで型を明示すればおｋ / “PDOでの数値列の扱いにはワナがいっぱい — A Day in Serenity (Reloaded) — PHP, FuelPHP, Linux or something” http://t.co/WAunUvOhVb
— ニノミヤ　カズノリ (@wakuworks) December 16, 2013

明示的にbindすればいいはず。。 / “PDOでの数値列の扱いにはワナがいっぱい — A Day in Serenity (Reloaded) — PHP, FuelPHP, Linux or something” http://t.co/R51XFuhufX
— Hiraku (@Hiraku) December 16, 2013

まとめに解決策書いとく方が親切なんじゃね？とか思ったり。bindParamで型指定すればいいのでは？ / “PDOでの数値列の扱いにはワナがいっぱい — A Day in Serenity (Reloaded) — PHP, Fu…” http://t.co/yZshNuAN4p
— るっか和尚 (@lucca0show) December 16, 2013

ということで、今日はPDOで数値列を扱う場合、どうコーディングしたらいいのかについて書いてみようと思います。

「PDOでの数値列の扱いにはワナがいっぱい」をまだ読まれていない方は先にそちらをお読みください。

どうすればいいのか？

数値が文字列型として扱われてしまうことが問題なので、解決策は型を明示することです。この場合、PDOには、以下の2つのメソッドがあります。

bindParam()は変数（参照渡し）を、bindValue()は値を、プリペアドステートメントのパラメータにバインドしますが、いずれも第3引数がdata_typeになっており、ここでPDOの定数でデータ型を指定できます。指定しなかった場合は、PDO::PARAM_STRになりますので、やっぱり文字列型とされてしまいます。

また、bindParam()は変数を参照としてバインドするので、実際の値はPDOStatement::execute()がコールされたときに決まります。素直なPHPerの皆さんは無駄に参照を使い、混乱し、さらに「PHPの参照は糞参照」などど世界の中心で叫ぶ必要はまったくありませんので、通常はbindValue()を使いましょう。

具体的なコード

それでは具体的なコードで検証してみましょう（動作確認環境：PHP 5.3.27 & MySQL 5.5.16、PHP 5.5.6 & MySQL 5.6.14）。

テーブルなどは「PDOでの数値列の扱いにはワナがいっぱい」のものを流用します。

try {
  $db = new PDO(
    'mysql:host=127.0.0.1;dbname=sample;charset=utf8'
    , 'root'
    , ''
  );

  echo 'MySQL: エミュレーション On';
  $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $int = '3';
  $str = 'Ruby';

  $prepare = $db->prepare('SELECT * FROM example WHERE id = :id and language = :lang');
  $prepare->bindValue(':id', $int, PDO::PARAM_INT);
  $prepare->bindValue(':lang', $str, PDO::PARAM_STR);
  $prepare->execute();
  //var_dump($int, $str);

  $db = null;
} catch (Exception $e) {
  var_dump($e->getMessage());
}


try {
  $db = new PDO(
    'mysql:host=127.0.0.1;dbname=sample;charset=utf8'
    , 'root'
    , ''
  );

  echo 'MySQL: エミュレーション Off';
  $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $int = '3';
  $str = 'Ruby';

  $prepare = $db->prepare('SELECT * FROM example WHERE id = :id and language = :lang');
  $prepare->bindValue(':id', $int, PDO::PARAM_INT);
  $prepare->bindValue(':lang', $str, PDO::PARAM_STR);
  $prepare->execute();
  //var_dump($int, $str);

  $db = null;
} catch (Exception $e) {
  var_dump($e->getMessage());
}

念のため、PDOでのプリペアドステートメントのエミュレーションをOnとOffの場合の2パターン記述してます。エミュレーションOnとは「安全なSQLの呼び出し方」での「動的プレースホルダ」のこと（PDO_MySQLでのデフォルト）、エミュレーションOffとは「静的プレースホルダ」のことです。

上記のコードを実行すると、MySQLのクエリログは以下のようになりました。

153 Connect root@localhost on sample
153 Query   SELECT * FROM example WHERE id = '3' and language = 'Ruby'
154 Connect root@localhost on sample
154 Prepare SELECT * FROM example WHERE id = ? and language = ?
153 Quit    
154 Execute SELECT * FROM example WHERE id = '3' and language = 'Ruby'
154 Close stmt  
154 Quit

153が最初の接続（動的プレースホルダ）、154が次の接続（静的プレースホルダ）ですが、どちらも結果として、以下のSQLが実行されていることがわかります。

SELECT * FROM example WHERE id = '3' and language = 'Ruby'

あれ、おかしいですね。PDO::PARAM_INTを明示したにも関わらず、id = '3'とシングルクォートで囲まれており、文字列となってしまっています。これでは、PDOStatement::execute()の引数で値を渡す場合と変わりません。

何が問題だったのか？

勘の鋭い方はすでにお気付きかと思いますが、問題は数値列の値が、PHPで文字列型だったという点です。

  $int = '3';

これを、

  $int = 3; // 整数

にすれば、期待したように数値列が数値列として扱われる以下のSQL文が実行されます。

SELECT * FROM example WHERE id = 3 and language = 'Ruby'

$_GET、$_POSTなどの値は原則すべて文字列です（配列を渡すこともできますが）ので、キャストせずに使えば結局、数値を文字列として扱っていることになり、SQLでの暗黙の型変換が発生してしまいます。

PDO::PARAM_INTとは一体何だったのか？

PDOでの型を指定する定数について調べると、現在の実装は以下のようになっているようです。

PDO::PARAM_STRは値を文字列に変換
PDO::PARAM_INTはboolをintに変換（他は何もしない）
PDO::PARAM_BOOLはintをboolに変換

まとめ

ということで、PDOで数値列を扱う場合の私が考える正しい解決策をサンプルコードで示すと以下のようになります。

  $prepare = $db->prepare('SELECT * FROM example WHERE id = :id and language = :lang');
  $prepare->bindValue(':id', (int) $int, PDO::PARAM_INT);
  $prepare->bindValue(':lang', $str, PDO::PARAM_STR);
  $prepare->execute();

（2015-04-10 追記）ただし、文字列を数値にキャストするとオーバーフロー／アンダーフローの可能性があります。PHPで扱えない範囲の数値をデータベースで使っている場合は注意が必要です。

今日解説したPDOのワナ