How to handle 'IN' case in SQL Statement with the way of Parameterized Query?

Question

To avoid SQL injection attacks in my project, I'm attempting access database with Parameterized Query way. Right now I know how to handle equal case like below (With Spring JdbcTemplate):

String sql = "SELECT * FROM T_USER WHERE USERNAME = ? AND PASSWORD = ?"
jdbcTemplate.query(sql, 
                   new UserRowMapper(), 
                   new Object[]{"%admin%", "%password%"});

Above code runs no problem, but I had no idea how to handle the 'IN' case, following is my case, and it works failed:

String sql = 
   "SELECT * FROM T_USER WHERE USERNAME = ? AND PASSWORD = ? AND CLASS_ID IN (?)"
jdbcTemplate.query(sql, 
                   new UserRowMapper(), 
                   new Object[]{"%admin%", "%password%", "1,2,3"});

Anybody give me guidance? Thanks a lot.

Which RDBMS are you using, and which language (C#, Java, VB.Net, Turbo Pascal)? — Adriaan Stander, Nov 02 '12 at 03:55
I'm using DB2 and with Java language (Spring JdbcTemplate), thanks for reply. — Brady Zhu, Nov 02 '12 at 03:57
You can build your query to have parameters inside the IN clause from java. See this question in C#: http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause — nawfal, Jun 28 '14 at 06:22

Yogendra Singh · Accepted Answer · 2012-11-02T05:24:19.020

1

I think you can create a List and pass it as 3rd parameter. Also You need to use LIKE in place of = in first two column filters.

List<Integer> classIds = new ArrayList<Integer>();
classIds.add(1);
classIds.add(2);
classIds.add(3);

String sql = "SELECT * FROM T_USER WHERE "+
              "USERNAME LIKE ? AND PASSWORD LIKE ? AND CLASS_ID IN (?)";
jdbcTemplate.query(sql, new Object[]{"%admin%", "%password%", classIds},
                                                         new UserRowMapper());

Please note: Here is the syntax:

public List query(String sql, Object[] args, RowMapper rowMapper) 
             throws DataAccessException

EDIT: Please try namedParameterJdbcTemplate as bwlow:

String sql = "SELECT * FROM T_USER WHERE "+
           "USERNAME LIKE :uname AND PASSWORD LIKE :passwd AND CLASS_ID IN (:ids)";
 Map<String, Object> namedParameters = new HashMap<String, Object>();
 namedParameters.put("uname", "%admin%);
 namedParameters.put("passwd", "%password%");
 namedParameters.put("ids", classIds);
 List result = namedParameterJdbcTemplate.query(sql, namedParameters, 
                                                             new UserRowMapper());

edited Nov 02 '12 at 05:24

answered Nov 02 '12 at 04:10

Yogendra Singh

33,927
6
63
73

Thanks for your reply. I follow your way to do it got below error: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT* FROM PFRAS.USER_PROFILE WHERE USERNAME LIKE ? AND PASSWORD LIKE ? AND ID IN (?)]; nested exception is com.ibm.db2.jcc.am.io: [jcc][1091][10824][3.57.82] 数据转换无效：参数实例 [1, 35] 对于所请求的转换无效。 ERRORCODE=-4461, SQLSTATE=42815 – Brady Zhu Nov 02 '12 at 04:26
Sorry, I use a Chinese-version DB2, some message may makes you confused. – Brady Zhu Nov 02 '12 at 04:28
@BradyChu: I updated the answer. Basically, RowMapper is third argument in `query()` method and you were using as second. Pleas swap and try. – Yogendra Singh Nov 02 '12 at 04:34
the same exception encounters also. – Brady Zhu Nov 02 '12 at 04:43
@BradyChu: What is the data type of CLASS_ID column? Also you are using `%...%` in the param then you should use `LIKE` not `=`. Updated the answer. – Yogendra Singh Nov 02 '12 at 04:48
Data type for the CLASS_ID (IN Case) is Integer – Brady Zhu Nov 02 '12 at 04:51
Can a Integer-type column use LIKE? Anyway, I will have a try – Brady Zhu Nov 02 '12 at 04:55
@BradyChu: I used the like for other two columns i.e. USERNAME & PASSWORD, not for CLASS_ID. Please check the updated answer. – Yogendra Singh Nov 02 '12 at 04:55
As I mentioned in my question: I use LIKE for USERANEM & PASSWORD no problem. The issue point is at how to handle CLASS_ID. – Brady Zhu Nov 02 '12 at 05:03
@BradyChu: I think CLASS_ID handling was fixed by using the `List` and issue was coming because of `=` in `UserName` and `Password`. Did you try it as suggested in the updated answer? Is it still failing?? – Yogendra Singh Nov 02 '12 at 05:05
Yes, I tried by 'SELECT * FROM T_USER WHERE USERNAME LIKE ? AND PASSWORD LIKE ? AND ID IN (?)'. But still failed. – Brady Zhu Nov 02 '12 at 05:14
@BradyChu: Added another way using `namedParameterJdbcTemplate`. Hope that helps. – Yogendra Singh Nov 02 '12 at 05:25
This a good suggestion with namedParameterJdbcTemplate. But it should be approved by my PM, I have no right to change the architecture of project. Thanks! Yogendra Singh! – Brady Zhu Nov 02 '12 at 05:27
@BradyChu: You know your workplace better but I don't think using `namedParameterJdbcTemplate` in place of `jdbcTemplate` template is an architectural change :) Both are part of same `Spring Framework`. – Yogendra Singh Nov 02 '12 at 05:34

score 0 · Answer 2 · edited May 23 '17 at 11:55

Three options:

Generate different JDBC queries for each length of the IN LIST, and parameterize each INDIVIDUAL item, e.g. this answer
For small tables, you can cheat and use a LIKE statement, e.g. this answer
Use a SPLIT function (anti-LISTAGG) to turn the delimited list into individual rows of one column each, and JOIN against it. Example SPLIT function
- You'll parameterize the argument to the function as a single string

How to handle 'IN' case in SQL Statement with the way of Parameterized Query?

2 Answers2