Skip site navigation (1) Skip section navigation (2)

Re: proof concept: do statement parametrization

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Florian Pflug <fgp(at)phlo(dot)org>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept: do statement parametrization
Date: 2010-07-05 03:31:34
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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
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'
[pavel(at)nemesis ~]$ 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.



> 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

In response to

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2010-07-05 05:51:56
Subject: Implementation of median in PostgreSQL - questions
Previous:From: Takahiro ItagakiDate: 2010-07-05 02:23:52
Subject: Always truncate segments before unlink

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group