SQL Server: Escape single quotes, double quotes, XML and forward slashes in JSON String
If you are writing the T-SQL directly then, given a valid JSON text you need to:
- Surround it with single quotes.
- Escape any single quotes in it
- Escape any escape sequences
So the JSON:
[1, "Kennedy O'Brian", { "hello": "world" }]
Would become
Messages='[1, "Kennedy O\'Brian", { "hello": "world" }]'
Example without STRING_ESCAPE:
Check example below with xml structure data where I added single quotes, double quotes, forward slashes in the JSON string and IsJSON is also false.
DECLARE @str VARCHAR(800) = '<root>
<blog lastUpdated="03/22/2022">kennedyihe.com</blog>
<author>kennedy</author>
</root>'
DECLARE @json VARCHAR(800) = '[{"Name":"Kennedy","Gender":"Male","About":"'[email protected]+'"}]'
SELECT @json as Json
SELECT ISJSON(@json) IsJson
Result
Json
----------------------------------------
[{"Name":"Kennedy","Gender":"Male","About":"<root> <blog lastUpdated="03/22/2022">kennedyihe.com</blog> <author>kennedy</author> </root>"}]
IsJson
----------------------------------------
0
Example with STRING_ESCAPE:
In the below example, check the result of STRING_ESCAPE() and IsJSON is also true. STRING_ESCAPE() requires two parameters of value and escape_type. The escape type here will be ‘JSON’.
DECLARE @str VARCHAR(800) = '<root>
<blog lastUpdated="03/22/2022">kennedyihe.com</blog>
<author>kennedy</author>
</root>'
DECLARE @json VARCHAR(800) = '[{"Name":"Kennedy","Gender":"Male","About":"'+ STRING_ESCAPE(@str, 'JSON')+'"}]'
SELECT @json as Json
SELECT ISJSON(@json) IsJson
Result
Json
----------------------------------------
[{"Name":"Kennedy","Gender":"Male","About":"<root>\r\n\t\t\t\t\t\t\t\t<blog lastUpdated=\"03\/22\/2022\">kennedyihe.com<\/blog>\r\n\t\t\t\t\t\t\t\t<author>kennedy<\/author>\r\n\t\t\t\t\t\t\t<\/root>"}]
IsJson
----------------------------------------
1
Enjoy!