[PHP] プリペアードステートメントを深堀り～SQLインジェクション対策

○プリペアードステートメント
DBへのクエリ中のパラメータを動的に変更するときの仕組み。
っていうのが、今までの自分の解釈。

正確には↓
http://php.net/manual/ja/pdo.prepared-statements.php

当ブログでも以前ちょっとさわった。
PDO::exec,query,executeの違い

で、今どきの参考書なんかは、
PDOでプリペアードステートメントを使って
DB連携するやり方を説明してるのがほとんどなのかな？

そんなわけで、PDOを使い始めたころから
あたりまえのようにやってきた書き方ですが、
それが、なんと！

SQLインジェクションの対策にもなっているらしい。

ということで、
プリペアードステートメントを使った場合、
具体的にどんな処理がされているのか見て行きます。

○準備
SQLのクエリログを有効にする。

https://mariadb.com/kb/en/library/general-query-log/

“my.ini”の”[mysqld]”のくだりに下記を挿入

general-log
general-log-file=queries.log
log-output=file

で、MariaDBを再起動すると、
“queries.log”にクエリのログを吐き出すようになる。
(場所は、”datadir”以下)

テーブルをテキトーに用意して、

MariaDB [test]> select * from test1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | name1 |
|    2 | name2 |
|    3 | name3 |
+------+-------+

こんな感じでプログラムを作ってみました。

-------------- form.php --------------------------------
<form method="post" action="sql_injection.php">
       <input type="text" name="value">
       <input type="submit" value="送信">
</form>


-------------- sql_injection.php --------------------------------
<?php

$value = $_POST['value'];


$db = new TestDB();
$sql = "INSERT into test1 values (4, '{$value}')";
$db->pdo->exec($sql);

print("送信しました。");


class TestDB {
       public $pdo;
       const DSN = 'mysql:dbname=test; host=localhost; charset=utf8';
       const USER = [ユーザー名];
       const PASS = [パスワード];
       
       public function __construct(){
              try {
                     $this->pdo = new PDO(self::DSN, self::USER, self::PASS);
                     $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
              } catch(PDOException $Exception) {
                     die('エラー:' . $Exception->getMessage());
              }
              
       }
              
}

それで、”form.php”を開いた時のテキストボックスに、
「name4′); INSERT into test1 values (5, ‘name5’);–」
と入力して、

送信をクリック。

「送信しました。」が表示されるが、
テーブルを見てみると、

MariaDB [test]> select * from test1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | name1 |
|    2 | name2 |
|    3 | name3 |
|    4 | name4 |
|    5 | name5 |
+------+-------+

2行追加されてますね。

ちなみにクエリログには、

181027 21:15:59        3 Connect    [ユーザー名]@localhost as anonymous on test
            3 Query    INSERT into test1 values (4, 'name4'); INSERT into test1 values (5, 'name5');--')
            3 Quit    

プログラムと入力値のほぼそのままで、
コロンで区切られて、2つのクエリが実行されているんですね。
(“–“以降はコメントアウトなので無視され、構文エラーにはならない。)

これが、SQLインジェクションか。

この例は比較的やさしいですが、
もっとたくさんのクエリを突っ込んだり、
全消しとかエグいこともできるわけですからね。

では、PHPプログラムのクエリの部分を、
プリペアードステートメントを使った表現に
書き換えてみる。

$sql = "INSERT into test1 values (6, :value)";
$stt = $db->pdo->prepare($sql);
$stt->bindvalue('value', $value);
$stt->execute();

で、同じ文章をフォームで送ってみると、

MariaDB [test]> select * from test1;
+------+------------+
| id   | name       |
+------+------------+
|    1 | name1      |
|    2 | name2      |
|    3 | name3      |
|    4 | name4      |
|    5 | name5      |
|    6 | name4'); I |
+------+------------+

ちょっと文字数制限のせいで、
途中で切れちゃってますが、
増えてるレコード自体は1行だけ。

クエリログでは、

4 Query    INSERT into test1 values (6, 'name4\'); INSERT into test1 values (5,　\'name5\');--')

シングルクォーテーション(「’」)が、
“\”でエスケープされてます。

というわけで、これはおそらく、
途中の「’」は全てただの文字扱いになり、

プログラム中の最後の「’」で締めくくるまで、
フォームに入力した全体が、
挿入する文字列値として認識された。
ってことでしょう。

ちなみにここで、
エスケープされる文字って何なんでしょうね？

いろいろ試してみたい。

↑の例を見る限りでは、
「’」で囲まれてる部分は、「;」も「)」も、
ただの文字列として扱われてるっぽいです。

じゃあ、これらの文字が、
「’」で囲まれていない場合はどうなんでしょうか？

プログラムのクエリ部分をを下記のように書き換える。

$sql = "SELECT * from test1 where id = :value";
$stt = $db->pdo->prepare($sql);
$stt->bindvalue('value', $value);
$stt->execute();

分かりにくいので、
テーブルも元に戻しておきます。

MariaDB [test]> select * from test1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | name1 |
|    2 | name2 |
|    3 | name3 |
+------+-------+

で、フォームには、
「1; INSERT into test1 values (5, ‘name5’);–」
と入力して送信。

結果

MariaDB [test]> select * from test1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | name1 |
|    2 | name2 |
|    3 | name3 |
+------+-------+

テーブルは変わりありません。

ログは、

4 Query    SELECT * from test1 where id = '1; INSERT into test1 values (5, \'name5\');--'

フォームに入力した分、
まるっと「’」で囲まれてますねｗ
そういうことか。
確かに、↑↑の例でもそうなってますね。

まとめると、
prepare->bindvalueを使うと、
渡した値全体を「’」で囲って文字列にしてしまう。
値の中に「’」があった場合はエスケープする。と。

それもまた、”bindvalue”メソッドの
“data_type”引数を渡していないので、
デフォルトで文字列扱いをしているため。

http://php.net/manual/ja/pdostatement.bindvalue.php

じゃあ、”data_type”でint型を指定した時は？

$stt->bindvalue('value', $value, PDO::PARAM_INT);

で、↑と同じ値をフォームに入れて送信。

クエリログ

5 Query    SELECT * from test1 where id = 1

最初の”1″だけ取り出されたみたいですね。
ちなみにフォームの入力値、
最初の文字を”2″に変えたら”where id = 2″に、
“a”とか”b”に変えたら”where id = 0″になりました。

int型にすると、最初が数字の場合に、
その数字だけを読み取るみたいです。

では、この辺で。