BUG #15632: Correctly escaped strings are mishandled in function

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15632: Correctly escaped strings are mishandled in function

apt.postgresql.org Repository Update
The following bug has been logged on the website:

Bug reference:      15632
Logged by:          Kaleb Akalework
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Mon, Feb 11, 2019 at 11:04 AM PG Bug reporting form
<[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

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

Kaleb Akalework
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 <[hidden email]>
Sent: Monday, February 11, 2019 1:19 PM
To: Kaleb Akalework <[hidden email]>; [hidden email]
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 <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Mon, Feb 11, 2019 at 11:22 AM Kaleb Akalework
<[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

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

Kaleb Akalework
In reply to this post by David G Johnston
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 <[hidden email]>
Sent: Monday, February 11, 2019 1:19 PM
To: Kaleb Akalework <[hidden email]>; [hidden email]
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 <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

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

Kaleb Akalework
In reply to this post by David G Johnston
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 is still broken because of the single quote. Using did not work for me as well. Please if you can reformulate this and show me, I would be happy to use it. But I was successful in doing so. The problem is an escaped string is transformed before being used. Can you please try it on your end and see if it works for you?

-----Original Message-----
From: David G. Johnston <[hidden email]>
Sent: Monday, February 11, 2019 1:28 PM
To: Kaleb Akalework <[hidden email]>
Cc: [hidden email]
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 <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

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

Kaleb Akalework
In reply to this post by David G Johnston
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 string as 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 <[hidden email]>
Sent: Monday, February 11, 2019 1:28 PM
To: Kaleb Akalework <[hidden email]>
Cc: [hidden email]
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 <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Mon, Feb 11, 2019 at 11:47 AM Kaleb Akalework
<[hidden email]> 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 final string 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.