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

BUG #15632: Correctly escaped strings are mishandled in function

From
PG Bug reporting form
Date:
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.


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

From
"David G. Johnston"
Date:
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 function its impossible
to say what.

> This will throw an error

How hard is it to include the actual error?

David J.


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

From
Kaleb Akalework
Date:
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.

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

From
"David G. Johnston"
Date:
On Mon, Feb 11, 2019 at 11:22 AM Kaleb Akalework
<kaleb.akalework@asg.com> wrote:

>     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 ';

PostgreSQL provides two features to avoid writing SQL-injection prone
code like this.  Use one of them.

quote_literal() and related functions

The format() function and its %L and related specifiers.

You can also place "$n" placeholders into the dynamic command as pass
literals in via EXECUTE USING.

Or some combination of the above.

This is all nicely covered in the documentation for pl/pgsql regarding
executing dynamic commands.

https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

David J.


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

From
Kaleb Akalework
Date:
The actual error I get is error: -42 42601 syntax error at or near "SOLD". Because it removed the double single quote.

-----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.

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

From
Kaleb Akalework
Date:
Yes I so that and I tried all of that and It did not work. Quote_lietral just double quotes it like this "SOLD'", which
isstill broken because of the single quote. Using did not work for me as well. Please if you can reformulate this and
showme, I would be happy to use it. But I was successful in doing so. The problem is an escaped string is transformed
beforebeing used. Can you please try it on your end and see if it works for you?
 

-----Original Message-----
From: David G. Johnston <david.g.johnston@gmail.com> 
Sent: Monday, February 11, 2019 1:28 PM
To: Kaleb Akalework <kaleb.akalework@asg.com>
Cc: 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:22 AM Kaleb Akalework <kaleb.akalework@asg.com> wrote:

>     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 ';

PostgreSQL provides two features to avoid writing SQL-injection prone code like this.  Use one of them.

quote_literal() and related functions

The format() function and its %L and related specifiers.

You can also place "$n" placeholders into the dynamic command as pass literals in via EXECUTE USING.

Or some combination of the above.

This is all nicely covered in the documentation for pl/pgsql regarding executing dynamic commands.


http://TOC.ASG.COM:8080/?dmVyPTEuMDAxJiZiYzk3NTU2ZjZlODIyMzQ3Yz01QzYxQkVCMV8zNzIwNV8xNTk1Nl8xJiY1OTUzNGVmMGRhMWNmMzI9MTIzMiYmdXJsPWh0dHBzJTNBJTJGJTJGd3d3JTJFcG9zdGdyZXNxbCUyRW9yZyUyRmRvY3MlMkYxMSUyRnBscGdzcWwtc3RhdGVtZW50cyUyRWh0bWwlMjNQTFBHU1FMLVNUQVRFTUVOVFMtRVhFQ1VUSU5HLURZTg==

David J.

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

From
Kaleb Akalework
Date:
To be clear what I want is to send in 'SOLD''' in the function so in the query it can be used as is to store the final
stringas SOLD'. IF I use the functions you mention it will just "" or add more quotes. Which is not what I want.
 



-----Original Message-----
From: David G. Johnston <david.g.johnston@gmail.com> 
Sent: Monday, February 11, 2019 1:28 PM
To: Kaleb Akalework <kaleb.akalework@asg.com>
Cc: 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:22 AM Kaleb Akalework <kaleb.akalework@asg.com> wrote:

>     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 ';

PostgreSQL provides two features to avoid writing SQL-injection prone code like this.  Use one of them.

quote_literal() and related functions

The format() function and its %L and related specifiers.

You can also place "$n" placeholders into the dynamic command as pass literals in via EXECUTE USING.

Or some combination of the above.

This is all nicely covered in the documentation for pl/pgsql regarding executing dynamic commands.


http://TOC.ASG.COM:8080/?dmVyPTEuMDAxJiZiYzk3NTU2ZjZlODIyMzQ3Yz01QzYxQkVCMV8zNzIwNV8xNTk1Nl8xJiY1OTUzNGVmMGRhMWNmMzI9MTIzMiYmdXJsPWh0dHBzJTNBJTJGJTJGd3d3JTJFcG9zdGdyZXNxbCUyRW9yZyUyRmRvY3MlMkYxMSUyRnBscGdzcWwtc3RhdGVtZW50cyUyRWh0bWwlMjNQTFBHU1FMLVNUQVRFTUVOVFMtRVhFQ1VUSU5HLURZTg==

David J.

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

From
"David G. Johnston"
Date:
On Mon, Feb 11, 2019 at 11:47 AM Kaleb Akalework
<kaleb.akalework@asg.com> wrote:
> To be clear what I want is to send in 'SOLD''' in the function so in the query it can be used as is to store the
finalstring as SOLD'. IF I use the functions you mention it will just "" or add more quotes.
 

Please don't top-post.

The methods mentioned work perfectly; any failure to do what you are
desiring is from improper usage.  Mistakes cannot be pointed out
unless you show them but here is one that does work.

create or replace function echo(in_str text)
returns text
language plpgsql
AS $func$
DECLARE qry text; res text;
BEGIN
--qry = 'SELECT ' || quote_literal(in_str) || ';';
--qry = format('SELECT %L', in_str);
--EXECUTE qry INTO res;
qry = 'SELECT $1';
EXECUTE qry INTO res USING  in_str;
RETURN res;
END;
$func$
;
SELECT echo($in$SOLD'$in$);
-> SOLD'

A combination of "format" (for identifiers) and "execute using" (for
literals) is arguably the best solution.

David J.