DBMSの脆弱なAPI仕様は何時まで放置されるのか？

Computer, Development, PHP Security, Security 9月 5, 2017 #SQLインジェクション, #アンチプラクティス, #エスケープ
(Last Updated On: 2018年8月8日)

広く使われているデータベースでもAPIが仕様として脆弱な物が長年放置されています。OracleやMS SQL Serverを利用している方ならよくご存知だと思います。

MS SQL Server（TransactSQL）の場合

例えば、MS SQL Serverはリテラル（SQL文中の変数）をエスケープする関数を持っていません。このためエスケープが必要な場合、REPLACEを使い自分で”置換”してエスケープしなければならない、と公式マニュアルにも書いてあります。

  • Never build Transact-SQL statements directly from user input; use stored procedures to validate user input.（決してユーザー入力から直接Transact-SQL文を生成してはならない。ストアードプロシージャーを使ってユーザー入力をバリデーションすること）
  • Validate user input by testing type, length, format, and range. Use the Transact-SQL QUOTENAME() function to escape system names or the REPLACE() function to escape any character in a string.（ユーザー入力は型、長さ、形式と範囲をチェックしてバリデーションすること。Transact-SQLのQUOTENAME()関数を使い識別子（システム名）をエスケープすること、またはREPLACE()関数を使いその他全ての文字列をエスケープすること。）
  • Implement multiple layers of validation in each tier of your application.（貴方のアプリケーションのそれぞれのティアーで多層レイヤーの入力バリデーションを実装すること）

（今回のブログのテーマとは関係ないですが、マイクロソフトの方は流石にセキュアコーディングをよく理解されていますね！入力バリデーションがないとプログラムは正しく動作しないです。参考： ※1、※2）

QUOTENAMEという識別子（SQL文中のテーブル名やカラム名を識別する部分）用のエスケープ関数はあるのですが、これは識別子用のエスケープ関数です。SQL仕様では識別子は128バイトまでなので、それ以上の文字列はエスケープできません。長い文字列に使うとSQLインジェクションに脆弱になったりします。このため、公式マニュアルにも「長い文字列にQUOTENAMEは使わないように」とわざわざ記載されています。

Wrapping Parameters with QUOTENAME() and REPLACE()

In each selected stored procedure, verify that all variables that are used in dynamic Transact-SQL are handled correctly. Data that comes from the input parameters of the stored procedure or that is read from a table should be wrapped in QUOTENAME() or REPLACE(). Remember that the value of @variable that is passed to QUOTENAME() is of sysname, and has a maximum length of 128 characters.

@variable Recommended wrapper
Name of a securable QUOTENAME(@variable)
String of ≤ 128 characters QUOTENAME(@variable, ””)
String of > 128 characters REPLACE(@variable,””, ”””)

When you use this technique, a SET statement can be revised as follows:

–Before:

SET @temp = N’select * from authors where au_lname=”’

+ @au_lname + N””

–After:

SET @temp = N’select * from authors where au_lname=”’

+ REPLACE(@au_lname,””,”””) + N””

なぜこのような事になったのか？それは「SQLインジェクション対策にはプリペアードクエリだけで十分！」という開発側の強い思い入れ（SQL標準もかな？この辺りは詳しく追っていません）があるからでしょうか？

 

本当にリテラル（パラメーター）エスケープは必要ないのか？

現実にはリテラルをエスケープで対応したいコードが存在したりします。そこで開発者はリテラル用のエスケープ機能を検索します。運良く、QUOTENAMEの仕様的制限／リスクやREPLACEでエスケープすべきこと、を検索できれば良いのですが常にそうだとは限りません。

適切なモノが検索できなかった場合、危険なQUOTENAMEをリテラルに使ったり、最悪の場合は「APIがないのでこのまま書いてOKだと思った」ということになります。（コード検査をしているとこのケースが無視できないことが分かります。今時はエスケープばかりなどというコードはほぼなく、全面的にプリペアードクエリを使っているコードにこのようなケースがあります）

この仕様は「SQLセキュアコーディング」では基礎中の基礎なので「知らない開発者が悪い」と開発者は主張と思います。しかし、その仕様は本当に「妥当で安全」なのでしょうか？

SQLは可変長テキストインターフェース（ざっくり言うと構文があるモノ）に分類されます。可変長テキストインターフェースのほとんどがエスケープ処理をサポートしています。エスケープ処理が無いと正しく構文を処理できないからです。このため、マイクロソフトのドキュメントでも「エスケープする必要がある場合はREPLACE()で全てエスケープする」としています。しかし、REPLACE()はエスケープ関数ではなく、置換関数です。

参考： そもそもエスケープとは何なのか？

 

REPLACE（置換）関数＝エスケープ関数で良いのか？

そもそもエスケープが無いと正しく動作することができない、なのでマニュアルでも置換関数でエスケープするように指示している。にも関わずエスケープ用のAPIがない、という状態が続いています。

プリペアードクエリを使っていても、識別子エスケープのQUOTENAMEは必要です。プリペアードクエリを使っていても、複雑なSQL文の一部を別の場所（関数など）で生成しクエリを実行したい、というケースも無視できません。（備考：このケースの場合、生成した文とパラメーターがプリペア文の一部となるようにコードを書くことが困難になります）

プリペアードクエリだけ使っていれば大丈夫！！とならないケースは無視できません。コード検査をしているとエスケープ無しの怪しい変数のSQL埋め込み、は普通に見つかります。「プリペアードクエリを使っていればエスケープは必要ない」とする意見には全く同意できません。

名前は何でも構わないですが、例えばQUOTELITERALというリテラル用のエスケープ関数を用意すれば、つまらないノウハウ（QUOTENAMEは危険、置換関数で手動エスケープする）を知らなくても安全なコードを書くことができるようになります。識別子用のエスケープAPI、パラメーター用のエスケープAPI、両方そろっていれば「識別子、パラメーターにはそれぞれエスケープが必須なのだな！」と理解ると思います。1

SQLに限らず、可変長テキストインタフェースを持つAPI（HTMLなども）ではエスケープ処理が欠かせません。ユーザーによるエスケープが全く必要ないAPIがあるならまだ良いです。しかし、TransactSQL2のプリペアードクエリにはQUOTENAMEによる識別子エスケープが欠かせません。欠かせないのでマイクロソフトの公式マニュアルでも”識別子はQUOTENAMEで全てエスケープするように”と解説しています。

エスケープ処理が無いと正しく構文を処理できない処理系は、必ず正しくエスケープできる関数／APIも提供すべきでしょう。こっちには関数があるけど、こっちには関数がない、という仕様は初心者に優しくありません。つまり、セキュアにしようと思ったAPI設計は実は、間違えやすい＝セキュアでないAPI仕様と言えるのでは？

 

初心者キラーのマニュアル

Transact-SQLマニュアルの文字列の項目には識別子用のQUOTENAME以外の文字列エスケープ関数には文字列出力用3のESCAPE_STRINGという関数があります。ESCAPE_STRINGは”SQLリテラル用”のエスケープ関数ではありません！この関数ではSQLは安全になりません。適切なリテラルエスケープAPIがないことより初心者キラー仕様になっていると思います。このページを見て、初心者がどうするのか？私はなかなか予測できません。（初心者にはITエンジニアの卵の学生なども含まれます）

「だからプリペアードクエリを使うのです」という声が聞こえてきそうですが、整合性のないAPIが理解りづらいのは変わりません。

プリペアードクエリだけでよいなら、置換関数のREPLACE()をエスケープ用に使うことを解説するも必要ない、となりますがマニュアルに記載する必要があって記載されています。APIを用意せず置換関数でエスケープするのは前近代的な方法ではないでしょうか？

 

まとめ

APIを設計する場合でエスケープ処理が必要なモノがある場合、必ずエスケープ用のAPIを用意しましょう。その方がAPIとして整合性を保てて解りやすいAPIになります。他の便利なAPIがある場合は、それらのAPIマニュアルから簡単に参照できるようにしておくと良いです。

プリペア文の中に変数を１つでも埋め込む場合、それはダイナミッククエリ（文字列で組み立てたクエリ）になります。プリペアードクエリだけ、を使っているつもりで実はダイナミッククエリをかなり使っているのが現実です。

エスケープが必要な場面が現実にあり、エスケープAPIが無いため脆弱なコードが生まれる。なのでセキュリティ対策用のマニュアルで「”必ず”置換関数を使ってエスケープすること」と解説している、といった現状には矛盾を感じざるを得ません。

在って当たり前のエスケープ処理関数は用意しておき「プリペアードクエリの方が速いし、便利ですよ」と教えてあげる方がセキュアになるのではないでしょうか？

 

参考：

これを書いた当時（2013年12月）は様々な反論がありましたが、現在のマイクロソフトさんのSQLインジェクション対策マニュアルに書いてあるような事を解説していただけです。

 

  1. 理解するためにはテキスト処理の基本中の基本の「エスケープとは何なのか？」「テキストインタフェースとは何なのか？」を知っている必要があります。しかし、基礎知識なので教育で十分にカバーできることではないでしょうか？ ↩
  2. SQL識別子がパラメーターの場合、識別子用のエスケープ処理が必要になるのはTransactSQLに限ったことではありません。全てのSQLデータベースに共通して識別子のエスケープ処理は必要です。 ↩
  3. 文字列出力用とはSELECT ESCAPE_STRING(some_data) FROM tbl WHERE id = 1234; のようにSELECTの直後に来るデータを出力用にエスケープするための物ということです。 ↩

投稿者: yohgaki