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

2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 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

Hmm this needs be better documented. This is DDL case, so you cannot
use USING ever.

> EXECUTE USING is thus dramatically reduced.

it's depend - sure, you cannot use USING for DDL statements, but DDL
statements are usually small and not complicated. USING is for large
DML statements, there is primary goal. Simply, this is for statements
with executing plan - ALTER hasn't plan, so you cannot use USING.

> 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.

you can use quote_literal function for protection against SQL injection.

>
> (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
>