Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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();
share|improve this answer

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 . "'");
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com?utm_source=stackoverflow.com&amp;utm_medium=site-ui&amp;utm_campaign=footerlink\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2014 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a>                   with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2014.11.28.2054\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </no"], "copyright__copyright__copyright": ["ory \"use prepared statements\" comment]\n\nEx:\n\n$string = \"My name is\nJohn\";\n\n$filtered_string = str_replace(\"\\n\", \" \", $string); // filter\n$escaped = mysql_real_escape_string($filtered_string); // sql escape\nmysql_query(\"INSERT INTO `messages` SET `message` = '\" . $escaped . \"'\");\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}