|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #42569 json_encode does not escape single quotes
Submitted: 2007-09-05 18:22 UTC Modified: 2007-09-06 11:00 UTC
From: prikid at gmail dot com Assigned:
Status: Not a bug Package: JSON related
PHP Version: 5.2.4 OS: FreeBSD 6
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
Block user comment
Status: Assign to:
Bug Type:
From: prikid at gmail dot com
New email:
PHP Version: OS:


 [2007-09-05 18:22 UTC] prikid at gmail dot com
When using json_encode() and trying to insert into mysql table field with single quotes - single quote is not escaped and mysql produces error.

Reproduce code:
$name = "Mike O'Brien";
$email = "";
$settings = array("name"=> $name,"email" => $email);
$json = json_encode($settings);
$sql = "INSERT INTO `users` VALUES('".$json."')";
$result = mysql_query($sql) or die (mysql_error());

Expected result:
Successfull mysql insertion with escaped single quote.

Actual result:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Brien


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2007-09-05 18:41 UTC] bas at tobin dot nl
that's correct... JSON is just a way to serialize data to a string.

It's not the purpose of JSON to serialize data to be directly included in a statement for a specific database. 

The way MySQL escape strings is not a "universal" method as for instance MS SQL does it another way. 

If you wish to use JSON to store data in MySQL you should escape the JSON string with the specific MySQL function to escape strings, see the 
mysql_escape_string() and mysql_real_escape_string() functions
 [2007-09-05 19:06 UTC] prikid at gmail dot com
$name = "Mike O'Brien";
$name = mysql_escape_string($name);
$email = "";
$settings = array("name" => $name,"email" => $email);
$json = json_encode($settings);


output is:
string(49) "{"name":"Mike O\\'Brien","email":""}"

Double quoted and still generates error when trying to insert into mysql db.
 [2007-09-06 11:00 UTC]
First you json_encode() then you do the mysql_escape_string()

PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Thu Oct 22 13:01:24 2020 UTC