[JDBC] how to Escape single quotes with PreparedStatment

[JavaNoobie]

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.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-Escape-single-quotes-with-PreparedStatment-tp4718287p4718287.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

--
Sent via pgsql-jdbc mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

[dmp]

JavaNoobie wrote:
> 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.

Perhaps to get a better idea of what exactly the query string
is that is being executed you could:

System.out.println(query);

stmt = con.prepareStatement(query);
rs= stmt.executeQuery();

danap.

[Andrew Hastie]

Better still, with the Postgres driver once you have prepared the
statement (including when you have "?" parameters for substitution), you
can use the "toString()" method of the PreparedStatement object to see
what the final SQL statement is that will be executed.

So, try this to get a better trace of what is failing:-

PreparedStatement stmt = con.prepareStatement(query);
System.out.println("SQL=" + stmt.toString());
ResultSet rs= stmt.executeQuery();

Hope this helps.

Andrew

[Maciek Sakrejda]

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

What do you mean by "passed into it"? There are no parameter markers
in your query. PostgreSQL/JDBC can only handle escaping of parameter
values for you if you provide them as actual parameters, rather
concatenating them into the query string.

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

[Radosław Smogura]

JavaNoobie <vive...@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

[JavaNoobie]

Hi All ,
Thank you all for your replies. The prepared statement block that I try to
execute , after adding parameters is as follows ,(I've simplified the query
so that I can understand the concept)

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(%\"?\"%))";

stmt = con.prepareStatement(query);
stmt.setString(1, name);
rs= stmt.executeQuery();
However upon executing the block, I get an error as follows
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:53)
at
org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:118)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2184)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1303)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1289)
at com.enzen.cis.dao.DAOConsumerSearch.getcList(DAOConsumerSearch.java:5

Any idea why this could be happening?
Thank you.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-Escape-single-quotes-with-PreparedStatment-tp4718287p4722152.html

Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

--

[ml-tb]

Hi,

Am Montag, 22. August 2011 schrieb JavaNoobie:
> 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(%\"?\"%))";

It should be:

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(%?%))";

If you double quote the question mark, it would be an identifier (filed
name, tabel name ...). The question mark is the insert position for the
parameter. The prepared statement system escapes/expanded/... the
inserted value when necessary. The parameter number ist the count of a
question mark from left to right.

Bye Thomas

>
> stmt = con.prepareStatement(query);
> stmt.setString(1, name);
> rs= stmt.executeQuery();
> However upon executing the block, I get an error as follows
> org.postgresql.util.PSQLException: The column index is out of range:
> 1, number of columns: 0.
> at
> org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.j
> ava:53) at
> org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleP
> arameterList.java:118) at
> org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2S
> tatement.java:2184) at
> org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2St
> atement.java:1303) at
> org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2St
> atement.java:1289) at
> com.enzen.cis.dao.DAOConsumerSearch.getcList(DAOConsumerSearch.java:
> 5
>
> Any idea why this could be happening?
> Thank you.
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/how-to-Escape-single-quotes-

> with-PreparedStatment-tp4718287p4722152.html Sent from the PostgreSQL

[JavaNoobie]

Hi,
I tried that example but it threw an error as follows.
org.postgresql.util.PSQLException: ERROR: syntax error at or near "%"
Position: 158
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)

Why could this be happening?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-Escape-single-quotes-with-PreparedStatment-tp4718287p4722528.html

[Andrew Hastie]

I think you're defining the "?" parameter incorrectly for the PreparedStatement.

Try this instead:-

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(?))";

query.setString(1,"%Fred%");

Where "Fred" is the string you're searching for.

You cannot use the ? token to substitute part of data value, only a whole value. As you are parameterizing the "pattern" argument to the LIKE expression which is a String argument, you must pass the complete pattern as a String parameter.

Hope this helps,
Andrew

[JavaNoobie]

Hi Andrew,
Thanks for the reply.
I'm, able to do that effectively . here's my corrected code snippet

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(?))OR (lower(?)='') AND
(lower(RESIDING_VILLAGE) LIKE (lower(?) OR (lower(?)='')) AND
(lower(WENEXA_ID) LIKE (lower(?))OR(lower(?)='')) LIMIT ? OFFSET ?";
stmt = con.prepareStatement(query);
stmt.setString(1,"%"+name+"%");
stmt.setString(2,"%"+name+"%");
stmt.setString(3,"%"+village+"%");
stmt.setString(4,"%"+village+"%");
stmt.setString(5,"%"+wenexaid+"%");
stmt.setString(6,"%"+wenexaid+"%");
stmt.setInt(7,pageLimit);
stmt.setInt(8,pageOffset);
rs= stmt.executeQuery();
However , it throws near the LIMIT clause. As below:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "LIMIT"
Position: 302

at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)

at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)

Any idea about this one?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-Escape-single-quotes-with-PreparedStatment-tp4718287p4722805.html

[Chris Wareham]

On 20/08/11 12:55, JavaNoobie wrote:
> 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.
>

You should use placeholders (the ? character) in your prepared
statement, and then call the setter methods to insert your query terms.
The JDBC driver will take care of all escaping and quoting for you. You
should also consider converting the query columns to full text indexes
using tsearch. And finally, ou can also simplify your query by only
searching for the non-empty terms.

StringBuilder query = new StringBuilder("SELECT count(*) over() AS
rowcount, consumer_id, wenexa_id, consumer_name, contact_no,
residing_village FROM db_consumer WHERE TRUE");

if (!name.isEmpty()) {
query.append(" AND consumer_name ILIKE ?");
}

if (!village.isEmpty()) {
query.append(" AND residing_village ILIKE ?");
}

if (!wenexaid.isEmpty()) {
query.append(" AND wenexa_id ILIKE ?");
}

buf.append(" LIMIT ? OFFSET ?");

stmt = con.prepareStatement(query);

if (!name.isEmpty()) {
stmt.setString(2, "%" + name + "%");
}

if (!village.isEmpty()) {
stmt.setString(4, "%" + village + "%");
}

if (!wenexaid.isEmpty()) {
stmt.setString(6, "%" + wenexaid + "%");
}

stmt.setInt(7, pageLimit);
stmt.setInt(8, pageOffset);

rs = stmt.executeQuery();

[Chris Wareham]

On 20/08/11 12:55, JavaNoobie wrote:

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

You should use placeholders (the ? character) in your prepared

statement, and then call the setter methods to insert your query terms.
The JDBC driver will take care of all escaping and quoting for you. You
should also consider converting the query columns to full text indexes

using tsearch. And finally, you can also simplify your query by only

searching for the non-empty terms.

StringBuilder query = new StringBuilder("SELECT count(*) over() AS
rowcount, consumer_id, wenexa_id, consumer_name, contact_no,
residing_village FROM db_consumer WHERE TRUE");

if (!name.isEmpty()) {
query.append(" AND consumer_name ILIKE ?");
}

if (!village.isEmpty()) {
query.append(" AND residing_village ILIKE ?");
}

if (!wenexaid.isEmpty()) {
query.append(" AND wenexa_id ILIKE ?");
}

buf.append(" LIMIT ? OFFSET ?");

stmt = con.prepareStatement(query);

if (!name.isEmpty()) {
stmt.setString(2, "%" + name + "%");
}

if (!village.isEmpty()) {
stmt.setString(4, "%" + village + "%");
}

if (!wenexaid.isEmpty()) {
stmt.setString(6, "%" + wenexaid + "%");
}

stmt.setInt(7, pageLimit);
stmt.setInt(8, pageOffset);

rs = stmt.executeQuery();

--

[JavaNoobie]

Hi,
The following code works properly.

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(?))OR (lower(?)='') LIMIT
? OFFSET ? "; // AND (lower(RESIDING_VILLAGE) LIKE (lower(?) OR
(lower(?)='')) AND (lower(WENEXA_ID) LIKE (lower(?))OR(lower(?)=''))" ;

log.info(query); // Get the wenexa ID to be passed from servlet
here....

stmt = con.prepareStatement(query);
stmt.setString(1,"%"+name+"%");
stmt.setString(2,"%"+name+"%");

stmt.setInt(3,pageLimit);
stmt.setInt(4,pageOffset);

However , when I try the code -

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(?))OR (lower(?)='') AND
(lower(RESIDING_VILLAGE) LIKE (lower(?) OR (lower(?)='')) AND
(lower(WENEXA_ID) LIKE (lower(?))OR(lower(?)='')) LIMIT ? OFFSET ?";
stmt = con.prepareStatement(query);
stmt.setString(1,"%"+name+"%");
stmt.setString(2,"%"+name+"%");
stmt.setString(3,"%"+village+"%");
stmt.setString(4,"%"+village+"%");

stmt.setInt(5,pageLimit);
stmt.setInt(6,pageOffset);
rs= stmt.executeQuery();

It throws the previous error:org.postgresql.util.PSQLException: ERROR:

syntax error at or near "LIMIT"
Position: 302
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)

Any idea why this could be happening?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-Escape-single-quotes-with-PreparedStatment-tp4718287p4722898.html

[Chris Wareham]

On 22/08/11 11:04, Chris Wareham wrote:
>
> stmt = con.prepareStatement(query);
>

Should of course be:

stmt = con.prepareStatement(query.toString());

Chris

[Chris Wareham]

Sigh. I really need more coffee at this time of the morning. The
setters need to having an incrementing index:

StringBuilder query = new StringBuilder("SELECT count(*) over() AS
rowcount, consumer_id, wenexa_id, consumer_name, contact_no,
residing_village FROM db_consumer WHERE TRUE");

if (!name.isEmpty()) {
query.append(" AND consumer_name ILIKE ?");
}

if (!village.isEmpty()) {
query.append(" AND residing_village ILIKE ?");
}

if (!wenexaid.isEmpty()) {
query.append(" AND wenexa_id ILIKE ?");
}

buf.append(" LIMIT ? OFFSET ?");

stmt = con.prepareStatement(query.toString());

int i = 0;

if (!name.isEmpty()) {
stmt.setString(++i, "%" + name + "%");
}

if (!village.isEmpty()) {
stmt.setString(++i, "%" + village + "%");
}

if (!wenexaid.isEmpty()) {
stmt.setString(++i, "%" + wenexaid + "%");
}

stmt.setInt(++i, pageLimit);
stmt.setInt(++i, pageOffset);

rs = stmt.executeQuery();

[Oliver Jowett]

On 22 August 2011 22:26, JavaNoobie <vive...@enzentech.com> wrote:

>  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(?))OR (lower(?)='')  AND
> (lower(RESIDING_VILLAGE) LIKE (lower(?) OR (lower(?)='')) AND
> (lower(WENEXA_ID) LIKE (lower(?))OR(lower(?)='')) LIMIT ? OFFSET ?";

> It throws the previous error:org.postgresql.util.PSQLException: ERROR:

> syntax error at or near "LIMIT"
>  Position: 302
>        at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
>        at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
> Any idea why this could be happening?

You have a syntax error in your query, just like the errors says.
Count the parentheses.

(I really can't believe this thread is still going)

Oliver

[Radosław Smogura]

Just for info, if name is empty, then probably there will be no
parameter 8, etc.
Use following block:
int i=2;
if (!name.isEmpty()) {
stmt.setString(i, "%" + name + "%");
i++;
}
etc...
stmt.setInt(i, pageOffset);

Regards

[JavaNoobie]

Sorry for all that unwarranted noise .
Problem solved.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-Escape-single-quotes-with-PreparedStatment-tp4718287p4723279.html

Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

--