0 0 0 0


How do prepared statements help us prevent SQL injection attacks?

Wikipedia says:

Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

I cannot see the reason very well. What would be a simple explanation in easy English and some examples?

Best Answer:

The idea is very simple - the query and the data are sent to the SQL server separately.
That's all.

The root of the SQL injection problem is mixing of the code and the data.

In fact, our SQL query is a full legitimate program. And we are creating this program dynamically by adding some data on the fly. Thus, this data may interfere with program code and even alter it, as every injection example shows it:

$expected_data = 1;
$query         = "SELECT * FROM users where id=$expected_data";

will produce a regular query

SELECT * FROM users where id=1

while this code

$spoiled_data = "1; DROP TABLE users;"
$query        = "SELECT * FROM users where id=$spoiled_data";

will produce malicious sequence

SELECT * FROM users where id=1; DROP TABLE users;

It works because we are adding data directly to the program body and it become a part of the program.

So, the data may alter the program and depending on the data passed, we will have either regular output or table users deleted.

While in case of prepared statements we don't alter our program, it remains intact
That's the point.

We are sending program to the server first

$db->prepare("SELECT * FROM users where id=?");

where the data is substituted by some variable called "placeholder".

Note that the very same query being sent to the server, without any data in it! And then we're sending the data with the second request, totally separated from the query itself:


so, it can't alter our program and do any harm.
Quite simple - isn't it?

However, it worth to be noted that not every time you're using placeholder, it is processed as a prepared statement.

Placeholder is a general idea for substituting actual data with a variable for the future processing, while prepared statement is the only subset of them.

So, sometimes we actually have to compose query along with data, but if every bit of data is properly formatted according to it's type - nothing wrong could be happen.

So, for example, by default PDO do not use prepared statements but rather compose the regular query. But because it substitutes every placeholder with properly formatted data, nothing bad could happen.

But it you want to tell PDO to do it straight way, to use real prepared statements, you have to set this setting:

$dbh->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

Please also note that formatting is not to be confused with escaping. Formatting involves a lot more actions than silly escaping. So, do not try manual escaping at home, at least until you get to know every formatting rule for the every distinct query part.

The only thing I have to add, always omitted in the every manual:

Prepared statements can protect only data, but can't defend the program itself.
So, once we have to add, say, a dynamical identifier - a field name, for example, prepared statements can't help us. I've explained the matter recently, so I won't repeat myself.

Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs