Insert into SQL with Groovy using variable for values

2
votes

I'm trying to use a variable for the values part of an insert SQL statement. What's odd is that if I copy the values of what I see in the debugger then the statement runs and inserts correctly. If I use the variable then it gives me a "Sorry, wrong number of values" error.

Here's my insert

db.execute("""
        INSERT INTO TABLE 
        (UNIT, ORD#, DISP, DATE, TIME, AMT, LAT, LONG, DRVR, OWNR, SETT, STAT) 
        values ${parsedOrderNumbers}
        """)

parsedOrderNumbers looks like in the debugger, but it might also have " " surrounding it since it's a String. I'm not sure and think that might be what is happening here with the error or maybe with how Groovy interpolates ${parsedOrderNumbers}

('12345', '1234567', '' ,'2020-04-11', '234', '35.00', '39.693702697753906', '-75.53226470947266', '', '', '', ''), ('20514', '9876543', '' ,'2020-04-12', '004', '24.00', '39.27902603149414', '-76.55120086669922', '', '', '', '')

If I copy this and replace ${parsedOrderNumbers} then the statement runs. Any ideas?

2
javasqlgroovydb2
are you using groovy.sql? - injecteer
Can you print (debug) the value of ${parsedOrderNumbers} and check what is in that, and how that differs from your hard coded string - Srinika Pinnaduwage
When I look at it in the debugger it shows " on the beginning and the end of the string. That's the only difference I see. Like "('12345', '1234567', etc...)" - Matt
Yes, I am using groovy.sql - Matt

2 Answers

3
votes

The correct way to parameterize sql for JDBC is:

db.executeInsert("INSERT INTO TABLE MyTable VALUES (?, ?, ?)", ['some', 123, myVar])

Docs: https://docs.groovy-lang.org/latest/html/documentation/sql-userguide.html#_creating_inserting_data

3
votes

When you use ${} syntax together with GString version of execute() (Sql.execute(GString gstring)), groovy-sql will treat each ${} as a bind variable. You're getting the error Sorry, wrong number of values because groovy-sql only saw one bind variable when you're trying to insert 12 values.

What you're doing is actually not best practise, form both security (SQL injection) and performance (DB query cache) perspective.

If you really want to generate the VALUES clause upfront then use the String version of Sql.execute(String query) like this.

db.execute("""
        INSERT INTO TABLE 
        (UNIT, ORD#, DISP, DATE, TIME, AMT, LAT, LONG, DRVR, OWNR, SETT, STAT) 
        values ${parsedOrderNumbers}
        """.toString())

However, I recommend doing what @ou_ryperd suggested or something like this.

db.execute("""
        INSERT INTO TABLE 
        (UNIT, ORD#, DISP, DATE, TIME, AMT, LAT, LONG, DRVR, OWNR, SETT, STAT) 
        values ($unit, $orderNo, $disp, $date, $time, $amt, $lat, $long, $drvr, $ownr, $sett, $stat)
        """)