MS SQL Backslash preceding new line/line feed remo

Question

I'm working in classical ASP and am trying to simply insert some user input into my MS SQL 2008 database. This is something I do basically every day, but I don't think I've ever experienced this bug before.

The bug I am getting is that, if a user ends a line of text in backslash, and starts a new line below, both the backslash and line break are lost after the data is stored in the DB.

If i try the following statement, hardcoded from an ASP file:

UPDATE TBLarticle_text SET Introduction = 'Text on first line \" & vbCrLf & " text on second line' WHERE ArticleGuid = 28

The resulting data is without the backslash or the line break. The string is correct if stored in a variable and printed on the page.

Here is the example user input (normally from a form, but it's not really relevant). The input:

Text on first line \
text on second line

... is stored as:

Text on first line  text on second line

I don't see any issues if the backslash is followed by anything other than a line break.

Answer 1

I am seeing a similar issue. I would say the best way to work around this is to not let ASP pass such strings to SQL Server. You can clean this up by simply replacing that character sequence and injecting a space at the end of such a line (which a user is unlikely to ever notice):

sql = REPLACE(sql, "\" & vbCrLf, "\ " & vbCrLf)

You don't have to be using ASP or VBScript to observe this behavior:

CREATE TABLE #floobar(i INT, x VARCHAR(255));

INSERT #floobar SELECT 1, 'foo \
bar';
INSERT #floobar SELECT 2, 'foo \\
bar';
INSERT #floobar SELECT 3, 'foo 
bar';

SELECT * FROM #floobar;

I don't know that you're ever going to get Microsoft to fix this to not treat this character sequence special, so the "fix" is going to be to work around it. You may also have to watch out for non-traditional CR/LF, e.g. CHR(10) or CHR(13) on their own, or also vbTab (Chr(9))).

Answer 2

I know this is old, but I just came across a MS KB article that discusses this: http://support.microsoft.com/kb/164291/en-us. The long and short of it is that the three characters \<CR><LF> together is some weird escape sequence, and you need to replace all occurrences of \<CR><LF> with \\<CR><LF><CR><LF> when inserting or updating because the first backslash in \\<CR><LF><CR><LF> escapes the weird escape sequence, i.e. the next three characters \<CR><LF>, and then the additional <CR><LF> puts the carriage return back in place. Annoying, yes.