What is the best way to escape MySQL data to output to JSON?

I need to output data for javascript (array of array) from MySQL

I am using this code, which works fine except that REPLACE function

I need to modify all the text that goes into JS array's inverted comas ' -- ' as per JS format. And can be used for all the fields in 1 go.

I need a replace function that will escape all data, i.e. \ -> \\ ; ' -> \' ; newline -> \n

$sth=$dbh->prepare('开发者_开发技巧
    SELECT GROUP_CONCAT(
        "\n[\'",
        CONCAT_WS("\',\'", d6id, REPLACE(d6name,"\'","\\\\\'"), d6date),
        "\']"
    )
    FROM d6lastdate
');
$sth->execute();($json)=$sth->fetchrow_array();

Output

['0000000001','CA\'T','2011-09-26'],
['0000000002','CWE','2011-09-23'],
['0000000003','MAT','0000-00-00'],

The best way is probably to do this outside of MySQL.

$data = $sth->fetchrow_arrayref();
$json = encode_json($data);

encode_json is provided by JSON::XS.

$sth=$dbh->prepare('
    SELECT d6id, d6name,d6date
    FROM d6lastdate
');
$sth->execute();
$json = encode_json($sth->fetchrow_arrayref());

encode_json is provided by JSON::XS, among others.

Using PHP is not always an option (what if you want a stored procedure to insert a row in a table, where one or more fields are json formatted strings?).

Your initial approach is almost good... if you except that the result is not valid JSON:

• You need to use double quotes (") instead of simple quotes (') as string delimiter
• Don't forget to escape the backslash (\) too
• If your strings include special characters, be sure that they are encoded in UTF-8
• The last comma (,) will cause issue and need to be removed

Eric

Edit: You also need to escape carriage returns, tabs and a few other characters. Have a look at string definition on this page: http://www.json.org . You may validate your resulting json string with http://jsonlint.com .

use Data::Dumper;
$Data::Dumper::Terse = 1;
$Data::Dumper::Indent = 0;

$sth=$dbh->prepare('
    SELECT d6tag, d6id, d6name, d6cat, d6date
    FROM d6lastdate
    ORDER BY d6date
');$sth->execute();

$json=Dumper($sth->fetchall_arrayref);