Re: Sql injection attacks

Bill Moran <wmoran(at)potentialtech(dot)com> writes:

> Geoff Caplan <geoff(at)variosoft(dot)com> wrote:
>
> > Hi folks,
> >
> > I'm new to Postgres and trying to get up to speed on the security
> > issues. There seems to be remarkably little Postgres specific stuff on
> > preventing SQL injection attacks.
> >
> > Most of the online literature is on MS SQL Server. There, the
> > consensus seems to be that the range of potential attacks is so wide
> > that attempting to spot attack signatures in posted data is a doomed
> > enterprise, and that the safest general approach for any dynamically
> > built query is to execute it as a stored procedure.
>
> Huh?
>
> To protect yourself from SQL injections, just pass all your data through
> PQescapeString()

Or better yet don't mix your data with your code.

Any the literature that suggests interpolating your data into your SQL queries
using some escaping mechanism is in my humble opinion, leading you down the
garden path. It's the wrong way to think about things.

You should never ever write code that mixes data with executable code. Doing
so is just asking for trouble. Even if you know about PQEscapeString, it's
hard to verify that PQEscapeString has been called in every single place where
it's needed. One day you'll miss one place and all that effort becomes futile.

Better to just never mix the two. Let the driver handle marshalling the data
and transporting it to the database. All good driver APIs have an interface
that allows you to ship the data as separate parameters.

Something like (in Perl)

$sth = $dbh->prepare('select * from foo where a=?');
$sth->execute($dangerous_data);

or (in PHP)

$row = $db->getone('select * from foo where a=?', array($dangerous_data));

If you get into the habit of doing things this way normally, never just
interpolating variables into your sql code, then the rare instance when you do
have to interpolate something will stand out like a sore thumb. And it should
be easy to see whether that something is safely coming from static data in the
code or is dangerously coming from outside data from the network.

Note that not all database engines and drivers will actually be able to avoid
interpolating the data into the SQL eventually. Postgres as of 7.4 is capable
of it but even for Postgres not all the drivers have been updated. But that's
mostly irrelevant, at least make it the responsibility of the driver to do the
interpolating, it's more likely to get it right and whatever it does, it will
at least be 100% consistent about it.

But mixing the data with the code, even if you use PQEscapeString is just a
bad programming practice.

--
greg