以前別のブログに書いてた内容を持ってきたものです。
経緯・やりたいこと
Web画面から指定されたユーザIDと対応するデータを、DBから取り出して出力するのを想定するよ！
↓
画面入力をそのままSQL文に組み込んじゃうと、ユーザIDに「100 or 1」とか「100;DELETE FROM `user_mst` WHERE 1」とか指定されたらヤバイよね！（SQLインジェクション）
↓
SQLインジェクションを防ぐにはプリペアードステートメントを使用するのが良いらしい！
↓
プリペアードステートメント利用時に、悪意ある入力してみた例がパッと見つからない……実際やってみよう。
プリペアードステートメントって？
[http://php.net/manual/ja/pdo.prepared-statements.php:title]
曰く、「実行したい SQL をコンパイルした 一種のテンプレートのようなもの」らしい。ざっくりと「あらかじめ発行するSQLのテンプレートをDBに登録（宣言）しておき、実行時に与えられたパラメータをDBMS側で組み立てる仕組み（≒最初に宣言しておいた型を外れるクエリは実行できない）」みたいな感じで一旦捉えて進むことにしました。
事前準備
環境
Windows 8.1 + XAMPP Version 7.0.24
クエリログ出力設定
下記サイトを参考にphp.iniの[mysqld]ブロック末尾に追加
general_log = 1
general_log_file = "C:\xampp\mysql\data\query.log"
[http://www.puniokid.com/tips/mysql/88/:title]
DB
user_mstテーブル作成&テストデータ投入
テストコード
書籍『いきなりはじめるPHP~ワクワク・ドキドキの入門教室~』を参考に作成してみる。
なお、本書籍では「100 or 1」のような入力をしても全レコード取得されないことが紹介されていました。
<?php
// ユーザの入力を想定
$userInput = '';
// クエリ
$sql = 'SELECT * FROM user_mst WHERE user_id = ?';
$data[] = $userInput;
// DB接続
$dsn = 'mysql:dbname=dbtest;host=localhost';
$user = 'root';
$password = '';
$dbh = new PDO($dsn, $user, $password);
$dbh->query('SET NAMES utf8');
// クエリ実行
$stmt = $dbh->prepare($sql);
$stmt->execute($data);
// データ出力
while (1) {
$rec = $stmt->fetch(PDO::FETCH_ASSOC);
if ($rec == false) {
break;
}
printValue($rec);
}
$dbh = null;
function printValue($v) {
echo '<pre>';
var_dump($v);
echo '</pre>';
echo '====================================';
}
?>
以上のテストコードの、userInput変数に、画面から入力されたというテイで値を入れて試してみます。
実験①（$userInput='1'で初期化）
実行結果
array(3) {
["user_id"]=>
string(1) "1"
["name"]=>
string(10) "テスト1"
["age"]=>
string(2) "10"
}
====================================
クエリログ
105 Query SET NAMES utf8
105 Query SELECT * FROM user_mst WHERE user_id = '1'
105 Quit
実験②（$userInput='2 or 1'で初期化）
実行結果
array(3) {
["user_id"]=>
string(1) "2"
["name"]=>
string(10) "テスト2"
["age"]=>
string(2) "20"
}
====================================
クエリログ
106 Query SET NAMES utf8
106 Query SELECT * FROM user_mst WHERE user_id = '2 or 1'
106 Quit
実験③（$userInput='3;DELETE FROM `user_mst` WHERE 1'で初期化）
実行結果
array(3) {
["user_id"]=>
string(1) "3"
["name"]=>
string(10) "テスト3"
["age"]=>
string(2) "30"
}
====================================
クエリログ
138 Query SET NAMES utf8
138 Query SELECT * FROM user_mst WHERE user_id = '3;DELETE FROM `user_mst` WHERE 1'
138 Quit
まとめと疑問
SQLインジェクションを防げるのはわかりましたが、②と③の結果が予想外でした。「SELECT * FROM user_mst WHERE user_id = '2 or 1'」というクエリで、user_id=2のレコードだけ取得される、という動きです。追加で下記3つを試した結果、intの場合where句の指定に数値以外が出てきた時点でクエリを打ち切るような動きをするのだろうかと予想しました。
-PHPからではなく、直接DBにクエリ実行してみても取得レコードは同じ
-「SELECT * FROM user_mst WHERE user_id = '33 or 1'」と発行すると取得レコード0件
-int型(user_id)ではなくvarchar型(name)を対象に「SELECT * FROM user_mst WHERE name = 'テスト1 11'」を発行してみた所、取得レコード0件
ご存知の方はご教示いただけると嬉しいです。
ひとまず最初の目的は達成できたので今回はこのへんで。
追記1
Twitterで下記記事を紹介してもらいました。ありがとうございます。
[http://d.hatena.ne.jp/ockeghem/20070502/1178042280:title]
こちらから引用させていただくと、
SQLインジェクションの場合には、'1a'に相当するパターンのバリエーションがとりあえずは問題になるわけだが、多くの実装ではこの場合をエラーとしている。ただし、MySQLのWhere句の場合はエラーとならず、'1a'→1というような変換を実施しているように見受ける。ANSI/JISでこのような細かい規定があるかどうかは不明だが、実際の実装系での挙動は重要である。
という記述があり、今回のケースでも（規定がどうかはひとまず置いといて）'2 or 1'→'2’と変換されたという考えで合っていそうです。
追記2
postgreSQLでwhere句のint型のところに『'1 or 2'』と入れると型が違うエラーになったとご報告いただきました。ありがとうございます。
INT型カラムのwhere句にシングルクォートで囲った値を指定した場合、DBによってはエラーになり、MySQLでは暗黙の型変換が行われるようですね。今回はSQLインジェクションが起こり得る入力をする実験でしたが、意図せぬ挙動を回避するためフロント、サーバ、もしくは両方で気を使う必要があると、改めて考える良い機会になりました。