Re: Sql injection attacks

Greg Stark <gsstark(at)mit(dot)edu> wrote:

>
> 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));

I've hit a lot of problems with these type of interfaces making it very
difficult to execute complex queries. But it may just be my unfamiliarity
with such coding conventions. I avoid them because they're difficult, but
they're difficult because I avoid them.

However, how do you suggest that rule of thumb be done when working in C?

--
Bill Moran
Potential Technologies
http://www.potentialtech.com