ストアドプロシージャとは
ストアドプロシージャとは、データベースに対する複数の操作をまとめた手続きや関数のことです。一般的なプログラミング言語では、サブルーチンや関数に相当するものです。一般的なSQL問い合わせでは条件分岐や繰り返し処理はできませんが、ストアドプロシージャは、通常の手続き型言語のように分岐や繰り返しが可能です。
ストアドプロシージャは、データベースサーバー内で処理が閉じているためネットワーク処理に対するオーバーヘッドがないこと、またSQL文の構文解析や問い合わせの実行計画作成をストアドプロシージャ定義の際に済ませておけることから、実行効率がよいという特徴があります。
なぜストアドプロシージャがSQLインジェクション対策になるのか
なぜストアドプロシージャがSQLインジェクション対策になるのでしょうか。その理由は、先に述べたように、ストアドプロシージャの定義時に構文解析をあらかじめ行っている点にあります。通常のSQL呼び出しでは、SQL文を呼び出すたびにSQL文の構文解析を行いますが、その際、外部からのパラメータによりSQL文の意味が変わってしまう場合があります。この状況がSQLインジェクション脆弱性です。
しかし、ストアドプロシージャは、その定義時にあらかじめ構文解析を行っていることから、外部からのパラメータなどによりSQL文の意味が変わることはないはずです。そして、これは先に紹介した選択肢1のプリペアードステートメントがSQLインジェクション対策になる理由でもあります。プリペアードステートメントは日本語訳の用語では「準備された文」ですが、この「準備」はあらかじめSQL文を構文解析することを意味します。従って、プリペアードステートメントの使用もSQLインジェクション対策として有効とされています。
ストアドプロシージャを使いさえすればSQLインジェクション対策になるわけではない
それでは、ストアドプロシージャを使いさえすれば、SQLインジェクション脆弱性が入り込む心配はないのでしょうか。実はそうではありません。ストアドプロシージャを使っていても、SQLインジェクション脆弱性が入り込む余地はあります。それは以下の2つの場合です。
- ストアドプロシージャ中でSQL文を動的に組み立てる場合
- ストアドプロシージャを呼び出す際にSQL文を動的に組み立てる場合
以下、ストアドプロシージャを使っていてもSQLインジェクション脆弱性が混入する例を、サンプルプログラムを用いて説明します。
ストアドプロシージャの内部でSQL文を動的に組み立てるサンプル
まずは、ストアドプロシージャのサンプルを紹介しましょう。以下は、各国の郵便番号（Zipコード）から住所を求めるストアドプロシージャです。郵便番号と住所の対応表は、国別のテーブルに掲載されており、テーブル名はzip_<国名コード>となっています。日本の場合はzip_jp、米国の場合はzip_usという具合です。このテーブルには、郵便番号（zipcode）と住所（address）という2つの列があります。以下のMySQL向けストアドプロシージャzip2addressは、郵便番号zipcodeと国名コードcountryを受け取り、住所を問い合わせます。
001|CREATE PROCEDURE zip2address(IN zipcode TEXT, IN country TEXT)
002|BEGIN
003| SET @zipcode = zipcode;
004| SET @sql_str = CONCAT('SELECT address FROM zip_', country, ' WHERE zipcode=?');
005| PREPARE stmt1 FROM @sql_str;
006| EXECUTE stmt1 USING @zipcode;
007| DEALLOCATE PREPARE stmt1;
008|END
- 1行目：zip2addressプロシージャを定義します。入力引数としてzipcodeとcountryを宣言します。
- 2行目：BEGINで複合ステートメントを開始します（トランザクションの開始ではありません）。
- 3行目：ローカル変数@zipcodeに引数zipcodeの値を割り当てます。
- 4行目：テーブル名断片zip_に国名コードを連結するために、CONCAT関数を用いて文字列連結を行い、SQL文文字列を組み立て、ローカル変数@sql_strに代入します。SQL文中の「?」はプレースホルダと言って、パラメータの位置を確保するものです。
- 5行目：先のSQL文字列からSQL文を準備（prepare）します。
- 6行目：プレースホルダ（?）に@zipcodeの値を割り当ててSQL文を実行します
- 7行目：SQL文を開放します
- 8行目：複合ステートメントの終わりです
このストアドプロシージャzip2addressの呼び出し例をmysqlコマンドで行った例を示します。
mysql> CALL zip2address('1060045', 'jp');
+-----------------------------+
| address |
+-----------------------------+
| 東京都港区麻布十番 |
+-----------------------------+
JavaからJDBCを用いて同じストアドプロシージャ呼び出しをする例を以下に示します（主要部のみ）。
Class.forName("com.mysql.jdbc.Driver"); // MySQL用JDBCドライバ
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/… // 接続
CallableStatement statement = con.prepareCall("CALL zip2address(?, ?)");
statement.setString(1, "1420063"); // zipcodeの値を指定
statement.setString(2, "jp"); // country の値を指定
ResultSet rs = statement.executeQuery(); // 問い合わせ実行
SQL文の準備にprepareStatementではなくストアドプロシージャ向けのpreapareCallを使う以外は通常のSQL文呼び出しと同じです。結果は先と同じなので省略します。
