From: | Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion |
Date: | 2010-01-14 14:26:27 |
Message-ID: | 3eff28921001140626v2f5d5957l10853bd3fa37d4e9@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2010-01-14 14:36:17 | Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion |
Previous Message | louis | 2010-01-14 11:06:23 | BUG #5277: plperl can't get args properly |