Re: how to Escape single quotes with PreparedStatment

On Mon, 22 Aug 2011 11:04:56 +0100, Chris Wareham wrote:
> 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();
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