From: | Radosław Smogura <mail(at)smogura(dot)eu> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Cc: | JavaNoobie <vivek(dot)mv(at)enzentech(dot)com> |
Subject: | Re: how to Escape single quotes with PreparedStatment |
Date: | 2011-08-21 10:45:14 |
Message-ID: | 201108211245.14465.mail@smogura.eu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
JavaNoobie <vivek(dot)mv(at)enzentech(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | vanayya | 2011-08-22 03:00:06 | Re: Display SQL from Java Prepared Statement |
Previous Message | Maciek Sakrejda | 2011-08-21 01:52:49 | Re: how to Escape single quotes with PreparedStatment |