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

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.