Groovy SQL named list parameter

Question

I want to use a keyset of a Map as a list parameter in a SQL query:

query = "select contentid from content where spaceid = :spaceid and title in (:title)"
sql.eachRow(query, [spaceid: 1234, title: map.keySet().join(',')]) {
    rs ->
        println rs.contentid
}

I can use single values but no Sets or Lists. This is what I've tried so far:

map.keySet().join(',')
map.keySet().toListString()
map.keySet().toList()
map.keySet().toString()

The map uses Strings as key

Map<String, String> map = new TreeMap<>(String.CASE_INSENSITIVE_ORDER);

Also, I don't get an error. I just get nothing printed like have an empty result set.

try `[spaceid: 1234, title: "'" + map.keySet().join("','") + "'" ]` — injecteer, Sep 28 '17 at 13:39

Marmite Bomber · Accepted Answer · 2021-08-04T07:21:06.637

You appoach will not give the expected result.

Logically you are using a predicate such as

 title = 'value1,value2,value3'

This is the reason why you get no exception but also no data.

Quick search gives a little evidence, that a mapping of a collections to IN list is possible in Groovy SQL. Please check here and here

So very probably you'll have to define the IN list in a proper length and assign the values from your array.

 title in (:key1, :key2, :key3)

Anyway something like this works fine:

Data

create table content as 
select 1 contentid, 1 spaceid, 'AAA' title from dual union all
select 2 contentid, 1 spaceid, 'BBB' title from dual union all
select 3 contentid, 2 spaceid, 'AAA' title from dual;

Groovy Script

map['key1'] = 'AAA'
map['key2'] = 'BBB'

query = "select contentid from content where spaceid = :spaceid and title in (${map.keySet().collect{":$it"}.join(',')})"
println query
map['spaceid'] = 1
sql.eachRow(query, map) {
    rs ->
        println rs.contentid
}

Result

select contentid from content where spaceid = :spaceid and title in (:key1,:key2)
1
2

The key step is to dynamicall prepare the IN list with proper names of the bind variable using the experssion map.keySet().collect{":$it"}.join(',')

Note

You may also want to check the size if the map and handle the case where it is greater than 1000, which is an Oracle limitation of a single IN list.

Thanks for your help! I had to add to remove whitespaces and dashes from the keys in order to make your solution work for me .replaceAll("\\s","").replaceAll("-","") — CaptainMango, Oct 02 '17 at 06:14
This looks like it's just doing fancy string concatenation, rather than actually using a parameterized SQL statement, so wouldn't this still be vulnerable to SQL injection? — nbrooks, Jul 29 '19 at 19:18
Nope @nbrooks this approach uses (dynamicaly created) bind variables. Same concept is used in Hybernate. — Marmite Bomber, Jul 29 '19 at 19:43

score 0 · Answer 2 · edited Jun 12 '19 at 09:40

It has worked for me with a little adaptation, I've added the map as a second argument.

def sql = Sql.newInstance("jdbc:mysql://localhost/databaseName", "userid", "pass")
    Map<String,Long> mapProduitEnDelta = new HashMap<>()
    mapProduitEnDelta['key1'] = 1
    mapProduitEnDelta['key2'] = 2
    mapProduitEnDelta['key3'] = 3
    produits : sql.rows("""select id, reference from Produit where id IN (${mapProduitEnDelta.keySet().collect{":$it"}.join(',')})""",mapProduitEnDelta),

Display the 3 products (colums + values from the produit table) of id 1, 2, 3

Groovy SQL named list parameter

2 Answers2