php json_encode utf8 char problem ( mysql )

Question

I am writing to the database in the form of data from a form with jQuery json_encode.

However, data from the database will corrupt.

$db->query("SET NAMES utf8");

$kelime = array("Merhaba","Dünya");
$bilgi = json_encode($kelime);

$incelemeEkle = "
INSERT INTO incelemeRapor SET
bigData = '".$bilgi."'
";
$db->query($incelemeEkle);

Database Table Schema;

CREATE TABLE `incelemeRapor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bigData` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

MySQL Inserted Example Data;

["Merhaba","Du00fcnya"]

Solution 1

Always escape your data before puting it in a SQL query:

$incelemeEkle = "
INSERT INTO incelemeRapor SET
bigData = '".mysql_real_escape_string($bilgi)."'
";

(added mysql_real_escape_string() call)

json_encode() encodes non-ascii characters with the \u<code-point> notation; so json_encode(array("Merhaba","Dünya")); returns ["Merhaba","D\u00fcnya"].

Then this string is embeded in a SQL query:

INSERT INTO incelemeRapor SET
bigData = '["Merhaba","D\u00fcnya"]'

There is no special meaning for the escape sequence \u, so MySQL just removes the \; and this results in ["Merhaba","Du00fcnya"] being stored in database.

So if you escape the string, the query becomes:

$incelemeEkle = "
INSERT INTO incelemeRapor SET
bigData = '["Merhaba","D\\u00fcnya"]'
";

And ["Merhaba","D\u00fcnya"] is stored in the database.

Solution 2

I tried with mysql_real_escape_string() but not worked for me (result to empty field in database).

So I looked here : http://php.net/manual/fr/json.constants.php and the flag JSON_UNESCAPED_UNICODE worked for me fine :

$json_data = json_encode($data,JSON_UNESCAPED_UNICODE);

JSON_UNESCAPED_UNICODE is available only since PHP 5.4.0 !

Solution 3

So in addition to ensuring that your database is using utf8_unicode_ci, you also want to make sure PHP is using the proper encoding. Typically I run the following two commands at the top of any function which is going to potentially have foreign characters within them. Even better is to run it as one of the first commands when your app starts:

mb_language('uni');
mb_internal_encoding('UTF-8');

Those two lines have saved me a ton of headaches!

Comments

[bluesmoon]

you don't need to base64 encode JSON. It's ASCII safe. Only 7 bit characters.

[Joe Scylla]

You're right. But base64 encoding avoids any problems with backslashes (or other special chars)