Attempting to insert value into JSON array

Question

I'm trying to insert a JSON entry into my table, but the catch is that this JSON string has a single quote character. The below code works perfectly when the string for mainIdea is Its nice but what I want is It's nice with an apostrophe. What would I have to change about the below code to make it work with an apostrophe? I've tried It\'s nice but that doesn't work either.

$jsonDic='{"mainName": "Steve Jobs","mainIdea": "Its nice"}';
$dictionaryToBeAdded=json_decode($jsonDic);
var_dump($dictionaryToBeAdded);
$data=mysql_query("SELECT arrayOfRequests FROM users WHERE email='$email'");
if($result = mysql_fetch_array( $data )) {
   //get json encoded arrayOfNotifs
    $decodeArray=$result['arrayOfRequests']; //this is empty
    //decode it
    $arrayOfRequests=json_decode($decodeArray);
    //add dictionary to be added
    $arrayOfRequests[]=$dictionaryToBeAdded;
    $sendBackArray=json_encode($arrayOfRequests);
    //update db
    mysql_query("UPDATE users SET arrayOfRequests ='$sendBackArray' WHERE email='$email'");
}

"I'm trying to insert a JSON entry into my table" that's the problem, db normalisation 101 - don't do that! — user557846
– user557846, Commented Feb 16, 2012 at 23:06
you should have a new table for arrayOfRequests, with each in its own 'cell' joined to the users table . — user557846
– user557846, Commented Feb 16, 2012 at 23:10
I shouldn't have JSON strings in my table? What then should I have? — Snowman
– Snowman, Commented Feb 16, 2012 at 23:10
You're storing encoded subfields in a database field, which makes it harder to search for values in the subfields. — Jonathan M
– Jonathan M, Commented Feb 16, 2012 at 23:10
Break your JSON into fields, and create matching fields in your db. This may involve new tables in order to normalize the data. — Jonathan M
– Jonathan M, Commented Feb 16, 2012 at 23:11

Salman Arshad · Accepted Answer · 2012-02-17 12:09:18Z

1

You need to escape your data before you attempt to use it in a database query:

mysql_query("UPDATE users SET arrayOfRequests ='$sendBackArray' WHERE email='$email'");
// ---------------------------------------------^                            ^
// --------------------------------------------------------------------------+

Imagine what would happen if $sendBackArray contains ', email =' and $email contains ' OR '' = '.

mysql_query("UPDATE users SET arrayOfRequests ='" . mysql_real_escape_string($sendBackArray) . "' WHERE email='" . mysql_real_escape_string($email) . "'");

answered Feb 17, 2012 at 12:09

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

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

Comments

mauris · Accepted Answer · 2012-02-16 23:05:38Z

0

You have to escape your slash as well:

$jsonDic='{"mainName": "Steve Jobs","mainIdea": "It\\\'s nice"}';

answered Feb 16, 2012 at 23:05

mauris

43.6k16 gold badges102 silver badges132 bronze badges

4 Comments

Snowman Over a year ago

This is giving me NULL for var_dump($dictionaryToBeAdded);

Jonathan M Over a year ago

Actually, JSON requires double-quoted keys. See json.org. Also they're not invalid for javascript.

mauris Over a year ago

@jonathanm - yep just realised.

Snowman Over a year ago

I didn't create that string by hand..it was an Objective C dictionary which was encoded to JSON with JSONKit

Collectives™ on Stack Overflow

Attempting to insert value into JSON array

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related