CODEWITHSUNDEEP
javascriptmysqljsonperlescaping
Atul Gupta
Atul Gupta

Reputation: 755

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

Upvotes: 0

Views: 2555

Answers (4)

Eric
Eric

Reputation: 11

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 .

Upvotes: 1

Atul Gupta
Atul Gupta

Reputation: 755

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

Upvotes: 0

Ramon Poca
Ramon Poca

Reputation: 1929

$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.

Upvotes: 2

Arnaud Le Blanc
Arnaud Le Blanc

Reputation: 99929

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.

Upvotes: 5

Related Questions