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!
This should work as well I believe:
ReplyDeletedef sqlStatement = """select * from ${Sql.expand tableName} where $columnName > $key"""
So do I understand correctly that your suggestion is to introduce an SQL injection vulnerability?
ReplyDeleteYes 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.
Look at the method groovy.sql.Sql.expand. It can control which parameters should be expanded.
ReplyDeleteTim,
ReplyDeleteThanks 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!
great article
ReplyDeleteSql.expand, From documentation:
ReplyDeleteWhen 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')
}
Thanks for supply a fix/alternative for this. I think someone made the same suggestion, but without providing a code example.
Delete