SQL - replacing placeholder with parameter values

Question

I have a function that generates an SQL string. The query works fine and I fetch the data I need. The issue I have is that I am building a query as a string which is vulnerable to an SQL injection attacks. I passing uuids as a parameter in an example below, but not tuples. For tuples I am just building a string input. It looks like this:

private fun generateSQLForTuples(tuplesList: List<List<String>>):String =
    """
      select id, subcustomer
      from customer
      where uuid = any(:uuids)
      union
      select id, subcustomer
      from customer
      where (customer_id, subcustomer) in (${toJdbcTuples(tuplesList)})
    """.trimIndent()

Since, I didn't find the right jdbc data type for tuples I am generating a string and passing it directly with toJdbcTuples(tuplesList) function. Now, since I would like to have some kind of protection from SQL injection attacks, I thought of creating first a list of placeholders for tuples that would look like this:

((?, ?), (?, ?))

And then I would create a list to match the placeholder. This is the code where I create placeholders and tuples:

val placeholdersForTuples = customers.map { listOf("?", "?") }
val tuples = customers.map { listOf(it["customerId"] as String, it["subCustomer"] as String) }
val existingCustomers = fetchRows(
      ctx, generateSQLForTuples(placeholdersForTuples), mapOf("uuids" to customers.map { it["uuid"] })
  )

But, I am not sure how should I then pass the list with actual tuple values as a parameter into a prepared statement with placeholders? I am already passing a named parameter uuids to fetchRows function for an SQL that is generated with generateSQLForTuples(tuples). How can I do that for tuples placeholders as well?

Answer 1

Tuples expects to work with Object[], so for it to work with a List it should be a List<Object[]>.

val tuples = java.util.List.of(arrayOf<Any>("AAA", 111, 'X'), arrayOf<Any>("BBB", 222, 'Y'))


 val sql = ("SELECT * FROM MY_TABLE "
            + "WHERE (COL_1, COL_2, COL_3) "
            + "IN (:values)") // <-- the placeholder


    val list: Unit = jdbcTemplate.queryForList(
        sql,
        MapSqlParameterSource() // bind the list to the placeholder
            .addValue("values", tuples)
    )

courtesy: Alessandro