Hatena::Grouprubyist

Rubyで遊ぶよ

 | 

2009-05-04

SQLiteのエスケープとlike演算子についてのメモ

06:18

以下はあくまで SQLite を使い始めて間もない自分があれこれ試した上でのメモで、仕様やソースコードをちゃんと読んだわけではない。

Ruby について書くが、他の言語にも類推して置き換えられると思う。


プレースホルダーの使い方

まず、SQLitesql 文に任意の値を使う場合、

db.execute("SELECT * FROM mytable WHERE data = ?", foo)

と、SQLite のプレースホルダーを使うこともできるし、

db.execute("SELECT * FROM mytable WHERE data = '#{foo}'")

と、String を逐一作ることもできる。

プレースホルダーを指定した場合は、明示しなくても適切にエスケープ処理が施される。つまり、1番目の例は、

db.execute("SELECT * FROM mytable WHERE data = #{"'"+foo.gsub(/'/,"''")+"'"}")

みたいなのと同じになる。(ちょっと調べたところ、NULL 文字のエスケープも必要らしい↓)

ちなみに SQL 文では、文字リテラル中 (シングルクオートの間) のシングルクオートはシングルクオート2つにエスケープするのはちょっとびっくりした (シングルクオート2つでクエリを書くと、シングルクオート1つのものが挿入されるたりマッチする)。

また、プレースホルダーのところにはちゃんとクオート (に相当するもの?) が入るらしく、'?' とか '?hoge' などとすることはできない。


like演算子の特殊文字のエスケープ

次に、like 演算子で検索する場合を考える。

db.execute("SELECT * FROM mytable WHERE data LIKE ?", foo)

とした場合、プレースホルダーは上に書いた規則で変換されるだけで、ワイルドカードの % と _ は自分でエスケープしないといけない。

つまり、「% という文字」にマッチさせたくて % と書いても、それは「ゼロ文字以上の任意の文字」という特殊文字として解釈されてしまう。これを防ぐために、「% という文字」にマッチさせるエスケープシークエンスが必要になる。

MySQL などでは、like 演算子のエスケープはデフォルトでバックスラッシュであるらしいのだが、SQLite ではデフォルトでは用意されていないっぽい。

なので、一つの方法としては

db.execute("SELECT * FROM mytable WHERE data LIKE ? ESCAPE '$'", foo.gsub(/[$%_]/){|s| '$'+s} + '%')

と明示的にエスケープ文字を指定することができる。この場合、ワイルドカードとしての % や _ を含まないクエリが出来上がる。(この例だと前方一致検索になる)

ちなみに、$ を使う必要は無いが、$ や @ がよく使われるらしい。


この方法を使うのには大きな問題点がひとつある。

like 演算子とプレースホルダーや escape 演算子を組み合わせると、例えインデックスをしてあったとしても、クエリがものすごく遅くなるということだ。(おそらくインデックスが使われていないのだろう。インデックスのやり方によってはうまくいくのかもしれないが、自分が試した collate nocase を使う方法だと明らかに遅くなった)

他はどうか知らないが、SQLite に限って言えば、like 演算子 + プレースホルダー、または like 演算子 + escape 演算子は鬼門ということになる。


つまり

以上のことを考えると、高速 like 検索を行う場合は、

  1. insert の前に % と _ を自前でエスケープしておく。
  2. プレースホルダーは insert 文では使ってもいいが select 文では使わない。(ただし、同じエスケープ関数を使ったほうがデータがマッチしなくて困ることがないのでいいかもしれない)
  3. インデックスを collate nocase で作る。
  4. select 文の部分では自前でシングルクオート (など) をエスケープする。
  5. 最後に、出力結果をアンエスケープする。

という手順を踏むのが良いらしい。(もちろん、like 検索を行わないとわかっている場合はこの限りではない)

ESCAPE = {'$'=>'$$', '%'=>'$%', '_'=>'$_'}
UNESCAPE = {'$$'=>'$', '$%'=>'%', '$_'=>'_'}
def sql_like_escape(word)
    return word.to_s.gsub(/[$%_]/){|s| ESCAPE[s]}
end

def sql_like_unescape(word)
    return word.gsub(/\$[$%_]/){|s| UNESCAPE[s]}
end

def sql_escape(word) # NULL 文字のエスケープがよくわからん。gsub(/[[:cntrl:]]/,'') かな?
    return word.gsub(/'/,"''")
end

db = SQLite3::Database.new('mydata.db')

db.execute("CREATE TABLE mytable (data COLLATE NOCASE);")

db.execute("INSERT INTO mytable (?);",sql_like_escape(foo)) # × データ数回

db.execute("CREATE INDEX mytable_idx ON mytable (data);")

db.execute("SELECT * FROM mytable WHERE data LIKE '#{sql_escape(sql_like_escape(bar))+'%'}'") { |row|
  row.each { |s|
    puts sql_like_unescape(s)
  }
}

など (前方一致検索の場合)。このときも $ である必要はなく、\ でも @ でもなんでもいいのだが、バックスラッシュだと他の部分のエスケープと重なったときに見にくくなるのでこうした。


これで SQL インジェクション対策はどうなんだろ? たぶん大丈夫だけどよくわからん。


結局昨日のやつのエスケープ処理は上のような感じになりましたとさ。

TutuTinyTutuTiny2019/04/01 22:56Today anybody has an Android device. This has lead to developing a millions of apps that provide servises such as mobile games, development, entertainment and others. To download any of these applications a user must get an account at either Google Play or Apple Store. But not all apps are available there as they don't meet the official rules. To avoid this a new app was developed - TutuApp. This helper works both on Android and iOS run smartphones and tablets and it has the biggest store of apps and games. We name a few: Minecraft, Fortnite, Pokemon and many others. They come with zero ads and locked content! Whant to know more? Visit <a href=https://tutuapp-app.com>https://tutuapp-app.com</a> to download TutuApp for free on your Android or iOS smartphone. Installation instructions for the app are available there.

 |