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

PHP Advent Calendar 2013 in Adventarの15日目です。

みなさん、史上空前のSQLのエスケープブームの中、いかがお過ごしでしょうか？

なお、「我が社のプリペアドステートメントは大丈夫なのか？」という疑問をお持ちの方には、以下の記事をお薦めします。

漢(オトコ)のコンピュータ道: SQLインジェクション対策に正解はない

さて、あまりにエスケープが人気なので、プリペアドステートメントにもう少しがんばってもらいたい気がしました。そこで、今日は、以下の徳丸さんの大変に力作な記事に関連した、PDOでのプリペアドステートメントについての記事を書いてみたいと思います。

PHPとセキュリティの解説書12種類を読んでSQLエスケープの解説状況を調べてみた | 徳丸浩の日記

一応、今でこそ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での暗黙の型変換が発生します。この問題の詳細は、徳丸さんの以下の記事を参照してください。

SQLの暗黙の型変換はワナがいっぱい - 徳丸浩の日記(2009-09-24)

まとめ

多くのPHP解説書は、PDOのプリペアドステートメントに関して、数値列を文字列として扱ってしまうようなSQL文を実行しているサンプルコードが書いてあるのではないか？という疑問があります（ただし、『PHP逆引きレシピ第2版』にはないはずです。万一、ありましたら見落としのバグなのでお知らせください。訂正を出します）。

少なくとも『パーフェクトPHP』にはそのようなサンプルコードがありました。

今日解説したPDOのワナ