JDBC には PreparedStatement クラスが用意されていて、SQL 文の一部をパラメータ化することができる。
パラメータ部分のエスケープ処理は JDBC ドライバが行ってくれるので、SQL インジェクション対策として最近では必須になりつつあるこのクラス。しかし、like 演算子を使う場合は、ちょっと注意が必要だ。
Lv 1
ネットで検索してみると、失敗例としてひっかかってくるのがこのパターン。
Statement stmt = conn.prepareStatement("select * from items where name like '?%'");
stmt.setString(1, "hatena");
ResultSet rs = stmt.executeQuery();name が "hatena" で始まる行を選択しようとしているわけだけど、これだと例外が発生する。
PreparedStatement に渡す SQL 文ではパラメータを ? で表す。でも、その値が文字列であっても、引用符はつけないきまりになっている。上のパターンでは、% をつけようという気持ちが強すぎたのか、? を引用符の中に入れてしまっている。
ちなみに、% の部分は文字列として扱われないとダメなので、単純に引用符を外しても、構文エラーとなってやはり例外が発生する。
Lv 2
ネットで検索してみると、上のパターンをやっちゃった人に対して、こんなパターンでいけるよというアドバイスがされてたりする。
Statement stmt = conn.prepareStatement("select * from items where name like ?");
stmt.setString(1, "hatena%");
ResultSet rs = stmt.executeQuery();Statement stmt = conn.prepareStatement("select * from items where name like ? || '%'");
stmt.setString(1, "hatena");
ResultSet rs = stmt.executeQuery();この 2 つは、たいていの場合はうまくいく。でも、完璧じゃない。検索するのが "hatena" ならいいけど、"果汁_1%" で始まるのを検索したい場合は、まずい。"_" と "%" は、like ではワイルドカードとして扱われる特別な文字だからだ。
Lv 3
なんだよ、エスケープすればいいんだろ、ということで次に出てくるのがこのパターン。
Statement stmt = conn.prepareStatement("select * from items where name like ?");
stmt.setString(1, "果汁\\_1\\%%");
ResultSet rs = stmt.executeQuery();
Lv 4
というわけで、RDBMS に依存しないように書いてみる。
Statement stmt = conn.prepareStatement("select * from items where name like ? {escape '\\'}");
stmt.setString(1, "果汁\\_1\\%%");
ResultSet rs = stmt.executeQuery();うわ! なんか SQL の後ろに変なのついてる! ……という感じだけど、これは、JDBC の仕様に載ってる、れっきとした「エスケープ構文」。こう書くことで、エスケープに使う文字を指定できる。
[2010-12-20 追記] エスケープ文字として '\\' つまり「\」を指定しているので、入力文字列中の「_」「%」および「\」に対して、前に「\」を付ける必要がある。例えば、「果汁_1%の値段は\100」で始まるデータを検索したい場合は次のようになる。
stmt.setString(1, "果汁\\_1\\%の値段は\\\\100%");Java の文法の話になってしまうけど、文字列リテラル・文字リテラルに対するエスケープにも「\」が使われるため、重ねて '\\' と表記しなければならない点にも注意が必要そう。例えば
stmt.setString(1, "果汁\_1\%%");と書いてしまうと、コンパイルされた時点で「果汁_1%%」というStringになってしまう。
いっそ「\」じゃなくて「@」とかにした方がいいのかもしれない。その場合はもちろん「入力文字列中の『_』『%』および『@』に対して、前に『@』を付ける」というエスケープを行って、SQL 中には "like ? {escape '@'}" と書くことになる。[追記ここまで]
ただし、JDBC ドライバによってはこの記法に対応していない、みたいな記事も見た。なのでもうひとつ候補を挙げる。それは、DatabaseMetaData を使うやり方。
DatabaseMetaData meta = conn.getMetaData();
Statement stmt = conn.prepareStatement("select * from items where name like ?");
stmt.setString(1, escape("果汁_1%", meta.getSearchStringEscape()), "%");
ResultSet rs = stmt.executeQuery();ここでしれっと書いてる escape(String s, String escape) メソッドは、自前で作る必要がある。文字列 s を順番に見ていって、"_" か "%" が出てきたら escape をその前に入れるやつ。こうすれば、使っている RDBMS に合わせたエスケープができる。
[2011-09-05 追記] 「"_" か "%" が出てきたら escape をその前に入れる」って書いてしまったけど、escape 自体が出てきたときにも「escape をその前に入れる」必要がある。例えばエスケープ文字として「\」を使うのであれば、「\」自体の前にも「\」を入れなければならない。[追記ここまで]
Lv 5...?
と、ここまで書いてきたけど、実はあんまり自信がなかったりする。うーんうーん。
