CODEWITHSUNDEEP
phpmysqljson
uwe
uwe

Reputation: 4097

How to store JSON string in MySQL db

I'm storing JSON data in a MySQL table using the code below. It works fine if the JSON is short but breaks for longer text. The "field_json" is a LONGTEXT.

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

The error I'm getting is:

Invalid query: 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 'G '","username":"C0WB0Y","lastName":"","id":31874363},{"pathToPhoto":"22960/phot' at line 2

Upvotes: 12

Views: 53316

Answers (4)

Martin Samson
Martin Samson

Reputation: 4090

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

Otherwise, here's 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 ). The mysql extension has been deprecated as of 5.5

Upvotes: 21

Harry Forbess
Harry Forbess

Reputation: 2134

try this

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

Upvotes: 2

nickb
nickb

Reputation: 59709

Escape 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);

Upvotes: 4

Max
Max

Reputation: 1040

You need to escape the quotes in your JSON string - otherwise they terminate the SQL-Query resulting in the exception you got.

Upvotes: 3

Related Questions