PDO を使ってデータベース操作を行うときの、プリペアードクエリに少し躓いたのでまとめておこうと思います。
参考サイト：
- http://nippondanji.blogspot.com/2010/01/sql.html
- http://nippondanji.blogspot.com/2013/12/sql.html
- https://blog.ohgaki.net/are-you-using-prepared-query-only
PDO （PHP Data Object） とは
PHP の中で DB に接続し、操作を行うためのインターフェースです。DB の種類ごとに異なる関数を使う必要がなく、使用する DB の種類を変更する際にも対応しやすいという利点があります。
プリペアードクエリ とは
PDO が提供する、DB へ送る SQL 文を２段階に分けて実行する手法です。１段階目では、 SQL 文を 解析・コンパイル・最適化 し、２段階目で実行します。また、１段階目でパラメータをプレースホルダに置き換え、２段階目でそのプレースホルダにパラメータを渡すことが可能です。
プリペアードクエリを使うメリットは次の通りです。
- SQL 文の解析～最適化は最初の１回だけ行えばよく、その SQL を何回も行うときに高速な動作が期待できる。
- SQL 文の構成に入力値を使うときSQL インジェクションの危険があるが、適切にプレースホルダを用いることで容易にインジェクション対策ができる。
今回は、２つ目のインジェクション対策に焦点を当てます。
実際に使ってみる
今回はテキストボックスへの入力を受けて、下のテーブルから値を取り出し名前を表示するようにします。「きゅうり」を受け取ったら「きゅうり」を表示するという単純なものです。
+----------+-------+
| name | price |
+----------+-------+
| キャベツ | 200 |
| きゅうり | 80 |
| かぼちゃ | 300 |
+----------+-------+まず、プリペアードクエリを使わない方法でやってみます。コードは以下の通りです。
$sql = 'SELECT name FROM food WHERE name="'.$_POST['food'].'"';
$foods = $db->query($sql)->fetchALL(PDO::FETCH_ASSOC);
foreach ($foods as $food) {
foreach ($food as $name) {
print $name;
}
}入力を行った結果を見てみます。
入力：「きゅうり」
入力：「きゅうり” or name=”キャベツ」
インジェクション対策を行ってないので、意図的に SQL 文を書き換えられています。
次に、プリペアードクエリを使いますが、入力値をプレースホルダで置き換えずにやってみます。コードは以下の通りです。
$sql = 'SELECT name FROM food WHERE name="'.$_POST['food'].'"';
$stmt = $db->prepare($sql);
$stmt->execute();
$foods = $stmt->fetchALL(PDO::FETCH_ASSOC);
foreach ($foods as $food) {
foreach ($food as $name) {
print $name;
}
}結果を見てみます。
入力：「きゅうり」
入力：「きゅうり” or name=”キャベツ」
プリペアードクエリを使ったのにインジェクションされています。
それでは、プレースホルダを使ったプリペアードクエリを試してみましょう。コードは以下です。
$sql = 'SELECT name FROM food WHERE name=?';
$stmt = $db->prepare($sql);
$stmt->execute(array($_POST['food']));
$foods = $stmt->fetchALL(PDO::FETCH_ASSOC);
foreach ($foods as $food) {
foreach ($food as $name) {
print $name;
}
}結果を見てみます。
入力：「きゅうり」
入力：「きゅうり” or name=”キャベツ」
インジェクション を防げました！
まとめ
今回は、
- プリペアードクエリを使わない通常クエリ
- プレースホルダを用いないプリペアードクエリ
- プレースホルダを用いたプリペアードクエリ
の３通りについて試し、プレースホルダを用いたプリペアードクエリのみがインジェクションを防ぐことができました。prepare() でクエリを解析した後にプレースホルダに値を渡すので、不適切な入力を防いでくれています。
なので、入力値を用いて DB を操作する際には、プレースホルダを用いてプリペアードクエリを使うのがほぼ必須になります。構文によってどうしても難しい場合は、かなり厳格な入力バリデーションが必要になります。
