How to save JSON string in MySQL db

Question

I store JSON data in a MySQL table using the following code. It works fine if JSON is short but breaks for longer text. "Field_json" is LONGTEXT.

$sql = sprintf("UPDATE mytable SET field_json = '$json_string' WHERE id = $userid"); $result = mysql_query($sql); 

The error I get is:

Invalid query: you have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use near 'G' "" Username ":" C0WB0Y "," LastName ":" "," identifier ": 31874363}, {" pathToPhoto ":" 22960 / ph ' in line 2

Answer 1

Use placeholders, otherwise you are susceptible to SQL injection: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

Otherwise, this is a quick fix: http://php.net/manual/en/function.mysql-real-escape-string.php

 $sql = sprintf( "UPDATE mytable SET field_json = '%s' WHERE id = '%s'", mysql_real_escape_string($json_string), mysql_real_escape_string($userid) ); $result = mysql_query($sql); 

EDIT

Please use PDO ( http://www.php.net/manual/en/book.pdo.php ). mysql extension deprecated from 5.5

Answer 2

You need to avoid quotes in your JSON string, otherwise they will end SQL-Query, resulting in you get.

Answer 3

Reset the JSON string:

 $json_string = mysql_real_escape_string( $json_string); $sql = sprintf("UPDATE mytable SET field_json = '$json_string' WHERE id = $userid"); $result = mysql_query($sql); 

Answer 4

try it

  $json_string = mysql_real_escape_string( $json_string ); $sql = sprintf("UPDATE mytable SET field_json = '$json_string' WHERE id = $userid"); $result = mysql_query($sql);