脆弱性の確認
ここまでは正常系のお話です。郵便番号と国別コードを外部から指定できる場合、このプログラムにはSQLインジェクション脆弱性があります。攻撃例を紹介しましょう。郵便番号に1を指定したうえで、国別コードに以下を指定します。
jp WHERE 1 <> ? UNION SELECT CONCAT(cardname, ':', cardnumber, ':', expire, ':', secret) FROM ecsite.cardinfo --
ストアドプロシージャで生成されるSQL文は下記のとおりです。プレースホルダ「?」には値1を埋めています。
SELECT address FROM zip_jp WHERE 1 <> 1 UNION SELECT CONCAT(customer_id, ":", cardname, ":", cardnumber, ":", expire, ":", secret) FROM ecsite.cardinfo -- WHERE zipcode=?
上記に出てくるUNIONは2つの問い合わせ結果を1つにまとめる演算です。1番目のSELECT文はWHERE句が 1 <> 1 すなわちFALSEなので1件もヒットしません。2番目のSELECT文は、カード情報を保持するecsite.cardinfoというテーブルから、カード登録名（cardname）、カード番号（cardnumber）、有効期限（expire）、セキュリティコード（secret）を連結したものを返します。この攻撃の結果として、以下が表示されます。
TARO YAMADA:1234567890123456:062017:543
SQLインジェクション攻撃により、クレジットカード情報が漏洩しました。
ストアドプロシージャ内であってもSQL文を動的に組み立ててはいけない
先のサンプルでストアドプロシージャを用いているのにSQLインジェクション脆弱性が混入してしまった理由は、SQL文を動的に組み立てているからです。そして、この例は、組み立てられたSQL文の文字列は、PREPARE文により準備されているので、「プリペアードステートメントを使っているがSQLインジェクション脆弱性がある」という例にもなっています。
SQL文を動的に組み立てないためには、以下のような方法があります。
- 国別のテーブルを1つのテーブルにまとめ、国を表す列を追加する
- 国の数が少なければ、国毎のSQL文を用意し、切り替えるようにする
これらの方法が取れない場合は、国名コードが英字2文字であることを確認して、違反している場合はエラーとして処理を停止するか無効な値（NULLなど）を返す方法（入力値検証）が考えられます。
下記は、郵便番号を1つのテーブルzipに統合するという方針によるストアドプロシージャの修正例です。SQL呼び出し1つだけなので、もはやストアドプロシージャにする必要もないくらいですね。国名コードの型も従来のTEXT型から、VARCHARとして文字数も可能な限り制限しています。
CREATE PROCEDURE zip2address(IN zipcode VARCHAR(10), IN country VARCHAR(2)) BEGIN SELECT address FROM zip WHERE zip.country=country AND zip.zipcode=zipcode; END
この場合、先のJavaによるサンプルコードの実行結果は以下のようにエラーになります。
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'country' at row 2
「countryとしてデータが長すぎる」というエラーメッセージが表示されています。これは、countryの型をVARCHAR(2)に制限したことによる効果ですが、仮にTEXT型のままだった場合もSQLインジェクション攻撃は成功せず、空の結果が返るだけです。
