Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
3年以上前投稿 修正あり

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

GitHubのエンジニアがデータベースのスローログから見つけた、LIKE句を使ったクエリーのパフォーマンス問題につながる可能性のある文字列。問題の仕組みと、それを回避するためのスニペット。

原文
LIKE injection - GitHub Engineering (English)
翻訳依頼者
D98ee74ffe0fafbdc83b23907dda3665
翻訳者
D98ee74ffe0fafbdc83b23907dda3665 doublemarket
翻訳レビュアー
B5aa4f809000b9147289650532e83932 taka-h F14f7aec988d2bd9a998b805b6ba4c9d kwatch
原著者への翻訳報告
未報告


先日、例外情報のトラッカーを見回していたら、目を引くスロークエリーログを発見しました。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句のサニタイズのために提供されているという指摘がありました。

次の記事
MySQL 5.0から5.7へ直接'インプレース'アップグレードする
前の記事
マイクロサービス時代のHAProxy

Feed small 記事フィード

新着記事Twitterアカウント

新着翻訳リクエストTwitterアカウント