Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Killer Backslash in TSQL

--it is quite startling to see that this, in TSQL, executes.
Select    \
/*
---------------------
0.00
 
(1 row(s) affected)
 
so does this....
*/
Select    null  /\/\/\/\/\/\/\/\/\/\/\/\
/*
---------------------
NULL
 
(1 row(s) affected)
*/
/* Good old SQL Server. We can execute smileys*/
Select    \-0   [(-:]
/* however, there is a serious side to this. Now go to Adventureworks (here I'm in good ol' SQL Server 2005) and execute this.*/
 
SELECT
      [TerritoryID],/*Primary key for SalesTerritory records.*/
      [Name],/*Sales territory description*/
      [CountryRegionCode],/*ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode. */
      [Group],/*Geographic area to which the sales territory belong.*/
      [SalesYTD],/*Sales in the territory year to date.*/
\     [SalesLastYear],/*Sales in the territory the previous year.*/
      [CostYTD],/*Business costs in the territory year to date.*/
      [CostLastYear],/*Business costs in the territory the previous year.*/
      [rowguid],/*ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.*/
      [ModifiedDate] /*Date and time the record was last updated.*/
FROM  [Sales].[SalesTerritory]  /*Sales territory lookup table.*/
 
/* notice anything wrong? Yeah. All your Sales last year have been zeroed. a pesky backslash has crept in. You just put that backslash in some important code that is calculating your sales commission or your sales performance. What happens? Maybe some salesman loases commission. Maybe someone loses his job as a result. Whose fault is it? You'd better get checking! */
 
/* now take out the backslash and observe the difference. Yes, far better sales figures */
 
/* so what is going on here?
Well. It turns out that the parser is treating the \ as a currency symbol. No, you won't find a currency called the back-slash, but this is the wacky world of Microsoft. To add to the confusion, with money input, if you leave out the value, it assumes that you want to enter 0 back-slashes. It also allows as many spaces as you like between the currency symbol and the value, even if it exists.*/
 
Select  \-0-\  1-0-1    --Star wars fighters
 
 
/*
---------------------
-2.00
 
(1 row(s) affected)*/
 
Select\   ThisisVerySilly
 

/*
ThisisVerySilly
---------------------
0.00
 
(1 row(s) affected)*/
 
/*

This is such an insidious bug that I feel it is important to warn you about it. I believe it has always been in SQL Server but I reckon it is worth running the occasional check on your code to make sure that a backslash hasn't crept in when you're sleepily typing in code late in the day. */;

Comments

Posted by Steve Jones on 29 July 2010

You need some real work to do. This is crazy.

Posted by Peter Edmunds on 31 July 2010

The bug is crazy, Phil keeps us sane.

I've never met anyone one else who could be asked to write a

select star wars fighters

and not produce Msg 102, Level 15, State 1, Line 1 ...

Posted by ta.bu.shi.da.yu on 1 August 2010

Nobody sane would ever advocate executing smileys! Smileys are good and noble emoticons who show that we are joking and stop emails from causing ill feelings towards others.

Say "no" to smiley abuse in SQL Server.

Posted by ta.bu.shi.da.yu on 1 August 2010

P.S. There is such a thing as the universal currency symbol, it's just not on our keyboards. It is ¤, or in html it's ¤ and in unicode it is CURRENCY SIGN (U+00A4).

Whether that is why Microsoft does this or not, I don't know. However, if you check the Microsoft compatibility tables, you'll see the following:

msdn.microsoft.com/.../bb510680.aspx

"The backslash character (\) is accepted in all string to money conversions in all languages. ISNUMERIC would return true when \ is used as a currency symbol.

"For databases on versions of SQL Server earlier than SQL Server 2005, this new behavior breaks indexes and computed columns that depend on an ISNUMERIC return value that contains \ and for which the language is neither Japanese nor Korean."

What is the reason for this? Well, it's because in Korean the reverse solidus (or backslash) is Unicode character REVERSE SOLIDUS (U+005C) in the Latin block. However, in code page 932 (JIS) 0x005C is the yen symbol, and in code page 949 (KSC) it is the won symbol.

Michael Kaplan gives a lot more info on this, btw. See blogs.msdn.com/.../479561.aspx for more details.

Posted by Charles Kincaid on 2 August 2010

I just tried "SELECT \" on 2000.  I get:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near '\'.

So it's new in 2005

Posted by Phil Factor on 2 August 2010

Charles

You're right. I just checked myself. SQL Server 2000 handles it correctly, producing an error.

I rechecked on 2005 and 2008 just to make sure I hadn't been dreaming, and it seems consistent in both cases, producing the results I reported.

Posted by weitzera on 2 August 2010

I can think of no good reason why I should be allowed to execute SELECT $345 let alone SELECT \ 0,0-\ or any other currency designator (Whether you use a ',' or a '.' as your decimal point should be configurable, but I don't think those should be interchangeable due to the frequency of values like this: 12,345.67)

It could be handy if you were trying to build dynamic sql based on user input, but wait a second, I think there are other problems with that...  I'm fine with ISNUMERIC and CONVERT accepting these _in_ STRINGS but they have no place in our code.  (Of course with convert, you'll have to break your expressions down to their atomic parts, but again, you're not dynamically executing arbitrary input from your users, are you?)

Leave a Comment

Please register or log in to leave a comment.