
Exploiting hard filtered SQL Injections - hachiya
http://websec.wordpress.com/2010/03/19/exploiting-hard-filtered-sql-injections/
======
tptacek
By the time you're trying to figure out how to keep an SQL Injection vector
from becoming exploitable, you've already lost.

Just use prepared statements, and make sure that anything that isn't
parameterizable (table names, column sort orders, limits, offsets, etc) is
hardcoded and/or typecast (for instance, keep a table of valid table names,
and look them up from HTTP parameters).

------
IgorPartola
I am curious, why would you bother with all the regex filtering instead of
proper escaping or (even better) using prepared statements?

~~~
eli
and if you were determined to use a regex, wouldn't you create a whitelist
pattern (i.e. just numbers) rather than a blacklist (no quotes, whitespace,
etc)?

~~~
tptacek
Yes. Better yet: if your parameter is numeric (say, a LIMIT), typecast it to
an integer (to_i, int(), etc).

------
Rust
Out of curiosity, do these attack vectors still apply to MySQL prepared
statements? From his query example, the prepped version would read "SELECT
id,name,pass FROM users WHERE id = ? AND pass = ?" and the two parameters
would be passed in as an array of strings.

~~~
DrewHintz
> do these attack vectors still apply to MySQL prepared statements?

No.

~~~
tptacek
Sure they do. All you have to do is find a query that uses user input to
select a table, or that accepts an offset for pagination and doesn't
explicitly typecast it. We find SQLI in parameterized queries all the time.

You should absolutely use prepared statements, but don't kid yourself about
their magic powers.

------
appathy
$id = (int)$_GET['id'];

~~~
jrockway
The data validation is so you can display a nice error message. To protect the
database, you need to use prepared statements instead of
mysql_really_really_try_hard_to_quote_the_string_or_something.

~~~
agentq
In the particular case of integer ids, however, can't you just make sure
what's being passed is an integer? Similarly, for a 'simple' username, check
against "^[a-zA-Z0-9]+$"?

