The following bug has been logged on the website:
Bug reference: 15632
Logged by: Kaleb Akalework
Email address: kaleb.akalework@asg.com
PostgreSQL version: 11.1
Operating system: Windows/Linux
Description:
I'm working on a Java app, and I used prepared statements to create a
function call to postgressql. The java database preparedstament method
correctly adds an additional quote to the existing single quote to escape.
And then I make the postgressql function call, and the function does not
execute because it removes the 2 single quotes and replaces it with one.
Here is the steps to reproduce it.
1. preparestament stmt = .....
2. stmt.setString (3, " SOLD' ")
3. stmt.execute(); (In the java code I see select * from
public.ModifyTopicValue('DEPT', 'SOLD', 'SOLD''') ) Notice how the last
parameter had the single quote escape by another single quote.
Inside my function I take these parameters and build a query like this
WITH upd AS ( UPDATE topic$_dept SET topic_value = 'SOLD'' WHERE
topic_value = 'SOLD' RETURNING 1 ) SELECT COUNT(*) FROM upd
This will throw an error because inside the function SOLD'' is converted to
SOLD' (SET topic_value = 'SOLD'' ). Why is postgressql removing the
additional escape character that was passed in. I would have expected it be
used like this SET topic_value = 'SOLD''' as it was passed in.