IN 子句和占位符 [英] IN clause and placeholders
问题描述
我正在尝试在 Android 中执行以下 SQL 查询:
I'm attempting to do the following SQL query within Android:
String names = "'name1', 'name2"; // in the code this is dynamically generated
String query = "SELECT * FROM table WHERE name IN (?)";
Cursor cursor = mDb.rawQuery(query, new String[]{names});
但是,Android 不会用正确的值替换问号.我可以执行以下操作,但是,这并不能防止 SQL 注入:
However, Android does not replace the question mark with the correct values. I could do the following, however, this does not protect against SQL injection:
String query = "SELECT * FROM table WHERE name IN (" + names + ")";
Cursor cursor = mDb.rawQuery(query, null);
我怎样才能解决这个问题并能够使用 IN 子句?
How can I get around this issue and be able to use the IN clause?
推荐答案
"?, ?, ..., ?"
形式的字符串可以是动态创建的字符串并且安全放入原始SQL查询中(因为它是一种限制形式,不包含外部数据),然后占位符就可以正常使用了.
A string of the form "?, ?, ..., ?"
can be a dynamically created string and safely put into the original SQL query (because it is a restricted form that does not contain external data) and then the placeholders can be used as normal.
考虑一个函数 String makePlaceholders(int len)
返回 len
用逗号分隔的问号,然后:
Consider a function String makePlaceholders(int len)
which returns len
question-marks separated with commas, then:
String[] names = { "name1", "name2" }; // do whatever is needed first
String query = "SELECT * FROM table"
+ " WHERE name IN (" + makePlaceholders(names.length) + ")";
Cursor cursor = mDb.rawQuery(query, names);
只需确保传递与位置一样多的值.SQLite 中默认的最大主机参数限制是 999 - 至少在正常构建中,不是确定 Android :)
Just make sure to pass exactly as many values as places. The default maximum limit of host parameters in SQLite is 999 - at least in a normal build, not sure about Android :)
这是一种实现:
String makePlaceholders(int len) {
if (len < 1) {
// It will lead to an invalid query anyway ..
throw new RuntimeException("No placeholders");
} else {
StringBuilder sb = new StringBuilder(len * 2 - 1);
sb.append("?");
for (int i = 1; i < len; i++) {
sb.append(",?");
}
return sb.toString();
}
}
这篇关于IN 子句和占位符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!