php - JSON Safe Values from MySQL -

i'm using mysql's group_concat() generate json string. decode in php json_decode().

i'm escaping double quotes in values this:

replace(coalesce(`column_name`, ''), '"', '\\\\"') 

my problem there's other character in record that's invalid , causing json_error_syntax (4) when trying decode. rather track down specific character that's causing problem, i'd apply more generic solution makes values "safe".

this blog solves problem using mysql's hex() function encode value, , php function on decode end each value:

function hextostr($hex) {     $string = '';     ($chariter = 0; $chariter < strlen($hex) - 1; $chariter += 2)     {         $string .= chr(hexdec($hex[$chariter] . $hex[$chariter + 1]));     }     return $string; } 

i'm looking solution requires less work on decode end. ideally doing work in mysql.

---

after 3 downvotes , close vote, i'm not sure how better structure question. want escape values in mysql they'll "json safe".

update: i've switch mariadb, , using column_json function, takes care of double quote escaping, not control characters. here's new solution.

---

old solutiong

keep double quote escaping in query:

replace(coalesce(`column_name`, ''), '"', '\\\\"') 

add function escaping backslash , control characters, leaving escaped double quotes intact:

/**  * makes sure json values built group_concat() in mysql safe json_decode()  * assumes double quotes escaped  *  * @param string $mysql_json  * @return string  */ function mysql_json_escape($mysql_json) {     $rtn = '';     ($i = 0; $i < strlen($mysql_json); ++$i) {         $char = $mysql_json[$i];         if (($char === '\\') && ($mysql_json[$i + 1] !== '"')) {             // escape slash, leave escaped double quotes intact             $rtn .= '\\\\';         } elseif (($ord = ord($char)) && ($ord < 32)) {             // hex encode control characters (below ascii 32)             $rtn .= '\\u' . str_pad(dechex($ord), 4, '0', str_pad_left);         } else {             $rtn .= $char;         }     }     return $rtn; } 

call this:

$data = json_decode(mysql_json_escape($mysql_json));