java or groovy Insert map to database

Question

I have code like this

def myMap = [key1 : val1, key2 : val2]

sql.executeInsert("Insert into tableName(key1, key2) Values(?, ?)", myMap)

I want to create a method that will do this with any map that I pass in. As in, it should be able to create an insert statement with the correct mapping of keys to values, and most importantly be sql injection-safe. But all the examples I can find of doing inserts either aren't injection-safe or are hardcoded to particular values.

For instance, here is an example doing it in a non-injection safe way, http://www.bloggure.info/.groovy/work/quickly-insert-data-to-a-sql-database-in-groovy/

This may help: [How to get map keys in groovy](http://stackoverflow.com/questions/4898062/get-key-in-groovy-maps). Once you know the keys, you can construct your statement and bind parameters — BackSlash, May 01 '17 at 19:18
You cannot bind column identifiers in JDBC, only column _values_. Are the column ids coming as user input, as well, or why can't you just concatenate them to the query string? — Mick Mnemonic, May 01 '17 at 20:06
@MickMnemonic The column IDs are coming in externally, and I don't want to just concat them because that's not sql injection safe. For instance, my code might break if there's an apostrophe in one of the values (in fact, that's what happened when I ran the code from the link I posted). — Steve, May 01 '17 at 21:45
Possible duplicate of [How do I sanitize SQL without using prepared statements](http://stackoverflow.com/questions/1677465/how-do-i-sanitize-sql-without-using-prepared-statements) — Mick Mnemonic, May 01 '17 at 21:52

score 1 · Accepted Answer · answered May 02 '17 at 11:06

1

You should be able to do something like this:

import groovy.sql.*

def myMap = [key1 : 'val1', key2 : 'val2']

def generateSql(Map map) {
    Closure<String> keys = { -> map.keySet().collect { Sql.expand(it) }.join(', ') }
    Closure<String> values = { -> map.keySet().collect { ":${it}" }.join(', ') }
    "insert into tablename(${keys()}) values(${values()})"
}

sql.executeInsert(generateSql(myMap), myMap)

answered May 02 '17 at 11:06

tim_yates

167,322
27
342
338

How does this solution sanitize the sql? – Steve May 02 '17 at 13:33
See _"Named and ordinal parameters"_ [here](http://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html) and [Sql.expand](http://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html#expand-java.lang.Object-) – tim_yates May 02 '17 at 16:07
Interesting, http://stackoverflow.com/questions/2267756/dynamically-set-the-db-in-a-sql-query This gives a good explanation of sql.expand, which was confusing initially. Thanks! – Steve May 02 '17 at 18:07
The expand wasn't working for me, so I took it out and it still works. And so far it's handling everything malicious I throw at it. – Steve May 02 '17 at 20:29

java or groovy Insert map to database

1 Answers1