php json_encode utf8 char problem ( mysql ) [duplicate]

This question already has an answer here: json_encode problems with utf8 [closed] (1 answer) Closed 6 years ago.

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开发者_StackOverflowü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"]

---

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.

---

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 !

---

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!

---

Like user576875 says, you just need to correctly treat your string before inserting it into the database. mysql_real_escape_string() is one way to do that. Prepared statements are another way. This will also save you from the SQL injection security issue that you might be susceptible to if you write user input directly into SQL. Always use one of the above two methods.

Also, note that this has little to do with UTF8. JSON is ASCII safe, so as long as you use an ASCII like character set (utf8, iso-8859-1), the data will be inserted and stored correctly.

---

I would apply BASE64 encoding to the JSON string. This should work with nearly every php setting, database, database version and setting:

$values = array("Test" => 1, "the" => 2, "West" => 3);
$encoded = base64_encode(json_encode($values));
$decoded = json_decode(base64_decode($encoded), true);