SQL識別子のエスケープ

(更新日: 2017/03/27)

SQLの識別子(テーブル名やフィールド名)はプリペアードクエリではエスケープできません。最近の開発者はSQLの”パラメーター”には注意を払うようになったので、SQLパラメーターによるSQLインジェクションはかなり少くなってきました。

この結果、相対的にSQL識別子によるSQLインジェクション脆弱性の割合が増えています。実際、私がコード検査を行っているアプリケーションでも識別子が原因でSQLインジェクションに脆弱であるケースが半数くらいになっています。

出力対策はセキュアコーディングの基本の1つです。プリペアードクエリだけでSQLによるインジェクションは防げません。DBMSに限らず、他のシステム(ライブラリも含む。特に文字列をパースする正規表現、XML処理など)にデータを送信する場合、完全に無害化する必要があります。

参考: CERTトップ10セキュアコーディング習慣7. 他のシステムに送信するデータを無害化する

 

SQL識別子によるSQLインジェクションパターン

識別子のエスケープを解説する前に、SQL識別子によるSQLインジェクションパターンを紹介します。次に挙げる識別子の使い方において、必要なエスケープ処理、バリデーション処理がない為にSQLインジェクションに脆弱になるケースが多いです。

  1.  ソート対象のカラム
  2.  検索対象のカラム
  3.  クエリ対象のテーブル名(特にプレフィックス、スキーマ)
  4. エイリアスの定義(例: SELECT name as “名前” FROM mytable)

ユーザー入力のソート対象、検索対象のカラムがSQLインジェクション脆弱性の原因になることは

がSQLインジェクションに脆弱であるのと同じです。以下のようなクエリが

SQLインジェクションに脆弱であることは明らかです。

経験則的に多いインジェクションパターンを紹介しましたが、SQLインジェクションに脆弱になるパターンはこれらに限りません。識別子に対して必要なエスケープ処理、バリデーション処理がない場合はどこでもSQLインジェクションに脆弱になる可能性があります。

 

PostgreSQLの識別子エスケープ

PostgreSQLの場合、識別子は”(ダブルクオート)で囲むことができます。予約語(SELECT、userなど)でない場合はダブルクオートを省略できます。クオート文字の”(ダブルクオート)は”(ダブルクオート)でエスケープします。

PostgreSQLはセキュリティに対して十分な注意を払っているデータベースです。今回紹介するデータベースの中で唯一識別子のエスケープAPIを準備しています。(参考: 32.3.4. SQLコマンドに含めるための文字列のエスケープ処理のPQescapeIdentifier)

PHPの場合、PQescapeIdentifierのラッパーAPIであるpg_escape_identifier関数が用意されています。

(PHP 5 >= 5.4.4, PHP 7)
pg_escape_identifier — テキスト型フィールドに挿入するために、識別子をエスケープする

string pg_escape_identifier ([ resource $connection ], string $data )

PHP+PostgreSQLの場合、pg_escape_identifier関数を使うだけで識別子が正しくエスケープされます。

pg_escape_identifierと同等の処理を行うには次の3つの処理を行なえば良いです。

  • 識別子データの文字エンコーディングがデータベース接続クライアントエンコーディングとして正しいか、文字エンコーディングのバリデーションを行う。
  • “(ダブルクオート)を”(ダブルクオート)でエスケープする。
  • 識別子データの前後を”(ダブルクオート)で囲む。

例)

PHP 5.4.4に追加した関数なので、実際にはpg_escape_identifier関数を使うだけでしょう。

PostgreSQLの識別子の最大長は63バイト(文字でなくバイト)である点にも注意1が必要です。

参考:

 

MySQLの識別子エスケープ

MySQLは識別子エスケープ方法は定義していますが、APIはありません。MySQLの場合、APIが無い上、データベースモードが識別子エスケープに影響するのでPostgreSQLより複雑です。

通常モードの場合、識別子は`(バッククオート)2で囲みます。また、識別子に利用できる文字は次の通りです。

  • Permitted characters in unquoted identifiers:
    • ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)
    • Extended: U+0080 .. U+FFFF
  • Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000:
    • ASCII: U+0001 .. U+007F
    • Extended: U+0080 .. U+FFFF
  • ASCII NUL (U+0000) and supplementary characters (U+10000 and higher) are not permitted in quoted or unquoted identifiers.
  • Identifiers may begin with a digit but unless quoted may not consist solely of digits.
  • Database, table, and column names cannot end with space characters.

https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

文字エンコーディングをバリデーションした上で、`(バッククオート)でエスケープします。

例)

ANSI QUOTESモードの場合はPostgreSQLと同様に”(ダブルクオート)で囲み”(ダブルクオート)でエスケープします。文字リテラルもPostgreSQLと同様に’(シングルクオート)で囲み’(シングルクオート)でエスケープになります。

詳しくはMySQLのマニュアルを参照してください。

モードによって動作がエスケープ動作が変るMySQLはより識別子エスケープAPIの必要性が高いと言えます。しかし、無い物は仕方がないのでエスケープが必要な場合は”正しくエスケープ”しなければなりません。

識別子の最大長にも注意が必要です。64文字を超える識別子はエスケープ関数でバリデーションして拒否しなければなりません。

参考:

 

SQLite3の識別子エスケープ

SQLite3の識別子は更に複雑です。

  • ‘keyword’ A keyword in single quotes is a string literal.
  • “keyword” A keyword in double-quotes is an identifier.
  • [keyword] A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
  • `keyword` A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.

https://www.sqlite.org/lang_keywords.html

文字リテラルの様にも、ANSI SQLの様にも、MS ACCESS/MySQLの様にも識別子を記述できます。

エスケープ方法が複数用意されている点は複雑ですが、文字リテラルと同じように記述できるので”文字リテラルと同じ方法でエスケープ”できます。

SQLite3はマルチバイト文字としてUTF-8のみ受け付けます。文字エンコーディングがUTF-8としてバリデーション済み(つまりUTF-8文字列として保障済み)の文字列を

(PHP 5 >= 5.3.0, PHP 7)
SQLite3::escapeString — 適切にエスケープされた文字列を返す

public static string SQLite3::escapeString ( string $value )
SQL 文の中で使えるよう適切にエスケープされた文字列を返します。

http://php.net/manual/ja/sqlite3.escapestring.php

escapeStringメソッドでエスケープすればOKです。しかし、次のような警告文がマニュアルには記載されています。

For resilience when confronted with historical SQL statements, SQLite will sometimes bend the quoting rules above:

  • If a keyword in single quotes (ex: ‘key’ or ‘glob’) is used in a context where an identifier is allowed but where a string literal is not allowed, then the token is understood to be an identifier instead of a string literal.
  • If a keyword in double quotes (ex: “key” or “glob”) is used in a context where it cannot be resolved to an identifier but where a string literal is allowed, then the token is understood to be a string literal instead of an identifier.

Programmers are cautioned not to use the two exceptions described in the previous bullets. We emphasize that they exist only so that old and ill-formed SQL statements will run correctly. Future versions of SQLite might raise errors instead of accepting the malformed statements covered by the exceptions above

識別子/文字リテラルがコンテクストに応じて適当に処理されることを利用するな、と警告しています。このため、基本的には識別子(リテラルも)はANSI SQL方式でクオートしエスケープする方が良いでしょう。

ANSI SQL方式でエスケープする場合は、文字エンコーディングをバリデーションして”(ダブルクオート)で囲み、”(ダブルクオート)でエスケープすれば良いです。これは単純な文字列置換で行えます。

 

まとめ

標準に準拠するならANSI SQL方式のエスケープ方式を採用する方が互換性が高くなります。これなら、SQL準拠のDBMS全てで”正しい”クエリとして実行できます。MySQLの標準モードがANSI QUOTESモードではないことが難点です。

完全に環境を制御できる場合、MySQLでも積極的にANSI SQLモード(ANSI QUOTEモード)を使いたいです。

注意しなければならない点は、クオート文字で囲むと文字列がそのまま(つまり大文字、小文字の違いをそのまま)で使われます。囲まない場合は大文字/小文字を区別しません。PostgreSQLの場合は大文字を小文字に変換し、その上で小文字として扱います。本来、この辺りの細かい動作は”ルール”に従って使っていれば問題にならない3はずです。MySQL/SQLiteでの正確な動作は把握していません。問題がある場合はコメントを頂けると助かります。

エスケープ方法を色々書いてきましたが、そもそもエスケープが必要ない英数字のみを識別子に使う方法でも全く構いません。この場合、識別子が英数字のみであることをバリデーションします。バリデーションの方がシンプルで解りやすい、というメリットもあります。

識別子やその一部がユーザー入力の場合でバリデーションが必要な場合、必ず”入力処理”でバリデーションすべきです。自分が書いているコードがアプリケーションコードならアプリケーションレベルの入力処理でバリデーションします。DBのトランザクションは簡単にロールバックできますが、DBの処理に至るまででコードが処理してしまった部分は簡単にロールバックできません。早すぎるバリデーションで困ることはほぼありませんが、遅すぎるバリデーションは場合によって非常に困った状態4になることもあります。

参考:

 


  1. SQL標準では127バイトまでの識別子(ISO/IEC 9075-2:1999 – In a <regular identifier>, the number of <identifier part>s shall be less than 128.)をサポートすべきですが、63バイトまでの場合に比べ、無視できない性能劣化があるためPostgreSQLの識別子の最大長は63バイトまでに制限されています。 
  2. 半角のバッククオートはこのブログでは特殊文字なので、全角のバッククオートで記述しています。 
  3. ルール/規約/標準は、使っているモノの違いを気にしなくても取り決めに従っていれば決められた通りに動作する、ようにする為のモノです。しかし、そのようになっていない事が結構あるのも現実です。例えば、整数リテラルが要求されるコンテクストで文字リテラルが与えられた場合、文字リテラルを整数リテラルとして処理すべきです。しかし、MySQLの場合は何故か浮動小数点リテラルとして解釈する仕様(というより仕様バグ)があったりします。更にキャスト構文があるにも関わらず、何故かINT(32ビット)でのキャストしかサポートしない仕様(これも仕様バグでしょう)もあり、64ビット整数の場合は処理できない問題があります。整数1つでも完全に正しく取り扱うことは結構骨が折れます。 
  4. 例えば、Webクライアントにレスポンスを返した後にDBでの処理を行う、非同期処理(メッセージキューなど)による疎結合設計を採用している、といった場合に困ることがあります。入力処理の安全対策であるバリデーションはできるだけ早く、出力処理の安全対策であるエスケープはできるだけ遅く(可能であれば出力の直前)にする、という基本方針で実装すると安全かつ効率的なコードが書けます。バリデーションが遅すぎて非効率になる良い例はPCREです。PCREはマルチバイト文字としてUTF-8文字エンコーディングをサポートしていますがu修飾子を使うと、遅すぎる入力バリデーションのために何十倍も遅くなります。遅すぎる入力バリデーションに良いことはありません。因みに、出力時にもう一度バリデーションする事はセキュアコーディング的には120%オススメです。セキュアコーディングに於ける入力のセキュリティ対策と出力のセキュリティ対策は”独立した対策”です。 

Comments

comments