来自MySQL的JSON安全值

Question

I'm using MySQL's GROUP_CONCAT() to generate a JSON string. I then decode it in PHP with json_decode().

I'm already escaping double quotes in the values like this:

REPLACE(COALESCE(`column_name`, ''), '"', '\\\\"')

My problem is that there's some other character in a record that's invalid and causing a JSON_ERROR_SYNTAX (4) when trying to decode. Rather than track down the specific character that's causing this problem, I'd like to apply a more generic solution that makes the values "safe".

This blog solves the problem by using MySQL's HEX() function to encode the value, and then this PHP function on the decode end for each value:

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

I'm looking for a solution that requires less work on the decode end. Ideally doing all the work in MySQL.

---

After three downvotes and close vote, I'm not sure how to better structure my question. I simply want to escape values in MySQL so that they'll be "JSON safe".

Answer 1

Update: I've switch to MariaDB, and am using the COLUMN_JSON function, which takes care of double quote escaping, but not control characters. Here's my new solution.

---

Old Solutiong

Keep the double quote escaping in the query:

REPLACE(COALESCE(`column_name`, ''), '"', '\\\\"')

Add this function for escaping the backslash and control characters, but leaving the already escaped double quotes intact:

/**
 * Makes sure the JSON values built by GROUP_CONCAT() in MySQL are safe for json_decode()
 * Assumes that double quotes are already escaped
 *
 * @param string $mysql_json
 * @return string
 */
function mysql_json_escape($mysql_json)
{
    $rtn = '';
    for ($i = 0; $i < strlen($mysql_json); ++$i) {
        $char = $mysql_json[$i];
        if (($char === '\\') && ($mysql_json[$i + 1] !== '"')) {
            // escape a back slash, but 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 it like this:

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