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

Question

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?

Chesty

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

Penance

So it is faster but security is the same? I mean you can't be safer than completely safe. I would also like some proof of the speed theories. – Apsis

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

Laskowski

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

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. – Laskowski

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. – Laskowski

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

Donte

Answer 4

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.

Malm

Answer 5

Prepared statements solve a fundamental problem of application security that mere data sanitation does not: They result in a complete separation of the data and the instructions. When the two get confused, insecurity is the result. This is true for SQL injection as well as buffer overflows.

(There are other ways to be insecure.)

Babettebabeuf

Answer 6

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.

Venom

I believe if I am reading that linked blog correctly that he has issue with addslashes being insecure, not mysql_real_escape_string. He says at the end that the latter is a valid option, if people remember to do it, but people tend to forget to call it. Using prepared statements helps with that memory issue. – Foreordain

This is an interesting link too, that points to a potential issue with mysql_real_escape_string, albeit obscure: ilia.ws/archives/… – Foreordain

Answer 7

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

Myo