データベースのバインド変数のメリットを語る（②メリット：SQLインジェクションの防止）

2018/7/14 全面改訂

====================================================================

0. はじめに

前回の投稿では、データベースのバインド変数とは何か？を解説しましたが、今回は使うことによるメリットその1をお伝えしたいと思います。一言で言うと、SQLインジェクションを防止できる点です（※1）。

1. 前提ケース

前回の投稿で紹介したテーブルを再度出します。

id	passwrod	username	email
22222	password2	ルフィー	[e-mail]
55555	password5	桜木花道	[e-mail]
11111	password1	剣桃太郎	[e-mail]
33333	password3	きんにくすぐる	[e-mail]

そのテーブルからユーザIDとパスワードを指定して情報を取得するわけですが、IDは「11111」を知っているが、PASSWORDのYYYのところには「' OR 'A' = 'A」を入れてSQLを発行した場合を考えます。SQLはこうなります。

SELECT * FROM USERTABLE WHERE ID=11111 AND PASSWORD='' OR 'A' = 'A';

バインド変数を使わなかった場合と使った場合で結果がどうなるかを見ていきます。

2. バインド変数を使わなかった場合

バインド変数を使わなかった場合、発行された上記のSQLは以下のように解釈されます。

次の1．または2．のいずれかの条件に合致する場合、USERTABLEから全ての情報を引き出す。
＜条件1. ＞
USERIDが11111かつPASSWORDが空白（WHERE句の「ID=11111 AND PASSWORD=''」に該当）。

＜条件2. ＞
値「A」と値「A」が等しい（WHERE句の「'A' = 'A'」に該当）。

IDが11111でPASSWORDが空白のユーザ情報はこのテーブルにはありませんので、まず条件1．に当てはままりません。次に、2. の条件は、だれが見ても当てはまります。さて、前回の投稿で紹介したJavaのコードを以下のように修正・追加して、SQLを発行します。

System.out.println("***** main method start *****");
・・・(詳細省略)・・・
// Statementを用意する。
statement = connection.createStatement();
// SQLを宣言する。可変値idとpasswordを入れてSQLを構成する。
String sql = "select * from app.usertable where id = 11111 and password = '' OR 'A' = 'A'";
// 結果を取得する。
resultSet = statement.executeQuery(sql);
// 結果を表示する。
while(resultSet.next()) {
                System.out.println("ID：　" + resultSet.getInt(1));
                System.out.println("氏名：　" + resultSet.getString(3));
                System.out.println("メールアドレス：　" + resultSet.getString(4));
}

・・・(詳細省略)・・・
System.out.println("***** main method end *****");

結果は以下のようになります。DBMSはDerby(JavaDB)とMySQLで試しましたが、同じ結果でした。

***** main method start *****
ID：　22222
氏名：　ルフィー
メールアドレス：　[e-mail]
ID：　55555
氏名：　桜木花道
メールアドレス：　[e-mail]
ID：　11111
氏名：　剣桃太郎
メールアドレス：　[e-mail]
ID：　33333
氏名：　きんにくすぐる
メールアドレス：　[e-mail]
***** main method end *****

あらら！？テーブルの情報全て引き出せてしまいました。本来であればIDとPASSWORDに正しい値を入力して該当する情報をUSERTABLEから取得しなければならないのが、PASSWORDに「' OR 'A' = 'A」を入力することで、不正にUSERTABLEのすべての情報を引き出すことができてしまいます。これがSQLインジェクションの一例です。

3. バインド変数を使った場合

では、このSQLに関して、バインド変数を使うとどうなるのでしょうか？前回の投稿で紹介した、Javaのコードを以下のように少し変えます。

System.out.println("***** main method start *****");
・・・(詳細省略)・・・
// SQLを宣言する。カラムidとpasswordに対して「?」を入れることで、これらはバインド変数であることを宣言する。
String sql = "select * from app.usertable where id = ? and password = ?";
// バインド変数を格納したSQLを発行するため、PreparedStatementを用意する。
preparedStatement = connection.prepareStatement(sql);
// idに対して値を代入する。この「1」というのは、1番目のバインド変数idを指す。
preparedStatement.setInt(1, 11111);
// passwordに対して値を代入する。この「2」というのは、2番目のバインド変数passwordを指す。
preparedStatement.setString(2, "'' OR 'A' = 'A'");
// 結果を取得する。
resultSet = preparedStatement.executeQuery();
// 結果を表示する。
while(resultSet.next()) {
                System.out.println("ID：　" + resultSet.getInt(1));
                System.out.println("氏名：　" + resultSet.getString(3));
                System.out.println("メールアドレス：　" + resultSet.getString(4));
}

・・・(詳細省略)・・・
System.out.println("***** main method end *****");

上記のコードを実行することでSQLが発行されますが、結果は以下のようになります。こちらもDBMSはDerby(JavaDB)とMySQLで試しましたが、同じ結果でした。

***** main method start *****
***** main method end *****

あら？何も表示されませんではありませんか？一体何が起きたのでしょうか？

バインド変数のついたSQLが発行されるととどうなるのかを示したのが以下の流れです。

①バインド変数の値と、それを除くSQLが別々にDBMSへ発行される。
②バインド変数の値は、SQLにマージされる前に解釈され、「'」はエスケープ処理される。つまり「'」が文字の「'」として解釈されるために単一引用符が2つついた形「''」として変換される。
③②で処理されたバインド変数の値とSQLがマージされて実行される。

③で実行されるSQLは
SELECT * FROM USERTABLE WHERE ID=11111 AND PASSWORD=''' OR ''A'' = ''A';
のとおりになります。どうでしょう。インジェクション攻撃されたときのSQLと違っているのがわかるかと思います。そうです。PASSWORDが「'' OR ''A'' = ''A」として解釈されています。このようなパスワードを持つユーザはUSERTABLEに存在しないため、すべての情報を引き出すことはできなくなります。

上記は一例です。「'」だけでなく、「>」や「<」などをエスケープする方法もありますので、データベースのマニュアル等を参照くださればと思います(※2)。

4. おわりに

さて、次回このシリーズの最後では、バインド変数のメリットその2を語りたいと思います。

脚注

(※1)
SQLインジェクションに関する解説は以下URLをご参照ください。
https://ja.wikipedia.org/wiki/SQL%E3%82%A4%E3%83%B3%E3%82%B8%E3%82%A7%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3

(※2)
Oracle Databaseに関してはこちらをご参照ください。
http://www.oracle.com/technetwork/jp/database/articles/pickup/index-1622026-ja.html

(※3)
今回の記事を作成するにあたり以下のURLも参照しました。

IPA 情報処理推進機構「安全なSQLの呼び出し方」
http://www.ipa.go.jp/files/000017320.pdf

Hatena Diary [セキュリティ]Oracleでのエスケープ
http://d.hatena.ne.jp/teracc/20071230