CODEWITHSUNDEEP
jdbcgroovybatch-processing
Jared
Jared

Reputation: 39877

Batch insert using groovy Sql?

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?

Upvotes: 9

Views: 19718

Answers (4)

btiernay
btiernay

Reputation: 8129

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

Upvotes: 1

Weirong Xu
Weirong Xu

Reputation: 29

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.

Upvotes: 1

avgvstvs
avgvstvs

Reputation: 6315

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

Upvotes: 0

topchef
topchef

Reputation: 19783

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

Upvotes: 20

Related Questions