MIME-Version: 1.0
In-Reply-To: <162867791001140054w2f383578v7b59b5545310f21f@mail.gmail.com>
References: <201001131910.o0DJArAo086208@wwwmaster.postgresql.org>
	<162867791001140054w2f383578v7b59b5545310f21f@mail.gmail.com>
Date: Thu, 14 Jan 2010 15:26:27 +0100
Message-ID: <3eff28921001140626v2f5d5957l10853bd3fa37d4e9@mail.gmail.com>
Subject: Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion
From: Vincenzo Romano <vincenzo.romano@notorand.it>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-bugs@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

2010/1/14 Pavel Stehule <pavel.stehule@gmail.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