Re: Sql injection attacks

At 09:58 AM 7/26/2004 +0100, Geoff Caplan wrote:
>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.

They are not really different schools of thought.

My suggestion:
0) Make it much easier to the right thing than the wrong thing. Prepared
statements help. You may still need to make it simpler for the programmers.
1) validate/filter/escape all input to your program so that your program
(NOT other programs[1]) can deal with it.
2) validate/filter/escape output from your program to each destination
accordingly so that each destination can deal with it and treat it correctly.
3) Assume by default input could be from hostile sources, unless provable
otherwise.

[1] Do not combine filters, doing so often corrupts data. Boneheaded
thinking like PHP's magic quotes create the silly multiple backslashed
single quotes and similar stuff.

Also for PostgreSQL at least do not forget that _ and % are wildcards for
LIKE and similar queries and these wildcards are probably NOT escaped
whether explicitly by postgresql's builtin escaping functions or
automatically by the parameterizing methods. When I last checked you have
to escape them yourself if you want to.

Though insertion of wildcards is not exactly SQL injection they often cause
applications to behave differently from design. A wildcard in a LIKE query
could cause a full table scan, and thus:

1) Use up resources (CPU, memory, disk/network bandwidth). Possibly more
easily allow a Denial of Service attack.
2) Indicate to the attacker how many rows there are (e.g. how many
customers a particular CA had at a particular moment. Polling regularly
indicates how many customers added, or whether _your_ row was added
successfully (*grin*) ).

>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.

Don't do that. If you need to do that, you are doing things wrong. Very wrong.

Link.