Re: how to Escape single quotes with PreparedStatment

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