2

Using the STRING_ESCAPE function found here I am escaping certain columns with string values so that they fit in a JSON format. However when it is applied to URLs it behaves, in my opinion, strangely.

SELECT STRING_ESCAPE('https://www.website.com/page', 'json')

returns https:\/\/www.website.com\/page.

I can understand that indeed according to this post forward slashes are allowed, not required in JSON and therefore they are included when using this function. But if you create an HTML tag with this value (https:\/\/www.website.com\/page) the link no longer works as, my browser at least, is trying to surf to https://www.website.com//page.

Since I don't know if my original string contains \/ I can't just use REPLACE(STRING_ESCAPE([column], 'json'), '\/', '/') to solve this.

Is there an option to disable the escaping of forward slashes? Or any other clever solution to this problem?

gotqn
  • 42,737
  • 46
  • 157
  • 243
Vqf5mG96cSTT
  • 2,561
  • 3
  • 22
  • 41
  • 2
    I believe the problem is displaying an HTML tag with a json escaped value. – EzLo Jan 09 '19 at 16:08
  • 1
    Why not able to replace them? – gotqn Jan 09 '19 at 16:22
  • I guess your data can hold many kinds os data like URL, Dates, Paths, etc. Do you need to use a custom solution for each kind of data? – jean Jan 09 '19 at 16:41
  • @jean That is correct, it can hold anything JSON can. I'm not sure how to answer that question. I wouldn't want a custom solution for every kind of data if it's possible to avoid. – Vqf5mG96cSTT Jan 09 '19 at 16:47
  • Is it be OK, if you have a way to encode this value in the SQL and then to decode it in the client site using native functions? In the JSON you can have a flag to mark if the value is encoded? – gotqn Jan 10 '19 at 06:16

2 Answers2

1

Preamble: The original problem is attempting to JSON-escaped value that is not later consumed as JSON as URLs are not JSON strings. However, this answer does not focus on that incorrect domain usage and/or failure to decode the JSON value before the usage in HTML.

Rather, this answer corrects this misbelief:

Since I don't know if my original string contains [\/] I can't just use a replace to solve this.

As long as STRING_ESCAPE is used, it is a valid approach to perform the replacement after using STRING_ESCAPE, as shown in the original question:

REPLACE(STRING_ESCAPE([column], 'json'), '\/', '/')

This is because STRING_ESCAPE escapes every / and \, meaning that any \/ in the original source is also escaped as \\\/. (It would only be problematic if \ was not also escaped.)

Consider these examples, which result in a valid JSON string content without the escaped solidus, that demonstrate the correctness of the simple REPLACE approach.

INPUT             STRING_ESCAPE       REPLACE(.., '\/', '/')
hello\world       hello\\world        hello\\world
hello/world       hello\/world        hello/world
hello\/workd      hello\\\/world      hello\\/world
\\/\/\\           \\\\\/\\\/\\\\      \\\\/\\/\\\\

The values above represent the actual string content.

user2864740
  • 60,010
  • 15
  • 145
  • 220
0

I'm not aware of any option to disable escaping a forward slash, but if you're looking for ideas for a workaround you could replace forward slashes with some dummy value, then do the escaping, then restore the forward slashes by replacing the dummy values with the forward slashes.

Thinking something like this:

DECLARE @replace_value VARCHAR(36) = NEWID();
SELECT 
    STRING_ESCAPE('https://www.website.com/page', 'json'),
    REPLACE(STRING_ESCAPE(REPLACE('https://www.website.com/page', '/', @replace_value), 'json'), @replace_value, '/');

Not the prettiest, but might get the job done.

Eilert Hjelmeseth
  • 618
  • 2
  • 5
  • 12