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?

From stackoverflow

• 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!

  paperless : But how does the database know the difference between what is dangerous and between what i really want it to do?

  alex : Well it knows what characters are evil, so it should prepend the escape character '\' in MySQL before the evil character. This still does the query as-is, but it won't honour any special characters that appear in a binding.

  alex : Also to note, you specify the 'bindings' separately (not concatenated in the query), and simply insert a placeholders in the query to where they should appear. The database (I think, maybe PDO does it?) then escapes characters inside the bindings.

  paperless : I understood now. Thanks a lot for your help :P

  alex : No worries, glad to help :)

• Very best case, it might not be, but it's at least equally safe; and why take the chance?

• 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.

  paperless : That's a good example, thank you.

• Because with prepared statements, you can't forget to escape the content. So there are no way to introduce insecurity.

  mysql_real_escape_string is as safe as prepared statements IF you remember to use mysql_real_escape_string each time you call mysql_query, but it's easy to forget.

• The function is not safe because of this exploit http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string. That is why prepared statements are preferred and it gives performance improvement as well.

• 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.

  paperless : Thanks for explaining :)