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

On any data that is not going to be put into an SQL query. If you need to escape output, use htmlspecialchars() (or similar). The same holds true for database input; escape it only beforeit is going in.

Answer 2

Judging from that particular comment on the site specifically for that code example, I think he's saying if magic_quotes is off and you're sure you'll only be using your code on a server with it off you can edit the code and remove the if(get_magic_quotes_gpc())... etc

In general though it's useless against data you include inside a query which you don't use quotes on - ie integers for ids, these need to be checked for type.

Answer 3

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 "

Answer 4

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 5

you should use mysql_real_escape_string when you want to escape a string to be included in a sql query that is going to a mysql database - anything outside of that scope would be a clear indication of 'when not to use' it :)

here's a verbose implementation:

function clean( $p )
{
  if( function_exists('mysql_real_escape_string') ) {
    if( function_exists('get_magic_quotes_gpc') ) {
      if( get_magic_quotes_gpc() ) {
        $p = stripslashes( $p );
      }
    }
    return mysql_real_escape_string( $p );
  } else {
    return $p;
  }
}