[groovy-user] Dynamic expressions in Groovy SQL

[Alexander Fedulov]

Hi all,

I need to execute a LIKE sql expression using groovy sql. The problem is
that LIKE requires it's argument to be in quotes, like this:

sql.firstRow("SELECT * FROM scores WHERE name LIKE \"John Doe\";")?.score
>score: 123

which works fine unless you pass the name as a parameter:

sql.firstRow("SELECT * FROM scores WHERE name LIKE \"$name\";")?.score

causing the program to fail with the following message without producing the
desired result:

Warnung: In Groovy SQL please do not use quotes around dynamic expressions
(which start with $) as this means we cannot use a JDBC PreparedStatement
and so is a security hole. Groovy has worked around your mistake but the
security hole is still there. The expression so far is: SELECT * FROM
cpu_score WHERE name LIKE ('?');
>score: null

I have tried single quotes and plain Sting concatenation " + name + "
without success.
Any advice?

Alex



--
View this message in context: http://groovy.329449.n5.nabble.com/Dynamic-expressions-in-Groovy-SQL-tp5710851.html
Sent from the groovy - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Tim Yates]

Doesn't this work:

    sql.firstRow("SELECT * FROM scores WHERE name LIKE $name")?.score

Tim

[Alexander Fedulov]

Nope.

This is equivalent to

SELECT * FROM scores WHERE name LIKE John Doe

which will also fail when executed directly in mysql, because John Doe
without quotes is attempted to be interpreted as a part of mysql syntax.

12:46:28 SELECT * FROM score WHERE name LIKE John Doe Error Code: 1064. 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 'John Doe' at line 1

Each example here:
http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like
LIKE Function uses quotes, and this is exactly what groovy sql forbids me
to do.

Alex



--
View this message in context: http://groovy.329449.n5.nabble.com/Dynamic-expressions-in-Groovy-SQL-tp5710851p5710853.html

[Tim Yates]

That looks like you typed the query directly into MySQL...

Did you actually try it in Groovy? Groovy should deal with the quotes for you

Tim

[Alexander Fedulov]

I have tried both. I am not sure whether groovy sql can be clever enough to
deal with these kind of things. If it should be, it definitely does not cut
it for the LIKE operator. Or do you assume that it should just blindly wrap
every ${expression} into quotes? What if you would want to pass a function
name or an SQL operator as a parameter?

Alex



--
View this message in context: http://groovy.329449.n5.nabble.com/Dynamic-expressions-in-Groovy-SQL-tp5710851p5710855.html

[Tim Yates]

Right here's an example...

It works like I said, you don't need to add quotes yourself...

// Grab an in-memory database for testing

@GrabConfig(systemClassLoader=true)

@Grab('org.hsqldb:hsqldb:2.2.8')

import groovy.sql.Sql

// Get a connection

def sql = Sql.newInstance( "jdbc:hsqldb:mem:database", "sa", "", "org.hsqldb.jdbcDriver" )

// Create a table

sql.execute '''create table TEST (

              |  name varchar(50)

              |)'''.stripMargin()

// Populate it

['tim', 'tina', 'dave', 'doris', 'claire', 'colin'].each {

  sql.execute "INSERT INTO TEST( name ) VALUES ( $it )"

}

// Find all names like 't%'

def likeStr = 't%'

println sql.rows( "SELECT * FROM TEST WHERE name LIKE $likeStr" )

// Drop our test table

sql.execute 'DROP table TEST'

[Tim Yates]

The output of that script is:

[[NAME:tim], [NAME:tina]]

[Tim Yates]

Hope that worked for you :-)

Tim

[Dinko Srkoc]

On 31 July 2012 13:07, Alexander Fedulov <ijustwant...@gmail.com> wrote:
> I have tried both. I am not sure whether groovy sql can be clever enough to
> deal with these kind of things. If it should be, it definitely does not cut
> it for the LIKE operator. Or do you assume that it should just blindly wrap
> every ${expression} into quotes? What if you would want to pass a function
> name or an SQL operator as a parameter?

Groovy Sql need not be clever because it doesn't work that way. When you write:

"SELECT * FROM scores WHERE name LIKE $name"

what you get is not String but GString[1]. The actual method invoked
in your case is:

GroovyRowResult firstRow(GString) [2]

In effect, using GString is the same as using explicit parameters:

sql.firstRow("SELECT * FROM scores WHERE name LIKE ?", [name])

Your variable `name` is used as a parameter, consequently creating
Java's PreparedStatement[3] that is used under the hood.

As for function names and SQL operators - those can't be used as
parameters simply because they are not parameters. You can, however,
use them in Groovy almost like parameters:

def op = Sql.expand('LIKE')
sql.firstRow("SELECT * FROM scores WHERE name $op $name")

Finally, I would suggest going through javadoc for groovy.sql.Sql[4],
as it is, for this particular class, quite informative.

Cheers,
Dinko

[1]: http://groovy.codehaus.org/Strings+and+GString#StringsandGString-GStrings
[2]: http://groovy.codehaus.org/gapi/groovy/sql/Sql.html#firstRow(groovy.lang.GString)
[3]: http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html
[4]: http://groovy.codehaus.org/gapi/groovy/sql/Sql.html

[Alexander Fedulov]

Tim, Dinko, thank you both for your replies. I am sorry that my answer is so
delayed - I had too many first prio tasks last week. Today I experimented a
bit and I can confirm (for my case at least) - I cannot make GroovySQL work
correctly without the quotation marks in my full text search. I have
modified the code snippet that Tim kindly provided:

@GrabConfig(systemClassLoader=true)
@Grab('mysql:mysql-connector-java:5.1.6')

import groovy.sql.Sql

// Get a connection

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/test", "root",
"root", "com.mysql.jdbc.Driver")

try{
sql.execute 'DROP table cpu_test'
}catch(ex){
}

sql.execute '''CREATE TABLE `cpu_test` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NULL ,
PRIMARY KEY (`id`) ,
FULLTEXT INDEX `name_index` (`name` ASC) )
ENGINE = MyISAM;
'''.stripMargin()

// Populate it
[ 'amd athlon', 'amd athlon dual core 5000b', 'amd athlon', 'amd athlon ii
dual-core m320', 'amd athlon ii x3 440', 'amd athlon dual core 4450b', 'amd
athlon xp'].each {
sql.execute "INSERT INTO cpu_test( name ) VALUES ( $it )"
}

def name = 'amd athlon ii x3 440'

def query = "SELECT * FROM cpu_test WHERE MATCH name AGAINST ('\"$name\"' IN
BOOLEAN MODE);".toString();
println 'This works:'
println sql.firstRow(query)

println 'This does not:'
println sql.firstRow("SELECT * FROM cpu_test WHERE MATCH name AGAINST ($name
IN BOOLEAN MODE)")

// Drop our test table

sql.execute 'DROP table cpu_test'


Output:
This works:
[id:5, name:amd athlon ii x3 440]
This does not:
[id:1, name:amd athlon]


As you might notice, I switched from using LIKE to using MATCH AGAINST based
on Mysql MyISAM engine and FULLTEXT index, but I experience the same issues
with LIKE, when I use the full database, and not just a small sample value
set. Any ideas of how to make this work without leaving security holes?



--
View this message in context: http://groovy.329449.n5.nabble.com/Dynamic-expressions-in-Groovy-SQL-tp5710851p5710881.html

[Dinko Srkoc]

Hi Alexander,

On 6 August 2012 18:57, Alexander Fedulov <ijustwant...@gmail.com> wrote:
> [...] Today I experimented a

> bit and I can confirm (for my case at least) - I cannot make GroovySQL work

> correctly without the quotation marks in my full text search. [...]

I'll admit that that's surprising.

> [...]

> // Populate it
> [ 'amd athlon', 'amd athlon dual core 5000b', 'amd athlon', 'amd athlon ii
> dual-core m320', 'amd athlon ii x3 440', 'amd athlon dual core 4450b', 'amd
> athlon xp'].each {
> sql.execute "INSERT INTO cpu_test( name ) VALUES ( $it )"
> }

I take it that this parametered SQL statement works?

>
> def name = 'amd athlon ii x3 440'
>
> def query = "SELECT * FROM cpu_test WHERE MATCH name AGAINST ('\"$name\"' IN
> BOOLEAN MODE);".toString();
> println 'This works:'
> println sql.firstRow(query)

Yes, that would work. It's just a plain String. Why are there single
and double quotes surrounding `name` parameter though?

>
> println 'This does not:'
> println sql.firstRow("SELECT * FROM cpu_test WHERE MATCH name AGAINST ($name
> IN BOOLEAN MODE)")

That is unexpected. Still, I don't think that's Groovy's fault. At
this time I would put my money of MySQL's driver as being the culprit.

> [...]

> As you might notice, I switched from using LIKE to using MATCH AGAINST based
> on Mysql MyISAM engine and FULLTEXT index, but I experience the same issues
> with LIKE, when I use the full database, and not just a small sample value
> set. Any ideas of how to make this work without leaving security holes?

I'm not very familiar with MySQL, so I have no opinion of `LIKE` vs
`MATCH AGAINST` (nor the knowledge, for that matter, to make an
opinion).
Are you saying that `LIKE` works for a small sample value set, but not
for the full database?

Here is what I would do:

* use a newer MySQL driver. I believe 5.1.21 is the most recent one.
* try the explicit parameters: sql.firstRow('... WHERE name LIKE ?', [name])
* try it in Java, using JDBC directly
* try it with another DBMS, just to see that the thing actually works
somewhere else. I have tried it with IBM Informix and PostgreSQL
(admittedly, using `LIKE`).

Cheers,
Dinko