MySQL- Insert single escape character into MySQL JSON field

Question

In dealing with the headache of the different rulesets with TEXT escaping and JSON escaping, I've come across the issue where double escaping is required to convert a string to a JSON literal. For example, the original UPDATE looks like this:

UPDATE sourcing_item_data SET data_JSON='{"test": "test \ test"}' WHERE ID = 1;

The above simply removes the '\'.

The problem is I can't see how we get a single backslash into the system. Using two \'s causes the Invalid JSON error. Using three \'s does the same. Using four \'s puts in two \'s.

How does one get a single backslash into a JSON literal from a string with MySQL?

Also, has anyone written a SP or Function that scans a string that's supposed to be converted to MySQL JSON to ensure the string is "scrubbed" for issues (such as this one)?

Thanks!

which data type is the column data_JSON ???

ScaisEdge
– ScaisEdge

2020-06-18 15:53:43 +00:00
Commented Jun 18, 2020 at 15:53 — ScaisEdge
– ScaisEdge, Commented Jun 18, 2020 at 15:53

Barmar · Accepted Answer · 2020-06-18 17:31:29Z

1

Four backslashes works.

UPDATE sourcing_item_data SET data_JSON='{"test": "test \\\\ test"}' WHERE ID = 1;

You need to double the backslash to escape it in JSON, and then double each of those to escape in the SQL string.

If you print the JSON value it will show up as two backslashes, but that's because it shows the value in JSON format, which means that the backslash has to be escaped. If you extract the value and unquote it, there will just be one backslash.

select data_JSON->>"$.test" as result 
from sourcing_item_data
WHERE id = 1;

shows test \ test

DEMO

edited Jun 18, 2020 at 17:31

answered Jun 18, 2020 at 15:54

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

7 Comments

Floobinator Over a year ago

Is that the only way to do it? The problem is I dynamically create the SQL execution statements, and this complicates things greatly as I would have to build a key extractor and assign all of the fields accordingly. I'd prefer to follow a full string approach if possible rather than inserting functions within the dynamic SQL creation. Is it even possible to get the single backslash without using JSON_OBJECT?

Barmar Over a year ago

Updated the answer. 4 backslashes does what you want.

Floobinator Over a year ago

OK I'm starting to see how this mess is working (I so regret choosing MySQL over MSSQL). What exactly does the ->> do? You are correct, converting a single / to //// seems to solve the problem. Interestingly, when I SELECT the JSON value from the database it shows // in the field but the ->> shows just one slash. What's the use case scenario for both of those? When pulling a JSON field value from the database should I always be referencing the key as ->>? What about when I want to entire value without a key assignment?

Barmar Over a year ago

--> is equivalent -> plus JSON_UNQUOTE().

Floobinator Over a year ago

I appreciate your help on this. So here's the key question. How do I go about writing a function or SP that ensures any incoming JSON "value" is properly scrubbed for storage and use within MySQL but also properly translates to a TEXT value when the dynamic system builds the INSERT and UPDATE values? For example, I'll get an update of JSON data that needs to be written to a specific record, and our system translates that update to a manually built UPDATE statement, so I have to ensure the TEXT value of that JSON object doesn't cause errors.

|

Collectives™ on Stack Overflow

MySQL- Insert single escape character into MySQL JSON field

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related