Addslashes, mysql_real_escape always adding two slashes?

Question

I'm having an issue with inserting JSON into a database, my intention is to take the variables, json_encode them, remove slashes (from magic_quotes), and then addslashes back in to escape the quotes in {"key":"value"}

Unfortunately, strip_slashes on the encoded string isn't doing anything, and outputs this

{"content":"<p>This string has it\'s downsides</p>","date":1271352514}

I have then tried addslashes, and mysql_real_escape_string, both output

"{\\"content\\":\\"<p>This string has it\\\'s downsides</p>\\",\\"date\\":1271352514}"

I can't work out why it's adding in two slashes? And I'm tearing my hair out over this, everytime I try to stripslashes it leaves one in, and adding slashes adds two. Any help would be hugely appreciated!

Answer 1

If you already have a JSON string like this (by the way: In JSON the / needs to be escaped too):

{"content":"<p>This string has it\'s downsides<\/p>","date":1271352514}

Then you just need to apply mysql_real_escape_string on it to escape it so that it can be used to insert it into a MySQL string declaration:

$query = "INSERT INTO … SET json='".mysql_real_escape_string($json).'"';

And if you have Magic Quotes enabled, you should disable or remove them before that step so that your $json string is really just valid JSON.

Answer 2

First, you should really consider turning magic_quotes off... To quote the manual:

Warning

This feature has been DEPRECATED as of PHP 5.3.0. Relying on this feature is highly discouraged.

---

That being said, use json_encode() to build your JSON array (instead of building your own), and finish off with a single call to mysql_real_escape_string() while querying as such:

$data = array();
$data['content'] = "<p>This string has it's downsides</p>";
$data['date'] = 1271352514;

$json = json_encode($data);

mysql_query("INSERT INTO data
                VALUES ('" . mysql_real_escape_string($json) . "');");

---

Also, the PHP group recommends you use mysqli instead of mysql. Its Object Oriented API and support for parametrized queries greatly improve both development speed, code maintenance and security.

Here is the above code written using mysqli:

$data = array();
$data['content'] = "<p>This string has it's downsides</p>";
$data['date'] = 1271352514;

$json = json_encode($data);

$sqlInsert = $mysqli->prepare("INSERT INTO data VALUES (?);")
$sqlInsert->bind_param("s", $json);
$sqlInsert->execute();