SQL識別子のエスケープ

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

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

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

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

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

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

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

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

$sql = "SELECT * FROM mytable WHERE id = '". $_GET['id']. "';";

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

$sql = "SELECT * FROM mytable ORDER BY ". $_GET['sort_column'] ." ;";

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つの処理を行なえば良いです。

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

例）

my"table は "my""table" になる

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

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

参考：

• SQL識別子のエスケープ （PostgreSQLの識別子エスケープの解説）

MySQLの識別子エスケープ

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

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

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

例）

my`table は `my``table` とする

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

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

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

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

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

参考：

• Escaping Column and Table and Names in MySQL (Part 1)
• Escaping Column and Table and Names in MySQL (Part 2)

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の場合は大文字を小文字に変換し、その上で小文字として扱います。本来、この辺りの細かい動作は”ルール”に従って使っていれば問題にならない³はずです。MySQL/SQLiteでの正確な動作は把握していません。問題がある場合はコメントを頂けると助かります。

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

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

参考：

• 完全なSQLインジェクション対策