Escaping user input necessary if using json_encode?

Question

If I take some input from a user in $_POST and json_encode it

$json = json_encode($_POST);

and put it in the query

$save = mysqli_query($con, "INSERT INTO table (json) VALUES ('$json')");

Is this prone to SQL injection? Does this input needs to be escaped? In my tests, I couldn't run any queries with input like

') SELECT * FROM table; --

but I'm not even remotely good at this.

PS - This is a test for learning. I'm not actually doing this in a project.

Why take the risk - just use prepared statements every time you get worried about this and let the api/database take care of it. — Nigel Ren, Jul 24 '21 at 11:27
never escape anything. Just use prepared statements 100% of time — Dharman, Jul 24 '21 at 11:42

score 1 · Accepted Answer · answered Jul 24 '21 at 14:02

For the record, yes it is vulnerable. json_encode() does not escape special characters except for ".

Here's a demo:

<?php
$a = [ "name" => "O'Reilly" ];
$j = json_encode($a);
echo "$j\n";

Output:

{"name":"O'Reilly"}

Now what would happen if you interpolated this into an SQL string?

You'd get an unescaped single-quote character inside a single-quoted SQL string literal, which causes a syntax error.

INSERT INTO table (json) VALUES ('{"name":"O'Reilly"}')
                                            ^

The advice in the comments above is correct: When in doubt, use query parameters. Then you don't have to worry about whether the string is safe.

1 Answers1