CODEWITHSUNDEEP
javasqlspringdb2
Brady Zhu
Brady Zhu

Reputation: 1405

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

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.

Upvotes: 2

Views: 1027

Answers (2)

Yogendra Singh
Yogendra Singh

Reputation: 34367

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());

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

Three options:

  1. Generate different JDBC queries for each length of the IN LIST, and parameterize each INDIVIDUAL item, e.g. this answer
  2. For small tables, you can cheat and use a LIKE statement, e.g. this answer
  3. 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

Upvotes: 0

Related Questions