Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion

Hello

it is not bug.

DDL statements like CREATE TABLE, ALTER TABLE are doesn't support
parametrisation - you cannot use a placeholder for parameter
everywhere. So you cannot to write PQexecParams(..."ALTER TABLE test
ALTER COLUMN $1 ...", ...), so it cannot be supported by EXECUTE
USING. Parameters are available only for DML statements - for
statements with execution plan. You can store a plan and you can call
stored plan with different parameters - it means - parameter cannot be
a SQL identifier - like column or table name, because this changes a
plan.

so

you can do

EXECUTE 'SELECT * FROM tab WHERE col =3D $1' USING var1

but you cannot do:

EXECUTE 'SELECT * FROM $1 WHERE col =3D 10' USING var1, because SELECT
FROM tab1 or SELECT FROM tab2 needs different execution plans. You
cannot do too:

EXECUTE 'CREATE TABLE $1' USING var1, bacause CREATE TABLE is DDL
statement without plan, and without possibility to use a parameters.

You have to do:

EXECUTE 'SELECT * FROM ' || var1::regclass || ' WHERE col=3D10' --
var1::regclass is minimum protection against SQL injection
EXECUTE 'CREATE TABLE '|| quote_ident(var1) || '(....

Regards
Pavel Stehule



2010/1/13 Vincenzo Romano <vincenzo.romano@notorand.it>:
>
> The following bug has been logged online:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A05274
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Vincenzo Romano
> Email address: =C2=A0 =C2=A0 =C2=A0vincenzo.romano@notorand.it
> PostgreSQL version: 8.4.2
> Operating system: =C2=A0 Linux
> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0[PL/PgSQL] EXECUTE ... USING vari=
able expansion
> Details:
>
> My system says:
> ~ lsb_release -a
> LSB Version:
> :core-3.1-amd64:core-3.1-noarch:core-3.2-amd64:core-3.2-noarch:desktop-3.=
1-a
> md64:desktop-3.1-noarch:desktop-3.2-amd64:desktop-3.2-noarch
> Distributor ID: Fedora
> Description: =C2=A0 =C2=A0Fedora release 12 (Constantine)
> Release: =C2=A0 =C2=A0 =C2=A0 =C2=A012
> Codename: =C2=A0 =C2=A0 =C2=A0 Constantine
>
> If you try the following:
>
> CREATE TABLE test ( i INT );
>
> CREATE OR REPLACE FUNCTION func()
> =C2=A0RETURNS void
> =C2=A0LANGUAGE plpgsql
> AS $function$
> DECLARE
> =C2=A0e TEXT;
> =C2=A0t TEXT;
> =C2=A0i INT;
> BEGIN
> =C2=A0i :=3D 42;
> =C2=A0t :=3D 'answer';
> =C2=A0EXECUTE 'SELECT $1' INTO e USING t;
> =C2=A0RAISE INFO =C2=A0'%',e;
> =C2=A0EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING i;
> END;
> $function$;
>
> SELECT func();
>
> The first EXECUTE...USING replaces the variable $1 with the value of the
> variable "t". The first output line reads:
>
> INFO: =C2=A0answer
>
> The second EXECUTE...USING doesn't do the replacement and triggers an
> error:
>
> ERROR: =C2=A0there is no parameter $1
> CONTEXT: =C2=A0SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT=
 $1"
> PL/pgSQL function "func" line 10 at EXECUTE statement
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>