mysql_real_escape_string() to allow ' and "

Question

I'm making a new question seeing as the other question is inactive and it has changed very much. My db code is this:

$sql2="INSERT INTO $tbl_name(s_id, s_name, s_email, s_content)VALUES('$id', '$s_name', '$s_email', '$s_content')";
$result2=mysql_query($sql2);

if($result2){
echo "Successful<BR>";

}
else {
echo "ERROR";
}

I can input letters numbers, but not ' or " - how can I use mysql_real_escape_string() to fix this? Thanks.

possible duplicate of [MySQL not letting ' and " in Longtext](http://stackoverflow.com/questions/6883351/mysql-not-letting-and-in-longtext) — Álvaro González, Jul 30 '11 at 16:01

score 5 · Answer 1 · edited Jul 30 '11 at 15:59

5

Just use mysql_real_escape_string() to escape your strings before injecting them into your SQL query.

For example :

$s_name_escaped = mysql_real_escape_string($s_name);
$s_email_escaped = mysql_real_escape_string($s_email);
$s_content_escaped = mysql_real_escape_string($s_content);

$sql2="INSERT INTO $tbl_name(s_id, s_name, s_email, s_content)
       VALUES('$id', '$s_name_escaped', '$s_email_escaped', '$s_content_escaped')";

Or, maybe even better : stop using the old mysql_* functions, and use either mysqli_* or PDO, with Prepared statements.

edited Jul 30 '11 at 15:59

Tomalak

332,285
67
532
628

answered Jul 30 '11 at 15:57

Pascal MARTIN

395,085
80
655
663

1

+1 - @fealiuex: an example of how to use prepared statement is here: http://stackoverflow.com/questions/2893025/quick-mysqli-security-question - there are [many others on the site](http://www.google.com/search?q=mysqli+prepared+site:stackoverflow.com) – Tomalak Jul 30 '11 at 16:02
It's going to be deprecared *(if it is one day)* because it doesn't support "new" *(not new anymore actually)* features of MySQL, which where introduced with MySQL 4.1, like prepared statements -- "when" is another question, and I'm not sure a decision has been taken already ; not for PHP 5.4, at least *(which is only in alpha-stage)* – Pascal MARTIN Jul 30 '11 at 16:26

score 0 · Answer 2 · answered Jul 30 '11 at 16:00

mysql_real_escape_string() doesn't prevent you from using characters like ' or " that could possibly facilitate a SQL Injection. It simply escapes these characters so MySQL interprets them as their literal value and not as a command in the query. So you answer is, just use it. You don't have to do anything else.

score 0 · Accepted Answer · answered Jul 30 '11 at 16:00

You should escape your string variable inside or outside your query with mysql_real_escape_string:

$name    = mysql_real_escape_string($s_name);
$email   = mysql_real_escape_string($s_email);
$content = mysql_real_escape_string($s_content);

$sql2 = "INSERT INTO $tbl_name(`s_id`, `s_name`, `s_email`, `s_content`) \n";
$sql2.= "VALUES('$id', '$name', '$email', '$content')";

mysql_real_escape_string() to allow ' and "

3 Answers3