問題描述

為了防止 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 case 的參數化查詢嗎?我知道這項工作可以通過NamedParameterJdbcTemplate來完成，是否只有NamedParameterJdbcTemplate可以做IN case查詢?

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 嗎?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！