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

Re: proof concept: do statement parametrization

From: Florian Pflug <fgp(at)phlo(dot)org>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept: do statement parametrization
Date: 2010-07-04 23:30:01
Message-ID: 308EF99C-0A24-41ED-8E62-E9FD30C2BB32@phlo.org (view raw or flat)
Thread:
Lists: 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


In response to

Responses

pgsql-hackers by date

Next:From: Takahiro ItagakiDate: 2010-07-05 02:23:52
Subject: Always truncate segments before unlink
Previous:From: Pavel StehuleDate: 2010-07-04 16:47:32
Subject: Re: proof concept: do statement parametrization

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