How to store JSON string in MySQL db

Question

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

Please, please, please consider using prepared statements. Well, only if you prefer your site un-hacked ... — cheeken
– cheeken, Commented Jan 24, 2012 at 16:57
Just as a side node, if you are storing JSON in a mysql table, maybe you should consider using a database that is better suited to store such data like CouchDB, MongoDB, etc — klaustopher
– klaustopher, Commented Jan 24, 2012 at 17:03
good point. This is just for an import job where I want to save the json in case I have to process it again — uwe
– uwe, Commented Jan 24, 2012 at 22:18

Martin Samson · Accepted Answer · 2014-06-25 22:06:42Z

21

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

edited Jun 25, 2014 at 22:06

answered Jan 24, 2012 at 16:56

Martin Samson

4,09024 silver badges25 bronze badges

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

2 Comments

Leysam Rosario Over a year ago

SQL Injection possible with $userid. You can use (int) $userid instead of mysql_real_escape_string.

Martin Samson Over a year ago

The question doesn't specify the data type for the id field, but in systems i've worked on, the user id is a uuid, not an int.

nickb · Accepted Answer · 2012-01-24 16:55:26Z

4

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

answered Jan 24, 2012 at 16:55

nickb

59.7k13 gold badges115 silver badges149 bronze badges

4 Comments

Martin Samson Over a year ago

in your example, $userid could cause an SQL injection.

nickb Over a year ago

It could, but not only do we not know where $userid is coming from, it's also not the source of the OPs problem.

Martin Samson Over a year ago

While not OP problem, the provided example silently leaves a potential security problem.

uwe Over a year ago

thanks. Good points about the security. It's an import job so security isn't an issue .

Max · Accepted Answer · 2012-01-24 16:54:34Z

3

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

answered Jan 24, 2012 at 16:54

Max

1,0221 gold badge13 silver badges27 bronze badges

Comments

Harry Forbess · Accepted Answer · 2012-01-24 16:55:46Z

2

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

answered Jan 24, 2012 at 16:55

Harry Forbess

2,1344 gold badges16 silver badges15 bronze badges

1 Comment

Martin Samson Over a year ago

SQL Injection possible with $userid

Collectives™ on Stack Overflow

How to store JSON string in MySQL db

4 Answers 4

EDIT

2 Comments

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

EDIT

2 Comments

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related