Json becomes invalid when saved to database

Question

I have a valid json. I'm pulling this json from an api and saving it to the database.No problem so far

   [
  {
    "MsgType": "TIMCustomElem",
    "MsgContent": {
      "Desc": "",
      "Data": "{\"conversationDesc\":\"\\nvxzzf\",\"deviceType\":\"Android\",\"otherUserMsg\":false,\"sender\":{\"birthday\":\"\",\"city\":\"\",\"coin\":70318,\"diamonds\":929696,\"emotional_state\":\"gizlilik\",\"family_chieftain\":0,\"family_id\":0,\"fans_count\":0,\"focus_count\":1,\"follow_id\":0,\"gameCurrency\":929696,\"gh_status\":0,\"head_image\":\"http://fw25live.oss-cn-beijing.aliyuncs.com/public/attachment/202101/29/16/6013cc8481bbc.png\",\"is_admin\":0,\"is_agree\":1,\"is_authentication\":0,\"is_edit_sex\":0,\"is_guardian\":0,\"is_noble_mysterious\":0,\"is_open_shop\":0,\"is_remind\":1,\"is_vip\":0,\"item\":{\"Yaş\":\"tahmin edersiniz\",\"Medeni durum\":\"gizlilik\",\"Meslek\":\"host\",\"LopMeID\":\"106050\"},\"job\":\"host\",\"levelImageResId\":2131232950,\"login_type\":0,\"luck_num\":\"\",\"luck_num_url\":\"https://test.lop.net/wap/index.php?ctl=luck&act=index\",\"members_url\":\"https://test.lop.net/wap/index.php?ctl=vip_pay&act=purchase\",\"n_svideo_count\":0,\"nick_name\":\"ibrahimozden\",\"nick_nameFormat\":\"ibrahimozden：\",\"noble_avatar\":\"\",\"noble_barrage\":0,\"noble_car\":0,\"noble_car_name\":\"\",\"noble_car_url\":\"\",\"noble_experience\":0,\"noble_icon\":\"\",\"noble_is_avatar\":0,\"noble_medal\":0,\"noble_name\":\"\",\"noble_silence\":0,\"noble_special_effects\":0,\"noble_stealth\":0,\"noble_vip_type\":0,\"open_podcast_goods\":0,\"pay_car\":\"https://test.lop.net/wap/index.php?ctl=car&act=index\",\"podcast_goods\":0,\"podcast_order\":0,\"podcast_pai\":0,\"proUser\":true,\"province\":\"\",\"seconds\":0,\"selected\":false,\"sex\":1,\"sexResId\":2131231724,\"shop_goods\":0,\"shopping_cart\":0,\"showId\":\"106050\",\"show_podcast_goods\":0,\"show_podcast_order\":0,\"show_podcast_pai\":0,\"show_shopping_cart\":0,\"show_svideo\":1,\"show_user_order\":0,\"show_user_pai\":0,\"signature\":\"\",\"society_chieftain\":0,\"society_id\":0,\"society_name\":\"\",\"sort_num\":-1,\"star_box\":\"\",\"stealth\":0,\"ticket\":1806,\"token\":\"\",\"use_diamonds\":70318,\"useable_ticket\":1806,\"user_id\":\"106050\",\"user_level\":43,\"user_order\":0,\"user_pai\":0,\"v_explain\":\"\",\"v_icon\":\"\",\"v_type\":\"0\",\"video_count\":0,\"viewer_guard_type\":0,\"vip_expire_time\":\"Açılmadı\"},\"text\":\"\\nvxzzf\",\"type\":20}",
      "Ext": "",
      "Sound": ""
    }
  }
]

The problem is this: This json insert in the database (mysql) as follows and I get invalid json.

[{"MsgType":"TIMCustomElem","MsgContent":{"Desc":"","Data":"{"conversationDesc":"\nvxzzf","deviceType":"Android","otherUserMsg":false,"sender":{"birthday":"","city":"","coin":70318,"diamonds":929696,"emotional_state":"gizlilik","family_chieftain":0,"family_id":0,"fans_count":0,"focus_count":1,"follow_id":0,"gameCurrency":929696,"gh_status":0,"head_image":"http://fw25live.oss-cn-beijing.aliyuncs.com/public/attachment/202101/29/16/6013cc8481bbc.png","is_admin":0,"is_agree":1,"is_authentication":0,"is_edit_sex":0,"is_guardian":0,"is_noble_mysterious":0,"is_open_shop":0,"is_remind":1,"is_vip":0,"item":{"Yau015f":"tahmin edersiniz","Medeni durum":"gizlilik","Meslek":"host","LopMeID":"106050"},"job":"host","levelImageResId":2131232950,"login_type":0,"luck_num":"","luck_num_url":"https://test99.lo.net/wap/index.php?ctl=luck&act=index","members_url":"https://test.lop.net/wap/index.php?ctl=vip_pay&act=purchase","n_svideo_count":0,"nick_name":"ibrahimozden","nick_nameFormat":"ibrahimozdenuff1a","noble_avatar":"","noble_barrage":0,"noble_car":0,"noble_car_name":"","noble_car_url":"","noble_experience":0,"noble_icon":"","noble_is_avatar":0,"noble_medal":0,"noble_name":"","noble_silence":0,"noble_special_effects":0,"noble_stealth":0,"noble_vip_type":0,"open_podcast_goods":0,"pay_car":"https://test.lop.net/wap/index.php?ctl=car&act=index","podcast_goods":0,"podcast_order":0,"podcast_pai":0,"proUser":true,"province":"","seconds":0,"selected":false,"sex":1,"sexResId":2131231724,"shop_goods":0,"shopping_cart":0,"showId":"106050","show_podcast_goods":0,"show_podcast_order":0,"show_podcast_pai":0,"show_shopping_cart":0,"show_svideo":1,"show_user_order":0,"show_user_pai":0,"signature":"","society_chieftain":0,"society_id":0,"society_name":"","sort_num":-1,"star_box":"","stealth":0,"ticket":1806,"token":"","use_diamonds":70318,"useable_ticket":1806,"user_id":"106050","user_level":43,"user_order":0,"user_pai":0,"v_explain":"","v_icon":"","v_type":"0","video_count":0,"viewer_guard_type":0,"vip_expire_time":"Au00e7u0131lmadu0131"},"text":"\nvxzzf","type":20}","Ext":"","Sound":""}}] 

My question is why json gets corrupted?

Answer 1

The backslashes are removed from your JSON because backslash is a special metacharacter in string literals in SQL.

This is not limited to JSON. If you insert a string like this:

INSERT INTO mytable SET t = '\"Murder,\" she said';

The \" sequences are interpreted as a single literal " character. See https://dev.mysql.com/doc/refman/8.0/en/string-literals.html#character-escape-sequences

If you need to store literal backslash characters in your database, you have two choices:

Double-backslash:

INSERT INTO mytable SET t = '\\"Murder,\\" she said';

Query parameter:

INSERT INTO mytable SET t = ?;

Then bind the JSON string to the query parameter. The SQL parser never sees the content of a parameter, so it won't reduce the \" to ".