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 = $1' USING var1

but you cannot do:

EXECUTE 'SELECT * FROM $1 WHERE col = 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=10' --
var1::regclass is minimum protection against SQL injection
EXECUTE 'CREATE TABLE '|| quote_ident(var1) || '(....

Regards
Pavel Stehule

2010/1/13 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
>
> The following bug has been logged online:
>
> Bug reference:      5274
> Logged by:          Vincenzo Romano
> Email address:      vincenzo(dot)romano(at)notorand(dot)it
> PostgreSQL version: 8.4.2
> Operating system:   Linux
> Description:        [PL/PgSQL] EXECUTE ... USING variable 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:    Fedora release 12 (Constantine)
> Release:        12
> Codename:       Constantine
>
> If you try the following:
>
> CREATE TABLE test ( i INT );
>
> CREATE OR REPLACE FUNCTION func()
>  RETURNS void
>  LANGUAGE plpgsql
> AS $function$
> DECLARE
>  e TEXT;
>  t TEXT;
>  i INT;
> BEGIN
>  i := 42;
>  t := 'answer';
>  EXECUTE 'SELECT $1' INTO e USING t;
>  RAISE INFO  '%',e;
>  EXECUTE '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:  answer
>
> The second EXECUTE...USING doesn't do the replacement and triggers an
> error:
>
> ERROR:  there is no parameter $1
> CONTEXT:  SQL 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>