Batch insert using groovy Sql?

Question

How can you do a batch insert using groovy Sql while simulating prepared statements? All the examples I've found are similar to the following and don't use prepared statements.

withBatch  { stmt ->
stmt.addBatch("insert into table (field1,field2) values('value1','value2')")
stmt.addBatch("insert into table (field1,field2) values('value3','value4')")
}

According to this link https://issues.apache.org/jira/browse/GROOVY-3504 there is no way to use prepared statements directly from within batch. What is the best way to simulate this so I can avoid having to write my own code to avoid sql injection?

score 20 · Answer 1 · answered Sep 01 '11 at 05:22

20

Groovy 1.8.1 introduced support for prepared statements with batching. Simple example:

sql.withBatch(20, """update some_table 
                        set some_column = :newvalue 
                      where id = :key """) { ps ->                 
          mymap.each { k,v ->
              ps.addBatch(key:k, newvalue:v)
          }
}

Also see my post on the topic: http://novyden.blogspot.com/2011/09/groovy-batch-prepared-statement-nice.html

answered Sep 01 '11 at 05:22

topchef

19,091
9
63
102

2

Thanks for the tip! To get this to work for MySQL InnoDB, I had to add "sql.connection.autoCommit = false" before the withBatch, and "sql.connection.commit()" to make it stick. – Andrew Sep 13 '11 at 04:41
How will you do this multiple columns ? – Anuj Kulkarni Apr 02 '15 at 21:05

score 1 · Answer 2 · answered Feb 08 '13 at 02:43

1

It's supported from version 1.8.1. You can read the Groovy 1.8.1 release notes for details. Pls check the API Document for help.

answered Feb 08 '13 at 02:43

Weirong Xu

29
3

score 1 · Answer 3 · edited Jun 09 '21 at 11:31

Also related is https://issues.apache.org/jira/browse/GROOVY-4328.

From the above JIRA:

...the best we could (simply) do is convert such a GString to a normal String. For that we could do a little bit more than we currently do by parsing the String and try to quote or escape "string" looking things but not number or date looking things but it might not be very elegant. Normally we would use the "?" character placeholders and a prepared statement and would have much less to do

In terms of simulating prepared statements, see Java - escape string to prevent SQL injection

That being said, you could apply a heuristic from the above and decorate the withBatch method

score 0 · Answer 4 · answered Dec 15 '11 at 14:52

Owasp ESAPI. https://www.owasp.org/index.php/Category:OWASP_Enterprise_Security_API

Your only option when prepared statements and stored procs are NOT an option, is to manually escape user input.

ESAPI has working, production ready reference methods.

Codec ORACLE_CODEC = new OracleCodec();
 String query = "SELECT user_id FROM user_data WHERE user_name = '" + 
   ESAPI.encoder().encodeForSQL( ORACLE_CODEC, req.getParameter("userID")) + "' and user_password = '"
   + ESAPI.encoder().encodeForSQL( ORACLE_CODEC, req.getParameter("pwd")) +"'";

Source: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Databas

Batch insert using groovy Sql?

4 Answers4

Linked