Why is using a mysql prepared statement more secure than using the common escape functions?

There's a comment in another question that says the following:

"When it comes to database queries, always try and use prepared parameterised queries. The mysqli and PDO libraries support this. This is infinitely safer than using escaping functions such as mysql_real_escape_string."

Source

So, what i want to ask is: Why are prepared parameterized queries more secure?

Solution 1:

An important point that I think people here are missing is that with a database that supports parameterized queries, there is no 'escaping' to worry about. The database engine doesn't combine the bound variables into the SQL statement and then parse the whole thing; The bound variables are kept separate and never parsed as a generic SQL statement.

That's where the security and speed comes from. The database engine knows the placeholder contains data only, so it is never parsed as a full SQL statement. The speedup comes when you prepare a statement once and then execute it many times; the canonical example being inserting multiple records into the same table. In this case, the database engine needs to parse, optimize, etc. only once.

Now, one gotcha is with database abstraction libraries. They sometimes fake it by just inserting the bound variables into the SQL statement with the proper escaping. Still, that is better than doing it yourself.

Solution 2:

For one, you're leaving the escaping of dangerous characters to the database, which is a lot safer than you, the human.

... it won't forget to escape, or miss out on any special characters which could be used to inject some malicious SQL. Not to mention, you could possibly get a performance improvement to boot!

Solution 3:

I am not extremely versed in security but here is an explanation that I hope will help you:

Let's say you have a statement like:

select [integer] from mydb

Pretend when you prepare it, the statement is compiled down to bytes in our imaginary sql implementation.

           01                  00 00                  23
Opcode for select          Prepared bytes      number of "mydb"
                          for your integer

Now when you execute, you will insert the number into the space reserved for your prepared statement.

Compare it to if you just use escape, you could possibly insert as much gibberish in there and maybe cause the memory to overflow, or some bizzare sql command that they forgot to escape.