I have a dilemma how should I mysql_real_escape_string() my variables without inserting them into the database \n, \r, \x00 when someone uses " ' or <br> on my comment field, I tried with preg_replace instead of mysql_real_escape_string, but seems I don't know exactly how to allow all the chars and signs I want.
-
1Huh? What are you trying to do?SLaks– SLaks2013-01-11 17:34:46 +00:00Commented Jan 11, 2013 at 17:34
Add a comment
|
2 Answers
mysql_real_escape_string only escapes values so that your queries don't break, it also protects against SQL injection if used correctly.
If you don't want certain characters you will need to use additional functions to strip them before you apply mysql_real_escape_string.
[insert obligatory "use prepared statements" comment]
Ex:
$string = "My name is
John";
$filtered_string = str_replace("\n", " ", $string); // filter
$escaped = mysql_real_escape_string($filtered_string); // sql escape
mysql_query("INSERT INTO `messages` SET `message` = '" . $escaped . "'");
1 Comment
AKS
"[insert obligatory "use prepared statements" comment]"
You should be able to use str_replace to help with this:
mysql_real_escape_string(str_replace(array("\n", "\r\n", "\x00", '"', '\''), '', $input));
Having said that, it is a good idea to switch to mysqli or PDO for database read / write. Both of these allow prepared statements, which reduce the risk of SQL injections.
Here's an example of PDO:
$stmt = $PDOConnection->prepare('INSERT INTO example_table (input_field) VALUES (:input_field)');
$stmt->bindParam(':input_field', $input);
$stmt->execute();
