How to store json string in mysql table

Question

Have small problem with storing json string in mysql table. String for example:

'{"variable":"content here with \" addsleshes "}'

created with json_encode. Store this string in DB with simple mysqli_query function.

mysqli_query('insert into ... values \'{"variable":"content here with \" addsleshes "}\' ');

After this :

  mysqli_query('select *..');
 mysqli_fetch_array($res);

When I get this field from Mysql and try to use php json_decode it will return NULL because I no longer have the slash before the " . Cannot use addslashes function as it will add slashes also like \"variable\". Not sure how to solve this problem?

You will have to show how you store this string,a nd how you retrieve it. A properly done system will NOT be stripping those slashes from the text. — Marc B
– Marc B, Commented Jul 2, 2014 at 18:27
updated the question. im using now for testing simply mysqli_query funciton in case my DB class is changing something, but still the same problem — abiku
– abiku, Commented Jul 2, 2014 at 18:45
well, yeah, you haven't escaped that string properly. If you had, the backslash that json_encode added would've gotten into the database. In other words, you're suffering from an sql injection attack vulnerability, and this string "corruption" is a symptom of it. — Marc B
– Marc B, Commented Jul 2, 2014 at 19:25

Abhineet Verma · Accepted Answer · 2014-07-02 19:04:36Z

3

Why don't you try reverse of it.

Use mysqli_escape_string before actually passing JSON to mysqli_query

$json = '["Json","Just a Sample Data","Data 2"]'
mysqli_query('insert into ... values'.mysqli_escape_string($json));

Now, When you retrieve data from database

mysqli_query('select *..');
mysqli_fetch_array($res);

Use stripslashes($str) to remove slashes from JSON.

It'll work the same way you're looking for.

answered Jul 2, 2014 at 19:04

Abhineet Verma

1,0288 silver badges18 bronze badges

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

Comments

Tripp Kinetics · Accepted Answer · 2014-07-02 18:26:53Z

0

Try:

'{"variable":"content here with \\" addsleshes "}'

answered Jul 2, 2014 at 18:26

Tripp Kinetics

5,4892 gold badges25 silver badges40 bronze badges

2 Comments

abiku Over a year ago

Yes, was thinking about this but im using json_encode to create a json string from php array. so i would have to do addslashes to every array cell before using json_enocde?

Marc B Over a year ago

no. json_encode() will do all the escaping for you. You need to figure out what/where is stripping the slash from the text.

Collectives™ on Stack Overflow

How to store json string in mysql table

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related