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.g.johnston@gmail.com> 
Sent: Monday, February 11, 2019 1:19 PM
To: Kaleb Akalework <kaleb.akalework@asg.com>; pgsql-bugs@lists.postgresql.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@postgresql.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
functionits impossible to say what.
 

> This will throw an error

How hard is it to include the actual error?

David J.