PDOでの数値列の扱いにはワナがいっぱい
PHP Advent Calendar 2013 in Adventarの15日目です。
みなさん、史上空前のSQLのエスケープブームの中、いかがお過ごしでしょうか？
なお、「我が社のプリペアドステートメントは大丈夫なのか？」という疑問をお持ちの方には、以下の記事をお薦めします。
さて、あまりにエスケープが人気なので、プリペアドステートメントにもう少しがんばってもらいたい気がしました。そこで、今日は、以下の徳丸さんの大変に力作な記事に関連した、PDOでのプリペアドステートメントについての記事を書いてみたいと思います。
一応、今でこそPDOは普通に使われていますが、細かい点までみていくと、仕様なのかバグなのか、あるいはこの仕様はちょっとどうかとか、機能が足りないとか、まだ、いろいろ出てくるんですよね。まあ、でもほとんどの場合、問題なく世界は動いているので、あまり神経質になる必要もないのでしょうが。
ということで、この記事もそのような些細な内容です。
徳丸さんが記事で書かれていないこと
記事中で言及されている
- 2 いきなりはじめるPHP~ワクワク・ドキドキの入門教室~(谷藤賢一著)
- 3 気づけばプロ並みPHP~ショッピングカート作りにチャレンジ!(谷藤賢一著)
- 4 基礎からのPHP(西沢 夢路著)
- 5 かんたんWebプログラミング! これから始める人のPHP学習帖(小川 淳一著)
- 6 パーフェクトPHP(小川 雄大、柄沢 聡太郎、橋口 誠著)
- 9 PHP逆引きレシピ 第2版(鈴木 憲治他著)
は、PDOを使っており、「数値列の扱い」は「プレースホルダのため考慮の必要がない」とされています。
これは、SQLインジェクション対策という観点からはその通りであり、何の問題もありません。
ただし、これらの書籍のPDOの解説が本当に正しいかどうかというと、実は、少々疑問があるのです。
ところが、私は、上記の書籍のうち、『パーフェクトPHP』と『PHP逆引きレシピ 第2版』しか持っていないため、それら以外の内容を確認することができません。ということで、ここでは『パーフェクトPHP』のみを取り上げます。
『パーフェクトPHP』のPDOのプリペアドステートメントの解説
P.449に解説があり、以下のコードがあります。
$sql = "SELECT * FROM book WHERE title = ?";
$stmt = $con->prepare($sql);
$stmt->execute(array('パーフェクトPHP'));
このコードには特に問題はありません。ただし、パラメータは数値列ではなく文字列です。
パラメータに数値を使っているところは意外に少なく、P.285に、以下のコードがありました。user_idがINT型です。
public function insert($user_id, $body)
{
$now = new DateTime();
$sql = "
INSERT INTO status(user_id, body, created_at)
VALUES(:user_id, :body, :created_at)
";
$stmt = $this->execute($sql, array(
':user_id' => $user_id,
':body' => $body,
':created_at' => $now->format('Y-m-d H:i:s'),
));
}
ここで、$this->execute()の実装は、P.229にあり、以下のようになっています。
public function execute($sql, $params = array())
{
$stmt = $this->con->prepare($sql);
$stmt->execute($params);
return $stmt;
}
ここで、$this->conはPDOインスタンスなので、値を引数で渡してPDOのexecute()メソッドを実行することになります。
何が問題なのか？
実は、PDOStatement::execute()の引数で値を渡す場合、すべての値は文字列型として扱われる、という仕様があります。これは、PHPマニュアルにも明記されています。PDOStatement::execute()のパラメータは、以下のように説明されています。
実行される SQL 文の中のバインドパラメータと同数の要素からなる、 値の配列。すべての値は PDO::PARAM_STR として扱われます。
http://php.net/manual/ja/pdostatement.execute.php
ここで、PDO::PARAM_STRは、
SQL CHAR, VARCHAR, または他の文字列データ型を表します。
http://php.net/manual/ja/pdo.constants.php
とされています。
PDOStatement::execute()の引数で値を渡す場合の検証
実際に検証してみましょう（PHP 5.3.27 & MySQL 5.5.16）。
まず、テーブルを作成しておきます。
CREATE DATABASE sample DEFAULT CHARACTER SET utf8;
CREATE TABLE IF NOT EXISTS `example` (
`id` INT(11) NOT NULL auto_increment,
`language` VARCHAR(10),
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `example` (`id`, `language`) VALUES
(1, 'PHP'), (2, 'Java'), (3, 'Ruby'), (4, 'Python'), (5, 'Perl');
次に、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->execute(array(':id' => $int, ':lang' => $str));
//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->execute(array(':id' => $int, ':lang' => $str));
//var_dump($int, $str);
$db = null;
} catch (Exception $e) {
var_dump($e->getMessage());
}
念のため、PDOでのプリペアドステートメントのエミュレーションをOnとOffの場合の2パターン記述してます。エミュレーションOnとは「安全なSQLの呼び出し方」での「動的プレースホルダ」のこと（PDO_MySQLでのデフォルト）、エミュレーションOffとは「静的プレースホルダ」のことです。
最後に、MySQLのmy.cnfでクエリログを取得するように設定しておきます。
general_log = 1
general_log_file = /opt/lampp/var/mysql/general.log
準備ができましたので、上記のコードを実行して、MySQLのクエリログを見てみましょう。
4 Connect root@localhost on sample
4 Query SELECT * FROM example WHERE id = '3' and language = 'Ruby'
5 Connect root@localhost on sample
5 Prepare SELECT * FROM example WHERE id = ? and language = ?
4 Quit
5 Execute SELECT * FROM example WHERE id = '3' and language = 'Ruby'
5 Close stmt
5 Quit
4が最初の接続（動的プレースホルダ）、5が次の接続（静的プレースホルダ）ですが、どちらも結果として、以下のSQLが実行されていることがわかります。
SELECT * FROM example WHERE id = '3' and language = 'Ruby'
ここで注目すべきはid = '3'の部分です。3がシングルクォートで囲まれています。つまり、数値が文字列として扱われています。
何が本当の問題なのか？
やっと、たどり着きました。
つまり、数値を文字列として扱っているため、SQLでの暗黙の型変換が発生します。この問題の詳細は、徳丸さんの以下の記事を参照してください。
まとめ
多くのPHP解説書は、PDOのプリペアドステートメントに関して、数値列を文字列として扱ってしまうようなSQL文を実行しているサンプルコードが書いてあるのではないか？という疑問があります（ただし、『PHP逆引きレシピ 第2版』にはないはずです。万一、ありましたら見落としのバグなのでお知らせください。訂正を出します）。
少なくとも『パーフェクトPHP』にはそのようなサンプルコードがありました。
今日解説したPDOのワナ
- PDOStatement::execute()の引数で値を渡す場合、すべての値は文字列型として扱われる
（2013-12-18 追記） どう解決したらいいかを、「PDOでの数値列の扱いにはワナがいっぱい（2）」に書きました。
明日は、ngyukiさんの「PHP の Windows 版でのみ発生する is_dir の奇妙な現象」です。
