Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (herse.apache.org: domain of larry.meadors@gmail.com
 designates 64.233.184.232 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:reply-to:sender:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth;
        b=Mr/9vNjS63Uai3KzUUp/bxEcFJvM8nYcMqNHYUjY1A7iZt2XRs75EH7aj9fnLIkHeZuc2saceQzz2ZbX6GKB7Nw6SXTXT6GBUN1KrzOZIZp3svXg0CNBm8VMeqzy+zo3LmfDzPJ44lHcsy9NBi4fp0PBPrQKuZ026Np0jGvEIDU=
Message-ID: <a10a823c0702151907x7aed3a84sa47cd8e1e3fa3a1c@mail.gmail.com>
Date: Thu, 15 Feb 2007 20:07:18 -0700
From: "Larry Meadors" <lmeadors@apache.org>
Reply-To: lmeadors@apache.org
Sender: larry.meadors@gmail.com
To: user-java@ibatis.apache.org
Subject: Re: parameter map and like %?% query
In-Reply-To: <8994700.post@talk.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <8994700.post@talk.nabble.com>

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.
>
>