Обсуждение: 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.stehule@gmail.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@gmail.com>: > On 10/16/07, Pavel Stehule <pavel.stehule@gmail.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.stehule@gmail.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@gmail.com>: > On 10/17/07, Pavel Stehule <pavel.stehule@gmail.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@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +