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

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Kaleb Akalework <kaleb(dot)akalework(at)asg(dot)com>
Cc: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #15632: Correctly escaped strings are mishandled in function
Date: 2019-02-11 18:27:46
Message-ID: CAKFQuwZW884phB7OmNUCciFen2-jm0RgpnhecG0nZ=UuWQhxBw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

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

David J.

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kaleb Akalework 2019-02-11 18:28:00 RE: BUG #15632: Correctly escaped strings are mishandled in function
Previous Message Kaleb Akalework 2019-02-11 18:22:20 RE: BUG #15632: Correctly escaped strings are mishandled in function