Skip site navigation (1) Skip section navigation (2)

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

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: (view raw or whole thread)
Lists: pgsql-hackers

this proposal change older unaccepted proposal .

* 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 ....

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

Sample (secure dynamic statement):
                 '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


pgsql-hackers by date

Next:From: Hiroshi SaitoDate: 2007-10-16 18:54:25
Subject: Re: [COMMITTERS] pgsql: Re-allow UTF8 encodings on win32.
Previous:From: Hiroshi SaitoDate: 2007-10-16 18:39:48
Subject: Re: [COMMITTERS] pgsql: Re-allow UTF8 encodings on win32.

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group