Hi all.
There's currently a limitation in the v8.4.2 implementation of the
EXECUTE...USING predicate in PL/PgSQL which prevents you from
exploiting the USING-supplied value list with DDL commands.
For example:

CREATE TABLE test ( i int );
...
EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING 42;

complains 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.
In both cases the $1 variable/placeholder refers to a constant value.
And actually, even if the "thing" defined after the USING lexeme was a
variable, that should be evaluated and substituted *before* executing
the command.

The current documentation
(http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN)
doesn't say so and clearly describes how this feature is meant to
work.
Quoting:
----
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. An example is:
----
(38.5.4. Executing Dynamic Commands)

It talks about "values", that is typed constants.
Please, refer also to the following discussion on pgsql-general mailing list:
http://archives.postgresql.org/pgsql-general/2010-01/msg00522.php

My proposal is to relax that (clearly running but undocumented)
constraint and allow any SQL command in the EXECUTE...USING predicate.
I would leave the responsibility to the programmer to ensure whether
the dynamic command makes any syntactic and semantic sense.

--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

Search Discussions

  • Pavel Stehule at Jan 14, 2010 at 2:53 pm
    Hello

    I thing, so this is bad idea.

    a) this behave depends on DDL implementation, not plpgsql implementation

    b) proposed implementation needs some escape magic. This was first
    implementation of USING clause and it was rejected. Some composite and
    nested values are significant break.

    see in archive http://archives.postgresql.org/pgsql-patches/2006-08/msg00267.php

    Regards
    Pavel Stehule


    2010/1/14 Vincenzo Romano <vincenzo.romano@notorand.it>:
    Hi all.
    There's currently a limitation in the v8.4.2 implementation of the
    EXECUTE...USING predicate in PL/PgSQL which prevents you from
    exploiting the USING-supplied value list with DDL commands.
    For example:

    CREATE TABLE test ( i int );
    ...
    EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING 42;

    complains 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.
    In both cases the $1 variable/placeholder refers to a constant value.
    And actually, even if the "thing" defined after the USING lexeme was a
    variable, that should be evaluated and substituted *before* executing
    the command.

    The current documentation
    (http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN)
    doesn't say so and clearly describes how this feature is meant to
    work.
    Quoting:
    ----
    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. An example is:
    ----
    (38.5.4. Executing Dynamic Commands)

    It talks about "values", that is typed constants.
    Please, refer also to the following discussion on pgsql-general mailing list:
    http://archives.postgresql.org/pgsql-general/2010-01/msg00522.php

    My proposal is to relax that (clearly running but undocumented)
    constraint and allow any SQL command in the EXECUTE...USING predicate.
    I would leave the responsibility to the programmer to ensure whether
    the dynamic command makes any syntactic and semantic sense.

    --
    Vincenzo Romano
    NotOrAnd Information Technologies
    NON QVIETIS MARIBVS NAVTA PERITVS

    --
    Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
  • Vincenzo Romano at Jan 15, 2010 at 8:14 am

    2010/1/14 Pavel Stehule <pavel.stehule@gmail.com>:
    Hello

    I thing, so this is bad idea.

    a) this behave depends on DDL implementation, not plpgsql implementation

    b) proposed implementation needs some escape magic. This was first
    implementation of USING clause and it was rejected. Some composite and
    nested values are significant break.

    see in archive http://archives.postgresql.org/pgsql-patches/2006-08/msg00267.php

    Regards
    Pavel Stehule


    2010/1/14 Vincenzo Romano <vincenzo.romano@notorand.it>:
    Hi all.
    There's currently a limitation in the v8.4.2 implementation of the
    EXECUTE...USING predicate in PL/PgSQL which prevents you from
    exploiting the USING-supplied value list with DDL commands.
    For example:

    CREATE TABLE test ( i int );
    ...
    EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING 42;

    complains 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.
    In both cases the $1 variable/placeholder refers to a constant value.
    And actually, even if the "thing" defined after the USING lexeme was a
    variable, that should be evaluated and substituted *before* executing
    the command.

    The current documentation
    (http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN)
    doesn't say so and clearly describes how this feature is meant to
    work.
    Quoting:
    ----
    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. An example is:
    ----
    (38.5.4. Executing Dynamic Commands)

    It talks about "values", that is typed constants.
    Please, refer also to the following discussion on pgsql-general mailing list:
    http://archives.postgresql.org/pgsql-general/2010-01/msg00522.php

    My proposal is to relax that (clearly running but undocumented)
    constraint and allow any SQL command in the EXECUTE...USING predicate.
    I would leave the responsibility to the programmer to ensure whether
    the dynamic command makes any syntactic and semantic sense.

    --
    Vincenzo Romano
    NotOrAnd Information Technologies
    NON QVIETIS MARIBVS NAVTA PERITVS
    Well, the very basic proposal could then be changed into:

    Allow some kind of syntactic replacement of the placeholders found
    into the command string with values taken from the USING clause
    evaluated straight into the PLPGSQL function body environment. The
    model could be the C language sprintf().

    Maybe you can think about using different placeholders for "static"
    (or "local") evaluation, like #1, #2 ... #n.

    For example, you could do something like this:

    EXECUTE 'ALTER TABLE '||tablename||' ADD CHECK col1=#1 and col2>=#2
    and col2<#3" USING currval1, currval2, currval3.

    The execution, within the PLPGSQL interpreter, would proceed like this:

    0. Concatenate the sub-strings to just 1.
    1. Evaluate the variable list after the USING clause (currval1,
    currval2, currval3) to their current values.
    2. Replace the placeholders with the natural ordered references within
    the command string
    3. Send the final string to the execution.

    This makes a lot of sense (in my opinion) for higher level functions
    (functions which create functions which execute dynamic commands).
    It's more like a string substitution but with knowledge of the syntac
    of the expressions following the USING clause.

    --
    Vincenzo Romano
    NotOrAnd Information Technologies
    NON QVIETIS MARIBVS NAVTA PERITVS
  • Pavel Stehule at Jan 15, 2010 at 8:52 am

    2010/1/15 Vincenzo Romano <vincenzo.romano@notorand.it>:
    2010/1/14 Pavel Stehule <pavel.stehule@gmail.com>:
    Hello

    I thing, so this is bad idea.

    a) this behave depends on DDL implementation, not plpgsql implementation

    b) proposed implementation needs some escape magic. This was first
    implementation of USING clause and it was rejected. Some composite and
    nested values are significant break.

    see in archive http://archives.postgresql.org/pgsql-patches/2006-08/msg00267.php

    Regards
    Pavel Stehule


    2010/1/14 Vincenzo Romano <vincenzo.romano@notorand.it>:
    Hi all.
    There's currently a limitation in the v8.4.2 implementation of the
    EXECUTE...USING predicate in PL/PgSQL which prevents you from
    exploiting the USING-supplied value list with DDL commands.
    For example:

    CREATE TABLE test ( i int );
    ...
    EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING 42;

    complains 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.
    In both cases the $1 variable/placeholder refers to a constant value.
    And actually, even if the "thing" defined after the USING lexeme was a
    variable, that should be evaluated and substituted *before* executing
    the command.

    The current documentation
    (http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN)
    doesn't say so and clearly describes how this feature is meant to
    work.
    Quoting:
    ----
    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. An example is:
    ----
    (38.5.4. Executing Dynamic Commands)

    It talks about "values", that is typed constants.
    Please, refer also to the following discussion on pgsql-general mailing list:
    http://archives.postgresql.org/pgsql-general/2010-01/msg00522.php

    My proposal is to relax that (clearly running but undocumented)
    constraint and allow any SQL command in the EXECUTE...USING predicate.
    I would leave the responsibility to the programmer to ensure whether
    the dynamic command makes any syntactic and semantic sense.

    --
    Vincenzo Romano
    NotOrAnd Information Technologies
    NON QVIETIS MARIBVS NAVTA PERITVS
    Well, the very basic proposal could then be changed into:

    Allow some kind of syntactic replacement of the placeholders found
    into the command string with values taken from the USING clause
    evaluated straight into the PLPGSQL function body environment. The
    model could be the C language sprintf().

    Maybe you can think about using different placeholders for "static"
    (or "local") evaluation, like #1, #2 ... #n.
    I disagree with this functionality for USING clause. Main parser
    doesn't support some enhanced syntax. But we can discus about some
    function 'printf' or 'format' that can help with similar task.

    some like

    EXECUTE format('ALTER TABLE % ADD CHECK (col1=% AND col2=%)',
    quote_ident(tablename), cval1, cval2, cval3);


    there was two proposals:
    a) based on syntax our RAISE statements
    b) based on printf syntax

    Personally prefer a) - it is simpler and enough

    Pavel

    http://wiki.postgresql.org/wiki/Sprintf
    http://archives.postgresql.org/pgsql-hackers/2009-09/msg00482.php
    http://archives.postgresql.org/pgsql-hackers/2009-09/msg00563.php





    For example, you could do something like this:

    EXECUTE 'ALTER TABLE '||tablename||' ADD CHECK col1=#1 and col2>=#2
    and col2<#3" USING currval1, currval2, currval3.

    The execution, within the PLPGSQL interpreter, would proceed like this:

    0. Concatenate the sub-strings to just 1.
    1. Evaluate the variable list after the USING clause (currval1,
    currval2, currval3) to their current values.
    2. Replace the placeholders with the natural ordered references within
    the command string
    3. Send the final string to the execution.

    This makes a lot of sense (in my opinion) for higher level functions
    (functions which create functions which execute dynamic commands).
    It's more like a string substitution but with knowledge of the syntac
    of the expressions following the USING clause.

    --
    Vincenzo Romano
    NotOrAnd Information Technologies
    NON QVIETIS MARIBVS NAVTA PERITVS
  • Vincenzo Romano at Jan 15, 2010 at 8:57 am

    2010/1/15 Pavel Stehule <pavel.stehule@gmail.com>:
    I disagree with this functionality for USING clause. Main parser
    doesn't support some enhanced syntax. But we can discus about some
    function 'printf' or 'format' that can help with similar task.

    some like

    EXECUTE format('ALTER TABLE % ADD CHECK (col1=% AND col2=%)',
    quote_ident(tablename), cval1, cval2, cval3);


    there was two proposals:
    a) based on syntax our RAISE statements
    b) based on printf syntax

    Personally prefer a) - it is simpler and enough

    Pavel


    I do like the printf-like approach more than my proposal!
    Do you think about a built-in implementation rather than the on in PLGSQL?

    --
    Vincenzo Romano
    NotOrAnd Information Technologies
    NON QVIETIS MARIBVS NAVTA PERITVS
  • Pavel Stehule at Jan 15, 2010 at 9:01 am

    2010/1/15 Vincenzo Romano <vincenzo.romano@notorand.it>:
    2010/1/15 Pavel Stehule <pavel.stehule@gmail.com>:
    I disagree with this functionality for USING clause. Main parser
    doesn't support some enhanced syntax. But we can discus about some
    function 'printf' or 'format' that can help with similar task.

    some like

    EXECUTE format('ALTER TABLE % ADD CHECK (col1=% AND col2=%)',
    quote_ident(tablename), cval1, cval2, cval3);


    there was two proposals:
    a) based on syntax our RAISE statements
    b) based on printf syntax

    Personally prefer a) - it is simpler and enough

    Pavel


    I do like the printf-like approach more than my proposal!
    Do you think about a built-in implementation rather than the on in PLGSQL?
    sure.

    the plpgsql isn't problem in 8.5, but integrated version can be little
    bit smarter.

    Pavel

    --
    Vincenzo Romano
    NotOrAnd Information Technologies
    NON QVIETIS MARIBVS NAVTA PERITVS

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJan 14, '10 at 2:44p
activeJan 15, '10 at 9:01a
posts6
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Pavel Stehule: 3 posts Vincenzo Romano: 3 posts

People

Translate

site design / logo © 2022 Grokbase