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

2010/1/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 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

Documentation (v8.*) clearly states that you cannot use the
placeholders for table and column names.
What I'm reporting here is that even:

EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING 42;

is complaining with:

ERROR: there is no parameter $1
CONTEXT: SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT $1"

while:

EXECUTE 'SELECT $1' USING 42;

works.
There's no variable, just a constant value. The usefulness of the
EXECUTE USING is thus dramatically reduced.
Also because the documentation reports something really meaningful and
reasonable:

The command string can use parameter values, which are referenced in
the command as $1, $2,
etc. These symbols refer to values supplied in the USING clause. This
method is often preferable to
inserting data values into the command string as text: it avoids
run-time overhead of converting the
values to text and back, and it is much less prone to SQL-injection
attacks since there is no need for
quoting or escaping.

(http://www.postgresql.org/files/documentation/pdf/8.4/postgresql-8.4.2-A4.pdf
page 800
or http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
"38.5.4. Executing Dynamic Commands")

--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS