MySQL_real_escape_string not adding slashes?

Question

So I do this:

   <?php
    session_start();
    include("../loginconnect.php");
mysql_real_escape_string($_POST[int]);
    $int = nl2br($_POST[int]);
    $query = "UPDATE `DB`.`TABLE` SET `interests`='$int' WHERE `user`='$_SESSION[user]'";
    mysql_query($query) or die(mysql_error());
    mysql_close($con);
    ?>

And let's say that $_POST[int] is "Foo' bar." The single-quote remains unescaped AND I get a MySQL error when running the script, due to the quote. What's wrong?

I also tried using $_POST['int'] with quotes, but that fails too. — , Jul 08 '11 at 17:16

score 2 · Accepted Answer · edited Jul 08 '11 at 17:22

2

m_r_e_s() RETURNS the escaped value, it doesn't modify the original.

$int = mysql_real_escape_string($_POST['int']);

$query = "UPDATE ... interests = '$int' ...";

Note that I've added quotes around the int in the POST value. Without the quotes, PHP sees it as a constant value (e.g. define()). If it doesn't find a constant of that name, it politely assumes you meant it to be used a string and adjust accordingly, but issues a warning. If you had done

define('int', 'some totally wonky value');

previously, then you'd be accessing the wrong POST value, because PHP would see it as $_POST[some totally wonky value] instead.

edited Jul 08 '11 at 17:22

Álvaro González

142,137
41
261
360

answered Jul 08 '11 at 17:15

Marc B

356,200
43
426
500

But in the database, the data remains unescaped. Why? – Jul 08 '11 at 17:18
Because the escaping is removed as part of going into the database. Escaping is only useful for the actual query portion, when you're building the SQL statement. After that, MySQL knows EXACTLY where things are, and there's no way for a "naughty" value to leak out. In a vague way, escaping is like handcuffs on a prisoner while transferring between facilities. Once the prisoner's in the new jail, the handcuffs are removed. – Marc B Jul 08 '11 at 17:20
So the data is still safe, even though I can't see the slashes? – Jul 08 '11 at 17:21
@Marc, I've taken the freedom of adding the quotes around the argument, I've assumed you forgot them. – Álvaro González Jul 08 '11 at 17:22

score 2 · Answer 2 · answered Jul 08 '11 at 17:16

2

You're not using the results of mysql_real_escape_string in your query. Try doing this:

$int = nl2br(mysql_real_escape_string($_POST[int]););

answered Jul 08 '11 at 17:16

Paul Sonier

38,903
3
77
117

score 0 · Answer 3 · answered Jul 08 '11 at 19:04

You should be using prepared statements. It has a slight learning curve over mysql_* functions, but is well worth it in the long run.
You should quote your strings, like $_POST['int'] instead of $_POST[int].
At the top of your file put error_reporting(-1);

MySQL_real_escape_string not adding slashes?

3 Answers3

Linked