NYCPHP Meetup

[Dan Cech]

Michael Southwell wrote:
> I have a mysqli script which works perfectly except when I try to force
> an exception
> by using a test attacking parameter. I can't get an exception thrown
> even though I also
> don't get any output. The docs say the prepared statement must be a
> single statement, and of course
> this is not. But why don't I get an exception here?
> 
> ...
> $param = "red;DELETE FROM wines";
> try {
>   if ( ! $query = $demo -> prepare( 'SELECT name FROM wines WHERE color
> = ?' ) ) throw new Exception ( "can't select from wines: " . $demo ->
> error );
>   if ( ! $query -> bind_param( 's', $param ) ) throw new Exception (
> "can't bind parameter: " . $demo -> mysqli_error );
>   if ( ! $query -> execute() ) throw new Exception ( "can't execute
> query: " . $demo -> mysqli_error );
> ...
> 

The reason you are not getting the error you think you will get is the
power of prepared statements.

SELECT name FROM wines WHERE color=?

becomes:

SELECT name FROM wines WHERE color='red;DELETE FROM wines'

If you tried to trick it with $param = "red';DELETE FROM wines"; you
would get:

SELECT name FROM wines WHERE color='red\';DELETE FROM wines'

The sql is perfectly valid, and simply returns an empty result set
because you do not have a row in the database that matches the WHERE
clause in your query.

I have been saying this for years, but if you properly handle (quote &
escape) data, the content of that data is only relevant to your business
logic, and does not affect the program itself regardless of the content.

The prepared query is doing this for you transparently, which is why
they are a great way to secure your application against sql injection.
You actually have to be trying in order to create a situation where the
application is vulnerable.

Dan