BUG #15632: Correctly escaped strings are mishandled in function

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: kaleb(dot)akalework(at)asg(dot)com
Subject: BUG #15632: Correctly escaped strings are mishandled in function
Date: 2019-02-11 17:55:07
Message-ID: 15632-d3748195ebf6f4cd@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15632
Logged by: Kaleb Akalework
Email address: kaleb(dot)akalework(at)asg(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Abhilash Mannathanil (amannath) 2019-02-11 18:04:02 Re: Error "new timeline 2 forked off current database system timeline" in cascaded replication, when master changes
Previous Message PG Bug reporting form 2019-02-11 17:43:13 BUG #15631: Generated as identity field in a temporary table with on commit drop corrupts system catalogs