When should I NOT use mysql_real_escape_string

Question

I saw this comment.... http://www.php.net/manual/en/function.mysql-real-escape-string.php#93005

And began to wonder why this would be a bad idea.

Answer 1

It's a bad idea for a couple reasons:

• First, it assumes that your inputs will always be going into the database and into the database alone. What if something is going to be used in HTML output? Or in an email? Or written to a file? Or lots of other things.. your filtering should always be context-sensitive.

• More importantly, it encourages sloppy use of GET, POST, etc because there's no indication that they've been filtered. If someone sees you use

  echo $_POST['name'];

  on a page, how would they know it's been filtered? Or even worse... are you sure it has been? What about that other app? You know, the one you were just handed? What would new developers do? Would they even know that filtering is important?

Answer 2

Ideally you should never have to escape anything prior to using it in a query via use of PDO prepared statements. The underlying libraries will take care of escaping for you.

In practice, if you can't/won't use prepared statements, the escaping should be done only immediately prior to building the query string. Don't blindly go and remap the contents of the various superglobals (GET, POST, REQUEST, COOKIES) on the assumption that everything will be going into a DB. Think of the case where you have to validate the form data first, and some field(s) isn't filled in correctly. Now you have to unescape everything from "database mode", and re-escape into "html mode" to reinsert the good data back into the form again.

The same goes for htmlentities/htmlspecialchars. Don't do until you know you're outputting into HTML/XML. Once you go applying escaping/encoding/quoting everywhere, you'll run the risk of double-encoding stuff and end up with useless constructs like "