以下はあくまで SQLite を使い始めて間もない自分があれこれ試した上でのメモで、仕様やソースコードをちゃんと読んだわけではない。
Ruby について書くが、他の言語にも類推して置き換えられると思う。
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 演算子で検索する場合を考える。
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 検索を行う場合は、
という手順を踏むのが良いらしい。(もちろん、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 インジェクション対策はどうなんだろ? たぶん大丈夫だけどよくわからん。
結局昨日のやつのエスケープ処理は上のような感じになりましたとさ。