PHP trouble json_decoding multi level json array

Question

I am trying to parse json from Amazon SNS using PHP and json_decode.

I am storing the json from Amazon in a mysql database, and then looping through the rows to hopefully display a table of all notifications.

This is the original json that comes in:

{
"Type": "Notification",
"MessageId": "4ecca6d5-3095-5d5c-a0f0-f013dc59f33e",
"TopicArn": "arn:aws:sns:eu-west-1:826492718737:Email_Notifications",
"Message": "{\"notificationType\":\"Delivery\",\"mail\":{\"timestamp\":\"2020-02-19T07:58:03.541Z\",\"source\":\"do-not-reply@supplyplus.org.uk\",\"sourceArn\":\"arn:aws:ses:eu-west-1:826492718737:identity/do-not-reply@supplyplus.org.uk\",\"sourceIp\":\"82.68.110.206\",\"sendingAccountId\":\"826492718737\",\"messageId\":\"010201705c743115-5bc7fc12-f36d-425a-8ff8-14ea0acf073b-000000\",\"destination\":[\"edward.hall@setsquaresolutions.com\"]},\"delivery\":{\"timestamp\":\"2020-02-19T07:58:04.226Z\",\"processingTimeMillis\":685,\"recipients\":[\"edward.hall@setsquaresolutions.com\"],\"smtpResponse\":\"250 2.0.0 OK  1582099084 z21si661666ejr.129 - gsmtp\",\"remoteMtaIp\":\"209.85.202.27\",\"reportingMTA\":\"a4-7.smtp-out.eu-west-1.amazonses.com\"}}",
"Timestamp": "2020-02-19T07:58:04.286Z",
"SignatureVersion": "1",
"Signature": "XjCUN+iogrinljvfc32wG55UUT1WUVhhIpXYPZtW9IcBiJfBnlQETy2LNlLPnGjtqcKKuV8IKQbC06DIW3EeiKGzfxeQE8OthJJOEGexcA1qr6EbMPLalhDRSmmoUC8a6vY4d3Gn/R98lzaij5UUbT9y4Oesuq8NFR98m8WpOZqMwBdjsRRhZuN676c/or9x6ibU1kWExXKxw/Cw77hhkT5Hd52gNsg2nla+V6oVWXfn/jCmUWByrJRFdxe1nUm+WbcNT4nCkqKxUBT/NTA5RkD72Mr+X/470WNXBJZ6OP/4tbby3bcn7BxzZBA780c2OBtRlseD2MvKJGE8qss2og==",
"SigningCertURL": "https://sns.eu-west-1.amazonaws.com/SimpleNotificationService-a86cb10b4e1f29c941702d737128f7b6.pem",
"UnsubscribeURL": "https://sns.eu-west-1.amazonaws.com/?Action=Unsubscribe&SubscriptionArn=arn:aws:sns:eu-west-1:826492718737:Email_Notifications:b584a2d6-c9e3-4401-a029-7b6fd4603274"

}

The json after it has been inserted into the database looks like this:

{
  "Type" : "Notification",
  "MessageId" : "3ce843a7-13c2-54f8-a445-1561121fd162",
  "TopicArn" : "arn:aws:sns:eu-west-1:826492718737:Email_Notifications",
  "Message" : "{"notificationType":"Delivery","mail":{"timestamp":"2020-02-18T15:15:40.879Z","source":"do-not-reply@example.com","sourceArn":"arn:aws:ses:eu-west-1:826492718737:identity/do-not-reply@example.com","sourceIp":"12.34.56.78","sendingAccountId":"826492718737","messageId":"0102017058de7ccf-55c4a34d-fcd1-449c-a82a-9946b316e551-000000","destination":["user@example.com"]},"delivery":{"timestamp":"2020-02-18T15:15:41.527Z","processingTimeMillis":648,"recipients":["user@example.com"],"smtpResponse":"250 2.0.0 OK  1582038941 p17si2420323wma.126 - gsmtp","remoteMtaIp":"12.34.56.78","reportingMTA":"a4-8.smtp-out.eu-west-1.amazonses.com"}}",
  "Timestamp" : "2020-02-18T15:15:41.574Z",
  "SignatureVersion" : "1",
  "Signature" : "UTzy1PLgzxyz7wczh/3ZrcKiT9MCFe7pcmeOLH3whZ6tRbq1B6pjv+cEjTFKR3RnH4D9CkDQq04vpTyj3ATwJ5ZSxVOuNmGEo1KuOjH8TveaB3PzQTpwQlsVg1fzW5wCmhND9dvDuXiTGc86Lvy4e9AlmGNHTrfbAa5fZIfFfP9PzEhHF8zcyIvZd//2Ni4hmAvx4MKtdlke/m2Uacn9bSF61pVD/QCZOsFkV7ovfqiRZZmdXOaFb8dTZeBlU98jJLvIwDk5C5lKG7Ru8V+Gs8g13iQyS3v7xI35lVQUo52U+srk5Ndc5AdR6C3COW2r12g7eWacypP+phU2vr8uTA==",
  "SigningCertURL" : "https://sns.eu-west-1.amazonaws.com/SimpleNotificationService-a86cb10b4e1f29c941702d737128f7b6.pem",
  "UnsubscribeURL" : "https://sns.eu-west-1.amazonaws.com/?Action=Unsubscribe&SubscriptionArn=arn:aws:sns:eu-west-1:826492718737:Email_Notifications:b584a2d6-c9e3-4401-a029-7b6fd4603274"
}

When I try to convert this to a php array using:

var_dump(json_decode($json, true));

It returns null, I have discovered that this only happens when I include the "Message" which includes sub-arrays. If I remove the "Message" part, then the php array returns correctly.

How can I correctly convert this json to a PHP associative array?

I am losing the backslashes after the json has been inserted into the database, if I can prevent that then this should work.

Edit:

I am inserting the json into the database like this, I have also tried json_encode and decode on the file_get_contents before inserting it:

$json = file_get_contents('php://input');

$mysqli->query("INSERT INTO `log` (json) VALUES('{$json}'));

can you insert complete json here, so that I can convert it, Thanks — suraj chand, Feb 18 '20 at 15:28
The JSON snippet provided is not valid JSON, try on https://jsonlint.com/ — Brett Gregson, Feb 18 '20 at 15:28
Remove the quotes `"` before and after the content of message. Then it works. — Robin Gillitzer, Feb 18 '20 at 15:30
obviously you attempt to inject `message` into the JSON is wrong, show us this part of your code. — mitkosoft, Feb 18 '20 at 15:51
You are not properly escaping your values prior to interpolating them into your SQL query. Due to that, the backslashes, which have a special meaning, are being interpreted and get lost. This destroys your JSON. **Properly prepare your SQL queries.** See the duplicate. You may also want to read http://kunststube.net/escapism. — deceze, Feb 19 '20 at 11:07

MaxT · Answer 1 · 2020-02-18T16:21:11.970

You are saying you are "including" message part so I guess this is not the original JSON payload received from amazon?

Can you tell what do you mean by including the Message part?

when you get JSON response from SNS do the following:

$data = json_decode($postData, true);
$data['Message'] = json_decode($data['Message'], true);

this will give you correct array structure. Depending on the string format you might need to unescape the field before decode. This is because Message contains another separate JSON message structure encoded as a string.

I sincerely doubt that above message dump contains all characters you receive in the post from SNS. SNS will send a valid JSON response, so I have to assume the actual rasponse has properly encoded json payload as a string in the Message field.

score -1 · Answer 2 · answered Feb 19 '20 at 10:56

-1

By escaping the backslashes that exist within the json before inserting it into the database I have managed to get this to work:

$json = str_replace('\\', '\\\\', $json);

$mysqli->query("INSERT INTO `log` (json) VALUES('{$json}')");

answered Feb 19 '20 at 10:56

Edward144

467
1
5
14

This is insufficient and still prone to SQL injection. – deceze Feb 19 '20 at 11:08
I understand that it is prone to sql injection and how to prevent against it, but this is something that is only accessible on the local network so I'm not too worried about that. I'm not sure why you have marked it as duplicate for preventing sql injection, as that was never part of my question? – Edward144 Feb 19 '20 at 14:19
Your issue is that you're not forming your SQL queries correctly, which is leading to errors down the line. Forming SQL queries correctly and preventing SQL injection are the same thing. If you're forming your queries correctly you're safe from injection. You need to learn how to properly use `mysqli` to insert arbitrary values into the database. That's why they're the same. – deceze Feb 19 '20 at 14:21

PHP trouble json_decoding multi level json array

2 Answers2