Android with php: Saving utf-8 string to MySQL

Question

I know I am asking a question that has been asked many times before, but I feel I need clarification on my problem.

I have an Android app that sends a JSON encoded string to a PHP script. The following code then saves the data as a complete JSON string. (There are also other functions to properly save the data to multiple fields, not just as one JSON string)

$json_complete = $_POST['questionnaire'];
$q = json_decode($json_complete, true);

//save complete json string to database
$query_upload = "INSERT INTO questions_json (q_id, json) VALUES('".mysql_real_escape_string($q[qID])."', '$json_complete') ON DUPLICATE KEY UPDATE json = VALUES(json)";
$result = mysql_query($query_upload) or errorReport("Error in query: $query_upload. ".mysql_error());
if (!$result)
    errorReport($result);

The problem is that some of the text included in the JSON string is taken from strings.xml in Android that is encoded in utf-8. As an example in the strings.xml file <item>Can\'t get there</item> is saved as "Canu0027t get there" in MySQL. Also "Wholesale & Retail" is saved as "Wholesale u0026 Retail".

I also had json_decode() in php fail on me because of non utf-8 encoded characters in the JSON string. Got the following error: Malformed UTF-8 characters, possibly incorrectly encoded.

How can I handle all these scenarios? Any help please.

Answer 1

You also have to mysql_real_escape_string() your $json_complete in your query, otherwise you'd loose the original /uXXXX encodings.

$json_complete = $_POST['questionnaire'];
$q = json_decode($json_complete, true);

//save complete json string to database
$query_upload = "INSERT INTO questions_json (q_id, json) VALUES('".mysql_real_escape_string($q[qID])."', '".mysql_real_escape_string($json_complete)."') ON DUPLICATE KEY UPDATE json = VALUES(json)";
$result = mysql_query($query_upload) or errorReport("Error in query: $query_upload. ".mysql_error());
if (!$result)
    errorReport($result);

Answer 2

In general, all strings nowadays should be UTF-8 because it allows for easy internationalization. You should set up your MySQL db such that string fields are stored as UTF-8 (utf8_general_ci) and you won't have problems with the conversion.

If you insist on another encoding in your db you may want to check out mb_check_encoding($string,'UTF-8')and mb_convert_encoding($string, 'ISO-8859-1', 'UTF-8').

Answer 3

Did you set the encoding for the post?

    List<NameValuePair> params = ...;
    HttpPost httppost = new HttpPost(hostname);
    httppost.setEntity(new UrlEncodedFormEntity(params, "UTF-8"));