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

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

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

ということで、今日は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のワナ

  • バインド時にPDO::PARAM_INTを指定しても、文字列型の値を渡せばSQLで文字列として扱われてしまう

整数型を指定しているにも関わらず、値がシングルクォートで囲まれるというのはバグじゃないんでしょうかね?PDOの偉い人がいましたら、ご意見を伺いたいです。

参考

Date: 2013/12/18

Tags: php, pdo, database