javascript - What is the best way to escape MySQL data to output to JSON? - Stack Overflow

admin管理员组

文章数量:1421263

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 as ' -- ' 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'],

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 as ' -- ' 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'],

• javascript
• mysql
• json
• perl
• escaping

Share Improve this question Follow edited Sep 2, 2011 at 22:14 Philipp Reichart 21k66 gold badges6060 silver badges6565 bronze badges asked Sep 2, 2011 at 22:08 Atul GuptaAtul Gupta 75511 gold badge77 silver badges2020 bronze badges 3

• 2 Wouldn't it be better to just json_encode the bare result of the query? – Ramon Poca Commented Sep 2, 2011 at 22:11
• 2 Wow, are you really trying to json_encode from SQL? – Ruan Mendes Commented Sep 2, 2011 at 22:20
• I was just trying to see if we can generate JSON from SQL directly, as half of my SELECT statements needs to output JSON.. I didn't try DBIx::JSON though. – Atul Gupta Commented Sep 3, 2011 at 20:28

Add a ment  | 

4 Answers 4

Sorted by: Reset to default 5

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 ma (,) 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 . You may validate your resulting json string with http://jsonlint. .

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);

本文标签： javascriptWhat is the best way to escape MySQL data to output to JSONStack Overflow