2010/7/5 Florian Pflug <f...@phlo.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. [pa...@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 [pa...@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 > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
