RE: BUG #15632: Correctly escaped strings are mishandled in function

Hello David,

Here is the actual function. And I called this using select * FROM public.ModifyTopicValue('DEPT', 'SOLD', 'SOLD''')

CREATE OR REPLACE FUNCTION ModifyTopicValue(
rtn_rc OUT INTEGER,
rtn_row_count OUT INTEGER,
p_topic_id IN VARCHAR,
p_old_value IN VARCHAR,
p_new_value IN VARCHAR)
RETURNS RECORD AS
$func$
DECLARE
v_tpc_data_type INTEGER;
v_tpc_db_table VARCHAR;
v_replace_str VARCHAR;

v_sql VARCHAR;
v_sqlstate VARCHAR;
v_message VARCHAR;
v_sql_state_rec RECORD;
BEGIN
rtn_row_count := 0;
v_replace_str := p_new_value;
v_tpc_db_table = 'topic$_dept';

v_sql := ' WITH upd AS ( ';
v_sql := v_sql || ' UPDATE ' || v_tpc_db_table || ' ';
v_sql := v_sql || ' SET topic_value = ' || v_replace_str || ' ';
v_sql := v_sql || ' WHERE topic_value = ''' || p_old_value || ''' ';
v_sql := v_sql || ' RETURNING 1 ) ';
v_sql := v_sql || ' SELECT COUNT(*) FROM upd ';

--PERFORM VDRDebug.DisplayMessage('v_sql: ' || v_sql);

BEGIN
EXECUTE v_sql INTO rtn_row_count;
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE, v_message = MESSAGE_TEXT;
SELECT * INTO v_sql_state_rec FROM MapSQLStateToErrorCode(v_sqlstate);
rtn_rc := -1 * v_sql_state_rec.rtn_rc;
PERFORM VDRDebug.DisplayMessage('Unable to update ' || p_topic_id
|| ' old value ' || p_old_value
|| ' new value ' || p_new_value
|| ' error: '
|| to_char(rtn_rc,'FM9999999999') || ' ' || v_sqlstate || ' ' || v_message);
RETURN;
END;

rtn_rc := 0;
RETURN;
END
$func$ LANGUAGE plpgsql VOLATILE;

-----Original Message-----
From: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Sent: Monday, February 11, 2019 1:19 PM
To: Kaleb Akalework <kaleb(dot)akalework(at)asg(dot)com>; pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15632: Correctly escaped strings are mishandled in function

*** External email: Verify sender before opening attachments or links ***

On Mon, Feb 11, 2019 at 11:04 AM PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
> I'm working on a Java app, and I used prepared statements to create a
> function call to postgressql.

PostgreSQL (one s)...

> 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

I doubt this is a bug but rather likely you are not doing something correctly here. But since you don't show the function its impossible to say what.

> This will throw an error

How hard is it to include the actual error?

David J.