Re: Sql injection attacks - Mailing list pgsql-general

Hi folks

Seems we have two schools of thought:

1) The validation/escaping approach, supported by Bill and Jim

2) The "don't mix data with code" approach supported by Peter and
Greg.

As I learn more about the issues, I am increasingly veering towards
the second approach.

Obviously, proper validation is a given for all kinds of reasons. But
the problem with validation/escaping as the primary defense against
injection seems to be that simply escaping would not catch every type
of insertion via strings.

If you are just saving the untrusted string as a value, then the
attacker needs to use a quote or comment to escape out of the value
and alter the query syntax. This is relatively easy to counter, as
Bill suggests.

But in web work, you are often using GET/POST data directly in your
SQL clauses, so the untrusted data is part of the query syntax and not
just a value.

In that scenario, if you just concatenate your untrusted data into
your query, it is relatively easy for the attacker to sneak in
AND/OR/UNION type clauses without using quotes. This means that you
have to check strings for attack signatures as well, which is not
trivial if you are going to avoid false positives. And it means that
every single untrusted string has to be screened by someone skilled at
analysing the potential threats and writing the right Regexes. And as
Greg point out, you only have to screw up once and you have opened up
your whole system. Plus, as new attacks are developed, you have to
audit all your code to counter them. This surely can't be the way to
go.

Bill wrote:

BM> I don't see how storing the SQL in some different location is the
BM> correct way to fix anything?

That was my original suggestion, and I agree it was naive. But I don't
think that is what Peter is suggesting. I think the idea is that
parameterised prepared statements compile the SQL statement before the
user input is added via the parameters, making it impossible for user
input to modify the actual SQL statement.

From the practical point of view, it surely seems easier to adopt a
particular style of programming ("any query using untrusted data MUST
be a parameterised prepared statement") than to analyse each and every
untrusted parameter for attack signatures? Or am I missing something
here?

------------------
Geoff Caplan
Vario Software
(+44) 121-515 1154