Using #PARAM# constructions within $DYNAMICSQL$

[DelGurth <de...@gmail.com>]

Hi,

I was wondering if it was possible to use #PARAM# constructions within
a $DYNAMICSQL$ query.I'm currently using iBatis sqlmap 2.3.0. And I'm
trying to get the following to work:

SELECT * FROM person
 $DYNAMICSQL$

With $DYNAMICSQL$ defined as: WHERE person.lastname LIKE '%#LASTNAME#%'

So the query being send to the database will end up as:

SELECT * FROM person
 WHERE person.lastname LIKE '%?%'

With that I hope the filter I'm creating is less prone to SQL
Injection since the user data #LASTNAME# is still entered using bind
variables and thus properly escaped.

Just doing as above currently gives me #LASTNAME# within the query, so
it doesn't seem to be working. But I was hoping I'm doing something
wrong. Or is the $$ construction being parsed/replaced in the same run
as the ## construction? And if that's the case, is there some way to
change that behaviour of iBatis, or is there a reason you shouldn't
want that?

I hope you can help me.

Regards,
Wessel van Norel

Re: Using #PARAM# constructions within $DYNAMICSQL$

[DelGurth <de...@gmail.com>]

On Wed, Apr 29, 2009 at 2:44 PM, Chris O'Connell
<oc...@gorillachicago.com> wrote:
> I sympathize with your problem.  Perhaps you could just build a set of sql
> includes, each of which is the correct sql for a particular use case.  Then,
> rather than building the sql string in your code, you instead just pass a
> parameter into iBatis that it can use to determine which is the correct sql
> include to use.  Then, you will be using ## for bind variables.
> Now, if you have hundreds of these cases, or there is some complicated
> algorithm that is build those 'where' clauses, then you might be out of
> luck.

Guess I'm out of luck then, since we want to use this to filter many
different queries using the same algorithm. And the query part of the
filter is being build by our algorithm, so it's "safe" when we are
able to use bind-variables for the userdata (the filter variables).

> Your best bet in that case might be to write your own sql injection
> cleaner for all input variables (not a bad idea in many cases, anyway, since
> you already have to worry about cross site scripting, cross site request
> forgery, css injections, etc. etc. :) ).
> Chris

Rather then writing a sql injection cleaner, I think our best option
is to change the iBatis code so it can do what we want. Since the
"only" change we need is parsing the query twice, first for the Simple
Dynamic SQL and then for the parameters. Taking into account that the
Simple Dynamic SQL that we write will be safe to use, since the user
entered information is put in the query using bind-variables.

Regards,
Wessel van Norel

Re: Using #PARAM# constructions within $DYNAMICSQL$

[Chris O'Connell <oc...@gorillachicago.com>]

I sympathize with your problem.  Perhaps you could just build a set of sql
includes, each of which is the correct sql for a particular use case.  Then,
rather than building the sql string in your code, you instead just pass a
parameter into iBatis that it can use to determine which is the correct sql
include to use.  Then, you will be using ## for bind variables.
Now, if you have hundreds of these cases, or there is some complicated
algorithm that is build those 'where' clauses, then you might be out of
luck.  Your best bet in that case might be to write your own sql injection
cleaner for all input variables (not a bad idea in many cases, anyway, since
you already have to worry about cross site scripting, cross site request
forgery, css injections, etc. etc. :) ).

Chris

On Wed, Apr 29, 2009 at 3:13 AM, DelGurth <de...@gmail.com> wrote:

> On Wed, Apr 29, 2009 at 5:30 AM, Nathan Maves <na...@gmail.com>
> wrote:
> > I have not tried this but I don't see why it would not work.
> >
> >
> > SELECT * FROM person
> >  WHERE person.lastname LIKE '%'||#lastname#||'%'
> >
> > the || is the concat operator for oracle.  it might be something else in
> > another vendor
>
> Probably you misunderstood my question. I'm not trying to get
> #lastname# itself within my query.
>
> $$ placeholders and ## placeholders are normally parsed by iBatis.$$
> placeholders are just substituted with the value of the variable and
> ## placeholders are turned into bind variables. But if you put a ##
> placeholder _within_ the value of a $$ placeholder, iBatis doesn't
> parse the ## placeholder, so it doesn't substitute it for a bind
> variable.
>
> So I wish iBatis parsed the query twice, first to replace the $$
> placeholders and then to replace the ## placeholders _including_ the
> placeholders that where set within my $$ placeholder.
>
> Hope I made it more clearly now.
>
> Regards,
> Wessel
>



-- 
-- 
Chris O'Connell
Application Developer
Gorilla
312.243.8777 x19

Re: Using #PARAM# constructions within $DYNAMICSQL$

[DelGurth <de...@gmail.com>]

On Wed, Apr 29, 2009 at 5:30 AM, Nathan Maves <na...@gmail.com> wrote:
> I have not tried this but I don't see why it would not work.
>
>
> SELECT * FROM person
>  WHERE person.lastname LIKE '%'||#lastname#||'%'
>
> the || is the concat operator for oracle.  it might be something else in
> another vendor

Probably you misunderstood my question. I'm not trying to get
#lastname# itself within my query.

$$ placeholders and ## placeholders are normally parsed by iBatis.$$
placeholders are just substituted with the value of the variable and
## placeholders are turned into bind variables. But if you put a ##
placeholder _within_ the value of a $$ placeholder, iBatis doesn't
parse the ## placeholder, so it doesn't substitute it for a bind
variable.

So I wish iBatis parsed the query twice, first to replace the $$
placeholders and then to replace the ## placeholders _including_ the
placeholders that where set within my $$ placeholder.

Hope I made it more clearly now.

Regards,
Wessel

Re: Using #PARAM# constructions within $DYNAMICSQL$

[Nathan Maves <na...@gmail.com>]

I have not tried this but I don't see why it would not work.


SELECT * FROM person
 WHERE person.lastname LIKE '%'||#lastname#||'%'

the || is the concat operator for oracle.  it might be something else in
another vendor

On Tue, Apr 28, 2009 at 4:24 PM, DelGurth <de...@gmail.com> wrote:

> Hi,
>
> I was wondering if it was possible to use #PARAM# constructions within
> a $DYNAMICSQL$ query.I'm currently using iBatis sqlmap 2.3.0. And I'm
> trying to get the following to work:
>
> SELECT * FROM person
>  $DYNAMICSQL$
>
> With $DYNAMICSQL$ defined as: WHERE person.lastname LIKE '%#LASTNAME#%'
>
> So the query being send to the database will end up as:
>
> SELECT * FROM person
>  WHERE person.lastname LIKE '%?%'
>
> With that I hope the filter I'm creating is less prone to SQL
> Injection since the user data #LASTNAME# is still entered using bind
> variables and thus properly escaped.
>
> Just doing as above currently gives me #LASTNAME# within the query, so
> it doesn't seem to be working. But I was hoping I'm doing something
> wrong. Or is the $$ construction being parsed/replaced in the same run
> as the ## construction? And if that's the case, is there some way to
> change that behaviour of iBatis, or is there a reason you shouldn't
> want that?
>
> I hope you can help me.
>
> Regards,
> Wessel van Norel
>

Example as dao impl inner class

[Ben Shory <be...@sapiens.com>]

Does anybody else think it's a good design to put the generated example
class as inner class in the dao implementation class (spring dao)?

Re: Using #PARAM# constructions within $DYNAMICSQL$

[DelGurth <de...@gmail.com>]

On Wed, Apr 29, 2009 at 12:24 AM, DelGurth <de...@gmail.com> wrote:
> Hi,
>
> I was wondering if it was possible to use #PARAM# constructions within
> a $DYNAMICSQL$ query.I'm currently using iBatis sqlmap 2.3.0. And I'm
> trying to get the following to work:
>
> SELECT * FROM person
>  $DYNAMICSQL$
>
> With $DYNAMICSQL$ defined as: WHERE person.lastname LIKE '%#LASTNAME#%'
>
> So the query being send to the database will end up as:
>
> SELECT * FROM person
>  WHERE person.lastname LIKE '%?%'
>
> With that I hope the filter I'm creating is less prone to SQL
> Injection since the user data #LASTNAME# is still entered using bind
> variables and thus properly escaped.
>
> Just doing as above currently gives me #LASTNAME# within the query, so
> it doesn't seem to be working. But I was hoping I'm doing something
> wrong. Or is the $$ construction being parsed/replaced in the same run
> as the ## construction? And if that's the case, is there some way to
> change that behaviour of iBatis, or is there a reason you shouldn't
> want that?
>
> I hope you can help me.
>
> Regards,
> Wessel van Norel
>

We have been working on a solution for our problem. The general idea
is to add an extra attribute to the <dynamic> tag,
"deferSubstitutions". If you specify it with true, the ##
substitutions are done after the $$ substitutions. With this we can
make it work with minimal changes to iBatis and keeping it backwards
compatible.

The question now is, should we propose this change to iBatis, and if
so how to do that?

I'll apply the patch we made to this message, so you can see what we
have changed.

Regards,
Wessel

P.s. this patch has been created on iBatis 2.3.0, not HEAD.