知っているようで知らないプリペアードクエリ

PostgreSQL Advent Calender 2012用のエントリです。

PostgreSQLや他のDBMSを利用していてプリペアードクエリを知らない方は居ないと思いますが、プリペアードクエリを使いこなす為のTIPSです。役に立つかどうか、は多少疑問ですが、内部がどうなっているか知っているとなにかの役に立つかも知れません。時間的制約で多少端折っているところは勘弁してください。

完全なSQLインジェクション対策は以下を参照してください。

libpqを知る

libpqとはPostgreSQLデータベースサーバにアクセスするためのC言語のライブラリです。PHP,Ruby,Perl,Python,NodeJS,etcはlibpqを利用してPostgreSQLにアクセスするAPIを提供しています。

では早速PostgreSQLのプリペアードクエリAPIはどのようなAPIなのかlibpqを見てみましょう。

http://www.postgresql.org/docs/9.2/static/libpq-exec.html

libpqには幾つかのプリペアードクエリ型のAPIがありますが、基本形であるPQprepareとPQexecPreparedを利用して解説します。PQprepareでクエリを準備しPQexecPreparedで実行します。

PQprepare

Submits a request to create a prepared statement with the given parameters, and waits for completion.

PGresult *PQprepare(PGconn *conn,
                    const char *stmtName,
                    const char *query,
                    int nParams,
                    const Oid *paramTypes);

PQexecPrepared

Sends a request to execute a prepared statement with given parameters, and waits for the result.

PGresult *PQexecPrepared(PGconn *conn,
                         const char *stmtName,
                         int nParams,
                         const char * const *paramValues,
                         const int *paramLengths,
                         const int *paramFormats,
                         int resultFormat);

PQprepareのAPIを見ると「const Oid *paramTypes」というデータ型を指定するらしきパラメータがあります。実はPostgreSQLのプリペアードクエリはデータ型を指定してプリペアする事も出来るのです。

現在ではWITHOUT OIDSでOID（オブジェクトID）が無いテーブルも作れるようになっていますが、PostgreSQLはオブジェクト指向型リレーショナルデータベースなので全てのオブジェクトにはOIDがありました。当然データ型にはOIDがあるのでデータ型を指定する場合、データ型のOIDを指定します。

このようなAPIであればプリペアードクエリであってもデータ型を指定して実行できます。しかし、多くの言語のPostgreSQLアクセスAPIではデータ型を指定しなくてもよい（あってもオプションになっている）APIとなっています。データ型を指定することで利便性は向上せず、簡単に新しいデータ型を作れるPostgreSQLでは、データ型のOIDを取得するために余計なクエリが必要となり、プリペアードクエリ本来の目的であるクエリの高速化には役立たないからだと思います。

恐らくほぼ全てのライブラリのPostgreSQLのプリペアードクエリAPIはデータ型のODI指定なしで利用できるようになっています。データ型を指定しなければクエリ実行も簡単になります。PQexecPreparedで渡すパラメータは全てC言語文字列の配列として渡すだけで済みます。

このエントリを執筆するにあたりPHPのpgsqlとRuby pgのコードを確認したところ、Ruby pgはOIDを指定する事もできるようになっていました。Ruby pgのプリペアードクエリ以外のコードも見てみました。完璧です。RubyにはいろいろPostgreSQL用のモジュールがあるようですが、Ruby pgを使いましょう。

プリペアードクエリを知る

プリペアードクエリがセキュリティ対策のように解説されている事がありますが、元々プリペアードクエリはエスケープに変わるセキュリティ対策として実装された物ではありません。クエリのパースとクエリプランの作成を省略し、クエリを高速に実行する仕組みとして考案されました。

一般的な注意事項

プリペアードクエリはクエリ高速化の為に作られた仕組みですが万能ではありません。例えば、プリペアードクエリのクエリプランはプリペア時に作成されます。しかし、クエリをプリペアする時にはパラメータが分からない為に通常のクエリのように最適化できません。データベースが大幅に変更されるようなデータの場合にも問題が発生します。プリペアした時点でのプランが最適とは限らず普通にSQL文を実行した方が速い、状況になる事もあります。これについては色々な対策が考えられているので、将来考慮しなくても良くなるかも知れませんが、今はまだ注意が必要です。（9.2のソースにはRevalidateCachedPlan関数が定義されていて、planが再作成される時もあるようです。ここは追いかけませんでした。FAQが少し古い？）

プリペア文を使っているからクエリが高速化されている！と思っていたら逆にAPIのせいで遅くなっていた、という場合もあります。DBアクセスの抽象化レイヤーは多数のプリペア文が作られリソースを使ってしまう問題を回避するため、DBアクセスオブジェクトが破棄される時にプリペア文も自動的に破棄するようになっています。この場合、常にプリペアとクエリ実行を別に行う為、SQL文を直接実行する時よりも遅くなってしまいます。

プリペアードクエリを使い切るにはDBMSの内部仕様を知るとともに、利用しているAPIの特徴も知った上で利用しないと思っているように動作していない事も少なくありません。プリペアした時点でのプランは最適とは限らず、一度設定したプランは更新されないためデータベースの状態にも影響されます。プリペアードクエリが遅い！と感じたときは、直接クエリとプリペアードクエリのどちらが速いか調べてみると良いでしょう。

自動的にプリペア文を開放するアクセス抽象化レイヤーを使っていない場合、サーバに不必要なプリペア文が大量に溜まらないように注意する必要があります。libpqのAPIを忠実にラップしているようなAPIの場合は、不必要なプリペア文が溜まり過ぎないように注意しましょう。

文字列型とBytea型

プリペアードクエリであっても、正しくデータ処理を行うにはエスケープ処理は必要になる場合があります。文字列型は文字列とBytea型はバイナリデータをテーブルに保存するデータ型です。PQexecPreparedでクエリパラメータは

const char * const *paramValues,

としてC言語文字列の配列として渡されています。C言語プログラマであれば「NULLがあると文字列が途切れてしまうのでは？」とピンと来たはずです。実際、

const int *paramLengths,

にNULLが渡されている場合（NULLで渡す事も許可されている）、PQescapeByteaConn関数でエスケープ処理しないと、NULL文字でデータが途切れてしまい不正なデータが保存されてしまいます。Bytea型フィールドではプリペアードクエリクエリであってもエスケープ処理は必要となることがあることを覚えておきましょう。文字列型も本来、データベース側ではNULL文字を検出してエラーを発生させるのですが、NULL文字の部分まで何も無かったように保存されてしまいます。libpqを使ってプリペアードクエリを実行する場合、paramLengthsは出来る限り指定するようにすると良いです。paramLengthsを設定するとバイナリデータをそのままByteaに渡しても正常に処理できます。PHPのpgsqlモジュールのプリペアードクエリAPIは、Bytea型のサポートより少し前に行われた為、パラメータが文字列以外のケースは考慮していませんでした。この為、Bytea型が上手く扱えない仕様になっています。

言い訳っぽくなりますが、比較的最近までバイナリセーフな型を持つ言語が内部のC言語APIがC言語文字列の制約を受ける問題は、問題だと考えられていませんでした。しかし、この動作は思わぬ脆弱性の原因となることもあり得ます。PHPでも5.3になるまで、Rubyも最近（今年）になるまでOSのファイル関数に依存するAPIを利用する場合、NULL文字で文字列が切断され、そのまま実行される問題がありました。（Perl/PythonはPHPより早く対応。メジャーなLLではRubyが一番遅かった）これはC言語プログラマであれば常識である動作ですが、C言語を知らないLLプログラマには常識的な動作とは言えない動作ですが、こうなっていました。

データベースの場合、途中でデータが切れてしまいデータが正しく保存できないのに、クエリがエラー無く実行できてしまう事も問題です。利用中の環境のAPIがNULL文字を処理していない場合はバグとしてレポートすると良いでしょう。PHPのpgsqlモジュールにはこの問題があります。確かバグレポートはまだだったと思います。早い者勝ちなので是非どなたかお願いします。（私が登録すると私が直すと思われてしまうので。。） PHPの文字列型はバイナリセーフなので長さを指定しておけばエスケープ処理なしでも挿入できるようになります。テキスト型のデータ型の場合、ASCIIエンコーディングでもNULL文字と受け付けず、エラーとなります。バイナリ型のByteaの場合、バイナリデータがそのまま利用できるようになります。互換性問題があるのでメンテナの時間があれば次のマイナーバージョンアップ時のアップデートになるでしょう。

ところで、MySQLでどうなるかNULL文字を混ぜてプリペアードクエリで挿入みると、テキスト型でも普通に挿入できてしまいました。これはコレでどうかと思いますが、仕様なので仕方ありません。PostgreSQLの場合、NULL文字は不正文字としてエラーとなります。これの意味する事はPHPのPDOなどの抽象化APIを使って切り替えている場合、バックエンドによって保存できるデータが異なる場合があるということです。これは「こうなる事がある」と覚えておき、必要ならばバリデーションでエラーにするしかありません。本当にNULL文字が必要なデータ（NULL区切りフォーマットのデータなど）以外、NULL文字が含まれる文字列は普通は必要ないです。何らかの攻撃を意図した文字列である可能性が高いです。NULLの有無をバリデーションするなら入力時にバリデーションすると良いでしょう。

配列型

SQLはテキストで記述するインターフェース（言語）です。配列型のデータも文字列として渡します。PostgreSQLのデータ型であれば、どれでも配列として処理できます。前述のBytea型を利用する場合はPQescapeByteaConn関数でのエスケープが必要です。文字列型を利用する場合はPQescapeStringConn関数を利用したエスケープも必要です。最近新しく追加されたJSON型の場合も文字列データ部分はエスケープ処理が必要です。エスケープしないとJSONフォーマットが崩れてしまい、エラーとなるのでエスケープが必須であることは仕方ないのです。

バイナリ型であるBytea型の場合、APIの実装によりエスケープ処理なしでも正しく取り扱う事も可能ですが、配列型はどうしようもありません。もちろんプログラムなのでAPIを作ってなんとかする、という力技もありますが、データ型を自由に定義できるPostgreSQLの場合、どのようなデータ型が作られ、どのようなエスケープ処理が必要になるのか分からない、という問題があります。例えば、hstore(Key Value型のデータ型）はまだ使った事がありませんが、テキスト型式に意味を持たせているのでエスケープ処理が必要でしょう。hstoreは以下のような表記でデータを挿入します。複数の値を1つのテキストとして、フィールドに挿入します。

k => v
foo => bar, baz => whatever
"1-a" => "anything at all"

テキストとして保存するのでは、検索が遅いのでは？と思うかも知れませんが、hstore型はGIN、GISTによるインデックスが可能なので、検索は高速に行えます。

識別子

PostgreSQLは識別子にダブルクオートで囲むと自由な文字列が使えます。非アスキーな文字、日本語なども使えます。libpqは識別子エスケープの為にPQescapeIdentifier関数を用意しています。

プリペアードクエリでも識別子がパラメータとなる場合がありますが、プリペアードクエリは識別子をパラメータ化する仕様にはなっていません。パラメータがユーザ起原である場合、SQLインジェクションが可能になります。識別子がパラメータの場合は、ユーザ起原であるかどうかに関わらず、APIを用いて全ての識別子をエスケープしてからクエリを実行すべきです。全てエスケープすればデータの起原にさかのぼってチェックしなくても、識別子をクエリの中に埋め込めます。

エスケープは基本中の基本

これまで説明したようにプリペアードクエリはエスケープ処理の多くを省略できるAPIですが、全てではありません。クエリエラーを防ぐためにも、安全性保証の為にもエスケープが必要な場合には、正しいエスケープ処理が欠かせません。ORM他のDBアクセス抽象化APIなど使っている場合には、独自の型式でパラメータとなる部分をプレイスホルダとして指定できるようにしている物もあります。しかし、識別子や配列をパラメータ化できるライブラリは今のところ聞いたことがありません。もし在るようでしたら教えて頂けると参考になります。

受け側のシステムが誤作動せずに受け入れられる正しい出力形式が決まっています。どのようなシステムであっても同じです。テキスト指向のインターフェースである場合、多くの場合、エスケープAPIを定めています。エスケープAPIが無くても、必要なエスケープ処理がマニュアルに記載されているはずです。例えば、MySQLは識別子のエスケープAPIは在りませんが、エスケープ処理はマニュアルに記載されています。

エスケープ処理は誤作動しない、安全なシステム構築には欠かせない仕組みです。バグのない、安全なシステムを作る為には出力先の入力仕様を正しく理解し、正しいエスケープ処理APIまたはエスケープ処理を用いてエスケープしなければなりません。エスケープ仕様を知る＝正しく安全な出力方法を知る、です。

サーバーのバックエンドを知る

PostgreSQLのクライアント側（libpq）の動作概要は分かりました。そこでサーバ側の動作に疑問に思ったかたも居るでしょう。PQprepareではデータ型としてOIDが指定できるが指定しなかったらどうなるのか？OIDを指定するとどんなメリットがあるのか？

PostgreSQLサーバはsrc/backend/commands/prepare.cで定義されているPrepareQueryでクエリを準備します。

/*
 * Analyze the statement using these parameter types (any parameters
 * passed in from above us will not be visible to it), allowing
 * information about unknown parameters to be deduced from context.
 *
 * Because parse analysis scribbles on the raw querytree, we must make a
 * copy to ensure we have a pristine raw tree to cache.  FIXME someday.
 */query = ameter types (any paramparse_analyze_varparams ((Node *) copyObject(stmt->query), 
  queryString,                                    
  &argtypes, 
  &nargs);

/*
 * Check that all parameter types were determined.
 */for (i = 0; i < nargs; i++)
{
    Oid argtype = argtypes[i];

    if (argtype == InvalidOid || argtype == UNKNOWNOID)
        ereport(ERROR,
                (errcode(ERRCODE_INDETERMINATE_DATATYPE),
                 errmsg("could not determine data type of parameter $%d",
                        i + 1)));
}

のparse_analyze_varparams()が呼ばれ、最終的にはparse_analyze_varparamsから呼ばれたexpression_tree_walker関数でデータ型（OID）が判別され、この関数に戻ってきます。これは予めOID分かっていて、指定できるならPREPAREを若干速く実行できる事を意味します。毎回PREPAREを強制されるライブラリを使っていない限り、PREPAREを何度も行わないので、正統派の使い方をしている場合ここでチューニングする意味は少ないでしょう。

プリペアされたクエリはsrc/backend/commands/prepare.cのExecuteQueryで実行されます。

ExecuteQuery(ExecuteStmt *stmt, const char *queryString, ParamListInfo params, DestReceiver *dest, char *completionTag)

この時、パラメータのタイプ（OID）が確認されます。実行時にも必ず確認されます。ExecuteQueryからEvaluateParamsが呼ばれ、そこでNodeの中からタイプ（OID)が取得され、パラメータとして指定したタイプ（OID)と比較されます。src/backend/parser/pase_coerce.cの

can_coerce_type(int nargs, Oid * input_typeids, Oid * target_typeids, CoercionContext ccontext)

で、

/* no problem if same type */ 
if (inputTypeId == targetTypeId) continue; 

/* accept if target is ANY */ 
if (targetTypeId == ANYOID) continue; 

/* accept if target is polymorphic, for now */ 
if (IsPolymorphicType(targetTypeId)) { 
    have_generics = true; /* do more checking later */ 
    continue; 
} 

/*
 * If input is an untyped string constant, assume we can convert it to
 * anything.
 */ 
if (inputTypeId == UNKNOWNOID) continue;

という形で処理され、結局データ型（OID）を指定しても、データ型を指定していない場合はほぼ変わりません。この後、クエリが実行されパラメータとして渡されたデータは必要であればターゲットのタイプに変換される事になります。データ型（OID）を指定しているとパラメータをパースするパフォーマンスが向上する可能性が残っていますが、パフォーマンス的にはデータ型（OID）を指定しても、しなくても、恐らくそれほど違いはないと思われます。データ型（OID)を明示的に指定する意味は速度的にはあまり無いようです。（このエントリを書くとき、実行の部分まで追いかなかったので違う場合は、ぜひ指摘をお願いします）

本来はベンチマークを取得して、どの程度の違いがあるか計測すべきですが時間の都合上省略させて頂きます。もしベンチマークをお持ちの方はぜひ教えてください！前にも書きましたがRuby pgはOID指定できるプリペアードクエリになっています。

まとめ

流石に最近ではプリペアードクエリさえ使っていれば安全で十分だとする乱暴な議論は聞かれなくなりましたが、エスケープなど必要ない、または無くすべき古い仕組みである、エスケープ不要のAPIを使えば知らなくても良い物、と考えている方も多いようです。

例えば、.NETのAPIはこのような考えに基づき設計されてるようで、文字列エスケープの関数自体がありません。この為、筆者がソースコードを検査した.NET業務アプリは自前でエスケープ関数を持っていました。正しく文字エンコーディングを考慮していない場合、自前のエスケープ関数は脆弱性の原因となり得ます。セキュリティ的には、恐らく、API設計者の意図に反して本末転倒な状態になっていると思います。フレームワークがエスケープAPIを持っていなくても、配列や自由な識別子の命名など、PostgreSQLの機能全てを使い切るには自前のエスケープ関数が欠かせません。

PostgreSQLに限らず、固定長でないテキストベースのインターフェースを利用する限り、エスケープ処理は確実に知っておくべき必須の基礎知識であり続けるでしょう。昔のようにバイナリベースのインターフェースに戻る事はないので、現時点では考えられないようなパラダイムシフトが起き、全てのテキストベースのインターフェースが無くなる、という状態になるまではエスケープの仕組みは過去の物にはなりません。このようなパラダイムシフトは起きる気配はないので、テキストベースのインターフェース（SQL、Javascript、HTML、XML、HTTP、SMTPなど）を利用している開発者は安心して正しいエスケープ方法（HTTP、SMTPなどの行ベースのインターフェースの場合はパラメータのバリデーション）を基礎知識として習得すると良いでしょう。

おまけ

PostgreSQL Advent Calender 2012 のエントリでORMについて議論があったようなのでオマケです。ORMはデータベースを単純なルックアップテーブルのように使うには非常に便利なツールですが、ツールにはそれぞれ向き不向きがあります。

アセンブラからC言語に替わった時はアドレスを意識しなくて済むようになり、C言語からJavaや.NET、LLに変わった時はメモリアロケーションを意識しなくて済むようになりました。世の中、どんどん便利になっていきます。ORMは多くの場合、SQLを意識しなくて済むようにするレベルには達しましたが、計算量やメモリ使用量を意識しなくても済むレベルには達していません。

実用的なアプリケーション開発の場合、ツール研究が主目的ではない事がほとんどです。ツールが不十分である場合、別の実用的なツールを使えば良いだけです。ORMが悪でもSQLが悪でもありません。どちらかだけを使わなければならないとする硬直した考えが「悪」ではないでしょうか？

ORM大好きな方が研究としてSQLオプティマイザ付きのORMを作る事には大賛成ですが、一般のシステム開発にORM「善」、SQL「悪」のような考え方を持ってくるのはナンセンスだと思いました。適材適所で使う、が正しいでしょう。ところで、”Window function ORM”をキーワードにGoogleで検索しても何もでてきません。PostgreSQLのWindow関数をサポートしている物はあるのでしょうか？

おまけ2

意外にこのエントリが読まれているようなので追記します。使っているAPIやフレームワークがプリペアードクエリしかサポートしてない！それでクエリが遅くて困る！という方も居ると思います。そういう場合の裏技としては「使い捨てのプリペアードクエリを使い、全てのパラメータをエスケープ処理してクエリを実行する」という方法もあります。そのような環境の場合、自前のエスケープ処理を行う必要がありますが、プリペアードクエリが遅くてどうしようもない、という場合にはこの方法で回避するしかありません。既にプリペアードクエリと呼べる使い方ではありませんが、それでクエリの実行が数倍（場合によってはもっと）速くなるのであれば、そういう使い方も必要悪でしょう。