[FIXED] Does using NamedParameterJdbcTemplate prevent SQL injection?

Issue

I have the following method that uses a NamedParameterJdbcTemplate to execute an SQL query in Spring Boot:

@Service
class MyRepository(
  val jdbcTemplate: NamedParameterJdbcTemplate
) {
  fun loadData(myKey: List<Int>): List<MyRow> {

    return jdbcTemplate.query(
      """
                select
                    io.KEY as itemKey,
                    art.ARTICLE_NR as articleNumber,
                    art.PRICE as price,   
                    concat(
                      concat(
                        concat(art.BEST_B, BEST_A), 
                          lpad(BEST_B, 2, '0')), 
                            lpad(BEST_A, 2, '0')) as group
                from
                    BUY.OPTION io
                    INNER JOIN BUYING.ART art ON (to_char(art.id) = io.keyb)
                where
                    io.KEY IN (:MYKEY)
      """.trimIndent(),
      parameters
    ) { rs, rowNum ->
      MyRow(
        itemOption = ItemOption(rs.getString("ITEMOPTION")),
        articleNumber = rs.getString("ARTICLENUMBER"),
        price = rs.getBigDecimal("PRICE")
        group = rs.getString("GROUP")
      )
    }
  }
}

Is this method already protected against SQL injection since it's using NamedParameterJdbcTemplate? Or do I have to do some extra steps for that?

Solution

Using parameters in a NamedParameterJdbcTemplate will use JDBC prepared statement with parameters, which will - in general - protect you against SQL injection.

I'm saying "in general", because the actual protection depends on the implementation of the specific JDBC driver used. Mainstream JDBC drivers will - bar any bugs - protect you against SQL injection because they either keep statements and parameter values separate (the statement is prepared with parameter placeholders, and on execute only the values are sent), or otherwise properly escape things when generating the actual query (i.e. default behaviour in the MySQL Connector/J driver). However, nothing keeps someone from writing a naive JDBC driver that will use string interpolation to generate a query without actually preventing SQL injection.

Answered By - Mark Rotteveel