0

I have query similar to this one:

"SELECT COUNT(*) FROM EMPLOYEE WHERE ADDRESS IN (" + listOfStringsSeparatedByComma + ")"

and the tools complain that there is potential for SQL injection.

In order to solve this, I saw that I can use this in Spring JDBC:

SqlParameterSource namedParameters = new MapSqlParameterSource()
    .addValue("addresses", Arrays.asList("Canada", "USA", "Finland"));
return namedParameterJdbcTemplate.queryForObject("SELECT COUNT(*) FROM EMPLOYEE WHERE ADDRESS IN (:addresses)", 
    namedParameters, Integer.class);

and it seems to work, since the output I get when I run this is:

Executing prepared SQL statement [SELECT COUNT(*) FROM EMPLOYEE WHERE ADDRESS IN (?, ?, ?)]
Setting SQL statement parameter value: column index 1, parameter value [Canada], value class [java.lang.String], SQL type unknown
Setting SQL statement parameter value: column index 2, parameter value [USA], value class [java.lang.String], SQL type unknown
Setting SQL statement parameter value: column index 3, parameter value [Finland], value class [java.lang.String], SQL type unknown

Now, my question is - can I somehow disable generation of prepared statement (or I guess it has to be prepared statement - but then to disable caching of it), since if number of parameters in namedParameters varies a lot, I will have a ton of prepared statements and then I will have a complaint from MySQL about too many prepared statements in cache.

Basically I want to have benefit of not handling possible SQL injection, and at a same time not having MySQL complaining about too many prepared statements generated.

Bojan Vukasovic
  • 2,054
  • 22
  • 43
  • I would not recommend disabling generation of prepared statements. Here's a solution that uses a temporary table: https://javabydeveloper.com/spring-jdbctemplate-in-clause-with-list-of-values/#5-3-how-to-work-with-large-number-of-values-in-in-clause – duffymo Jun 18 '21 at 13:18
  • @duffymo thanks, but I think this is a lot slower than any of the 2 cases I have above. do you know if there are any benchmarks? – Bojan Vukasovic Jun 18 '21 at 13:20
  • I would recommend that you make it work before you worrying about micro-optimizations. You have no data to back up that statement. – duffymo Jun 18 '21 at 13:21
  • This might also work: https://stackoverflow.com/questions/1327074/how-to-execute-in-sql-queries-with-springs-jdbctemplate-effectively. Why aren't you doing a search of SO? – duffymo Jun 18 '21 at 13:23
  • @duffymo the last comment is exactly what I use now. And it already works with this what I have, and as always it is a tradeoff. btw. I'm not sure if my user can create tables in db. – Bojan Vukasovic Jun 18 '21 at 15:42
  • Your user might not, but your app should be able to if you give it the proper credentials and permissions. – duffymo Jun 18 '21 at 16:25
  • seems that one way to do this is to have separate data source with `useServerPrepStmts=false`. I guess that way prepared statements will just be stored on the client and not on MySQL server. – Bojan Vukasovic Jun 18 '21 at 17:52
  • I would not do that. Good luck. – duffymo Jun 18 '21 at 17:54
  • maybe somebody else has other options... I do not like this additional table, since I first have to have MySQL user account that is allowed to create tables (which SA will not allow), and then I need to see how to differ name of the table between 32 partitions of same component running at the same time (so if I have same name of temp table, I guess it would clash; unless temp table is bound to transaction). – Bojan Vukasovic Jun 18 '21 at 18:01

0 Answers0