Обсуждение: how to Escape single quotes with PreparedStatment
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.
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.
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
On 20/08/2011 16:10, dmp wrote:
> 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.
>
> 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
JavaNoobie <vivek.mv@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
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.
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 > - jdbc mailing list archive at Nabble.com.
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
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
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 On 22/08/11 09:14, JavaNoobie wrote: > 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 > Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com. >
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
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
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();
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();
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
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
On 22/08/11 11:04, Chris Wareham wrote: > > stmt = con.prepareStatement(query); > Should of course be: stmt = con.prepareStatement(query.toString()); Chris
On 22/08/11 11:26, JavaNoobie wrote:
> 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?
>
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();
On 22 August 2011 22:26, JavaNoobie <vivek.mv@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
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
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.