php mysqli query after json_encode gives error

Question

I have 2 php arrays which I am encoding into json with json_encode and $mysqli->real_escape_string. Now, the output of these two arrays after json_encode is:

{\"arg1\":\"char*\",\"arg2\":\"char*\"}{\"arg1\":\"abc\",\"arg2\":\"bca\"}

Then, I am inserting this into mysql with a query. But I am getting the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\"arg1\":\"char*\",\"arg2\":\"char*\"}, {\"arg1\":\"abc\",\"arg2\":\"bca\"})' at line 1

What is the way I put this json into mysql?

Mysql query

"UPDATE test_info SET java_auto_frame=$frame_auto, java_manual_frame=$frame_manual, java_testcase=$testcases, param_types=$param_types, param_examples=$param_examples WHERE qid=$qid"

where, $param_types is the first json encoded array and $param_examples is the second one.

Obviously your `json` needs to be escaped decently if you are going to just paste it into a query like that... — NDM, Aug 06 '13 at 07:45
You need to escape your input data http://php.net/manual/en/security.database.sql-injection.php — Pekka, Aug 06 '13 at 07:45
Possible duplicate of [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php). You might think it's unrelated, but this is exactly the problem you're facing. — Álvaro González, Aug 06 '13 at 07:48

score 1 · Accepted Answer · answered Aug 06 '13 at 07:45

1

write your query like this

"UPDATE test_info SET java_auto_frame='$frame_auto', java_manual_frame='$frame_manual', java_testcase='$testcases', param_types='$param_types', param_examples='$param_examples' WHERE qid='$qid'"

answered Aug 06 '13 at 07:45

Rajeev Ranjan

4,152
3
28
41

score 1 · Answer 2 · answered Aug 06 '13 at 07:54

Use prepared statement of PDO (http://php.net/manual/en/book.pdo.php). Something like this:

$dbh = new PDO("mysql:host=localhost;dbname=database;","root","");
$sql = "UPDATE test_info SET java_auto_frame=:frame_auto, java_manual_frame=:frame_manual, java_testcase=:testcases, param_types=:param_types, param_examples=:param_examples WHERE qid=:qid";
$sth = $dbh->prepare($sql);
$sth->execute(array(
    'frame_auto' => $frame_auto,
    'frame_manual' => $frame_manual,
    'testcases' => $testcases,
    'param_types' => $param_types,
    'param_examples' => $param_examples,
    'qid' => $qid
));

While PDO is an alternative solution, OP is asking about MySQLi implementation. — Raptor, May 13 '15 at 03:29

php mysqli query after json_encode gives error

2 Answers2