PHP json_encode data with double quotes

Question

I'm using this simple code to transform database query results into JSON format:

$result = $mysqli->query("
    SELECT  
        date as a 
        , sum(sales) as b
        , product as c
    FROM  
        default_dataset
    GROUP BY
        date
        , product
    ORDER BY
        date        
");

$data = $result->fetch_all(MYSQLI_ASSOC);

echo stripslashes(json_encode($data));

The problem is that if there are double quotes in the data (e.g. in the product column) returned by this query. The json_encode function does not encode the data in a good JSON format.

Could someone help me how to escape the double quotes that are returned by the query? Thank you.

Answer 1

json_encode already takes care of this, you are breaking the result by calling stripslashes:

echo json_encode($data); //properly formed json

Answer 2

You will need htmlspecialchars instead of stripslashes with proper encoding (UTF-8, if your page uses UTF-8 charset) and ENT_QUOTES which will escape double quotes preventing data to break. See the code below:

echo htmlspecialchars(json_encode($data), ENT_QUOTES, 'UTF-8');

Answer 3

Example with a simple array with double quotes string value.

$yourArr = array(
    'title' => 'This is an example with "double quote" check it'
);

// add htmlspecialchars as UTF-8 after encoded
$encodeData = htmlspecialchars(json_encode($yourArr), ENT_QUOTES, 'UTF-8');

echo $encodeData;

Result:

{"title":"This is an example with \"double quote\" check it"}

According to PHP Manual:

That said, quotes " will produce invalid JSON, but this is only an issue if you're using json_encode() and just expect PHP to magically escape your quotes. You need to do the escaping yourself.