SQLiteのクォートにまつわる奇妙な仕様

SQLiteでは、ISO SQL標準同様に、文字列リテラルはシングルクォートで囲み、識別子をクォートする場合は、ダブルクォートで囲むことになっています。

'foo' : 文字列リテラル
"foo" : 識別子（テーブル名、列名等）

しかし、マニュアルによると、SQLiteのクォーティングには例外があります。それを実例で紹介しましょぅ。まずは、実験の準備として、列 a だけを持つテーブル a を作成します。

$ sqlite3 test.db
sqlite> CREATE TABLE a(a integer);
sqlite> INSERT INTO a VALUES(1);
sqlite> SELECT * FROM a;
1
sqlite>

続いて、以下を実行します。実行結果はどうなるでしょうか?

sqlite> SELECT 'a', "a", [a], `a`, "aa" FROM 'a'

これ、FROM 'a' のところが文法違反に見えますよね。FROMの後には表の名前が続くはずです。また、"aa" は列名だとするとそのような列は存在しないので、こちらもエラーになるはずです。
しかし、SQLiteの場合、上記SQL文はエラーにならず、以下の結果となります。

sqlite> SELECT 'a', "a", [a], `a`, "aa" FROM 'a';
a|1|1|1|aa
sqlite>

これはどういうことでしょうか?その答えは、SQLiteのマニュアルにあります。

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.

SQLite Query Language: SQLite Keywords より引用

すなわち、シングルクォートで囲まれたキーワードが、文字列リテラルが許可されていない箇所に置かれている場合、識別子として認識されます。上記の FROM 'a' の 'a' がこれに該当します。
また、ダブルクォートで囲まれたキーワードであるのに、該当する列名等がない場合、文字列リテラルとして認識されます。上記の "aa" がこれに該当します。

SQLite はなんてすごいんだ! 僕達の気持ちをここまでくんでくれるなんて!!

というのはもちろん冗談で、こんなのは余計なお世話としか言いようがありません。開発者が間違った場合でもエラーにならず、見つけにくいバグの原因になりそうです。

ちなみに、SQLiteは、識別子を [] で囲む方式（MS SQL風）や、バッククォートで囲む方式（MySQL風）もサポートしていますが、これらの場合は、自動的に文字列リテラルとして認識されることはないようです。だったらこれらを使えば…という意見もありそうですが、ISO標準にはない書き方なので、移植性がそこなわれますよね。
ということで、SQLiteを使う場合、もしクォートする必要がなければ、識別子はクォートしない方が無難なのではないでしょうか? そうすれば、勝手に文字列リテラルとして認識されることもないし、移植性も損なわれません。

なお、この問題に起因するセキュリティ上の問題はないか考えてみましたが、思いつきませんでした。思いついた方があれば、ぜひ教えてください。

追記(20:35)

この問題が原因で脆弱性となる例を考えましたので紹介します。
ログイン処理で、「ログイン状態を保存」をトークンを用いて実装していたとします。SQL文は下記となります（SQLインジェクション対策はされていると想定）。

SELECT * FROM users WHERE "token" = $token

クッキーなどにセットされたトークンが、列 token に一致するものがあれば、そのユーザで自動ログインするという想定です。
しかし、列 token のつづりを以下のように間違えていたとします。

SELECT * FROM users WHERE "tokem" = $token

すると、通常はランダム文字列であるトークンとして、固定の文字列 'tokem' を指定するとログインできてしまうことになります。"tokem"という列が存在しないため、WHERE句は 'tokem' = $token と等価だからです。

し・か・し・な・が・ら、この例では正常系がまともに動かないため、テストさえしていれば、この脆弱性が入ったままリリースされることはないでしょう…ということで、あまり現実的ではない…しかし、絶対にないとも言い切れない…脆弱性の例でした。

WordPressのプラグインNextGEN GalleryのSQLインジェクション脆弱性について検証した

エグゼクティブサマリ

WordPressのプラグインNextGEN Gallery for WordPress 2.1.79未満にはSQLインジェクション脆弱性があり、早急なバージョンアップを推奨する。当該脆弱性は潜在的にWordPressの内部メソッドwpdb::prepareの仕様上の問題が原因であり、他のプラグインにも類似脆弱性の残存の可能性がある。このため、この問題の影響を受けない安全な実装方針を示した。

はじめに

Sucuriブログに、NextGEN Gallery for WordPressのSQLインジェクション脆弱性が報告されました。脆弱性のサマリは下記となります。

対象バージョン	NextGEN Gallery for WordPress 2.1.79未満
影響	SQLインジェクションによる情報漏えいなど
攻撃の認証要否	不要
攻撃の難しさ	困難
対策	プラグインのアップデート (2.1.79にて修正)

脆弱なコードと検証

脆弱性はMixin_Displayed_Gallery_Queriesクラスのget_term_ids_for_tagsメソッドにあります。当該メソッドを下記に引用します。このメソッドは、タグを$tags配列として受け取り、タグ検索のSQL文を実行します。タグ検索のSQL文のIN句は、下記の※1（赤字）にて生成しています。エスケープなどがなされていないので心配になりますが、タグ中の引用符はHTMLエスケープ（SQLエスケープではない）され、バックスラッシュはフィルタリングで除去されるので、これら記号文字によるSQLインジェクション攻撃はできません。

function get_term_ids_for_tags($tags = FALSE)
{
    global $wpdb;
    // If no tags were provided, get them from the container_ids
    if (!$tags || !is_array($tags)) {
        $tags = $this->object->container_ids;
    }
    // Convert container ids to a string suitable for WHERE IN
    $container_ids = array();
    if (is_array($tags) && !in_array('all', array_map('strtolower', $tags))) {
        foreach ($tags as $ndx => $container) {   // ※1 タグ検索の IN 句の生成
            $container_ids[] = "'{$container}'";
        }
        $container_ids = implode(',', $container_ids);
    }
    // Construct query
    $query = "SELECT {$wpdb->term_taxonomy}.term_id FROM {$wpdb->term_taxonomy}\n
                  INNER JOIN {$wpdb->terms} ON {$wpdb->term_taxonomy}.term_id = {$wpdb->terms}.term_id\n
                  WHERE {$wpdb->term_taxonomy}.term_id = {$wpdb->terms}.term_id\n
                  AND {$wpdb->term_taxonomy}.taxonomy = %s";
    if (!empty($container_ids)) {
        $query .= " AND ({$wpdb->terms}.slug IN ({$container_ids}) OR {$wpdb->terms}.name IN ({$container_ids}))";
    }
    $query .= " ORDER BY {$wpdb->terms}.term_id";
    $query = $wpdb->prepare($query, 'ngg_tag');   // ※2
    // Get all term_ids for each image tag slug
    $term_ids = array();
    $results = $wpdb->get_results($query);
    if (is_array($results) && !empty($results)) {
        foreach ($results as $row) {
            $term_ids[] = $row->term_id;
        }
    }
    return $term_ids;
}

脆弱性の原因

WordPressのプラグインは、一般にWordPressがフレームワークとして提供するwpdbクラスのprepareメソッドを利用してSQL文を実行します。wpdb::prepareは、プレースホルダとしてsprintfに似た %s や %d を用いる独特の構文になっています。
では、タグとして%sを指定したら、何かおかしなことができるのではないでしょうか。やってみましょう。以下は、タグとして aaa%s を指定した場合に生成されるSQL文です。

$query string SELECT wp_term_taxonomy.term_id FROM wp_term_taxonomy
                  INNER JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
                  WHERE wp_term_taxonomy.term_id = wp_terms.term_id
                  AND wp_term_taxonomy.taxonomy = %s 
                  AND (wp_terms.slug IN ('aaa%s') OR wp_terms.name IN ('aaa%s')) ORDER BY wp_terms.term_id

しかし、これは実行時にエラーになります。prepareメソッドからPHPのvsprintf関数が呼び出される際に、%sが3個（上記赤字）あるのに、それに対する値が一つ（'ngg_tag'）しかないためです。
外部からのパラメータ指定で、SQL文生成のエラーになるという時点で、この箇所にバグがあることになります。何か攻撃の糸口はなんでしょうか。

実は、上記に関連して、以下の記事を書いたことがあります。

書式文字列によるSQLインジェクション攻撃例

上記の記事の中で、%s書式が余計に出てくるためにエラーになる状況について言及しており、そのエラー回避のテクニックについて、以下のように書いています。

 実はこのエラーを回避する方法があります。%sの代わりに、%1$sを指定するのです。これは、書式文字列上の位置に関わらず1番目のパラメータを受けるという意味です。

ということで、aaa%sの代わりに、aaa%1$s を指定してみましょう。今度はバインドまで行われ、生成されるSQL文は下記となります。

$query string SELECT wp_term_taxonomy.term_id FROM wp_term_taxonomy
                  INNER JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
                  WHERE wp_term_taxonomy.term_id = wp_terms.term_id
                  AND wp_term_taxonomy.taxonomy = 'ngg_tag'

                  AND (wp_terms.slug IN ('aaangg_tag') OR wp_terms.name IN ('aaangg_tag')) ORDER BY wp_terms.term_id

エラーにはならない代わりに、とりたてて攻撃もできなさそうなSQL文ができてしまいました。 それでは、次に、%1$%s を指定してみます。バインド前のSQL文は下記となります。

$query string SELECT wp_term_taxonomy.term_id FROM wp_term_taxonomy
                  INNER JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
                  WHERE wp_term_taxonomy.term_id = wp_terms.term_id
                  AND wp_term_taxonomy.taxonomy = %s
                  AND (wp_terms.slug IN ('aaa%1$%s') OR wp_terms.name IN ('aaa%1$%s'))

これがwpdb::prepareメソッドの中で、%sをシングルクォートで囲む処理が加わります。これは、SQL文の文字列リテラルはシングルクォートで囲むルールに対応するためです。この結果、SQL文は以下のように変形されます。最後の行のみを示します。

AND wp_term_taxonomy.taxonomy = '%s' AND (wp_terms.slug IN ('aaa%1$'%s'') OR wp_terms.name IN ('aaa%1$'%s''))

これがPHPのvsprintf関数で処理されるのですが、ここで上記の %1$'%s (上記赤字)に注目します。実は、これ全体で、vsprintfの書式になっています。1$は1番目のパラメータに対応するという意味、'%はパディングに%を用いるという意味です。ただし、桁指定子がないため、実際にはパティングの指定があっても何もしません。
この結果、バインド結果は下記になります。

AND wp_term_taxonomy.taxonomy = 'aaangg_tag' AND (wp_terms.slug IN ('aaangg_tag'') OR wp_terms.name IN ('aaangg_tag''))

なんということでしょう! シングルクォートの一つがパティング指定と解釈されたため、シングルクォートの対応が狂っています。SQLインジェクション攻撃ができそうな雰囲気が漂い始めました。
それでは、いようよ攻撃です。タグとして以下の文字列を指定します。

aaa%1$%s)) or 1=1#

生成されるSQL文（バインド前）は下記となります。

AND wp_term_taxonomy.taxonomy = %s AND (wp_terms.slug IN ('aaa%1$%s)) or 1=1#') OR wp_terms.name IN ('aaa%1$%s)) or 1=1#')) 

wpdbクラスのprepareメソッドにより%sが'%s'とクォートされた結果

AND wp_term_taxonomy.taxonomy = '%s' AND (wp_terms.slug IN ('aaa%1$'%s')) or 1=1#') OR wp_terms.name IN ('aaa%1$'%s')) or 1=1#'))

そして、バインド後は下記のとおりです。

AND wp_term_taxonomy.taxonomy = 'ngg_tag' AND (wp_terms.slug IN ('aaangg_tag')) or 1=1#') OR wp_terms.name IN ('aaangg_tag')) or 1=1#')) ORDER BY wp_terms.term_id 

赤字で示した部分が文字列リテラルをはみだし、SQL文の一部として解釈されています。SQLインジェクション攻撃の成功です。

脆弱性は誰のせい?

この問題は、NextGEN Gallery の脆弱性して報告されていますが、私は、wpdbクラスの仕様に問題があるように感じました。
一般に、プレースホルダの実装では、SQL文を構成する文字列リテラル中に「たまたま」プレースホルダ記号（? や :foo など）があってもプレースホルダとは解釈せず、文字列リテラルの一部であると解釈します。たとえば、PDOでは、下記の :user はプレースホルダとは解釈されません。

$db->prepare("SELECT * FROM employee WHERE user=':user'");

ところが、wpdbは、下記の %s がプレースホルダと解釈されることになります。

$wpdb->prepare("SELECT * FROM employee WHERE user='user%s'");

上記の場合、バインド値が'tanaka'の場合WHERE句は、WHERE user='user'tanaka'' となり、「tanaka」が文字列リテラルを「はみだす」危なっかしい状態になります。しかし、下記ではそのような現象は起こりません。

$wpdb->prepare("SELECT * FROM employee WHERE user='%s'");

こうなる理由は、wpdb::prepareメソッドの内部で以下の置換処理が行われているからです。

$query = str_replace( "'%s'", '%s', $query ); // 間違ってプレースホルダをクォートしている場合に対処
$query = str_replace( '"%s"', '%s', $query ); // 同上（ダブルクォートの場合）
$query = preg_replace( '|(?<!%)%f|' , '%F', $query ); // ロケールの影響を避けるため浮動小数点数をクォートする
$query = preg_replace( '|(?<!%)%s|', "'%s'", $query ); // 文字列のプレースホルダをクォートするが、%%sはクォートしない

このあたり、いかにもアドホックで間違いが入りやすい状態と考えます。wpdb::prepareはSQL文をパースしていないわけで、同種の問題が他のプラグイン等にも潜在的に残っている可能性が高いと予想します。

対策

プラグイン利用者側の対策は、当該プラグイン（NextGEN Gallery for WordPress）を最新版（2.1.79以降） にバージョンアップすることです。
NextGEN Gallery for WordPress 2.1.79内部での対応は、tag指定された文字列中の % を %% と二重にすることで対策しています。これは書式文字列攻撃としての対策で、局所的には妥当なものと考えます。

いったんまとめ

Sucuriが発見した攻撃手法は極めて巧妙です。外部からはシングルクォートがフィルタリングされてSQL文内に指定できないところ、wpdb::prepareが%sの前後に挿入するシングルクォートを攻撃に使っています。しかも、挿入される2つのシングルクォートのうち一つを、vsprintfの書式（パディング指定）として解釈させることにより取り除いている点が巧妙です。
前述のように、この脆弱性は、WordPressの内部メソッドwpdb::prepareの潜在的な問題をNextGEN Galleryプラグインが踏み抜いてしまったものと考えます。

wpdb::prepareはどのように使えばよいか?

前述のように、この問題はwpdb::prepareメソッドの仕様（あるいは実装）がイケテナイ点が潜在的な原因になっていますが、WordPressのプラグイン等を使う場合は、このメソッドを使わないわけにはいきません。では、プラグイン開発者はどのようにwpdb::prepareメソッドを使えばよいでしょうか?
その答えは、「SQL文中に外部由来の値を混ぜない」という原則を徹底することです。
そんなこと言っても、IN句の中身は可変数なのでどうすればいいのという疑問が出てきますが、Drupalが採用している方法が参考になります。
Drupalでは、IN句の中身を生成する際に、可変個のプレースホルダを内部的に生成しています。それを真似ると、今回のケースでは、以下のようなSQL文を生成すればよかったことになります。

BEFORE : wp_terms.slug IN ('foo', 'bar', 'baz')
AFTER : wp_terms.slug IN (%s, %s, %s)

これに伴って、バインド値も調整することになります。このような実装にすることにより、SQL文中に外部からプレースホルダを指定させられる攻撃（一種の書式文字列攻撃）の防止を含め、SQLインジェクション脆弱性がないことを明確にできます。

まとめ

WordPressのプラグインNextGEN Galleryに対する巧妙なSQLインジェクション攻撃について説明しました。この問題の原因は、wpdb::prepareメソッドの潜在的なイケテナサが原因で、かつNextGEN Galleryの実装に外部から%が指定される場合の考慮漏れにあります。NextGEN Galleryの実装方針にも改善の余地があります。
前述のように、wpdb::prepareメソッドの潜在的な問題が原因ですので、他のWordPressプラグインにも同種の問題が残っている可能性があります。今後、可能な範囲で調査してみたいところです。