Обсуждение: BUG #15794: Defects regarding stored procedure parameters
The following bug has been logged on the website: Bug reference: 15794 Logged by: Dada Zhang Email address: 740084020@qq.com PostgreSQL version: 10.7 Operating system: windows 10 Home Description: ( I am not good at English, English is not my native language, please excuse typing errors. ) 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." Such as: There have a table and some record: ``` CREATE TABLE "public"."student" ( "id" int4 NOT NULL, "name" varchar(255), PRIMARY KEY ("id") ); insert into "public"."student"(id,name) VALUES (1,'Zhang San'); insert into "public"."student"(id,name) VALUES (2,'Li Si'); ``` There have a function (it will caused: Field association \"id\" is ambiguous) ``` CREATE OR REPLACE FUNCTION public.update_student( id integer, name character varying) RETURNS "pg_catalog"."void" LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN UPDATE public.student SET name = update_student.name WHERE id = update_student.id; END; $BODY$; ``` But, when we change the function: (it will be ok, the sql execute successed) ``` CREATE OR REPLACE FUNCTION public.update_student( _id integer, name character varying) RETURNS "pg_catalog"."void" LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN UPDATE public.student SET name = update_student.name WHERE id = _id; END; $BODY$; ``` or (this function also have a problem, PostgreSQL after 9, SQL string escape is cumbersome) ``` CREATE OR REPLACE FUNCTION public.update_student( _id integer, name character varying) RETURNS "pg_catalog"."void" LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE v_sql varchar; BEGIN v_sql := 'UPDATE public.student SET name = ' || E'\'' || update_student.name || E'\'' || ' WHERE id = ' || E'\'' || id || E'\'' || ';'; EXECUTE v_sql; END; $BODY$; ``` The above. thank you.
On Tue, May 7, 2019 at 1:47 AM PG Bug reporting form <noreply@postgresql.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."
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.
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.