search

mirage-sql / mirage

Mirage-SQL - the SQL Centric Database Access Library for Java
Apache License 2.0
59 stars 24 forks source link

Support for 'for' (loop) command in the SQL parser. #2

Open aionescu-zz opened 11 years ago

aionescu-zz commented 11 years ago

Please add support for the "for" command in the SQL parser. E.g. Something like below might be a possibility:

select * from book
/* if $tags !=null AND !$tags.isEmpty() */
    where book.published > 2011
    /* FOR $tag in $tags */
       and  book.tag = /*$tag*/'mirage'
    /*END*/
/*END*/

Of course, one option might be for the above example to use IN with arrays, but because it's bound to only one parameter this is open to SQL Injection, so in this case the Prepared Statement doesn't bring too much advantage and security. (besides there are many more cases where it's simply not possible to reformulate the statement to make use of IN with arrays).

Thank you very much.

takezoe commented 11 years ago

Thanks for your request.

Yes, I understand a necessity for FOR loop. But I have one question in your comment.

it's bound to only one parameter this is open to SQL Injection

What does it mean? I think it's safe to give the array which has one element to IN parameter. Are you describing about other case?

If there are any cases which cause SQL injection with /*var*/ (NOT /*$var*/), it's a bug of Mirage, so I'll fix it as soon as possible.

aionescu-zz commented 11 years ago

But I have one question in your comment.

it's bound to only one parameter this is open to SQL Injection

What does it mean? I think it's safe to give the array which has one element to IN parameter. Are you describing about other case?

An array with more than one element is the problem.

I think last time I've tried, with a Prepared Statement was possible to bind only one parameter, not a "variable sized one", i.e. 

where something IN (?)

So since Mirage allows to "bind an array", it must concatenate the elements, and since it doesn't seems to use http://code.google.com/p/owasp-esapi-java/ that's probably not SQL injection proof.

takezoe commented 11 years ago

a Prepared Statement was possible to bind only one parameter, not a "variable sized one"

Yes, so Mirage expands array binding to the multiple placeholder.

where something IN /*array*/

to

where something IN (?, ?, ...)

See https://github.com/takezoe/mirage/issues/1#issuecomment-14438381 to know how to bind array as IN parameter in Mirage:

hansgru commented 11 years ago

Any news on the "loop" command support?

werne commented 10 years ago

I subscribe to @hansgru 's request. A "for" loop would simplify quite a few scenarios.

tnx

aadrian commented 9 years ago

+1