2010/1/14 Pavel Stehule <pavel.steh...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
