[SOLVED] Parameterized field names in Groovy Sql

Parameterized field names in Groovy Sql

I have a small tool that does a few simple operations on a database. It puts together SQL queries based on configuration (including field names) passed into it by running toString on GStrings. Recently I've been trying to move off of GString and use the more secure parameterized query feature. The problem is that my configurable field names do not seem to work. Consider this code:

import groovy.sql.Sql

Sql.withInstance('jdbc:comdb2://dev/brpcfgdb?comdb2dbname=comdb3db') { Sql sql ->
    sql.rows('SELECT * FROM docs WHERE :sourceFld IS :source', [sourceFld: 'source', source: 'www']).forEach { row ->
        println row
    }

    println "----------"

    sql.rows('SELECT * FROM docs WHERE source IS :source', [source: 'www']).forEach { row ->
        println row
    }
}

I'd expect the exact same rows to be printed above and below the dashed line. Instead, no rows are printed before, and the expected number are printed below.

Is it possible to specify field names in a parameterized query? Or am I stuck with concatenations and/or GStrings?

Solution

it's impossible to pass parametrized table & column names - jdbc drivers does not support this.

possible to build sql dynamically but potentially it leads to sql injection vulnerability.

you could do it through this code:

def sourceFld = 'surce'
sql.rows('SELECT * FROM docs WHERE ${sourceFld} IS :sourceVal', [sourceVal: 'www']).forEach { row ->
    println row
}

Map params = [
    source: 'www'
]
String query = 'SELECT * FROM docs WHERE ' + params.collect{k,v-> "$k = :$k" }.join(' AND ')
sql.rows(query,params).forEach { row ->
    println row
}