JdbcTemplate不支持参数化查询'IN'的情况?必须由NamedParameterJdbcTemplate? [英] JdbcTemplate does not support Parameterized Query 'IN' case? Must by NamedParameterJdbcTemplate?
问题描述
为防止SQL注入攻击,我的项目中的所有SQL语句代码都应该转换为参数化查询。但是当查询条件包含IN的情况时,我遇到问题。像这样(使用DB2数据库):
Aimed at preventing SQL injection attacks, all the SQL Statement code in my project should transformed to Parameterized Query. But I got a problem when the query condition includes a 'IN' case. Like this (Using DB2 database):
String employeeId = 'D2309';
String name = "%brady%";
List<Integer> userRights = new ArrayList<Integer>();
userRights.add(1);
userRights.add(2);
userRights.add(3);
String sql = "SELECT * FROM T_EMPLOYEE WHERE EMPLOYEE_ID = ? AND NAME LIKE ?
AND RIGHT IN (?)";
jdbcTemplate.query(sql, new Object[] {employeeId, name, userRights}, new
EmployeeRowMapper());
上述代码运行失败,但有例外:
The above code runs failed with the exception:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad
SQL grammar [SELECT * FROM T_EMPLOYEE WHERE EMPLOYEE_ID = ? AND NAME LIKE ? AND
RIGHT IN (?)]; nested exception is com.ibm.db2.jcc.am.io: [jcc][1091][10824]
[3.57.82] .... ERRORCODE=-4461, SQLSTATE=42815
这里的问题是JdbcTemplate是否支持IN的参数化查询?我知道这个工作可以通过NamedParameterJdbcTemplate来完成,而且NamedParameterJdbcTemplate是否只能在IN情况下查询?
The question here is that does not JdbcTemplate support Parameterized Query for IN case? and I know this work can be done by NamedParameterJdbcTemplate, and whether only NamedParameterJdbcTemplate can do IN case query?
非常感谢
推荐答案
正如我在评论中已经提到的,我不满意这个解决方案,因为它动态地生成了一些SQL语句。由于 userRights
的数量在1和n之间,所以在缓存中需要n个准备好的语句。
As I already mentioned in the comments, I'm not happy with this solution as it dynamically generates a number of SQL statements. Given the number of userRights
is between 1 and n, it requires up to n prepared statements in the cache.
下面的工作(我没有尝试)。
The below should work (I did not try it).
String employeeId = 'D2309';
String name = "%brady%";
List<Integer> userRights = new ArrayList<Integer>();
userRights.add(1);
userRights.add(2);
userRights.add(3);
// build the input string
StringBuilder sb = new StringBuilder();
for (int i = 0; i < userRights.size; i++) {
sb.append("?");
if (i < userRights.size() - 1) {
sb.append(", ");
}
}
// build the SQL
String sql = "SELECT * FROM T_EMPLOYEE WHERE EMPLOYEE_ID = ?" +
" AND NAME LIKE ?" +
" AND RIGHT IN (" + sb.toString() + ")";
// init the object array
// size is employeeId + name + right
Object[] param = new Object[2 + userRights.size()];
// fill it
param[0] = employeeId;
param[1] = name;
for (int i = 0; i < userRights.size(); i++) {
param[i + 2] = userRights.get(i);
}
jdbcTemplate.query(sql, param, new EmployeeRowMapper());
这篇关于JdbcTemplate不支持参数化查询'IN'的情况?必须由NamedParameterJdbcTemplate?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!