SQLインジェクション対策として PreparedStatement を使うのはいいけど、同時に like を使うときには注意しないといけない気がする。
ターゲットの DBMS は MySQL.
エスケープ処理を自前でやりたくない理由
以上一般的なSQLにおけるエスケープ処理について説明したが，実は不十分な対策である。残念なことにデータベースエンジンごとにSQLが独自拡張されていて，特殊文字が「'」「\」以外にも存在する。
たとえばMySQLでは，「'」「\」に加えて次の文字も機能をもつ特殊文字として扱われる。
* 「\0」ナルバイト文字
* 「\n」改行文字
* 「\r」キャリッジリターン文字
* 「\z」ファイル終端文字（Windowsのみ）
またMicrosoft のJetエンジンでは，次の文字も機能をもつ特殊文字として扱われる。
* 「|」VBAステートメント実行文字
IPA ISEC セキュア・プログラミング講座 - 2-1. SQL組み立て時の引数チェック
というDBMS依存の問題があるので、自前でエスケープ処理はできればやりたくない。
DBMSのバージョンによっても、もしかしたら変わるのかもしれないし。
MySQL の like について
expr LIKE pat [ESCAPE 'escape-char']
SQL の単純な正規表現比較を使用したパターンマッチ。1（TRUE）または 0（FALSE）を返す。LIKE では、次の 2 つのワイルドカード文字をパターン内で使用できる。
文字 説明 %任意の数の文字（ゼロ文字を含む）と一致する。 _厳密に 1 つの文字と一致する。
mysql> SELECT 'David!' LIKE 'David_';
-> 1
mysql> SELECT 'David!' LIKE '%D%v%';
-> 1
ワイルドカード文字を通常の文字として扱う場合は、直前にエスケープ文字を付ける。特定の ESCAPE 文字を指定しないと、‘\’ をエスケープ文字として使用するものとみなされる。
文字列 説明 \%1 つの %文字と一致する。\_1 つの _文字と一致する。
mysql> SELECT 'David!' LIKE 'David\_';
-> 0
mysql> SELECT 'David_' LIKE 'David\_';
-> 1
MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.3.2.1 文字列比較関数
Ref. MySQL AB :: MySQL 5.0 Reference Manual :: 11.4.1 String Comparison Functions
MySQLのワイルドカード用エスケープ文字は「\」 ← バックシュスラッシュ or 円マーク「￥」
ワイルドカードの「%」と「_」が PreparedStatement でどのように処理されるのか？
というわけで、RDBMS に依存しないように書いてみる。
Statement stmt = conn.prepareStatement("select * from items where name like ? {escape '\\'}");
stmt.setString(1, "果汁\\_1\\%%");
ResultSet rs = stmt.executeQuery();
うわ! なんか SQL の後ろに変なのついてる! ……という感じだけど、これは、JDBC の仕様に載ってる、れっきとした「エスケープ構文」。こう書くことで、エスケープに使う文字を指定できる。
ただし、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 に合わせたエスケープができる。
sardineの日記 - Java: PreparedStatement で like を扱う時の注意点
sardine さんのエントリを見ると、PreparedStatement はプレースホルダに入る文字列の「%」と「_」をエスケープしないような雰囲気。
だから、エスケープ構文を使ったり、escape メソッドを使って自力で変換をかけている。
// 与えられたキーワードを含むレコードを検索する
void searchRecords(Connection con, String keyword) throws Exception {
// PreparedStatementを作る： 動的データの箇所には ? を書いておく
PreparedStatement ps = con.prepareStatement("SELECT * FROM table1 WHERE name LIKE"
+ " CONCAT('%',?,'%')"); // 動的データの前後を%で挟む
// 本例はMySQLのやり方 (DBMSにより異なる)
ps.setString(1, keyword); // 動的データの具体値をセット
ResultSet rs = st.executeQuery();
while (rs.next()) {
……
}
rs.close();
ps.close();
}
というように、MySQL の CONCAT関数で文字列を接続する例があるが、これは「%」や「_」をプレースホルダーでエスケープしてくれるという前提に見える。
さて、どちらの挙動が正しいのか……
Statement 内の SQL エスケープ構文
まずは、Sun Mycrosystems の文書にあたってみる。
でも、エスケープ構文の話題しか見つからなかった……
4.1.5 Statement 内の SQL エスケープ構文
Statement オブジェクトは SQL エスケープ構文を使用する SQL 文を含むことができます。エスケープ構文は、この文の中のコードを異なる方法で処理するようドライバに伝えます。 Statement.setEscapeProcessing(true) または RowSet.setEscapeProcessing(true) を呼び出すことによって) エスケープ処理が有効になっている場合は、ドライバはエスケープ構文がないか走査し、個々のデータベースのコードへ変換します。このため、エスケープ構文は DBMS から独立しており、プログラマは他の方法では使用できないような機能を使用できるようになります。
エスケープ句は中括弧とキーワードによって区別されます。キーワードはエスケープ句の種類を示します。
{keyword .. . parameters . . . }
エスケープ句を識別するために、次のキーワードが使用されます。
* LIKE エスケープ文字のための escape パーセント記号 (%) および下線 (_) 文字は、SQL の LIKE 句ではワイルドカードのように機能します (% は 0 個以上の文字と一致し、_ は 1 つの文字と一致します)。これらを文字どおりに解釈するには、文字列の特殊なエスケープ文字であるバックスラッシュ (\) を直前に置きます。クエリーの終りに次の構文を組み込むと、どの文字をエスケープ文字として使用すべきかを指定することができます。
{escape 'escape-character'}
たとえば、次のクエリーでは、下線で始まる識別名を検索しています。 ここでは、バックスラッシュ文字をエスケープ文字として使用しています。
stmt.executeQuery("SELECT name FROM Identifiers
WHERE Id LIKE '\_%' {escape '\'}");
JDBC API 入門 - Statement - 5.1.6 Statement 内の SQL エスケープ構文
それにしても、JDBC API 入門 - 7. PreparedStatement のほうには like について何も書いてない。
あー、なんかエスケープ構文は不安な予感。
あと、なんだか似たような ESCAPE 句とやらもあるらしい。
MySQL 5.0.16以前では，LIKE句を使用する際に「LIKE "*%hoge" ESCAPE "*"」といったようにLIKE句中で使用する特殊文字をエスケープするためのESCAPE句に対する制限がなく，何文字であっても受け入れていました．
しかしSQL2003ではこのESCAPE句に設定できるのは1文字の長さに限るという規約が追加されましたので，
http://ikda.net/fswiki/wiki.cgi?page=SQL2003#p3
MySQL側もこの対応を行うこととなりました．
mir the tritonn - sql_mode=NO_BACKSLASH_ESCAPESについて
Connector/J のソースコードを漁る
しょうがないので、MySQL の JDBC ドライバである Connector/J のソースコードを見てみる。
ソースコードは MySQL AB :: Download Connector/J 5.0 から mysql-connector-java-5.0.7.zip をダウンロード。
com.mysql.jdbc.PreparedStatement クラスのソースコードをざっと見た感じでは 0, '\n', '\r', '\\', '\'', '"', '\032' をエスケープしているように見える。
「%」と「_」はエスケープしていないっぽい。
以下、該当箇所である setString メソッドの部分のソースコード。
/**
* Set a parameter to a Java String value. The driver converts this to a SQL
* VARCHAR or LONGVARCHAR value (depending on the arguments size relative to
* the driver's limits on VARCHARs) when it sends it to the database.
*
* @param parameterIndex
* the first parameter is 1...
* @param x
* the parameter value
*
* @exception SQLException
* if a database access error occurs
*/
public void setString(int parameterIndex, String x) throws SQLException {
// if the passed string is null, then set this column to null
if (x == null) {
setNull(parameterIndex, Types.CHAR);
} else {
checkClosed();
int stringLength = x.length();
if (this.connection.isNoBackslashEscapesSet()) {
// Scan for any nasty chars
boolean needsHexEscape = false;
for (int i = 0; i < stringLength; ++i) {
char c = x.charAt(i);
switch (c) {
case 0: /* Must be escaped for 'mysql' */
needsHexEscape = true;
break;
case '\n': /* Must be escaped for logs */
needsHexEscape = true;
break;
case '\r':
needsHexEscape = true;
break;
case '\\':
needsHexEscape = true;
break;
case '\'':
needsHexEscape = true;
break;
case '"': /* Better safe than sorry */
needsHexEscape = true;
break;
case '\032': /* This gives problems on Win32 */
needsHexEscape = true;
break;
}
if (needsHexEscape) {
break; // no need to scan more
}
}
if (!needsHexEscape) {
byte[] parameterAsBytes = null;
StringBuffer quotedString = new StringBuffer(x.length() + 2);
quotedString.append('\'');
quotedString.append(x);
quotedString.append('\'');
if (!this.isLoadDataQuery) {
parameterAsBytes = StringUtils.getBytes(quotedString.toString(),
this.charConverter, this.charEncoding,
this.connection.getServerCharacterEncoding(),
this.connection.parserKnowsUnicode());
} else {
// Send with platform character encoding
parameterAsBytes = quotedString.toString().getBytes();
}
setInternal(parameterIndex, parameterAsBytes);
} else {
byte[] parameterAsBytes = null;
if (!this.isLoadDataQuery) {
parameterAsBytes = StringUtils.getBytes(x,
this.charConverter, this.charEncoding,
this.connection.getServerCharacterEncoding(),
this.connection.parserKnowsUnicode());
} else {
// Send with platform character encoding
parameterAsBytes = x.getBytes();
}
setBytes(parameterIndex, parameterAsBytes);
}
return;
}
StringBuffer buf = new StringBuffer((int) (x.length() * 1.1));
buf.append('\'');
//
// Note: buf.append(char) is _faster_ than
// appending in blocks, because the block
// append requires a System.arraycopy()....
// go figure...
//
for (int i = 0; i < stringLength; ++i) {
char c = x.charAt(i);
switch (c) {
case 0: /* Must be escaped for 'mysql' */
buf.append('\\');
buf.append('0');
break;
case '\n': /* Must be escaped for logs */
buf.append('\\');
buf.append('n');
break;
case '\r':
buf.append('\\');
buf.append('r');
break;
case '\\':
buf.append('\\');
buf.append('\\');
break;
case '\'':
buf.append('\\');
buf.append('\'');
break;
case '"': /* Better safe than sorry */
if (this.usingAnsiMode) {
buf.append('\\');
}
buf.append('"');
break;
case '\032': /* This gives problems on Win32 */
buf.append('\\');
buf.append('Z');
break;
default:
buf.append(c);
}
}
buf.append('\'');
String parameterAsString = buf.toString();
byte[] parameterAsBytes = null;
if (!this.isLoadDataQuery) {
parameterAsBytes = StringUtils.getBytes(parameterAsString,
this.charConverter, this.charEncoding, this.connection
.getServerCharacterEncoding(), this.connection
.parserKnowsUnicode());
} else {
// Send with platform character encoding
parameterAsBytes = parameterAsString.getBytes();
}
setInternal(parameterIndex, parameterAsBytes);
}
}
「%」と「_」を手軽に標準的になんとかするクラスは無いものか……
com.mysql.jdbc.StringUtils クラスというのにヒントがありそうだけど、これ以上追いかけるのも面倒になってきた。
以下は、たぶん何か関係ありそうな wildCompare メソッドのソースコード。
/**
* Compares searchIn against searchForWildcard with wildcards (heavily
* borrowed from strings/ctype-simple.c in the server sources)
*
* @param searchIn
* the string to search in
* @param searchForWildcard
* the string to search for, using the 'standard' SQL wildcard
* chars of '%' and '_'
*
* @return WILD_COMPARE_MATCH_NO_WILD if matched, WILD_COMPARE_NO_MATCH if
* not matched with wildcard, WILD_COMPARE_MATCH_WITH_WILD if
* matched with wildcard
*/
public static int wildCompare(String searchIn, String searchForWildcard) {
if ((searchIn == null) || (searchForWildcard == null)) {
return WILD_COMPARE_NO_MATCH;
}
if (searchForWildcard.equals("%")) { //$NON-NLS-1$
return WILD_COMPARE_MATCH_WITH_WILD;
}
int result = WILD_COMPARE_NO_MATCH; /* Not found, using wildcards */
char wildcardMany = '%';
char wildcardOne = '_';
char wildcardEscape = '\\';
int searchForPos = 0;
int searchForEnd = searchForWildcard.length();
int searchInPos = 0;
int searchInEnd = searchIn.length();
while (searchForPos != searchForEnd) {
char wildstrChar = searchForWildcard.charAt(searchForPos);
while ((searchForWildcard.charAt(searchForPos) != wildcardMany)
&& (wildstrChar != wildcardOne)) {
if ((searchForWildcard.charAt(searchForPos) == wildcardEscape)
&& ((searchForPos + 1) != searchForEnd)) {
searchForPos++;
}
if ((searchInPos == searchInEnd)
|| (Character.toUpperCase(searchForWildcard
.charAt(searchForPos++)) != Character
.toUpperCase(searchIn.charAt(searchInPos++)))) {
return WILD_COMPARE_MATCH_WITH_WILD; /* No match */
}
if (searchForPos == searchForEnd) {
return ((searchInPos != searchInEnd) ? WILD_COMPARE_MATCH_WITH_WILD
: WILD_COMPARE_MATCH_NO_WILD); /*
* Match if both are
* at end
*/
}
result = WILD_COMPARE_MATCH_WITH_WILD; /* Found an anchor char */
}
if (searchForWildcard.charAt(searchForPos) == wildcardOne) {
do {
if (searchInPos == searchInEnd) { /*
* Skip one char if
* possible
*/
return (result);
}
searchInPos++;
} while ((++searchForPos < searchForEnd)
&& (searchForWildcard.charAt(searchForPos) == wildcardOne));
if (searchForPos == searchForEnd) {
break;
}
}
if (searchForWildcard.charAt(searchForPos) == wildcardMany) { /*
* Found
* w_many
*/
char cmp;
searchForPos++;
/* Remove any '%' and '_' from the wild search string */
for (; searchForPos != searchForEnd; searchForPos++) {
if (searchForWildcard.charAt(searchForPos) == wildcardMany) {
continue;
}
if (searchForWildcard.charAt(searchForPos) == wildcardOne) {
if (searchInPos == searchInEnd) {
return (WILD_COMPARE_NO_MATCH);
}
searchInPos++;
continue;
}
break; /* Not a wild character */
}
if (searchForPos == searchForEnd) {
return WILD_COMPARE_MATCH_NO_WILD; /* Ok if w_many is last */
}
if (searchInPos == searchInEnd) {
return WILD_COMPARE_NO_MATCH;
}
if (((cmp = searchForWildcard.charAt(searchForPos)) == wildcardEscape)
&& ((searchForPos + 1) != searchForEnd)) {
cmp = searchForWildcard.charAt(++searchForPos);
}
searchForPos++;
do {
while ((searchInPos != searchInEnd)
&& (Character.toUpperCase(searchIn
.charAt(searchInPos)) != Character
.toUpperCase(cmp)))
searchInPos++;
if (searchInPos++ == searchInEnd) {
return WILD_COMPARE_NO_MATCH;
}
{
int tmp = wildCompare(searchIn, searchForWildcard);
if (tmp <= 0) {
return (tmp);
}
}
} while ((searchInPos != searchInEnd)
&& (searchForWildcard.charAt(0) != wildcardMany));
return WILD_COMPARE_NO_MATCH;
}
}
return ((searchInPos != searchInEnd) ? WILD_COMPARE_MATCH_WITH_WILD
: WILD_COMPARE_MATCH_NO_WILD);
}
# それにしても、EXCEPTIONS-CONNECTOR-J というGPL特例の説明ファイルが気になる……
MySQL の環境で「ワイルドカード文字をエスケープする文字」を出力してみるテスト
結局、likeのワイルドカード文字である「%」と「_」だけは自力でエスケープしたほうが良さそう。
その他のDBMSを制御する文字は JDBCドライバがエスケープしてくれる。
エスケープするための文字は一般的には「\」っぽいけど、いちおうDBMS依存らしいので DatabaseMetaData#getSearchStringEscape を使うのが良さそう。
MySQL の環境で「ワイルドカード文字をエスケープする文字」を出力してみる。
サンプルソースコード:
import java.sql.*;
public class DbTest {
public static void main(String[] args) throws Exception {
// DBManager は自前のクラス
DBManager dbm =
new DBManager(
"com.mysql.jdbc.Driver",
"jdbc:mysql://192.168.0.1/hoge",
"hogeuser",
"hogepassword");
Connection con = dbm.createConnection();
DatabaseMetaData md = con.getMetaData();
// DatabaseMetaData (Java 2 Platform SE 5.0)
// http://java.sun.com/j2se/1.5.0/ja/docs/ja/api/java/sql/DatabaseMetaData.html#getSearchStringEscape()
// > ワイルドカード文字をエスケープするのに使用できる文字列を取得します。これは、パターンのカタログ検索パラメータで「_」や「%」をエスケープするのに使用できる文字列です (パターンであるためワイルドカード文字を使用)。
// > 「_」文字は任意の 1 文字を表します。「%」文字は 0 文字以上のシーケンスを表します。
String escape = md.getSearchStringEscape();
System.out.println("escape=" + escape);
}
}
出力結果:
escape=\
やっぱり「\」でした、と。
思えば遠くへ来たもんだ
結局、
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 に合わせたエスケープができる。
sardineの日記 - Java: PreparedStatement で like を扱う時の注意点
これが無難っぽい。
「%」と「_」を事前にエスケープしておいて、必要なら自前で「%」や「_」を付ける。
# 回り道くねくね、迷い道くねくね
SQL Injectionの仕組みと対策の「SQL文で危険な文字列」は参考になるかも。
tags: zlashdot Java Java MySQL
Posted by NI-Lab. (@nilab)
