Prev: [HACKERS] proof concept: do statement parametrization
Next: [HACKERS] Always truncate segments before unlink
From: Tom Lane on 4 Jul 2010 12:22 Andrew Dunstan <andrew(a)dunslane.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andres Freund on 4 Jul 2010 12:25 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 4 Jul 2010 12:41 2010/7/4 Tom Lane <tgl(a)sss.pgh.pa.us>: > Andrew Dunstan <andrew(a)dunslane.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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Pavel Stehule on 4 Jul 2010 12:47 2010/7/4 Andres Freund <andres(a)anarazel.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(a)postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Florian Pflug on 4 Jul 2010 19:30 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
First
|
Prev
|
Pages: 1 2 3 Prev: [HACKERS] proof concept: do statement parametrization Next: [HACKERS] Always truncate segments before unlink |