CODEWITHSUNDEEP
phpmysqlsql-injection
Sandokan
Sandokan

Reputation: 849

Why do prepared statements protect against injections?

I've come os far as to gaining basic understanding of prepared statements and I get that they prevent SQL-injection attacks. But I have yet to understand WHY they protect against said attacks. I know there are similar questions asked but I didn't find the answers entirely satisfying.

Example - very unsafe code
So here we have the most basic way to communicate with our database:

$query = "SELECT * FROM users where id=$username";

Without any protection, a user can input malicious code and thus "trick" the database engine to execute a devastating query:

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

What I don't understand is how a prepared statement manages to "filter out" such data. What is the mechanic behind it that does NOT lure the database to generate such a SQL-query as shown above? Is just as simple as escaping certain characters, like semicolon in the example above, or is it more complicated?

If I was to do the exact injection attack as in the example, but running it through a prepared statement, what kind of command string would reach the database engine?

Upvotes: 0

Views: 168

Answers (3)

Eugen Rieck
Eugen Rieck

Reputation: 65274

Basically, if you use standard untyped parameter binding, you will get 

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

Which will error out on the Database, but will do no harm.

Please understand, that this is not the sam thing as running

SELECT * FROM users where id='$username'

with a suitably escaped $username - it happens on a lower layer of your DB access stack.

Upvotes: 0

John V.
John V.

Reputation: 4670

Prepared statements don't just add in the text, they send it as data, and let the database process it separately. Because in reality the database doesn't actually use the SQL statements, it uses "compiled" versions of them.

Not quite sure I was clear, but it lies in how the query is sent to the database.

Upvotes: 1

Ted Hopp
Ted Hopp

Reputation: 234795

Prepared statements are usually built to use parameter binding. It's really the parameter binding that insulates against these kinds of attacks. You can use parameter binding without using prepared statements.

The second level of protection that prepared statements offer is that each is a single statement (so the use of ; to create two statements out of one won't work).

As a general rule, in order to be safe from injection attacks, the prepared statement must be prepared from data that is not derived from any external input.

Upvotes: 0

Related Questions