Re: [PATCHES] actualised execute using patch

2008-03-26 Thread Pavel Stehule 
On 25/03/2008, Zdenek Kotala [EMAIL PROTECTED] wrote:
 you have extra space onPavel Stehule napsal(a):

  Hello
  
   http://archives.postgresql.org/pgsql-patches/2007-10/msg00161.php
  
   I actualized this patch for current CVS


 Hi Pavel,

  I tested your patch and it works regarding to proposal. However
  I have some small comments:

  1) you have extra space in ./src/pl/plpgsql/src/gram.y on line 1351

I didn't find it

  2) there is some Czech text in regression tests diffs


fixed

  3) I would like to add two more tests:

  a) test which check if execute really takes 3rd parameter and inject it as a
  first one:

  create or replace function test(int,int,int) returns int as $$
  declare
a int;
  begin
   execute 'select $1' into a using $3;
   return a;
  end
   $$ language plpgsql;
  select test(1,2,3);

  b) and second test which control number of arguments:

  for example:

  create or replace function test(int,int,int) returns int as $$
  begin
   execute 'select $3' into a using $1;
   return a;
  end
   $$ language plpgsql;
  select test(1,2,3);


I did it



 Zdenek


*** ./doc/src/sgml/plpgsql.sgml.orig	2008-03-23 01:24:19.0 +0100
--- ./doc/src/sgml/plpgsql.sgml	2008-03-24 20:41:27.0 +0100
***
*** 1005,1011 
   commandEXECUTE/command statement is provided:
  
  synopsis
! EXECUTE replaceable class=commandcommand-string/replaceable optional INTO optionalSTRICT/optional replaceabletarget/replaceable /optional;
  /synopsis
  
   where replaceablecommand-string/replaceable is an expression
--- 1005,1011 
   commandEXECUTE/command statement is provided:
  
  synopsis
! EXECUTE replaceable class=commandcommand-string/replaceable optional INTO optionalSTRICT/optional replaceabletarget/replaceable /optional optional USING replaceable class=parameterexpression/replaceable optional, .../optional /optional;
  /synopsis
  
   where replaceablecommand-string/replaceable is an expression
***
*** 1046,1051 
--- 1046,1066 
   If the literalSTRICT/ option is given, an error is reported
   unless the query produces exactly one row.
  /para
+ 
+ para
+  The commandEXECUTE/command statement can take parameters. To refer 
+  to the parameters use $1, $2, $3, etc. Any parameter have to be bind to
+  any variable or any expression with USING clause. You cannot use bind 
+  arguments to pass the names of schema objects to a dynamic SQL statement.
+  The use of arguments is perfect protection from SQL injection.
+ programlisting
+ EXECUTE 'SELECT count(*) FROM '
+ 	|| tabname::regclass
+ 	|| ' WHERE inserted_by = $1 AND inserted = $2'
+INTO c
+USING checked_user, checked_date;
+ /programlisting
+ /para
  
  para
   commandSELECT INTO/command is not currently supported within
***
*** 1997,2003 
   rows:
  synopsis
  optional lt;lt;replaceablelabel/replaceablegt;gt; /optional
! FOR replaceabletarget/replaceable IN EXECUTE replaceabletext_expression/replaceable LOOP 
  replaceablestatements/replaceable
  END LOOP optional replaceablelabel/replaceable /optional;
  /synopsis
--- 2012,2018 
   rows:
  synopsis
  optional lt;lt;replaceablelabel/replaceablegt;gt; /optional
! FOR replaceabletarget/replaceable IN EXECUTE replaceabletext_expression/replaceable optional USING replaceable class=parameterexpression/replaceable optional, .../optional /optional LOOP 
  replaceablestatements/replaceable
  END LOOP optional replaceablelabel/replaceable /optional;
  /synopsis
*** ./src/pl/plpgsql/src/gram.y.orig	2008-03-26 07:30:27.0 +0100
--- ./src/pl/plpgsql/src/gram.y	2008-03-24 20:41:27.0 +0100
***
*** 21,26 
--- 21,27 
  
  static PLpgSQL_expr		*read_sql_construct(int until,
  			int until2,
+ 			int until3,
  			const char *expected,
  			const char *sqlstart,
  			bool isexpression,
***
*** 200,205 
--- 201,207 
  %token	K_THEN
  %token	K_TO
  %token	K_TYPE
+ %token	K_USING
  %token	K_WARNING
  %token	K_WHEN
  %token	K_WHILE
***
*** 892,899 
  		{
  			PLpgSQL_stmt_dynfors	*new;
  			PLpgSQL_expr			*expr;
  
! 			expr = plpgsql_read_expression(K_LOOP, LOOP);
  
  			new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
  			new-cmd_type = PLPGSQL_STMT_DYNFORS;
--- 894,907 
  		{
  			PLpgSQL_stmt_dynfors	*new;
  			PLpgSQL_expr			*expr;
+ 			int		term;
  
! 			expr = read_sql_construct(K_LOOP, 
! K_USING, 
! 0, 
! LOOP|USING, 
! SELECT , 
! true, true, term);
  
  			new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
  			new-cmd_type = PLPGSQL_STMT_DYNFORS;
***
*** 920,925 
--- 928,948 
  yyerror(loop variable of loop over rows must be a record or row variable or list of scalar variables);

Re: [PATCHES] actualised execute using patch

2008-03-25 Thread Zdenek Kotala 

you have extra space onPavel Stehule napsal(a):

Hello

http://archives.postgresql.org/pgsql-patches/2007-10/msg00161.php

I actualized this patch for current CVS


Hi Pavel,

I tested your patch and it works regarding to proposal. However
I have some small comments:

1) you have extra space in ./src/pl/plpgsql/src/gram.y on line 1351

2) there is some Czech text in regression tests diffs

3) I would like to add two more tests:

a) test which check if execute really takes 3rd parameter and inject it as a 
first one:


create or replace function test(int,int,int) returns int as $$
declare
  a int;
begin
 execute 'select $1' into a using $3;
 return a;
end
 $$ language plpgsql;
select test(1,2,3);

b) and second test which control number of arguments:

for example:

create or replace function test(int,int,int) returns int as $$
begin
 execute 'select $3' into a using $1;
 return a;
end
 $$ language plpgsql;
select test(1,2,3);



Zdenek


--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

[PATCHES] actualised execute using patch

2008-03-24 Thread Pavel Stehule 
Hello

http://archives.postgresql.org/pgsql-patches/2007-10/msg00161.php

I actualized this patch for current CVS

Regards
Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig	2008-03-23 01:24:19.0 +0100
--- ./doc/src/sgml/plpgsql.sgml	2008-03-24 20:41:27.0 +0100
***
*** 1005,1011 
   commandEXECUTE/command statement is provided:
  
  synopsis
! EXECUTE replaceable class=commandcommand-string/replaceable optional INTO optionalSTRICT/optional replaceabletarget/replaceable /optional;
  /synopsis
  
   where replaceablecommand-string/replaceable is an expression
--- 1005,1011 
   commandEXECUTE/command statement is provided:
  
  synopsis
! EXECUTE replaceable class=commandcommand-string/replaceable optional INTO optionalSTRICT/optional replaceabletarget/replaceable /optional optional USING replaceable class=parameterexpression/replaceable optional, .../optional /optional;
  /synopsis
  
   where replaceablecommand-string/replaceable is an expression
***
*** 1046,1051 
--- 1046,1066 
   If the literalSTRICT/ option is given, an error is reported
   unless the query produces exactly one row.
  /para
+ 
+ para
+  The commandEXECUTE/command statement can take parameters. To refer 
+  to the parameters use $1, $2, $3, etc. Any parameter have to be bind to
+  any variable or any expression with USING clause. You cannot use bind 
+  arguments to pass the names of schema objects to a dynamic SQL statement.
+  The use of arguments is perfect protection from SQL injection.
+ programlisting
+ EXECUTE 'SELECT count(*) FROM '
+ 	|| tabname::regclass
+ 	|| ' WHERE inserted_by = $1 AND inserted = $2'
+INTO c
+USING checked_user, checked_date;
+ /programlisting
+ /para
  
  para
   commandSELECT INTO/command is not currently supported within
***
*** 1997,2003 
   rows:
  synopsis
  optional lt;lt;replaceablelabel/replaceablegt;gt; /optional
! FOR replaceabletarget/replaceable IN EXECUTE replaceabletext_expression/replaceable LOOP 
  replaceablestatements/replaceable
  END LOOP optional replaceablelabel/replaceable /optional;
  /synopsis
--- 2012,2018 
   rows:
  synopsis
  optional lt;lt;replaceablelabel/replaceablegt;gt; /optional
! FOR replaceabletarget/replaceable IN EXECUTE replaceabletext_expression/replaceable optional USING replaceable class=parameterexpression/replaceable optional, .../optional /optional LOOP 
  replaceablestatements/replaceable
  END LOOP optional replaceablelabel/replaceable /optional;
  /synopsis
*** ./src/pl/plpgsql/src/gram.y.orig	2008-01-01 20:46:00.0 +0100
--- ./src/pl/plpgsql/src/gram.y	2008-03-24 20:41:27.0 +0100
***
*** 21,26 
--- 21,27 
  
  static PLpgSQL_expr		*read_sql_construct(int until,
  			int until2,
+ 			int until3,
  			const char *expected,
  			const char *sqlstart,
  			bool isexpression,
***
*** 200,205 
--- 201,207 
  %token	K_THEN
  %token	K_TO
  %token	K_TYPE
+ %token	K_USING
  %token	K_WARNING
  %token	K_WHEN
  %token	K_WHILE
***
*** 892,899 
  		{
  			PLpgSQL_stmt_dynfors	*new;
  			PLpgSQL_expr			*expr;
  
! 			expr = plpgsql_read_expression(K_LOOP, LOOP);
  
  			new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
  			new-cmd_type = PLPGSQL_STMT_DYNFORS;
--- 894,907 
  		{
  			PLpgSQL_stmt_dynfors	*new;
  			PLpgSQL_expr			*expr;
+ 			int		term;
  
! 			expr = read_sql_construct(K_LOOP, 
! K_USING, 
! 0, 
! LOOP|USING, 
! SELECT , 
! true, true, term);
  
  			new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
  			new-cmd_type = PLPGSQL_STMT_DYNFORS;
***
*** 920,925 
--- 928,948 
  yyerror(loop variable of loop over rows must be a record or row variable or list of scalar variables);
  			}
  			new-query = expr;
+ 			
+ 			if (term == K_USING)
+ 			{
+ for(;;)
+ {
+ 	int term;
+ 
+ 	expr = read_sql_construct(',', K_LOOP, 0, , or LOOP,
+ SELECT ,
+ true, true, term);
+ 	new-params = lappend(new-params, expr);
+ 	if (term == K_LOOP)
+ 		break;
+ }
+ 			}
  
  			$$ = (PLpgSQL_stmt *) new;
  		}
***
*** 954,959 
--- 977,983 
  			 */
  			expr1 = read_sql_construct(K_DOTDOT,
  	   K_LOOP,
+ 	   0,
  	   LOOP,
  	   SELECT ,
  	   true,
***
*** 975,980 
--- 999,1005 
  /* Read and check the second one */
  expr2 = read_sql_construct(K_LOOP,
  		   K_BY,
+ 		   0,
  		   LOOP,
  		   SELECT ,
  		   true,
***
*** 1222,1228