ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <lmead...@apache.org>
Subject Re: parameter map and like %?% query
Date Fri, 16 Feb 2007 03:07:18 GMT
The problem here is that iBATIS takes a query like this:

select * from employee where lastname like #value#

...and makes this from it:

select * from employee where lastname like ?

... and then we use a prepared statement to plop the parameter in there.

When you put the %% in there, it becomes this:

select * from employee where lastname like %?%

...and that isn't valid SQL, so you get a kaboom.

Personally, I add the %% in the code that calls the sqlMapClient, but
you have a few other options, some better than others.

For example, you could do this (as Jeff suggested):

select * from employee where lastname like $value$

.. and that will do what you want, BUT will also leave you wide open
for sql injection. If you are not familiar with that, what happens if
value is '%blah%';drop table employee;--'?

That would be an "OMG!" moment for most developers. :-)

A third option that might be better is this:

select * from employee where lastname like '%' || #value# || '%'

Note that this may not work with all databases, either.

Larry


On 2/15/07, cmose <cjmose@gmail.com> wrote:
>
> I hope this isn't a blatantly obvious question but it may well be...
>
> I'm trying to do a LIKE query using either a parameter map or an inline
> parameter map and I can't seem to get that to work with the % and %
> characters, what I would like to do is something along the lines of LIKE %?%
> or LIKE %#value#%  however, I can't seem to get that working. Every time I
> try it using %?% I get a mysql syntax error indicating that ibatis is adding
> quotes around the %'s e.g,. '%'value'%' which causes mysql to barf.
>
> I tried quoting the %?%, e.g., "%?%" but that causes ibatis to barf saying
> that it can't find a parameter...
>
> Does anyone have advice/can point out something obvious that I'm missing or
> is this just not possible?
>
> Thanks!
> --
> View this message in context: http://www.nabble.com/parameter-map-and-like-----query-tf3236388.html#a8994700
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>

Mime
View raw message