Inserting into a strings with special characters into an json mysql column (using php/mysql/Laravel)

Question

I am performing an insert on db table that has a column called json_props - when a word that has special characters like Everyone's this appears in the json column (and back on the frontend) like this

{"col1": "Everyone's"}

I am using the Laravel framework what is the best/recommended Laravel way to insert/escape this correctly into the database.

-- Expected output --

Insert string with special character (like an apostrophe) and it should look normal on the frontend like this e.g term's

-- Actual Output --

term's

are you using json data in blade? – Dilip Patel Sep 22 '21 at 08:59 — Dilip Patel, Sep 22 '21 at 08:59
no blade template - it is rendered in plain HTML – Zabs Sep 22 '21 at 09:00 — Zabs, Sep 22 '21 at 09:00

score 0 · Answer 1 · answered Sep 22 '21 at 09:12

when a word that has special characters like Everyone's this appears in the json column (and back on the frontend) like this {"col1": "Everyone's"}

This is the client software which replaces a quote char with according entity during inserting the value into the table. Not MySQL.

Find the place in your code where this substitution is performed, and replace it with quote char doubling. Then the value will be successfully saved and retrieved with MySQL.

CREATE TABLE test (json_props JSON);
INSERT INTO test VALUES ('{"col1": "Everyone''s"}');
SELECT CAST(json_props AS CHAR) FROM test;

CAST(json_props AS CHAR)
{"col1": "Everyone's"}

db<>fiddle here

Inserting into a strings with special characters into an json mysql column (using php/mysql/Laravel)

1 Answers1