Re: proof concept: do statement parametrization

Hello

I enhanced DO statement syntax to allowing a parameters. Syntax is
relative simple:

do ([varname] vartype := value, ...) $$ ... $$

It allows to pass a content of psql variables to inline code block to
allows more easy scripting

\set schema 'public'

do(text := :'schema') $$
declare r record;
begin
for r in
select * from information_schema.tables where table_schema = $1
loop
raise notice '>>> table %', r.table_name;
end loop;
end $$;
NOTICE: >>> table t
NOTICE: >>> table t1
DO

ToDo:

* doesn't allows SubLinks :(

pavel(at)postgres:5432=# do(text := (SELECT :'schema')) $$ declare r
record; begin for r in select * from information_schema.tables where
table_schema = $1 loop raise notice '>>> table %', r.table_name; end
loop; end $$;
ERROR: XX000: unrecognized node type: 315
LOCATION: ExecInitExpr, execQual.c:4868

ideas, notes, comments??

Regards

Pavel Stehule

On Jul4, 2010, at 08:41 , Pavel Stehule wrote:
> I enhanced DO statement syntax to allowing a parameters. Syntax is
> relative simple:
>
> do ([varname] vartype := value, ...) $$ ... $$

I think it'd be more useful to put the values at the very end of the statement, not somewhere in the middle. For positional parameters I envision

do (vartype, ...) $$ ... $$ using value, ...

and for named parameters it'd be

do (varname vartype) $$ ... $$ using varname := value, ...

I won't make a difference for your use-case, but it'd make it easier to call the same DO block with different parameters, like in the following shell snippet.

COMMANDS="DO (arg int) $$ ... $$"
(for a in arg1, arg2, arg3, arg4; do
echo "$COMMANDS USING $a;"
done) | psql

best regards,
Florian Pflug

2010/7/4 Florian Pflug <fgp(at)phlo(dot)org>:
> On Jul4, 2010, at 08:41 , Pavel Stehule wrote:
>> I enhanced DO statement syntax to allowing a parameters. Syntax is
>> relative simple:
>>
>> do ([varname] vartype := value, ...) $$ ... $$
>
>
> I think it'd be more useful to put the values at the very end of the statement, not somewhere in the middle. For positional parameters I envision
>
> do (vartype, ...) $$ ... $$ using value, ...
>
> and for named parameters it'd be
>
> do (varname vartype) $$ ... $$ using varname := value, ...
>
> I won't make a difference for your use-case, but it'd make it easier to call the same DO block with different parameters, like in the following shell  snippet.
>
> COMMANDS="DO (arg int) $$ ... $$"
> (for a in arg1, arg2, arg3, arg4; do
>  echo "$COMMANDS USING $a;"
> done) | psql
>
Your syntax is longer and less readable (my personal view). With
proposed syntax it is ensured so every parameter has a value. Next -
my syntax is reflecting fact, so these are not true parameters - it's
+/- similar to default values of function parameters. You cannot to
write do (a int := $1) $$ ... $$ - because utils statements hasn't
have variables.

I understand to your motivation - but you can use a printf command and
do it same work

CMD='do(a int := %s) $$ begin raise notice ''%%'',a; end; $$'
for a in $1 $2 $3 $4
do
if [ -n "$a" ]
then
echo `printf "$CMD" $a` | psql postgres
fi
done;

or better and safer - use a psql variables (it is preferred solution)

################################
for a in $1 $2 $3 $4
do
if [ -n "$a" ]
then
psql postgres --quiet --variable a=$a <<EOT

do (a int := :a) \$\$
begin
raise notice '%', a;
end; \$\$

EOT

fi
done
###############################

psql variables can be escaped more secure - so it is prefered

for a in `cat /etc/passwd | cut -d: -f1`
do
psql postgres --quiet --variable usrname=$a <<EOT
do (usrname varchar := :'usrname') \$\$
begin
raise notice '%', usrname;
end; \$\$
EOT
done

Regards

Pavel Stehule

> best regards,
> Florian Pflug
>
>

On Jul4, 2010, at 11:59 , Pavel Stehule wrote:
> 2010/7/4 Florian Pflug <fgp(at)phlo(dot)org>:
>> On Jul4, 2010, at 08:41 , Pavel Stehule wrote:
>>> I enhanced DO statement syntax to allowing a parameters. Syntax is
>>> relative simple:
>>>
>>> do ([varname] vartype := value, ...) $$ ... $$
>>
>> I think it'd be more useful to put the values at the very end of the statement, not somewhere in the middle. For positional parameters I envision
>>
>> do (vartype, ...) $$ ... $$ using value, ...
>>
>> and for named parameters it'd be
>>
>> do (varname vartype) $$ ... $$ using varname := value, ...

> Your syntax is longer and less readable (my personal view). With
> proposed syntax it is ensured so every parameter has a value. Next -
> my syntax is reflecting fact, so these are not true parameters - it's
> +/- similar to default values of function parameters.

Yeah, with your syntax omitting a value is syntactically invalid, while with mine it'd parse OK and fail later on. But I fail to see the drawback of that. I do agree that my suggestion is slightly more verbose, but it think thats compensated by the increase in usefulness.

> I understand to your motivation - but you can use a printf command and
> do it same work.

Sure. But by the very same argument, printf makes DO-block parameters redundant as a whole.

> or better and safer - use a psql variables (it is preferred solution)

I don't really buy that argument. By using a psql variable, you simply move the quoting & escaping business from SQL to the shell where psql is called. True, you avoid SQL injectiont, but in turn you make yourself vulnerable to shell injection.

best regards,
Florian Pflug

2010/7/4 Florian Pflug <fgp(at)phlo(dot)org>:
> On Jul4, 2010, at 11:59 , Pavel Stehule wrote:
>> 2010/7/4 Florian Pflug <fgp(at)phlo(dot)org>:
>>> On Jul4, 2010, at 08:41 , Pavel Stehule wrote:
>>>> I enhanced DO statement syntax to allowing a parameters. Syntax is
>>>> relative simple:
>>>>
>>>> do ([varname] vartype := value, ...) $$ ... $$
>>>
>>> I think it'd be more useful to put the values at the very end of the statement, not somewhere in the middle. For positional parameters I envision
>>>
>>> do (vartype, ...) $$ ... $$ using value, ...
>>>
>>> and for named parameters it'd be
>>>
>>> do (varname vartype) $$ ... $$ using varname := value, ...
>
>> Your syntax  is longer and less readable (my personal view). With
>> proposed syntax it is ensured so every parameter has a value. Next -
>> my syntax is reflecting fact, so these are not true parameters - it's
>> +/- similar to default values of function parameters.
>
> Yeah, with your syntax omitting a value is syntactically invalid, while with mine it'd parse OK and fail later on. But I fail to see the drawback of that. I do agree that my suggestion is slightly more verbose, but it think thats compensated by the increase in usefulness.
>
>> I understand to your motivation - but you can use a printf command and
>> do it same work.
>
> Sure. But by the very same argument, printf makes DO-block parameters redundant as a whole.
>

printf isn't nice, agree - it is just workaround for some special case
- when you don't store code in variable, then you have not any
problems.

>> or better and safer - use a psql variables (it is preferred solution)
>
> I don't really buy that argument. By using a psql variable, you simply move the quoting & escaping business from SQL to the shell where psql is called. True, you avoid SQL injectiont, but in turn you make yourself vulnerable to shell injection.

can you show some example of shell injection? For me, this way via
psql variables is the best. There are clean interface between outer
and inner space. And I can call simply just psql scripts - without
external bash.

best regards
Pavel

p.s. theoretically do statement can support both syntax, maybe mix of
all. It's only about 20 lines more in parser. But code will be little
bit more complex and I am not sure if it is necessary. I dislike the
space between variable definition and values - and you have to put
param list on the statement end.

>
> best regards,
> Florian Pflug
>
>

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> my syntax is reflecting fact, so these are not true parameters - it's
> +/- similar to default values of function parameters.

FWIW, that doesn't seem like a positive to me.

> You cannot to
> write do (a int := $1) $$ ... $$ - because utils statements hasn't
> have variables.

Yet. I don't particularly want to relax that either, but the syntax of
this feature shouldn't assume it'll be true forever.

I think it's better to not confuse these things with default parameters,
so Florian's idea looks better to me.

BTW, we intentionally didn't put any provision for parameters into DO
originally. What's changed to alter that decision?

regards, tom lane

On Sun, July 4, 2010 9:58 am, Tom Lane wrote:

>
> BTW, we intentionally didn't put any provision for parameters into DO
> originally. What's changed to alter that decision?
>

Nothing that I know of, I think there is just a little impatience here. I
think the consensus was that we needed to get some experience of DO in the
field before looking at a parameter mechanism. I still think that's the
correct position.

cheers

andrew

2010/7/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> my syntax is reflecting fact, so these are not true parameters - it's
>> +/- similar to default values of function parameters.
>
> FWIW, that doesn't seem like a positive to me.
>
>> You cannot to
>> write do (a int := $1) $$ ... $$ - because utils statements hasn't
>> have variables.
>
> Yet.  I don't particularly want to relax that either, but the syntax of
> this feature shouldn't assume it'll be true forever.
>
> I think it's better to not confuse these things with default parameters,
> so Florian's idea looks better to me.
>
> BTW, we intentionally didn't put any provision for parameters into DO
> originally.  What's changed to alter that decision?
>
>                        regards, tom lane
>

It just concept - nothing more. And my instinct speak so inline code
block without external parametrization is useless.

Regards

Pavel Stehule

2010/7/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> my syntax is reflecting fact, so these are not true parameters - it's
>> +/- similar to default values of function parameters.
>
> FWIW, that doesn't seem like a positive to me.
>
>> You cannot to
>> write do (a int := $1) $$ ... $$ - because utils statements hasn't
>> have variables.
>
> Yet.  I don't particularly want to relax that either, but the syntax of
> this feature shouldn't assume it'll be true forever.
>
> I think it's better to not confuse these things with default parameters,
> so Florian's idea looks better to me.

Maybe I am didn't explain well my idea. The most all is modificated
named notation enhanced about type info. It isn't default parameter
definition - so I use ":=" and not use "=". And it has same advantage
like named notation has. Using a keyword "USING" isn't perfectly clean
for me - I have a problem with position of parameters - but if other
people feel it different, I'll not have a problem.

do(a int := 20, b int := 20) $$ ... $$;
do (a int, b int) $$ .... $$ USING 10,20;

generally both syntaxes are used now.

This patch is just concept - I spoke it, I would to show attractive
behave, and Florian showed possible wery nice colaboration shell with
psql. I don't want to insult somebody.

Regards
Pavel Stehule

>
> BTW, we intentionally didn't put any provision for parameters into DO
> originally.  What's changed to alter that decision?
>
>                        regards, tom lane
>

Pavel Stehule wrote:
>> BTW, we intentionally didn't put any provision for parameters into DO
>> originally. What's changed to alter that decision?
>>
>>
>
> It just concept - nothing more. And my instinct speak so inline code
> block without external parametrization is useless.
>
>
>

You have said this before, IIRC, but frankly your instinct is just
wrong. It is no more useless than are parameter-less functions, and I
use those frequently. I used a DO block for some useful testing just the
other day.

This whole proposal strikes me as premature. What we need is some
experience from the field in using DO before we can sensibly decide how
it should be extended. And we won't get that until 9.0 has been released
and used for a while.

cheers

andrew

2010/7/4 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> Pavel Stehule wrote:
>>>
>>> BTW, we intentionally didn't put any provision for parameters into DO
>>> originally.  What's changed to alter that decision?
>>>
>>>
>>
>> It just concept - nothing more. And my instinct speak so inline code
>> block without external parametrization is useless.
>>
>>
>>
>
> You have said this before, IIRC, but frankly your instinct is just wrong. It
> is no more useless than are parameter-less functions, and I use those
> frequently. I used a DO block for some useful testing just the other day.
>
> This whole proposal strikes me as premature. What we need is some experience
> from the field in using DO before we can sensibly decide how it should be
> extended. And we won't get that until 9.0 has been released and used for a
> while.
>

just we have different opinion

Regards

Pavel

> cheers
>
> andrew
>

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> This whole proposal strikes me as premature. What we need is some
> experience from the field in using DO before we can sensibly decide how
> it should be extended. And we won't get that until 9.0 has been released
> and used for a while.

+1.

What strikes me about this proposal is that there isn't any way to pass
parameter strings without worrying about how to escape them; which means
that the actual functionality gain over 9.0 is at best rather limited.

Now you could get to that if we had support for utility statements
accepting parameter symbols, ie you could execute
DO ... USING $1, $2
with out-of-line parameter values passed using the PQexecParams protocol.
So maybe that's an orthogonal feature that should be done as a separate
patch, but without it I'm not sure there's really much point.

IIRC one of the stumbling blocks for parameters in utility statements
is that usually there's no good context for inferring their data types.
If we were to extend DO in the particular way Pavel suggests, then
there would be context for that case, but I'm not sure what we do about
the general case. We'd want to think about that before installing a
special-purpose rule that only works for DO.

regards, tom lane

On Sun, Jul 04, 2010 at 11:38:47AM -0400, Andrew Dunstan wrote:
>
>
> Pavel Stehule wrote:
> >>BTW, we intentionally didn't put any provision for parameters into DO
> >>originally. What's changed to alter that decision?
> >>
> >
> >It just concept - nothing more. And my instinct speak so inline code
> >block without external parametrization is useless.
> >
> >
>
> You have said this before, IIRC, but frankly your instinct is just
> wrong. It is no more useless than are parameter-less functions, and
> I use those frequently. I used a DO block for some useful testing
> just the other day.
In my opinion its even *more* useful than parameterless
functions. In many cases you will use DO to write upgrade scripts or
ad-hoc code.
In both cases its not really much of diference whether you write the
parameter inside the function or outside (as a parameter to it) and
escaping is not a critical part anyway.

So maybe I am missing the point of this discussion?

Andres

2010/7/4 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> This whole proposal strikes me as premature. What we need is some
>> experience from the field in using DO before we can sensibly decide how
>> it should be extended. And we won't get that until 9.0 has been released
>> and used for a while.
>
> +1.
>
> What strikes me about this proposal is that there isn't any way to pass
> parameter strings without worrying about how to escape them; which means
> that the actual functionality gain over 9.0 is at best rather limited.
>
> Now you could get to that if we had support for utility statements
> accepting parameter symbols, ie you could execute
>        DO ... USING $1, $2
> with out-of-line parameter values passed using the PQexecParams protocol.
> So maybe that's an orthogonal feature that should be done as a separate
> patch, but without it I'm not sure there's really much point.

If I remember well, you wrote so this way isn't directly possible. You
have to know a targer datatype - so you have to use syntax DO(target
type list) ... USING ... and there have to be mechanisms to put these
values to PL. Maybe you think to use only varchar variables and then
access to values via array (from PL)?

little bit different question - but I hope related to topic. I
thinking about CALL statement and "true procedures". There are three
request - transaction control, multi record sets, and using IN, OUT
parameters (compatibility issue and conformance with standard). Now I
don't know - CALL statement have to be util statement or classic plan
statement? I inclined to think so util statement can be better. But I
would to use a IN and OUT variables too - so some support for
PQexecParams protocol can be nice

CREATE OR REPLACE PROCEDURE foo(IN a int, IN b int, OUT c int)
...

and using from psql

CALL foo(10,10, :result);
\echo :result

Pavel

>
> IIRC one of the stumbling blocks for parameters in utility statements
> is that usually there's no good context for inferring their data types.
> If we were to extend DO in the particular way Pavel suggests, then
> there would be context for that case, but I'm not sure what we do about
> the general case.  We'd want to think about that before installing a
> special-purpose rule that only works for DO.
>
>                        regards, tom lane
>

2010/7/4 Andres Freund <andres(at)anarazel(dot)de>:
> On Sun, Jul 04, 2010 at 11:38:47AM -0400, Andrew Dunstan wrote:
>>
>>
>> Pavel Stehule wrote:
>> >>BTW, we intentionally didn't put any provision for parameters into DO
>> >>originally.  What's changed to alter that decision?
>> >>
>> >
>> >It just concept - nothing more. And my instinct speak so inline code
>> >block without external parametrization is useless.
>> >
>> >
>>
>> You have said this before, IIRC, but frankly your instinct is just
>> wrong. It is no more useless than are parameter-less functions, and
>> I use those frequently. I used a DO block for some useful testing
>> just the other day.
> In my opinion its even *more* useful than parameterless
> functions. In many cases you will use DO to write upgrade scripts or
> ad-hoc code.
> In both cases its not really much of diference whether you write the
> parameter inside the function or outside (as a parameter to it) and
> escaping is not a critical part anyway.
>
> So maybe I am missing the point of this discussion?

when the parameter are not outside, then they are not accessable from
psql. psql's variable expansion isn't working inside code literal. So
you have not any way to put some external parameters - for example -
when I would to prepare scripts for administration of databases for
some user - cleaning schema, preparing schema, etc, then I have to
write username directly to script. I cannot use a possibility of psql
to specify variables.

Regards

Pavel

>
> Andres
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Jul4, 2010, at 13:57 , Pavel Stehule wrote:
>> I don't really buy that argument. By using a psql variable, you simply move the quoting & escaping business from SQL to the shell where psql is called. True, you avoid SQL injectiont, but in turn you make yourself vulnerable to shell injection.
>
> can you show some example of shell injection? For me, this way via
> psql variables is the best. There are clean interface between outer
> and inner space. And I can call simply just psql scripts - without
> external bash.

Well, on the one hand you have (with your syntax)
echo "DO (a int := $VALUE) $$ ... $$" | psql
which allows sql injection if $VALUE isn't sanitized or quoted & escaped properly.

On the other hand you have
echo "DO (a int := :value) $$ ... $$$ | psql --variable value=$VALUE
which allows at least injection of additional arguments to psql if $VALUE contains spaces. You might try to avoid that by encoding value=$VALUE in double quotes, but I doubt that it's 100% safe even then.

The point is that interpolating the value into the command is always risky, independent from whether it's a shell command or an sql command.

best regards,
Florian Pflug

2010/7/5 Florian Pflug <fgp(at)phlo(dot)org>:
> On Jul4, 2010, at 13:57 , Pavel Stehule wrote:
>>> I don't really buy that argument. By using a psql variable, you simply move the quoting & escaping business from SQL to the shell where psql is called. True, you avoid SQL injectiont, but in turn you make yourself vulnerable to shell injection.
>>
>> can you show some example of shell injection? For me, this way via
>> psql variables is the best. There are clean interface between outer
>> and inner space. And I can call simply just psql scripts - without
>> external bash.
>
> Well, on the one hand you have (with your syntax)
> echo "DO (a int := $VALUE) $$ ... $$" | psql
> which allows sql injection if $VALUE isn't sanitized or quoted & escaped properly.

sure - but it is same for you syntax, isn't it? This is classical
dynamic SQL - and more used in from untyped language.

>
> On the other hand you have
> echo "DO (a int := :value) $$ ... $$$ | psql --variable value=$VALUE
> which allows at least injection of additional arguments to psql if $VALUE contains spaces. You might try to avoid that by encoding value=$VALUE in double quotes, but I doubt that it's 100% safe even then.

[pavel(at)nemesis ~]$ cat y.sh
a='some variable with " ajjaja" jjaja'
b='other variable with "jaja'
c="third 'variable"
psql postgres --variable a="$a" --variable b="$b" --variable c="$c" <<EOT
\echo 'a = ' :'a'
\echo 'b = ' :'b'
\echo 'c = ' :'c'
EOT
[pavel(at)nemesis ~]$ sh y.sh
a = 'some variable with " ajjaja" jjaja'
b = 'other variable with "jaja'
c = 'third ''variable'

it is safe - and it is only one really secure way. My design calculate with it

you can do

DO(a int := :'variable') ... and variable is well escaped and value is
casted to int. I am really very happy from :'xxx' feature.

regards

Pavel

>
> The point is that interpolating the value into the command is always risky, independent from whether it's a shell command or an sql command.
>
> best regards,
> Florian Pflug
>
>