Escaping quotes in SQL

Question

According to php.net I should use mysql_real_escape_string() and turn off magic quotes, because it's deprecated.

So I turned it off and I used mysql_real_escape_string(), but is it enough to use it just like in the following code?

$value = "It's Time!";
$escaped_value = mysql_real_escape_string($value);

mysql_query("INSERT INTO table (column, column2) VALUES ('{$escaped_value}', "0")");

When I check the data in the database it looks the same way as in $value so "It's Time" and not "It\'s Time". Is this normal? Shouldn't this add a slash before the quotes?

Answer 1

That's the correct behaviour, and it works! Quotes are escaped in the query, not in the database. \' in the SQL query must be converted to ' inside the database.

You escape strings, so the SQL query doesn't get messed up and lets input quotes to the database without the SQL query interpreting them as control characters.

Your escaped query would be:

mysql_query("INSERT INTO table (column, column2) VALUES ('It\'s time', "0")");

Your database data should be "It's time".

If it weren't escaped it would be:

mysql_query("INSERT INTO table (column, column2) VALUES ('It's time', "0")");

It will spit out an error regarding "s time'" (because it will only interpret until the next quote character, and will only see 'It'.) And "It's time!" is just an inoffensive string, but lots of attacks could derive from that if you don't escape your characters.

Answer 2

mysql_real_escape_string() is usually enough for preventing SQL injection.

You should also filter numeric values you are getting from the user (by using intval()).

Answer 3

Short answer

This is correct.

Long answer

This is correct, but your question indicates a lack of depth of understanding of what's going on here. Here's your query:

INSERT INTO table (column, column2) VALUES ('{$escaped_value}', "0")

Let's see what happens if you don't escape, and the user enters the following data:

Eve'

Remember that you are just passing a string to MySQL, the insertion is done by PHP's string processing. This means that in this instance, the query sent to mysql_query is:

INSERT INTO table (column, column2) VALUES ('Eve'', "0")

Which is a syntax error and would make the page error out. Now, when a user like me (i.e. a bastard intent on making your day a misery :D) spots one of these, we know it's time to play. What happens if the following data is supplied by a user?

Eve', "0"); DROP TABLE table--

Our query is expanded to:

INSERT INTO table (column, column2) VALUES ('Eve', "0"); DROP TABLE table--', "0")

This isn't a syntax error, but it is problematic... we are now executing a query we never intended to! (In case you don't recognise it, "--" denotes a comment in SQL, i.e. "ignore everything after this point").

Now this can't happen in this exact instance (mysql_query doesn't support multiple queries), but it's the sort of attack you are trying to prevent - a class of attacks known as SQL injection. Let's see what happens when you use mysql_real_escape_string!

The inputted data becomes:

Eve\', \"0\"); DROP TABLE table--

Which means our query string looks like:

INSERT INTO table (column, column2) VALUES ('Eve\', \"0\"); DROP TABLE table--}', "0")

Which is fine! The data the user entered will be stored in the database, as they entered it. Not without quotes, and not with extra backslashes, but as they entered it. This is important. If you store the data any other way, you will run into issues later, with de-escaping or double-escaping. Additionally, adding extra slashes or anything like that often ends up exposed to the users in one way or another, and it's a giant warning sign that things may not be being escaped properly. You want to avoid doing the wrong thing, and you especially want to avoid doing the wrong thing and advertising about it, which brings me to the next section:

Alternatives to escaping your data

1. Magic quotes. As you noted, deprecated (and with good reason), avoid.

2. Don't escape your data. I would advise against this option.

3. Strip bad characters from input. Annoying in nearly all situations, you should store what users enter, not what's technically easy to store.

4. Disallow bad characters from input. Sometimes acceptable (credit card number fields don't need to handle quotes), sometimes annoying, sometimes a massive warning sign (when on a password field for example)

5. Prepared statements. I said that the "filling in" of variables in the string was done by PHP and MySQL was just getting a query string, hence the need for escaping. Prepared statements offloads this work by being a little more intelligent, and using a prepared statement would look something like this (warning: pseudocode):

   $statement = $db->prepare('INSERT INTO table (column, column2) VALUES ("%1", "%2")'); $result = $statement->execute($value1, $value2);

There's a nice Stack Overflow question on SQL escaping methods and the answers there go into more depth, so you may want to read that.

Personally, this is the option I like. You can't forget to escape a variable and insert it into the database this way - either values are properly marshalled, or they don't get anywhere near the database, there is no halfway option. That is, assuming you enforce that all queries go through prepared statements, and no string concatenation like

$db->prepare('INSERT INTO table VALUES('.$value.')'))

Is done. In my opinion, this is easier than keeping track of which variables are sanitized and which are not. Some people like to escape strings as soon as they come from the user, but that's very awkward if they could go anywhere other than the database - back into the HTML, into memcache, etc. If you are going to do it yourself, I might suggest some Hungarian notation, for example:

$uValue = $_POST['value'];
$sValue = escape($uValue);
$db->query('INSERT INTO table VALUES(' . $sValue .')');

I first saw this idea in an excellent article Making Wrong Code Look Wrong by Joel Spolsky.

Conclusion

Hopefully you feel better prepared to make injection-proof sites now. Good luck with whichever method you chose, and have fun making sure you always escape all user input before it hits the database! ;)