Re: BUG #15794: Defects regarding stored procedure parameters

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: 740084020(at)qq(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15794: Defects regarding stored procedure parameters
Date: 2019-05-07 19:14:59
Message-ID: CAKFQuwbzOdxFDN80aXRaw26jgyb8W0MY6i_pwz1SuiPQFq-T=Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Tue, May 7, 2019 at 1:47 AM PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15794
>

Not a bug.

> When the parameter name of the stored procedure is the same as the table
> field name of the update statement, a problem is caused: "field association
> is ambiguous."
>

https://www.postgresql.org/docs/11/plpgsql-structure.html

See in particular the note. But usually parameters names are constructed
uniquely in order to simply avoid this issue.

> (this function also have a problem, PostgreSQL after 9, SQL string escape
> is
> cumbersome)
>

Then don't use string escaping to build dynamic SQL, use the recommended
format() function.

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

v_sql := 'UPDATE public.student SET name = ' || E'\'' ||
> update_student.name || E'\'' ||
> ' WHERE id = ' || E'\'' || id || E'\'' || ';';
> EXECUTE v_sql;

I don't even want to try and understand or explain how broken the above
might be...but I doubt it does what you think plus its vulnerable to SQL
injection.

David J.

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Thomas Munro 2019-05-08 00:17:42 Re: BUG #15793: Required Community Version Installs not the customized EnterpriseDB one.
Previous Message James Tomson 2019-05-07 16:22:39 Re: invalid memory alloc request size from pg_stat_activity?