use mysql_real_escape_string() but allow names with apostrophes

Question

I use mysql_real_escape_string() to validate all user inputs before I insert them in a sql database. One of the fields is name, and we've had issues with users with names like O'Reilly. Is there any way to use mysql_real_escape_string() to block injections but allow these names in the db?

Answer 1

The problem is most likely that the apostrophes get quoted twice: first by the evil and deprecated in 5.3 magic quotes and then by mysql_real_escape_string().

What you can do is either disable magic quotes or run stripslashes() on your input values before feeding them to mysql_real_escape_string()

Brief explanation of the problem:

• the user enters O'Reilly
• magic quotes automatically turn it into O\'Reilly
• the script feeds the string through mysql_real_escape_string() which escapes both the backslash and the apostrophe (again) yielding O\\\'Reilly
• the query is executed, the quoting is processed and the database understands that you want a backslash and an apostrophe since they where both escaped, and records O\'Reilly

Answer 2

As already mentionned : mysql_real_escape_string is not meant for input validation. If you want to validate inputs, use your own functions or the filter functions from php.

If you have too many slashes added automatically by php, disable magic quotes.

To prevent SQL injection, use parameterized queries with either PDO or mysqli.

Answer 3

+1 for using PDO. I've been using PDO in favour of a MySQL class acting as a database abstraction layer for a few months now and it's a breeze.

Traditionally, developers would use the stripslashes() function on data before applying a function like mysql_real_escape_string(). It's still a good idea to remove slashes from input data, but you can then either use the PDO method for escaping data (PDO::quote($data)) or binding the parameter.

Your query block would then look something like this:

$pdo = new PDO(DSN, DB_USER, DB_PASS);
$sql = "INSERT INTO table (field1, field2) VALUES (:value1, :value2)";
$smt = $pdo->prepare($sql);
$smt->bindParam(':value1', $value1, PDO::PARAM_STR);
$smt->bindParam(':value2', $value2, PDO::PARAM_STR);
$smt->execute();
$rows = $smt->rowCount(); // returns number of rows affected

I hope this helps somewhat. Take a look at http://php.net/manual/en/book.pdo.php for more information on PDO in PHP.