simpleJDBCTemplate not replacing quoted parameter

Question

Hi, I am using simpleJDBCTemplate to insert a value to a postgre database.

String sql "insert into testTable values(:bla, :blah, functionThatTakesAText(':blu'))"
BeanPropertySqlParameterSource namedParameters = new BeanPropertySqlParameterSource(lighting);
simpleJdbcTemplate.update(sql, namedParameters);

Now, the blu parameter is actually a number(the actual sql takes 2 real's ) that is read from a file given by the client.

As a result the database receives something like the following:

insert into testTable values(?, ?, functionThatTakesAText(':blu'))

and fails to replace the :blu parameter as expected.

The current workaround that I'm using is replacing the blu parameter with its value using a regex, but I'm unsure on how safe that is.

How would you solve that?

Answer 1

Spring will skip over anything inside quotes in the SQL (see the skipCommentsAndQuotes() method of NamedParameterUtils), on the basis that anything inside quotes shouldn't be touched.

That makes sense in this context - you would want the prepared statement to say

functionThatTakesAText(?)

rather than

functionThatTakesAText('?')

Try removing the quotes there, and the placeholder should be substituted correctly.