mysql_real_escape_string() but allow names with apostrophes

Question

This is probably a common thing but I have a question. Allow apostrophes while still maintaining the mysql_real_escape_string() tag.

I have this: $name = stripslashes(mysql_real_escape_string($_POST['stadium_name']));

and I test it on this:

$getInfoX = mysql_fetch_array(mysql_query("SELECT * FROM `stadiums` WHERE `stadium_name` = '$stadium_name'")) or die(mysql_error());

I could do an example inject like x'; DROP TABLE members; -- or a name with apostrophes like Stade de l'Aube... but the name with apostrophes get me an error like:

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 'Aube'' at line 1

What do I do?

use PDO or MySQLI, [read article for more info: Best way to prevent SQL injection](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection) — John Woo, Nov 09 '12 at 02:43
mysql_real_escape_string will deal with this for you. The error youre getting is from a mysql and not php yes? — case1352, Nov 09 '12 at 02:44
Why are you still stripping slashes after `mysql_real_escape_string` has escaped it for you? — Gapton, Nov 09 '12 at 02:51
Your problem is the `stripslashes()`. Remove that. (Dates back to magic_quotes workarounds. But here it's applied in the wrong order even.) — mario, Nov 09 '12 at 02:52

score 2 · Accepted Answer · answered Nov 09 '12 at 02:54

You chain the result of mysql_real_escape_string through stripslashes which basically removes everything mysql_real_escape_string added for safety reasons.

So if you have $stadium_name= "Fred's Stadium"; as input mysql_real_escape_string($stadium_name) returns "Fred\'s Stadium" which can be included into you query safely generating

"SELECT * FROM `stadiums` WHERE `stadium_name` = 'Fred\'s Stadium'"

as MySQL-query. Calling stripslashes on the mysql_real_escape_stringoutput removes the \ in front of the ' so you send the query

"SELECT * FROM `stadiums` WHERE `stadium_name` = 'Fred's Stadium'"

to MySQL thinks your string is 'Fred' followed by some garbage (which can turn out to be dangerous).

Solution is to use a separate variable to store the result of mysql_real_escape_string, as it is correct for usage in database queries but unsuitable to be displayed back to the user.

I hope this helps.

Regards

TC

So, store it using mysql_real_escape_string but when displayed ... use strip_slashes? — test, Nov 09 '12 at 10:02
Yep and use php.net/htmlentities before echoing it so you don't get HTML injected into you pages. — TheConstructor, Nov 09 '12 at 10:16

score 1 · Answer 2 · answered Nov 09 '12 at 02:53

Your problem is this:

$name = stripslashes(mysql_real_escape_string($_POST['stadium_name']));

stripslashes() undoes the escaping.

You've probably seen that function used as workaround for magic_quotes. If you were to apply it, then do so before the database escaping function.

mysql_real_escape_string() but allow names with apostrophes

2 Answers2