How to properly escape JSON string for use with MySQL INSERT INTO command?

Question

Can't believe there are no questions like this... Must be something really simple, but I spend 2 days trying to figure this one out.

I have a table and one of the coloumns has values in a JSON format. In PHP my syntax is like this (it's in a class function):

$sql = "INSERT INTO users.users (username, class, settings, email, password) VALUES ($this->username, $this->class, ".json_encode($this->settings).", $this->email, $this->hashpwd);";
        $STH = $DBH->prepare($sql);
        $STH->execute();

However this one of course breaks because JSON format contains commas and these commas are also separating the Insert values, so it breaks the query. And escape functions (like PDO->quote or mysqli_real_escape_string) don't escape commas either.

Error I am getting is of course:

...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 
'"usersetting1":"value","usersetting2":"value"}, [email protected], 712985cc'...

So is there any way to do this or do I have to use some kind of alt syntax for the query?

AlexL · Accepted Answer · 2014-10-26 14:08:43Z

2

Try this:

$sql = "INSERT INTO users.users (username, class, settings, email, password) VALUES (:username, :class, :json, :email, :password);";
    $STH = $DBH->prepare($sql);
    $STH->bindParam(':username', $this->username);
    $STH->bindParam(':class', $this->class);
    $STH->bindParam(':json', json_encode($this->settings));
    $STH->bindParam(':email', $this->email);
    $STH->bindParam(':password', $this->hashpwd);
    $STH->execute();

answered Oct 26, 2014 at 14:08

AlexL

1,70713 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Megakoresh Over a year ago

Worked perfectly, thanks. So In that sense binding parameters is the only way to do this?

AlexL Over a year ago

It is the recommended, safest way and saves you from SQL injection. You should always use binding parameters.

Collectives™ on Stack Overflow

How to properly escape JSON string for use with MySQL INSERT INTO command?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related