Обсуждение: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion
The following bug has been logged online: Bug reference: 5274 Logged by: Vincenzo Romano Email address: vincenzo.romano@notorand.it PostgreSQL version: 8.4.2 Operating system: Linux Description: [PL/PgSQL] EXECUTE ... USING variable expansion Details: My system says: ~ lsb_release -a LSB Version: :core-3.1-amd64:core-3.1-noarch:core-3.2-amd64:core-3.2-noarch:desktop-3.1-a md64:desktop-3.1-noarch:desktop-3.2-amd64:desktop-3.2-noarch Distributor ID: Fedora Description: Fedora release 12 (Constantine) Release: 12 Codename: Constantine If you try the following: CREATE TABLE test ( i INT ); CREATE OR REPLACE FUNCTION func() RETURNS void LANGUAGE plpgsql AS $function$ DECLARE e TEXT; t TEXT; i INT; BEGIN i := 42; t := 'answer'; EXECUTE 'SELECT $1' INTO e USING t; RAISE INFO '%',e; EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING i; END; $function$; SELECT func(); The first EXECUTE...USING replaces the variable $1 with the value of the variable "t". The first output line reads: INFO: answer The second EXECUTE...USING doesn't do the replacement and triggers an error: ERROR: there is no parameter $1 CONTEXT: SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT $1" PL/pgSQL function "func" line 10 at EXECUTE statement
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 =3D $1' USING var1 but you cannot do: EXECUTE 'SELECT * FROM $1 WHERE col =3D 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=3D10' -- var1::regclass is minimum protection against SQL injection EXECUTE 'CREATE TABLE '|| quote_ident(var1) || '(.... Regards Pavel Stehule 2010/1/13 Vincenzo Romano <vincenzo.romano@notorand.it>: > > The following bug has been logged online: > > Bug reference: =C2=A0 =C2=A0 =C2=A05274 > Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Vincenzo Romano > Email address: =C2=A0 =C2=A0 =C2=A0vincenzo.romano@notorand.it > PostgreSQL version: 8.4.2 > Operating system: =C2=A0 Linux > Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0[PL/PgSQL] EXECUTE ... USING vari= able expansion > Details: > > My system says: > ~ lsb_release -a > LSB Version: > :core-3.1-amd64:core-3.1-noarch:core-3.2-amd64:core-3.2-noarch:desktop-3.= 1-a > md64:desktop-3.1-noarch:desktop-3.2-amd64:desktop-3.2-noarch > Distributor ID: Fedora > Description: =C2=A0 =C2=A0Fedora release 12 (Constantine) > Release: =C2=A0 =C2=A0 =C2=A0 =C2=A012 > Codename: =C2=A0 =C2=A0 =C2=A0 Constantine > > If you try the following: > > CREATE TABLE test ( i INT ); > > CREATE OR REPLACE FUNCTION func() > =C2=A0RETURNS void > =C2=A0LANGUAGE plpgsql > AS $function$ > DECLARE > =C2=A0e TEXT; > =C2=A0t TEXT; > =C2=A0i INT; > BEGIN > =C2=A0i :=3D 42; > =C2=A0t :=3D 'answer'; > =C2=A0EXECUTE 'SELECT $1' INTO e USING t; > =C2=A0RAISE INFO =C2=A0'%',e; > =C2=A0EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING i; > END; > $function$; > > SELECT func(); > > The first EXECUTE...USING replaces the variable $1 with the value of the > variable "t". The first output line reads: > > INFO: =C2=A0answer > > The second EXECUTE...USING doesn't do the replacement and triggers an > error: > > ERROR: =C2=A0there is no parameter $1 > CONTEXT: =C2=A0SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT= $1" > PL/pgSQL function "func" line 10 at EXECUTE statement > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
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
2010/1/14 Vincenzo Romano <vincenzo.romano@notorand.it>: > 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 =3D $1' USING var1 >> >> but you cannot do: >> >> EXECUTE 'SELECT * FROM $1 WHERE col =3D 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=3D10' -- >> 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: =C2=A0there is no parameter $1 > CONTEXT: =C2=A0SQL 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-A= 4.pdf > page 800 > or http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPG= SQL-STATEMENTS-EXECUTING-DYN > "38.5.4. Executing Dynamic Commands") > > -- > Vincenzo Romano > NotOrAnd Information Technologies > NON QVIETIS MARIBVS NAVTA PERITVS >
> Pavel, > if it was a simple plain dynami statement, I could create it by string > concatenation, literal_ and ident_ quoting and so on. the situation isn't too simply like you see it. PostgreSQL supports composite and nested types. For this types you cannot use quote* functions. Next problem is NULL values. I did this work four years ago and http://archives.postgresql.org/pgsql-patches/2006-08/msg00271.php it was rejected - from good reasons. You can see more messages from this time in archive. In plpgsql we don't would to supply SQL parser - so when you would to identify pleaseholders, then your implementation will be slow or buggy. Regards Pavel Stehule > But this is not my case. > I'm exploring an alternative approach to the table partitioning > (http://notsononsense.blogspot.com/2009/12/postgresql-different-approach-= to-table.html). > And being able to handle dynamic DML and DDL is a key point, also for > the traditional one. > In practice, the whole EXECUTE...USING command is generated > dynamically, so not even the names or the values passed after the > USING lexem are known at that time. > > -- > Vincenzo Romano > NotOrAnd Information Technologies > cel. +39 339 8083886 =C2=A0| gtalk. vincenzo.romano@notorand.it > fix. +39 0823 454163 =C2=A0| skype. notorand.it > fax. +39 02 700506964 | msn. =C2=A0 notorand.it > NON QVIETIS MARIBVS NAVTA PERITVS >