Re: how to Escape single quotes with PreparedStatment

JavaNoobie <vivek.mv@enzentech.com> Saturday 20 of August 2011 13:55:45
> Hi All,
> I'm trying to write a preparedstatement query as below.
>
>     String query=  "SELECT count(*) over () as ROWCOUNT,
> CONSUMER_ID,WENEXA_ID,CONSUMER_NAME,CONTACT_NO,residing_village from
> db_consumer WHERE (lower(CONSUMER_NAME) LIKE (lower('%"+name+"%'))OR
> (lower('" + name + "')='')) AND (lower(RESIDING_VILLAGE) LIKE
> (lower('%"+village+"%')) OR (lower('" + village + "')='')) AND
> (lower(WENEXA_ID) LIKE (lower('%"+wenexaid+"%'))OR(lower( '" + wenexaid +
> "')='')) LIMIT '"+pageLimit+"'OFFSET '"+pageOffset+"'";
>
>                 stmt = con.prepareStatement(query);
>                 rs= stmt.executeQuery();
>
> However , the query fails with postgresql when a double quote is passed
> into it.I was under the impression that Prepared statement would take care
> of the same . But can anyone explain why I'm getting the error?
> Thank you.

Prepared statements do escaping (actualy PS do not make this, because those
sends just raw parameters). In order to make this your statement should look
like (e.g.)
(lower('" + name + "') => (lower(?)
then you call ps.setString(1, /*index of name*/, name);

Driver can't know what should be escaped or should not - in your query you
just pass full query string.

Consider following (SQL hacking guide) code
String query = "SELECT * FROM dummy WHERE name = '" + something + "'";
Driver should have possibility to look in your constructing expression to try
to guess that name is parameter (C#, allows simillar constructs), but it can't
because Java will do
StringBuilder sb = new StringBuillder();
sb.append("SELECT * FROM dummy WHERE name = '");
sb.append(something);
sb.append("'");
query = sb.toString();

From other side, one may want that "something" will be longer (something =
"'SomeName' and surname = 'SomeSureName");

And some one may want:
something = "'SomeName'; DROP TABLE security_log; SELECT * FROM dummy where
name='d"

Regards,
Radek