Here is another little gotcha that I have run into (again, due to not thinking and being dumb).
I would use Groovy SQL and start testing in the script leaving me with:
sql.eachRow(”SELECT userpk FROM users where email=’[email protected]’”) { user |
… do stuff
}
then at some point I would want to use a variable for the email and would end up with:
sql.eachRow(”SELECT userpk FROM users where email=’${email}’”) { user |
… do stuff
}
What is wrong with this? The quotes around the variable are wrong. You don’t think of it as “put in this variable here” as Groovy SQL can be smart and will make a prepared statement with question marks ? ? ?
So, taking out the quotes does the trick:
sql.eachRow(”SELECT userpk FROM users where email=${email}”) { user |
… do stuff
}
There is talk on allowing $email as well as ${email}. Bring it on :)
July 14th, 2004 at 12:46 pm
Maybe we could turn on warnings in Groovy SQL so that if you use a $expression inside some SQL it warns you if there are single or double quotes around the expression.
e..g
sql.eachRow “select * from blah where x = ‘$foo’”
would generate a warning but this wouldn’t
sql.eachRow “select * from blah where x = $foo”
July 14th, 2004 at 12:51 pm
That would be like butter :)
D
July 14th, 2004 at 3:22 pm
Dion, your wish is my command (again :).
CVS HEAD now warns if you accidentally quote a dynamic expression.
Strictly speaking, GroovySQL could be clever enough to trim off the unnecessary quotes for you automatically and still reuse the prepared statement – but for now the query still works (maybe there’s a need for this as it enforces string coercion?) – but gives a nice warning to let you know there’s a possible security leak.