動的SQLでPreparedStatement+プレースホルダを使用する

先日は[twitter:@bitter_fox]さんのJavaを創ろうでした。

Javaアドベントカレンダーで検索をかけるといろふアドベントカレンダーの方が先に引っかかってマジでビビった今日このごろ皆様どうお過ごしでしょうか。

動的SQLでPreparedStatement+プレースホルダを使用する

さて、本題に入ります。
最近PreparedStatement+プレースホルダを使用する件について非常に盛り上がっていたような気がします。

その中で静的なSQLならPreparedStatementが使用できるけれども、動的にSQLを組み立てる場合だとPreparedStatementが使用できないという話が入っていました。
「プリペアードクエリが基本だけど、動的に SQL を組み立てる場合もあるから、そういう場合に備えてエスケープも知っておいたほうがいいかも」

そんなわけないでしょうという話なのですが、実際にこれで納得してしまう人が居るというのがセキュリティ上の問題なのかなぁと思います。

以下の様なクラスを作成します。
https://gist.github.com/anonymous/7955143

以下のように使用します。

SQLBuilder builder = new SQLBuilder("select * from employee where deleted = '0'");
builder.append("employee_id =?", param("20141214",SQLTypes.VARCHAR));
PreparedStatement pstmt = builder. prepareAndSetParameters(conn);
pstmt.......

ほら、普通に動的SQLだけれどもプレースホルダが使用できた。
既存の処理も特に問題なく置き換えられるレベルだと思うのですが、いかがでしょうか？

エスケープ処理を知っていれば安全に作れる？

ちょっとSQLのエスケープについて学者さんたちが議論していて、自分たちはエスケープの方法をきちんと知っているけれども〜みたいな論調になっていたのが気になりました。
知っているかと実際に問題なく適用できるかというのは100億光年ほども離れていると思っていて、例えば、Oracleはエスケープ文字を環境設定で自由に変更することが出来ます。
http://www.shift-the-oracle.com/sqlplus/system-variable/escape.html

特定の環境で適切にエスケープできたものが他の環境でそのまま使えるとは限らないわけです。
例えば以下の記事ではその観点がさっくりと抜けています。
http://www.tokumaru.org/d/20080601.html

Oracleなんかはマニュアルに書いてないけれども、実は実装されていて変更できるパラメーターみたいなものが多数あるのでこの機能もいつから使えたのか・・・・・

本当に安全なアプリケーションを作りたいのならばプレースホルダを使いましょう。
プレースホルダが使えない実装になっていたらどれだけ便利に見えたとしてもその実装は実運用では使用しない方がいいです。

まとめ

PreparedStatementとプレースホルダを使用しましょう。
動的SQLであるというのは言い訳になってない。
今回のサンプルコードのエレガントな実装がほしいのならばQuerydslが良いと思います。