BUG #15794: Defects regarding stored procedure parameters

Started by PG Bug reporting formalmost 7 years ago2 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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.

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: PG Bug reporting form (#1)
Re: BUG #15794: Defects regarding stored procedure parameters

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."

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.