CODEWITHSUNDEEP
phpmysqljson
Tiger
Tiger

Reputation: 569

mysql_real_escape_string and json

I have the following json string inside an associative array:

[watch] => '{"tv":"Dexter's Labratory, \u05d1\u05d9\u05d3\u05d5\u05e8  \u05d9\u05e9\u05e8\u05d0\u05dc\u05d9 HOT, South Park, \u05e2\u05e1\u05e4\u05d5\u05e8, Teenage Mutant Ninja Turtles, Simpsons, The Samurai Pizza Cats, Dr. House, Futurama, House","movies":"Mufasa, Simba, \u05d7\u05d9\u05d9\u05dd \u05d1\u05e1\u05e8\u05d8, \u05e1\u05e8\u05d8\u05d9\u05dd \u05d7\u05d3\u05e9\u05d9\u05dd \u05d1\u05e7\u05d5\u05dc\u05e0\u05d5\u05e2, Future Shorts Israel, The Lion King"}'

Of course it needs to be escaped because it contains characters like ' inside (e.g Dexter's Labratory). The problem is that after using mysql_real_escape_string on it the output is:

[watch] => \'{\"tv\":\"Dexter\'s Labratory, \\u05d1\\u05d9\\u05d3\\u05d5\\u05e8  \\u05d9\\u05e9\\u05e8\\u05d0\\u05dc\\u05d9 HOT, South Park, \\u05e2\\u05e1\\u05e4\\u05d5\\u05e8, Teenage Mutant Ninja Turtles, Simpsons, The Samurai Pizza Cats, Dr. House, Futurama, House\",\"movies\":\"Mufasa, Simba, \\u05d7\\u05d9\\u05d9\\u05dd \\u05d1\\u

It escapes a shitload of stuff that should not be escaped, especially all the " characters (that I am pretty sure are part of the json encoding) making it not query-able. What's the right method to escape a json string? I guess I am missing something here.

Upvotes: 3

Views: 8808

Answers (3)

Lawrence DeSouza
Lawrence DeSouza

Reputation: 1027

It might be because of the Hebrew characters you have. It needs parsing. See this question:

JSON getting "name":"\u05d7\u05d1\u05e8\u05d4" for non-English

Upvotes: 0

s.webbandit
s.webbandit

Reputation: 17000

You shouldn't use already prepared JSON string.

You should use json_encode(); function to create your JSON string from array or object.

It's automatically escapes special characters. Or you can simply define in third argument of this function what to escape. Here is the PHP DOCs.

Upvotes: 3

Quentin
Quentin

Reputation: 943579

The right method to escape any string to put into an SQL query for MySQL is to use bound arguments. Using mysqli_real_escape_string trails behind and mysql_real_escape_string trails further behind.

Escaping characters that can have special meaning but don't in context does not cause a problem.

If you want to query the data sanely, then it shouldn't be stored in JSON format in the database. You can't access the fields in the JSON without extracting it from the database and parsing it.

Upvotes: 0

Related Questions