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?

Zarla

Use bound variables, and then you don't have to escape anything, nor do you have to worry about Little Bobby Tables. – Negrophobe

bobby-tables.com/php.html – Negrophobe

I would use prepared statements with PDO or at least with MySQLi (rather than MySQL). That way, no escaping/"slashing" is needed. – Reimer

@Reimer you've just got binding instead of escaping. not a big difference. – Boride

@Col. Shrapnel, the difference in usage is that you can't forget to bind. – Reimer

@Reimer makes sense, but your first argument, "no escaping is needed", doesn't. In fact you just changed one sanitization method to another. – Boride

@binary: Sure you can. You can do "INSERT INTO ... (?, '$foo', ?)" in PDO just as easy as with MySQL. Or the more famous: ORDER BY $foo ASC. Either way you need to be deligent. Sure, PDO is more forgiving (I'm not saying it's not worth it), but it's not a silver-bullet fix-all. You still need to use it properly... – Aniela

@Col. Shrapnel, I see prepared statements as something more than just a sanitization method. – Reimer

@Reimer true again. However, my own home-brewed prepared statements based on mysql driver and sprintf suits me well. So, it's not a wand but a wizard. Prepared statements is not a silver bulled itself. But knowledge is. – Boride

@ircmaxell, sure, PDO must be used properly, just like everything else. About doing "INSERT INTO ... (?, '$foo', ?)" - I don't think it can be done with MySQL extension (mysql_query() etc) - that's why I mentioned MySQLi (mysqli_query() etc). – Reimer

@Col. Shrapnel, it just can't be done. Just like a car (let's say, some standard sedan) can't fly - it's not designed to do that. If you write a wrapper of MySQL extension to simulate prepared statements, then it's not MySQL extension that handles all those ? - it's your code that does it. – Reimer

@Reimer just try it, dude. You'll be surprized. – Boride

@Reimer and consider such a query like SELECT * FROM table order by $field - how your PDO or MySQLi would help you? – Boride

@Col. Shrapnel, what should I try? How can I try using prepared statements with extension that's not designed for it (i.e., does not have any function for that)? And about that query - such queries are very rarely used (mostly for tiny enum-type stuff that's stored in basic 'id, name' table). In real world applications, there's at least limit clause, usually also where clauses. Anyway, even in such case PDO would win - for PDO it does not matter if you use such query with MySQL, MSSQL, PostgreSQL, Oracle or some other DBMS ;) – Reimer

@Reimer so, you think that "INSERT INTO ... (?, '$foo', ?)" would not work, but refuse to try it? And you still thinks that user defined ordering is rarely used and such an issue shouldn't be mentioned at all. Okay, no more questions. Good luck – Boride

@Col. Shrapnel, all I expect to see when using ? with mysql_query() is standard error message about syntax error. That's also what I got - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1. Happy now? Or you expected something magic here? And I have never said that user defined ordering is rarely used. What I say is that queries without limit and where clauses are not common. – Reimer

@Reimer I see now. I just didn't get that you're talking of mysql, because i see no point in your question in this case. – Boride

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.

Enlace

+1 for explaining syntax matters, without scaring tales and stupid comics about SQL injections – Boride

Answer 2

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! ;)

Schiedam

It doesn't really matter. The only real advantage prepared statements over escaping is a funny one: it never asks a programmer if he thinks to sanitize a variable, or not. So, it's more like a crutch. – Boride

Yes, it's a crutch. So is the fact I don't have to think about buffer overflows, or the HTTP protocol, the majority of the time I am writing PHP. Freeing my mind from such banal matters lets me focus on the real problem. "any processes that are quite mechanical may be turned over to the machine itself"! This also makes you far, far less likely to slip up and miss escaping one variable... and one is all it takes. – Schiedam

Answer 3

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()).

Bonhomie

what do you need else?unless do that and validate input? – Bonhomie

You also need to make sure the fields are properly surrounded by quotes, at the very least. – Silviasilviculture

To make it correct statement, mysql_real_escape_string() is enough for quote-delimited strings. – Boride

And the OP haven't asked about SQL inj. If you raised that topic, sanitizing identifiers should be mentioned too. – Boride