Re: SQL injection, php and queueing multiple statement

On Sat, 12 Apr 2008 11:11:48 -0400
"Jonathan Bond-Caron" <jbondc(at)gmail(dot)com> wrote:

> "My premise is that someone will do mistakes in the php code and
> I'd like to mitigate the effect of these mistakes."
>
> - Prepared statements is the only "bulletproof" technique

I'm not looking for something bullet proof, I'm looking for one more
mitigating factor.
I already use an "fprint" technique that cast or escape sql input
before passing it to pg_query... but being able to queue 2 statements
make it much easier to reach whatever target the attacker may have.

I hate to make comparisons but up to my knowledge:
- MySQL drivers for php don't let you queue more than one statement
in a query (that's not the right solution)
- MS SQL has a server switch that let you chose if you'd like to have
more than one statement or not

As said... there are blind sql injection techniques that once the
door is open will let the attacker download the whole content of a DB.
But generally that requires time and it is achieved causing sql
errors. Errors get logged. Log analysis may spot the problem before
it is too late.
I know that other techniques rely on measuring execution time... but
well you've already put the bar much higher than letting slip in

select id, name from table1 where id=7 and 1=1; drop table table2; --

Somehow the sql statement is parsed along the way and you can't just
skip everything that is past a ; since

select id, name from table1 where name ilike '%;';

is a legit statement.

And it should be a switch... since you may need to load functions
and functions generally contain multiple statement separated by ; or
you may have to execute more than one statement in one transaction...
Isolating the code that can issue multiple statement from the one
that can't would come handy.

Is there such a switch in the php driver for pg? or is it in the
server cfg? If there is not such a thing it would be nice to have it.

I may sound naive but having a way to protect the DB from this kind
of injections looks as a common problem, I'd thought there was
already a common solution.
If there is not such a switch is there a reason?

> - Recommended solution - use database abstraction & AUDIT your
> code / grabs all SQL statements / create a summary and make sure
> then each statement is safe

While this are other barriers to fight coding errors they have a much
higher cost than just being able to block queued statement.

> Database abstractions alone often give you a false sense of security
> especially if you use third part / open source solutions

It is exactly that false sense of security I'm trying to fight,
placing several barriers on the way of a potential attacker.

Being able to stop queued statements seemed a cheap barrier but with a
reasonably good ROI.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it