yohgaki's blog

SQLにエスケープなんて必要ない、と考えている方も居るとは思いますが現実にはエスケープを知っておくことは必須と言っても構わないと思います。

既にSQL識別子のエスケープについては書きましたが、今回はSQLエスケープというよりは安全にSQLデータベース利用する話です。先ずはエスケープの話を全て終わらせよう、と思っているのですがSQLエスケープのエントリが無いので作りました。私のブログを読んでいる方はエスケープ処理、プリペアードクエリの利用方法などはよくご存知だと思うのでここの部分は省略しています。

現在広く利用されているSQLデータベースのほとんどがプリペアードクエリをサポートしています。プリペアードクエリを利用すれば、SQL文とパラメーターを分離できるため、パラメーターがSQL文の一部として解釈されてしまう問題を回避できます。

プリペアードクエリを使うと次のメリットがあります。

• 一度SQL構文の解析を行えば、次からはSQL構文の解析が省略できる（高速化）
• パラメーターはSQL文と分離される（つまりパラメーターのエスケープ処理は必要ない）

しかし、デメリットと制限もあります。

• SQL構文の解析と同時にクエリプラン作成を行うため、SQL文にパラメーターを埋め込む場合に比べ、極端に遅くなる場合がある
• 抽象化レイヤー／ライブラリの実装によってはプリペアードクエリを利用した場合の方が遅い場合がある
• IN句など、パラメーター数が不定であるクエリにはあまり向いていない
• WHERE句の条件などのパラメーターは分離できるが、テーブル／フィールド名などの識別子は分離できない
• 識別子と同様にDESC／ASCなどのSQL語句も分離できない
• プリペアードクエリだけ使いなさい、とルール化してもクエリパラメーターの埋め込みを仕組みとして制限する事はできない

プリペアードクエリを利用するとパフォーマンスが低くなる問題は、クエリプランをプリペアードクエリを準備する時ではなく、プリペアードクエリを実行する際にパラメーターを利用して作成する事で回避できます。このような実装になっているかどうかはデータベースによるので、プリペアードクエリの場合には遅くなってしまい使い物にならない、という場合にはパラメーターをエスケープして埋め込む回避策が必要になります。

プリペアードクエリは元々クエリを高速化する為に考案された仕組みです。しかし、抽象化レイヤー／ライブラリによってはプリペアードクエリを利用する方が効率が悪い場合もあります。PHPの場合、PDOを利用するとプリペアされたSQL文はスクリプトの終了と同時に削除されます。プリペアードクエリによる高速化はSQL文の解析を省略することで実現しています。プリペアされたSQL文が削除されてしまうので、プリペアードクエリを利用した方が効率が悪くなってしまう場合があります。SQL文をプリペアする為のリクエストが余分にデータベースサーバーに送信される事になるので遅くなるのは当然です。セキュリティ的には多少遅くなる程度なので問題ないのですが、アプリケーション／データベースのパフォーマンスを極限にまで最適化したい場合もあります。このような場合には抽象化レイヤー／ライブラリの動作によってはプリペアードクエリを利用しない、という選択をする場合もあります。

パラメーター数が不定であるIN句などにプリペアードクエリは向いているとは言えませんが、プリペアードクエリを利用することは可能です。先に説明したケースと似ています。プリペアードクエリによる性能向上のメリットがない場合もあります。

今までの制限・デメリットは気にせずプリペアードクエリを利用する事は可能ですが、識別子が分離できない制限はエスケープするしかありません。データベースを管理するプログラムの場合、識別子のエスケープが必要になります。ほとんどのデータベースはテーブル・フィールド名などの識別子にエスケープが利用できるようになっています。しかし、全てのデータベースやデータベースアクセスライブラリが識別子のエスケープをサポートしている訳ではありません。PHPの場合、PostgreSQLモジュールのみが識別子のエスケープAPIをサポートしています。

参考：SQL識別子のエスケープ

DESC、ASCなどのSQL語句がパラメーターとなっている場合があります。直接DESC、ASCをユーザー入力から受け付けるのではなく、sort_asc=1またはsort_asc=0などとフラグで指定する方が良いと思いますが、DESC／ASCなどのSQL語句を入力として受け付けている場合はプリペアードクエリもエスケープも利用できないのでバリデーションを行います。

プリペアードクエリを使えばSQLインジェクションは無くなる、ということが無いことはプリペアードクエリを利用していてもインジェクションできてしまうアプリケーションがあることから明らかです。プリペアードクエリはパラメーターを埋め込む事を禁止できるAPIではないので仕方ありません。識別子／SQL語句のようにパラメーター（変数）の埋め込みがどうしても必要になる場合もあります。

安全なSQLデータベースの利用方法

データベースサーバーは他の出力先と同じ出力先です。安全に利用する為の基本原則は変わりません。出力先に安全に出力する為の原則が利用できます。

• 出力先の正しい入力仕様に従いエスケープして出力する
• 利用可能な安全なAPIを利用して出力する
• エスケープもAPIも利用できない場合はバリデーションする

これはセキュリティ対策を軸にした出力セキュリティ対策の優先順位です。出力先の違いに関わらず利用できます。「エスケープ処理可能な出力データは全てエスケープ関数で処理してから出力する」を原則とし、「出力を行う場合にエスケープ処理があるのか、必要なのか？」を確認するようにプログラマを指導すべきです。セキュリティ問題の多く原因が「出力する際に適切なエスケープ処理が必要である」事を認識せずに出力してしまった事にあるからです。

SQLデータベースの利用方法を定めたコーディング規約などでは順番を変えて、APIの利用、つまりプリペアードクエリを利用するを優先しても構いません。しかし、「プリペアードクエリのみを利用する」と定めたコーディング規約はセキュリティ問題の原因になります。

アプリケーションによっては、SQL文へ識別子／SQL語句を変数に入れて埋め込むことがどうしても必要になる場合もあります。IN句のようにプリペアードクエリにあまり向かないクエリや、プリペアードクエリではパフォーマンスに問題がある場合など、パラメーターの埋め込みを行う場合もあります。このような例外が発生した場合にも対応できるよう、コーディング規約では「プリペアードクエリのみを利用する」と原則だけ定めるだけでなく、「プリペアードクエリを利用してクエリパラメーターを渡す」という原則を定めた上で、例外として「エスケープ」と「バリデーション」について記載しておく方が安全です。

例外をルールとして定めておく事は非常に重要です。プリペアードクエリを利用しているにも関わらず、SQLインジェクションが可能な脆弱なアプリケーションを作ってしまった原因は、プリペアードクエリのパラメーターとして渡せない識別子／SQL語句の埋め込み、プリペアードクエリが向かないクエリでのプリペアードクエリの不使用だからです。

プリペアードクエリとエスケープ、バリデーション

（プリペアードクエリ、エスケープはデータベースや利用するモジュールによって異なる。バリデーションはほぼ同じ。解説はここでは省略。エスケープの仕方、プリペアードクエリの利用法、バリデーションの仕方などを理解すれば、フレームワークなどが安全なSQLデータベースの利用をサポートしているのかどうか確認できるようになる）

まとめ

出力先がデータベースだから1つの出力先と考えず、クエリパラメーター、識別子、SQL語句など、何を出力しているのか区別し、それぞれに適切なセキュリティ対策を行わないと「完璧かつ安全にSQLデータベースを利用する」ことは難しいです。

PHPのセキュリティ入門書に記載するコンテンツのレビューも兼ねてPHP Securityカテゴリでブログを書いています。コメント、感想は大歓迎です。