Wednesday, October 20, 2010

Be careful using GStrings for SQL

If you are like me, you love the ease with which you can create Groovy scripts to manipulate your databases. Gone are the days of writing utility classes to make these changes, thanks to Groovy and the SQL package. But if you aren't careful, you can run into a couple of strange errors if you use the GStrings.

For the below example, I have a table named LOCATION in my 'test' database that has two columns: location_id and location_name. The example below works fine and you notice that I am not using any variables in the SQL statement

import groovy.sql.Sql

db = Sql.newInstance('jdbc:mysql://127.0.0.1:3306/test', 'root', 'root', 'com.mysql.jdbc.Driver')

def sqlStatement = """select * from location where location_id > 3"""

db.eachRow (sqlStatement){ 
   println it
}

println "Done!"

Next, try making the SQL a bit more dynamic by specifying the table name, column name and key value as variables within the GString and watch what happens.

import groovy.sql.Sql

db = Sql.newInstance('jdbc:mysql://127.0.0.1:3306/test', 'root', 'root', 'com.mysql.jdbc.Driver')

def tableName = 'location'
def columnName = 'location_id'
def key = 3
def sqlStatement = """select * from $tableName where $columnName > $key"""

db.eachRow (sqlStatement){ 
   println it
}

println "Done!"

WARNING: Failed to execute: select * from ? where ? > ? because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''location' where 'location_id' > 3' at line 1

The quick fix I found was to call the toString() method on the GString and this resolves the problem. I found a line in "Groovy In Action" which helps explain the problem:
The special use of GStrings as SQL statements limits the use of placeholders to places where a question mark would otherwise be allowed in a prepared statement.


import groovy.sql.Sql

db = Sql.newInstance('jdbc:mysql://127.0.0.1:3306/test', 'root', 'root', 'com.mysql.jdbc.Driver')

def tableName = 'location'
def columnName = 'location_id'
def key = 3
def sqlStatement = """select * from $tableName where $columnName > $key""".toString()

db.eachRow (sqlStatement){ 
   println it
}

println "Done!"

Keep this in mind the next time you use the Groovy SQL class!

7 comments:

  1. This should work as well I believe:

    def sqlStatement = """select * from ${Sql.expand tableName} where $columnName > $key"""

    ReplyDelete
  2. So do I understand correctly that your suggestion is to introduce an SQL injection vulnerability?

    Yes GStrings with the groovy SQL package provides a convenient way for you to not have to use prepared statements directly but still benefit from their purpose.

    Calling toString on the GString would resolve the variables in the String and then pass the resolved String to the JDBC prepared statement without any prep work so the prepared statement won't be able to do anything about malicious input.

    ReplyDelete
  3. Look at the method groovy.sql.Sql.expand. It can control which parameters should be expanded.

    ReplyDelete
  4. Tim,

    Thanks for the suggestion, I will give it a try.

    I had encountered the problem with a different set of SQL but couldn't find that when I when to write the blog post. In that case, I had even tried using the prepared statement format passing the list of parms in the execute statement. The error message on that set of SQL complained about 'in correct number of parameters for the prepared statement' (or something close) which was what led me to looking thru some of the SQL doc.

    @Anonymous - Thanks for bringing up the point about SQL Injection, I hadn't thought about that. My case was simply a database script where I controlled to SQL. I am guessing the expand method mentioned by a couple others might be a better resolution to my problem but I didn't know about it at the time.

    Thanks for educating me!

    ReplyDelete
  5. Sql.expand, From documentation:

    When using GString SQL queries, allows a variable to be expanded in the Sql string rather than representing an sql parameter.

    Example usage:
    def fieldName = 'firstname'
    def fieldOp = Sql.expand('like')
    def fieldVal = '%a%'
    sql.query "select * from PERSON where ${Sql.expand(fieldName)} $fieldOp ${fieldVal}", { ResultSet rs ->
    while (rs.next()) println rs.getString('firstname')
    }

    ReplyDelete
    Replies
    1. Thanks for supply a fix/alternative for this. I think someone made the same suggestion, but without providing a code example.

      Delete