PostgreSQL Advent Calender用のエントリです。
エスケープ処理が必要なのにエスケープ用のAPIが無い状態は良くありません。エスケープしないために動かないのはまだ良い方です。エスケープが必要なのにエ スケープをしなくても動いてしまい、セキュリティ上の問題となる場合もあります。全てのアプリケーション・ライブラリはエスケープが必要なデータに対するAPIを持っておくべき です。今回はPostgreSQL 9.0から追加されたエスケープ関数を紹介します。
PostgreSQL使い始めて最初の頃に気づくのはuserなどの予約語がフィールド名に使えない事かも知れません。例えば、
yohgaki@[local] test=# CREATE TABLE user (name text); ERROR: syntax error at or near "user" 行 1: CREATE TABLE user (name text);
と失敗してしまいます。これはuserがPostgreSQLの予約語であるためSQL文の識別子として使用できないからです。MS Accessからデータベースに入った方には識別子に日本語を使う場合も多いので、PostgreSQLでは日本語のテーブル名やフィールド名はそのままでは使えない事に気が付いた方も多いのではないでしょうか？
日本語や予約語の識別子が古いPostgreSQL（7.xでも）使えます。PostgreSQL９．０以前でも”(ダブルクオート)を利用して予約語や日本語テーブル名・フィールド名を持つデータベースを作る事はできました。PostgreSQLは正しい文字エンコーディングで適切なエスケープ処理を行えば安全にどのようなテーブル名・フィールド名でも利用できます。例えば、識別子の間にスペースがあっても構いません。
yohgaki@[local] test=# CREATE TABLE "日本語 table" (name text); CREATE TABLE 時間: 380.505 ms yohgaki@[local] test=# \d "日本語 table" テーブル "public.日本語 table" カラム | 型 | 修飾語 -----------+------+----------- name | text |
これはPostgreSQL 8.4で実行した例です。このように比較的古いPostgreSQLも機能的には、どのようなテーブル・フィールド名でも使えるようになっていました。
PostgreSQL 9.0のlibpq（PostgreSQLクライアント用のライブラリ）からテーブル名・フィールド名をエスケープできる PQescapeIdentifierとリテラル（フィールドのパラメータ）をエスケープできるPQescapeLiteral関数が追加されました。 クエリパラメータの方はPQescapeStringConnというエスケープ関数が以前から利用可能でしたが、識別子のエスケープ関数はPostgreSQL 9.0で初めて追加されました。
エスケープのAPIを提供するということは「エスケープが必要である」とユーザに明示する意味もあります。あるべきAPIがやっと実装されたというべきかも知れません。
PQescapeIdentifier()とpg_escape_identifier()
PostgreSQL 9.0からはテーブル・フィールド名をエスケープするPQescapeIdentifier関数が追加されています。PQescapeIdentifier関数は文字列を識別子用にエスケープした上、ダブルクオートで囲んだ文字列を返します。
日本語\\ \"テーブル" は次のようにエスケープされる "日本語\ ""テーブル"
文字列がダブルクオートで囲まれ、エスケープが必要な文字(“)がダブルクオートでエスケープされている事がわかります。文字エンコーディンのチェックも行っています。この関数さえ利用していればダブルクオートで囲まれ、正しくエスケープ処理された文字列が返って来ます。正しくエスケープされているのでSQL文に埋め込んでも安全です。
データベース管理アプリを作った事がある方ならよくご存知と思いますが、プリペアードクエリはSQLインジェクション対策に役立ちません。しかし、このPQescapeIdentifierを使えば確実に安全なクエリをデータベースに送信できます。
PQescapeLiteralとpg_escape_literal
PQescapeLiteralはPQescapeStringConnに代わるクエリパラメータのエスケープ関数です。動作はPQescapeStringConnと大きくは異なりませんが、確実なパラメータ処理を行う為に最初に”E”を追加しエスケープされた文字列を’（シングルクオート）で囲みます。
以下はPQescapeLiteralを利用したPHPのpg_escape_literal関数の実行例です。
[yohgaki@dev php-src]$ ./php -r 'var_dump(pg_connect("host=127.0.0.1 dbname=test"),
pg_escape_literal("日本語 テキスト"));'
resource(4) of type (pgsql link)
string(26) " E'日本語 テキスト'"
データベースアクセス抽象化レイヤーには文字列をエスケープしてシングルクオートで囲む、quote()メソッドが実装されている事がありますが、それらの関数と同様の動作を行います。
C言語でプログラミングしていればPQescapeStringConnの方が便利である事も多いのですが、文字列の取り扱い（連結）が容易なスクリプト系の言語ではPQescapeLiteralの方が便利が良いです。
名前がEscape Literalとなっているのは良い命名だと思います。プリペアードクエリを使わずにSQL文やその一部を作る場合は、数値、日付、文字列、全てのパラメータ（リテラル）をエスケープ処理してから送信すべきだからです。この名前であれば初心者にありがちな「数値なのでエスケープ処理をしない」といった誤りを防止できると思います。
PHPのpg_escape_identifier()とpg_escape_literal()
PQescapeIdentifierとPQescapeLiteralはPostgreSQL 9.0以降のlibpqで利用できますが、PHPのpgsqlモジュールは9.0未満のlibpqとコンパイルした場合も利用できるようになっています。
PHPのpg_escape_identifier()とpg_escape_literal()は便利そうだ！と思った方。申し訳ないです。私が関数を実装していなかったので次にリリースされるPHP 5.4.0にも入っていません。もしかするとPHP 5.4.1には入るかも知れませんが、どうなるかはまだ分かりません。
どうしても使ってみたい方はPHPのsubversionレポジトリからpgsqlモジュールのソースコードをダウンロード＆コンパイルして使ってください。
よく誤解されるので念の為に書いておきます。もともと私はプリペアードクエリを使うな、と言っている訳ではありません。プリペアードクエリが使える場合はどんどん使えば良いと言っています。パフォーマンスにはうるさいので効率が良いプリペアードクエリの利用を薦めるのは当然です。
しかし、プリペアードクエリの使用が解決策になっていない場合が多くあります。趣味でシステム開発をしているならともかく既存のコードは会社の資産であり、様々な要素から検討した結果としてプリペアードクエリを使わないコードを採用することもよくあります。ROIを最大化するのが会社ですから当然です。このような場合にプリペアードクエリを使え、特定のORMを使え、特定の抽象化レイヤーを使え、NoSQLを使え、といっても何のソリューションにもなりません。
私も好んで使ってるプリペアードクエリですがセキュリティ対策とはしては、これだけでは不完全です。識別子のエスケープの例からも明白なように、プリペアードクエリは万能ではありません。プリペアードクエリは完全なセキュリティ対策を行う為に開発された物ではないので、完全でなくても文句は言えません。正しくSQLデータベースを利用するにはエスケープ処理を正しく知ることが最も重要です。セキュリティ対策として不完全なプリペアードクエリの利用が最も重要では無いことは、不完全であることからも明らかです。（出力のセキュリティ対策についてはこのブログにも書いているのでそちらをどうぞ）
今回はセキュリティ対策の為のエントリではないですがまとめです。
データベースに限らず全ての出力先のエスケープ処理を正しく理解する事が出力セキュリティ対策における最も重要です。エスケープ方法を知ること＝出力先への正しい出力（安全な出力）知ること、です。なかなか浸透しないので繰り返し書く事になっているのは残念ですが、この事を理解して実践すれば、SQLインジェクションに限らず、出力時にセキュリティホールを作ることを減らす事ができます。
最後にエスケープ方法を知ればほとんどの出力先に対する正しい出力（安全な出力）方法を知ることができます。しかし、それが全てではありません。これについては既に書いた記事もありますが、またそのうちこのブログか技術評論社さんの記事として改めて書くことにします。
参考：他のデータベースの識別子エスケープ
