【PHP】PDOの静的プレースホルダと動的プレースホルダの違いを確認する

追記

徳丸先生からのコメントにありますが、静的プレースホルダはエスケープ処理しません。
静的プレースホルダはデータベース側でバインドします。
バインドするときの値は、リテラルからはみ出さないので、エスケープ処理が不要ということです。
リテラルからはみ出さないということは、SQL 文として解釈されません。
よって、SQL インジェクションは発生しません。

静的プレースホルダと動的プレースホルダの違いは、本記事ではなく、下記の記事をご覧ください。

以下は、エスケープ処理すると勘違いして記述しています。

はじめに

PDOを勉強している初心者です。
エミュレートの設定がONだと動的プレースホルダになり、OFFだと静的プレースホルダになると学びました。
実際にMySQLのログからどのように違うのか確認することで理解を深めたいと思います。

動的プレースホルダと静的プレースホルダについては、独立行政法人情報処理推進機構（IPA）のウェブサイトに掲載されている「安全なSQLの呼び出し方」を見て理解しております。
それぞれの定義も上記の解説の通りとします。

今回は下記の2点を確認しました。

• 静的プレースホルダと動的プレースホルダは、MySQL側でどのように実行されるのか
• プレースホルダは本当にエスケープ処理されるのか

静的プレースホルダと動的プレースホルダの違いは他にもあります。

詳しく知りたい方は、

• Qiita - PHPでデータベースに接続するときのまとめ by @mpyw さん

をご覧ください。

環境

MacにXAMPPを入れた環境で確認します。

OS	XAMPP	PHP	MySQL	phpMyAdmin	Apache
Mac OS X Yosemite 10.10.1	XAMPP for Mac OS X 1.8.3-5	5.5.15	5.6.20	4.2.7.1	2.4.10

MySQLのログを取得する

まずは、MySQLのログを取得する必要があります。
XAMPPのインストールフォルダにMySQLのログを保存することもできるそうですが、うまく設定できませんでした(･ω･`；)

phpMyAdmin上でログを確認します。
下記を参考に設定しました。

• 【MySQL】一般クエリログをデータベースに保存する

下記のテーブルがデータベースにあります。

テーブル名：fruit

name	price
apple	100
banana	200
orange	300

静的プレースホルダと動的プレースホルダって本当に違うの？

静的プレースホルダと動的プレースホルダがどのように違うのかMySQLログから確認してみましょう。

静的プレースホルダの場合

エミュレートをOFFにし、静的プレースホルダの場合はどのようになるのか試してみたいと思います。
データベースの接続時にオプションでエミュレートをOFFにし、静的プレースホルダを使うようにしております。
尚、PHP5.2以上ではデフォルトでエミュレートがONになっております。
静的プレースホルダを使う場合は、オプションでエミュレートをOFFにしてください。
プレースホルダを使ってSELECT文を実行してみます。

実行したコード

try {

    // データベースに接続
    $pdo = new PDO(
        'mysql:host=localhost;dbname=test;charset=utf8',
        'ユーザー名',
        'パスワード',
        array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
        )
    );

    $stmt = $pdo->prepare('SELECT * FROM fruit WHERE price = ?');

    $price = 100;

    $stmt->bindValue(1,$price,PDO::PARAM_INT);
    $stmt->execute();

} catch (PDOException $e) {

    echo 'データベース接続エラー';

}

MySQLのログを確認して見ましょう。

Prepareの後にExecuteを実行しているのがわかりました。
問題なくプリペアドステートメントを実行しています。
次は動的プレースホルダでどのようになるか見ていきましょう。

動的プレースホルダの場合

今度はエミュレートをONにし、動的プレースホルダでどのようになるのか試してみたいと思います。
データベースの接続時にオプションでエミュレートをONにし、動的プレースホルダを使うようにしております。
PHP5.2以上ではデフォルトでエミュレートONになっているため、省略することも可能ですが、今回は記述します。
オプションの設定以外は先ほどと同じです。

実行したコード

try {

    // データベースに接続
    $pdo = new PDO(
        'mysql:host=localhost;dbname=test;charset=utf8',
        'ユーザー名',
        'パスワード',
        array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => true,
        )
    );

    $stmt = $pdo->prepare('SELECT * FROM fruit WHERE price = ?');

    $price = 100;

    $stmt->bindValue(1,$price,PDO::PARAM_INT);
    $stmt->execute();

} catch (PDOException $e) {

    echo 'データベース接続エラー';

}

MySQLのログを確認して見ましょう。

先ほどとは違い、Queryだけ実行されています。
既にバインドされたSQL文がデータベース側に渡されて実行されていますね。
予想通り動的プレースホルダになっています。

プレースホルダって本当にエスケープ処理してるの？

静的プレースホルダも動的プレースホルダも関係なくプレースホルダならエスケープ処理していると学びました。
本当にプレースホルダを使うとエスケープ処理されるのか見ていきましょう。
静的も動的も関係ないらしいですが、念のため両方確認しましょう。

静的プレースホルダの場合

わざとバインドする値にシングルクォートを記述します。
本来ならシングルクォートはエスケープ処理する必要があります。
静的プレースホルダが本当にエスケープ処理されるか見ていきましょう。

実行したコード

try {

    // データベースに接続
    $pdo = new PDO(
        'mysql:host=localhost;dbname=test;charset=utf8',
        'ユーザー名',
        'パスワード',
        array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
        )
    );

    $stmt = $pdo->prepare('SELECT * FROM fruit WHERE price = ?');

    $price = "100' OR 'A' = 'A";

    $stmt->bindValue(1,$price,PDO::PARAM_INT);
    $stmt->execute();

    $result = $stmt->fetchAll(PDO::FETCH_BOTH);
    print_r($result);

} catch (PDOException $e) {

    echo 'データベース接続エラー';

}

MySQLのログを確認して見ましょう。

シングルクォートがエスケープ処理されているのがわかります。
予想通りの結果ですが、プレースホルダ便利ですね。
次は動的プレースホルダの場合を見ていきましょう。

動的プレースホルダの場合

先ほどと同じくバインドする値にわざとシングルクォートを入れてみます。

実行したコード

try {

    // データベースに接続
    $pdo = new PDO(
        'mysql:host=localhost;dbname=test;charset=utf8',
        'ユーザー名',
        'パスワード',
        array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => true,
        )
    );

    $stmt = $pdo->prepare('SELECT * FROM fruit WHERE price = ?');

    $price = "100' OR 'A' = 'A";

    $stmt->bindValue(1,$price,PDO::PARAM_INT);
    $stmt->execute();

    $result = $stmt->fetchAll(PDO::FETCH_BOTH);
    print_r($result);

} catch (PDOException $e) {

    echo 'データベース接続エラー';

}

MySQLのログを確認して見ましょう。

動的プレースホルダでもエスケープ処理されているのがわかりました。

ただ、プレースホルダを使わなくても勝手にエスケープ処理されている可能性があります。
プレースホルダを使わない場合も確認しましょう。

プレースホルダを使わない場合

プレースホルダを使わずに変数をそのままSQL文に入れて実行してみます。
変数に代入する値に'A' = 'A'とありますが、これは全てを表すため、エスケープ処理がされていないとデータベースの情報が全て表示されてしまいます。

実行したコード

try {

    // データベースに接続
    $pdo = new PDO(
        'mysql:host=localhost;dbname=test;charset=utf8',
        'ユーザー名',
        'パスワード',
        array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        )
    );

    $price = "100' OR 'A' = 'A";

    $stmt = $pdo->query("SELECT name FROM fruit WHERE price = '$price'");

    $result = $stmt->fetchAll(PDO::FETCH_BOTH);
    print_r($result);

} catch (PDOException $e) {

    echo 'データベース接続エラー';

}

今回は、試すためにSQL文に変数をそのまま入れておりますが、このような記述はしてはいけません。
SQLインジェクションという攻撃をうけ、データベースの情報が漏洩するおそれがあります。
必ずプレースホルダを使ってエスケープ処理するか、自前でエスケープ処理をする必要があります。

MySQLのログを確認して見ましょう。

予想通りですが、勝手にエスケープ処理はされません。
エスケープ処理がされないため、データベースの全ての情報が表示されてしまいました。

最後に

予想通りの結果でしたが、静的プレースホルダと動的プレースホルダは別物です。
PDOを使うときは、どちらが有効になっているのか確認し、ユーザーの入力が伴うSQL文に関してはプレースホルダを使ってエスケープ処理するか、自前でエスケープ処理する必要があるということですね。
PDOについて理解できていない方は下記をご覧ください。

• Qiita -【PHP超入門】クラス～例外処理～PDOの基礎 by @7968

note

note でも記事を公開してるので、興味がある方はご覧ください。

・【初学者向けコードリーディング】 PHP の TODO アプリのコードを一緒に読み解こう