Обсуждение: PreparedStatements, LIKE and the % operator

Hi:

I am using postgres 8.2 with the 8.2.504 jdbc3 driver.

I am getting data from a untrusted source. Hence a
prepared
statement. I also need a partial match.

String query =  " select * from table_foo where bar =
LIKE %?% "
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, "haha");
....

This craps out when run. Try adding single quotes
before and
after the: %?%

String query =   " select * from table_foo where bar =
LIKE '%?%'  "
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, "haha");
...

This craps out too.

A quick search of the archives doesn't shed light on
this issue. I
don't need a JDBC escape since I want to use a % char.

So how do I use LIKE within a prepared statement ? I'm
sure I'm
missing something obvious here....

Best regards,
--j





____________________________________________________________________________________
Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html

Hi

Craps out how ?

bar = like %?%

is invalid

bar like '%?%'

is closer to the correct syntax

Dave
On 2-Feb-07, at 10:58 PM, j.random.programmer wrote:

> Hi:
>
> I am using postgres 8.2 with the 8.2.504 jdbc3 driver.
>
> I am getting data from a untrusted source. Hence a
> prepared
> statement. I also need a partial match.
>
> String query =  " select * from table_foo where bar =
> LIKE %?% "
> PreparedStatement ps = con.prepareStatement(query);
> ps.setString(1, "haha");
> ....
>
> This craps out when run. Try adding single quotes
> before and
> after the: %?%
>
> String query =   " select * from table_foo where bar =
> LIKE '%?%'  "
> PreparedStatement ps = con.prepareStatement(query);
> ps.setString(1, "haha");
> ...
>
> This craps out too.
>
> A quick search of the archives doesn't shed light on
> this issue. I
> don't need a JDBC escape since I want to use a % char.
>
> So how do I use LIKE within a prepared statement ? I'm
> sure I'm
> missing something obvious here....
>
> Best regards,
> --j
>
>
>
>
>
> ______________________________________________________________________
> ______________
> Don't pick lemons.
> See all the new 2007 cars at Yahoo! Autos.
> http://autos.yahoo.com/new_cars.html
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>

Hi:

> Craps out how ?
>
> bar = like %?%

That was a typo. Sorry. I wasn't using '=' at all.

>
> bar like '%?%'
> is closer to the correct syntax

That's what I was doing. The driver gives this error:

-----------------------------
org.postgresql.util.PSQLException: The column index is
out of range: 1, number of columns: 0.
    at
org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:52)
    at
org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:117)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2118)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1241)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1227)
....etc........
--------------------------------

I hacked around a bit and finally said:

.... bar like ? and foo like ?

ps.setString(1, "%" + myvalue + "%")
ps.setString(2, "%" + my_other_value + "%")

That worked but it's a bit counter-intuitive.

Best regards,
--j



____________________________________________________________________________________
Don't get soaked.  Take a quick peak at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather

j.random.programmer wrote on 03.02.2007 04:58:
> Hi:
>
> I am using postgres 8.2 with the 8.2.504 jdbc3 driver.
>
> I am getting data from a untrusted source. Hence a
> prepared
> statement. I also need a partial match.
>
> String query =  " select * from table_foo where bar =
> LIKE %?% "
> PreparedStatement ps = con.prepareStatement(query);
> ps.setString(1, "haha");

That should be:

String query = "select * from table_foo where bar like ?";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, "%haha%");

Thomas

I would suggest:

LIKE '%' || ? || '%'


On Feb 2, 2007, at 10:58 PM, j.random.programmer wrote:

> Hi:
>
> I am using postgres 8.2 with the 8.2.504 jdbc3 driver.
>
> I am getting data from a untrusted source. Hence a
> prepared
> statement. I also need a partial match.
>
> String query =  " select * from table_foo where bar =
> LIKE %?% "
> PreparedStatement ps = con.prepareStatement(query);
> ps.setString(1, "haha");
> ....
>
> This craps out when run. Try adding single quotes
> before and
> after the: %?%
>
> String query =   " select * from table_foo where bar =
> LIKE '%?%'  "
> PreparedStatement ps = con.prepareStatement(query);
> ps.setString(1, "haha");
> ...
>
> This craps out too.
>
> A quick search of the archives doesn't shed light on
> this issue. I
> don't need a JDBC escape since I want to use a % char.
>
> So how do I use LIKE within a prepared statement ? I'm
> sure I'm
> missing something obvious here....
>
> Best regards,
> --j
>
>
>
>
>
> ______________________________________________________________________
> ______________
> Don't pick lemons.
> See all the new 2007 cars at Yahoo! Autos.
> http://autos.yahoo.com/new_cars.html
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate