Re: how to Escape single quotes with PreparedStatment

From: Chris Wareham <cwareham(at)londonandpartners(dot)com>
To: JavaNoobie <vivek(dot)mv(at)enzentech(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: how to Escape single quotes with PreparedStatment
Date: 2011-08-22 10:04:56
Message-ID: 4E5229C8.1000300@londonandpartners.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message JavaNoobie 2011-08-22 10:26:06 Re: how to Escape single quotes with PreparedStatment
Previous Message Chris Wareham 2011-08-22 10:04:43 Re: how to Escape single quotes with PreparedStatment