LIKE句のSQLインジェクション

先日、例外情報のトラッカーを見回していたら、目を引くスロークエリーログを発見しました。SELECT ... WHERE ... LIKEといったクエリーのLIKE句にたくさんのパーセントが付いているのです。この部分はユーザが入力した部分なのは明らかで、最初私はSQLインジェクションを疑いました。

[3.92 sec] SELECT ... WHERE (profiles.email LIKE '%64%68%6f%6d%65%73@%67%6d%61%69%6c.%63%6f%6d%') LIMIT 10

コードを見てみると、ここで解釈されるメタ文字(%や_や\)のチェックをまったくせずにユーザが指定した文字列をLIKE句に直接使っていることがわかりました。

def self.search(term, options = {})
  limit = (options[:limit] || 30).to_i
  friends = options[:friends] || []
  with_orgs = options[:with_orgs].nil? ? false : options[:with_orgs]

  if term.to_s.index("@")
    users = User.includes(:profile)
                .where("profiles.email LIKE ?", "#{term}%")
                .limit(limit).to_a
  else
    users = user_query(term, friends: friends, limit: limit)
  end

  ...
end

これは本格的なSQLインジェクションとは言えませんが、この類のインジェクションの影響について考えました。スローログに記録が出るぐらいなので、この種の病的なクエリーは間違いなく何らかのパフォーマンス的なインパクトがあります。それはどのくらいのコストなのでしょうか？

データベースの専門家に聞いてみたところ、クエリー内のどこにワイルドカードがあるかによってそのコストが変わる、ということでした。%がクエリーの真ん中にある場合、データベースは先頭の方の文字でインデックスをチェックできます。%がクエリーの先頭にある場合、インデックスはまったく使われなくなるでしょう。この考え方を知って、%の位置を色々に変えていくつかのクエリーをテストデータベースで実行してみることにしました。

mysql> SELECT 1 FROM `profiles` WHERE `email` LIKE "chris@github.com";
1 row in set (0.00 sec)

mysql> SELECT 1 FROM `profiles` WHERE `email` LIKE "%ris@github.com";
1 row in set (0.91 sec)

mysql> SELECT 1 FROM `profiles` WHERE `email` LIKE "chris@github%";
1 row in set (0.00 sec)

mysql> SELECT 1 FROM `profiles` WHERE `email` LIKE "%c%h%r%i%s%@%g%i%t%h%u%b%.%c%o%m%";
21 rows in set (0.93 sec)

これにより、サニタイズしていないユーザが入力した文字列を指定したLIKE句は、パフォーマンスインパクトがある可能性が考えられますが、Ruby on Railsのアプリケーションではどう対処すべきでしょうか？Webを検索してみましたが、ぴったりな方法は見つかりませんでした。LIKEのメタ文字列をエスケープしているRailsヘルパーが無いようなので、書いてみました。

module LikeQuery
  # クエリーの`LIKE`句の中で特別な意味を持つ文字群
  #
  # `%` は複数の文字を意味するワイルドカード
  # `_` は1文字を意味するワイルドカード
  # `\` はメタ文字をエスケープするのに使われる
  LIKE_METACHARACTER_REGEX = /([\\%_])/

  # `LIKE`のメタ文字を何で置き換えるか。各メタ文字の最初にリテラルの
  # バックスラッシュを追加したい。String#gsubは2番目の引数として自身の
  # 置換する文字列をくくるのにバックスラッシュを使うので、バックスラッシュ自身を
  # エスケープするのに二重にする必要があります。
  LIKE_METACHARACTER_ESCAPE = '\\\\\1'

  # Public: SQLクエリーの`LIKE`句内で特別な意味を持つ文字をエスケープする
  #
  # value - エスケープする文字列
  #
  # 文字列を返す
  def like_sanitize(value)
    raise ArgumentError unless value.respond_to?(:gsub)
    value.gsub(LIKE_METACHARACTER_REGEX, LIKE_METACHARACTER_ESCAPE)
  end

  extend self

  module ActiveRecordHelper
    # 指定したプレフィックスの値を問い合わせる
    #
    # column - 問い合わせる列
    # prefix - 問い合わせに使うプレフィックスの値
    #
    # ActiveRecord::Relationを返す
    def with_prefix(column, prefix)
      where("#{column} LIKE ?", "#{LikeQuery.like_sanitize(prefix)}%")
    end

    # 指定したサフィックスの値を問い合わせる
    #
    # column - 問い合わせる列
    # suffix - 問い合わせに使うサフィックスの値
    #
    # ActiveRecord::Relationを返す
    def with_suffix(column, suffix)
      where("#{column} LIKE ?", "%#{LikeQuery.like_sanitize(suffix)}")
    end

    # 指定した文字が含まれる値を問い合わせる
    #
    # column    - 問い合わせる列
    # substring - 問い合わせに使う部分文字列の値
    #
    # ActiveRecord::Relationを返す
    def with_substring(column, substring)
      where("#{column} LIKE ?", "%#{LikeQuery.like_sanitize(substring)}%")
    end
  end
end

ActiveRecord::Base.extend LikeQuery
ActiveRecord::Base.extend LikeQuery::ActiveRecordHelper

それから全てのLIKEクエリーを試して確認し、今回のようなケースを11修正しています。こういったクエリーに対するリスクは相対的に下がりました。意味のない方法とはいえ、ユーザーがクエリーの目的に反したことができたということです。私たちにとってそれは、つまりDoS攻撃という意味合いを持っていたわけです。革新的なところは何もない上に、新しい脆弱性のかたちというわけでもありませんが、常に注意を払うべきものです。3秒かかるクエリーはかなり大きなパフォーマンスの問題になる可能性がありますし、アプリケーションレベルのDoS脆弱性は、軽減しておく必要があります。

更新 : 複数の人からRails 4.2のActiveRecordでは、sanitize_sql_likeヘルパーが、LIKE句のサニタイズのために提供されているという指摘がありました。