間違いだらけのSQL識別子エスケープこれから3回連載の予定で、SQL識別子のエスケープの問題について記事を書きます。SQL識別子のエスケープについてはあまり解説記事などがなく、エンジニア間で十分な合意がないような気がしますので、これらの記事が議論のきっかけになれば幸いです。3回の予定は以下のとおりです。間違いだらけのSQL識別子エスケープ(本稿)SQL識別子エスケープのバグの事例SQL識別子は結局どうすればよいかということで、まずはSQL識別子のエスケープの失敗例について説明します。この失敗例はあくまで説明のために作ったもので、実際のものではありません。想定が「ありえない」と思われるかもしれませんが、意図的なものですのでご容赦いただければと思います。「間違いだらけの」というタイトルは、今回の題材が間違いだらけという意味であり、巷のSQL呼び出しがそうであるという意味ではありません。本稿に登場する人物と団体は全て架空のものです。SQL識別子のクォートとエスケープSQLの識別子(テーブル名や列名など)には、引用符で囲う形式と囲わない形式があります。以下のいずれかに該当する場合は、識別子を引用符(標準ではダブルクォート)で囲む必要があります。引用符で囲むことを「クォートする」と呼びます。この引用符を重ねる処理を「識別子のエスケープ処理」と呼ぶことにします。MySQLの場合は、デフォルトでは、ダブルクォートを文字列リテラルを囲む目的で使うため、識別子はバッククォートで囲みます。このため、先の例はそれぞれ、`table`、`j&j`、`x"y`、`0x`となります(ただし、MySQLは数字で始まる識別子を引用符で囲まなくてもエラーにならないようです)。識別子にバッククォートを含む場合は、`x``y`のようにバッククォートを重ねます。後述するように、ANSI_QUOTESモードを設定している場合は、ダブルクォートは識別子のクォートに使えるようになります。脆弱なスクリプトの説明ここで脆弱なスクリプトについて説明します。とある地域の観光協会では、春・夏・秋・冬の季節毎にホームページの表示を切り替えており、コンテンツは、それぞれtb1、tb2、tb3、tb4という季節毎のテーブルに格納されています。ようにテーブル名を指定して、そのテーブルの内容を表示しています。このスクリプトには脆弱性があります)。この場合、呼び出されるSQL文は下記となります。tb2単純なSQLインジェクションこのスクリプトを運用してしばらくすると、コンテンツの内容が消去されるという不正アクセスが発生しました。アクセスログを調べると、以下のリクエストが原因のようです。/search.php?season=tb2;delete+from+tb2この際に生成されるSQL文は以下となります。FROM文が追加されています。PDO+MySQLの場合、複文の実行が可能なので、テーブルtb2の内容は全て削除されてしまいました。観光協会は地元のセキュリティ会社に相談したところ、テーブル名を`で囲むと良いとアドバイスを受けました。該当の箇所は下記となります。`$table`";この場合、左記の攻撃リクエストの結果生成されるSQL文は下記となります。tb2`がテーブル名として解釈されますが、この名前のテーブルは存在しないのでエラーにはなりますが、SQL文の構造は変化せず、SQLインジェクション攻撃は成立しなくなりました。バッククォート「`」によるSQLインジェクション観光協会はこの状態でしばらく運用をしていましたが、再度コンテンツが削除されるという不正アクセスがありました。今度は以下のリクエストが問題のようでした。/search.php?season=tb2`;delete+from+tb2%23tb2の後に「`」が追加されています。生成されるSQL文は下記の通りです。tb2#`今度は、外部から注入された「`」で識別子の引用符が閉じられて、その後の文字列が追加のSQL文と解釈されていました(#以降はコメント)。観光協会は再度地元のセキュリティ会社に相談に行くと、「識別子のエスケープをしていないのが原因だ。識別子のエスケープは世界の常識です。」と言われてしまいました。先に教えてくれればいいのにとは思いましたが、識別子のエスケープの方法を教えてもらって、スクリプトに組み込みました。`$etable`";この結果、生成されるSQL文は下記となります。tb2#`がテーブル名と解釈されるようになり、SQLインジェクション攻撃は防げるようになりました。文字エンコーディングの取り扱い不備によるSQLインジェクション観光協会のホームページはしばらく平穏な日々が続きましたが、またしてもデータが全て削除される事件が起こってしまいました。今度の原因は次のリクエストのようでした。search.php?season=%8d%60;delete+from+tb2%23生成されたSQL文は下記の通りです。Shift_JISで「港」という文字になります。運の悪いことに、「港」という名前のテーブルが元々あったのです(わざとらしい想定スマソ)。tb2#`港は良いとして、その後に「`」が追加されているのは、どうしてでしょうか?その秘密は「港」の2バイト目の%60にあります。これ、実は、バッククォート「`」と同じコードなのです。preg_replaceの文字列置換の際に文字エンコーディングを考慮していないので、「港」の2バイト目を「`」と見なして、これを重ねる処理をしてしまったのでした。地元のセキュリティ会社では、最初「文字エンコーディングのバリデーションががが」と言っていましたが、攻撃文字列はShift_JISとしてはバリッドでありバリデーションでは防御できないことがわかり、preg_replaceではなくmb_ereg_replaceを使ってエスケープ関数を書き換えました。$id);}また、バッククォート「`」は文字コードが0x60でShift_JISの2バイト目になることがあるため、予防的対策として、MySQLをANSI_QUOTESモードに設定して、識別子はダブルクォート「"」で囲むことにしました。ANSI_QUOTESモードの設定は、my.cnfに以下を設定します。sql-mode="ANSI"これにあわせて、SQL文組み立ての式を以下のように変更しました。\"$etable\""ダブルクォートの文字コードは0x22なので、Shift_JISの2バイト目に重なることはありません。MySQLの設定考慮漏れに起因するSQLインジェクション観光協会のホームページはしばらく平穏な日々が続きましたが、またしてもデータが全て削除される事件が起こってしまいました。今度の原因は次のリクエストのようでした。/search.php?season=tb2";delete+from+tb2%23生成されたSQL文は下記の通りです。tb2#"識別子をダブルクォートで囲むように変更した際に、エスケープの対象文字もダブルクォートに変更しておかなければならなかったのですが、それを忘れていたのが原因でした。その変更を入れたところ、攻撃はできなくなりました。観光協会のホームページはしばらく平穏な日々が続きましたが、今度はデータベースに保存された個人情報が漏えいするという事故が起こってしまいました。今度の原因は次のリクエストのようでした。/search.php?season=users生成されたSQL文は下記の通りです。"users"usersというテーブルには、ホームページに会員登録したユーザーの個人情報が登録されています。観光協会は、あわててサイトをいったん閉鎖して、個人情報漏洩の告知など事後対処に追われました。最終的にどうしたか結局、任意のテーブル名を外部から指定できる実装に問題があるということになり、こちらの記事を参考にして、季節は1~4の番号で指定する仕様に変更しました。すると、テーブル名を引用符で囲むこともエスケープも不要になりました(下記)。アプリケーション内部で使用する文字エンコーディングがShift_JISであることもよくないので、UTF-8に変更しました(コードは省略します)。$table";とりあえずのまとめテーブル名を外部から指定できる実装のアプリケーションを題材として、SQL識別子の扱いを原因とするSQLインジェクション攻撃と、間違った対策例を紹介しました。具体的には下記の通りです。識別子のクォートもれ識別子のエスケープもれ識別子エスケープの際の文字エンコーディング対応不備MySQLのANSI_QUOTESモードの考慮洩れ外部から指定するテーブル名の権限のチェック洩れ本稿では、このアプリケーションの最終的な実装を示しましたが、一般論としてSQL識別子をどう扱うのが良いかについては、稿を改めて説明します...が、その前に、次回は、SQL識別子のエスケープもれによる不具合の実例を紹介します。