Proposal: PL/pgSQL EXECUTE INTO USING (for 8.4)

Hello,

this proposal change older unaccepted proposal
http://archives.postgresql.org/pgsql-hackers/2006-03/msg01157.php .

Changes:
* based on prepared statements
* syntax and behave is near to Oracle
* usable as protection from SQL injection

New syntax:

a) EXECUTE stringexpr
[INTO [STRICT] varlist
[USING exprlist]

b) FOR varlist IN EXECUTE stringexpr USING exprlist LOOP ....

Reason:
* defence from SQL injection
* more readable, shorter, more comfortable

Sample (secure dynamic statement):
EXECUTE
'SELECT * FROM ' ||
CASE tblname
WHEN 'tab1' THEN 'tab1'
WHEN 'tab2' THEN 'tab2'
ELSE '"some is wrong"' END ||
' WHERE c1 = $1 AND c2 = $2'
USING unsecure_parameter1, unsecure_parameter2;

Difference between PL/SQL and proposal:
* allow only IN variables
* use PostgreSQL placeholders notation - "$"n instead ":"n

Compliance with PL/SQL
* You can use numeric, character, and string literals as bind arguments
* You cannot use bind arguments to pass the names of schema objects to
a dynamic SQL statement.

Best regards

Pavel Stehule

On 10/16/07, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> Hello,
>
> this proposal change older unaccepted proposal
> http://archives.postgresql.org/pgsql-hackers/2006-03/msg01157.php .
>

> Compliance with PL/SQL
> * You can use numeric, character, and string literals as bind arguments
> * You cannot use bind arguments to pass the names of schema objects to
> a dynamic SQL statement.

does this mean you can't dynamically sub in a variable for a table
name? if so, why keep that limitation? one of the main reasons to use
dynamic sql is for schema objects.

merlin

2007/10/16, Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On 10/16/07, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> > Hello,
> >
> > this proposal change older unaccepted proposal
> > http://archives.postgresql.org/pgsql-hackers/2006-03/msg01157.php .
> >
>
> > Compliance with PL/SQL
> > * You can use numeric, character, and string literals as bind arguments
> > * You cannot use bind arguments to pass the names of schema objects to
> > a dynamic SQL statement.
>
> does this mean you can't dynamically sub in a variable for a table
> name? if so, why keep that limitation? one of the main reasons to use
> dynamic sql is for schema objects.
>

No, it doesn't mean. You can create any SQL statement. Only you cannot
use binding (USING clause) for table name. Why? Because it's based on
prepared statements, and there you cannot use parameters for column's
or table's names.

You can: .. execute 'select * from || table || ' where a = $1' using var_a ..

Older patch was based on strings, and it was really ugly and without
any effects for security. Usually You have more params than table
names, so this limit is not too much hard. Now, patch is simple,
because there isn't any redundance.

Main reason for this patch is security. Not comfort for programmer.
But I belive, so it's good step forward.

Pavel

p.s. I though about it, and this is more consistent. You have only one
rule for params everywhere. ~ never use params as object names.

On 10/17/07, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> New syntax:
>
> a) EXECUTE stringexpr
> [INTO [STRICT] varlist
> [USING exprlist]
>
> b) FOR varlist IN EXECUTE stringexpr USING exprlist LOOP ....

Just chiming in with a +1. I would find this feature very useful.
Substitution of parameters is way more elegant than quoting, and the
syntax looks nice.

Regards,
BJ

2007/10/17, Brendan Jurd <direvus(at)gmail(dot)com>:
> On 10/17/07, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> > New syntax:
> >
> > a) EXECUTE stringexpr
> > [INTO [STRICT] varlist
> > [USING exprlist]
> >
> > b) FOR varlist IN EXECUTE stringexpr USING exprlist LOOP ....
>
> Just chiming in with a +1. I would find this feature very useful.
> Substitution of parameters is way more elegant than quoting, and the
> syntax looks nice.
>

I am doing some simple speed tests, and with USING run dynamic queries
little bit faster (15%). Prepared statement accepts params in binary
form, so we don't need call out functions.

Pavel

This has been saved for the 8.4 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Pavel Stehule wrote:
> Hello,
>
> this proposal change older unaccepted proposal
> http://archives.postgresql.org/pgsql-hackers/2006-03/msg01157.php .
>
> Changes:
> * based on prepared statements
> * syntax and behave is near to Oracle
> * usable as protection from SQL injection
>
> New syntax:
>
> a) EXECUTE stringexpr
> [INTO [STRICT] varlist
> [USING exprlist]
>
> b) FOR varlist IN EXECUTE stringexpr USING exprlist LOOP ....
>
> Reason:
> * defence from SQL injection
> * more readable, shorter, more comfortable
>
> Sample (secure dynamic statement):
> EXECUTE
> 'SELECT * FROM ' ||
> CASE tblname
> WHEN 'tab1' THEN 'tab1'
> WHEN 'tab2' THEN 'tab2'
> ELSE '"some is wrong"' END ||
> ' WHERE c1 = $1 AND c2 = $2'
> USING unsecure_parameter1, unsecure_parameter2;
>
> Difference between PL/SQL and proposal:
> * allow only IN variables
> * use PostgreSQL placeholders notation - "$"n instead ":"n
>
> Compliance with PL/SQL
> * You can use numeric, character, and string literals as bind arguments
> * You cannot use bind arguments to pass the names of schema objects to
> a dynamic SQL statement.
>
> Best regards
>
> Pavel Stehule
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +