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
– Adriaan Stander, Commented Nov 2, 2012 at 3:55
I'm using DB2 and with Java language (Spring JdbcTemplate), thanks for reply. — Brady Zhu
– Brady Zhu, Commented Nov 2, 2012 at 3:57
You can build your query to have parameters inside the IN clause from java. See this question in C#: stackoverflow.com/questions/337704/parameterize-a-sql-in-clause — nawfal
– nawfal, Commented Jun 28, 2014 at 6:22

Yogendra Singh · Accepted Answer · 2012-11-02 05:24:19Z

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 2, 2012 at 5:24

answered Nov 2, 2012 at 4:10

Yogendra Singh

34.5k7 gold badges66 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Brady Zhu Over a year ago

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 Over a year ago

Sorry, I use a Chinese-version DB2, some message may makes you confused.

Yogendra Singh Over a year ago

@BradyChu: I updated the answer. Basically, RowMapper is third argument in query() method and you were using as second. Pleas swap and try.

Brady Zhu Over a year ago

the same exception encounters also.

Yogendra Singh Over a year ago

@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.

|

Community · Accepted Answer · 2017-05-23 11:55:57Z

0

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

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Nov 2, 2012 at 4:04

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

14 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related