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

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 <david(dot)g(dot)johnston(at)gmail(dot)com>
Sent: Monday, February 11, 2019 1:28 PM
To: Kaleb Akalework <kaleb(dot)akalework(at)asg(dot)com>
Cc: 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:22 AM Kaleb Akalework <kaleb(dot)akalework(at)asg(dot)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.