security - How can prepared statements protect from SQL injection attacks?

Question

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 an easy English and some examples?

Answer 1

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

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

In fact, our SQL query is a legitimate program. And we are creating such a program dynamically, adding some data on the fly. Thus, the data may interfere with the program code and even alter it, as every SQL injection example shows it (all examples in PHP/Mysql):

$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 a malicious sequence

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

It works because we are adding the data directly to the program body and it becomes a part of the program, so the data may alter the program, and depending on the data passed, we will either have a regular output or a 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 a program to the server first

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

where the data is substituted by some variable called a parameter or a placeholder.

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

$db->execute($data);

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

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

Prepared statements can protect only data literals, but cannot be used with any other query part.
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.