From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Proposal: PL/pgSQL EXECUTE INTO USING (for 8.4) |
Date: | 2007-10-16 18:54:11 |
Message-ID: | 162867790710161154j5e0795afh6f7cbf22a266aa43@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Hiroshi Saito | 2007-10-16 18:54:25 | Re: [COMMITTERS] pgsql: Re-allow UTF8 encodings on win32. |
Previous Message | Hiroshi Saito | 2007-10-16 18:39:48 | Re: [COMMITTERS] pgsql: Re-allow UTF8 encodings on win32. |