mysql_real_escape_string（）但允许带撇号的名称

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?

Answer 1

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