Escape the '\' character

I noticed that the following character needs escaping with another \ but
only if it is not followed by another character
What is the rule
Are there any other character with that need escaping except ' (single
quote) and wildcard characters when used with LIKE
Thank you,
SamuelA lesser known fact about the Transact-SQL parser is that a backslash ('')
is a continuation character (like the C programming language). When a
backslash is found at the end of a line in a literal string, the backslash
and line terminator characters are ignored. Specifying the additional
backslash isn't technically an escape, it's just another character in the
literal string. For example
SELECT 'test\
ing'
-- result is 'testing'
SELECT 'test\\
ing'
-- result is 'test\ing'
BTW, I first learned of this issue when helping a user who was obfuscating
data. The backslash and newline characters were getting dropped when the
algorithm introduced a backslash at the end of a line. This is yet one
more reason that one should always use parameteritized SQL statements.
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>I noticed that the following character needs escaping with another \ but
>only if it is not followed by another character
> What is the rule
> Are there any other character with that need escaping except ' (single
> quote) and wildcard characters when used with LIKE
> Thank you,
> Samuel
>|||very interesting indeed,
thank you
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>A lesser known fact about the Transact-SQL parser is that a backslash ('')
>is a continuation character (like the C programming language). When a
>backslash is found at the end of a line in a literal string, the backslash
>and line terminator characters are ignored. Specifying the additional
>backslash isn't technically an escape, it's just another character in the
>literal string. For example
> SELECT 'test\
> ing'
> -- result is 'testing'
> SELECT 'test\\
> ing'
> -- result is 'test\ing'
> BTW, I first learned of this issue when helping a user who was obfuscating
> data. The backslash and newline characters were getting dropped when the
> algorithm introduced a backslash at the end of a line. This is yet one
> more reason that one should always use parameteritized SQL statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>|||So you never need to escape a letter except the single quote and except
wildcard character when using LIKE
What I still don't understand why if I type "A\" & VBCR & "B" I get
A
B
And if I type ' ' as the last character in the line in a multi line Textbox
it will NOT ignore it and I will get
A\
B
Why is that?
Thanks,
Samuel Shulman
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>A lesser known fact about the Transact-SQL parser is that a backslash ('')
>is a continuation character (like the C programming language). When a
>backslash is found at the end of a line in a literal string, the backslash
>and line terminator characters are ignored. Specifying the additional
>backslash isn't technically an escape, it's just another character in the
>literal string. For example
> SELECT 'test\
> ing'
> -- result is 'testing'
> SELECT 'test\\
> ing'
> -- result is 'test\ing'
> BTW, I first learned of this issue when helping a user who was obfuscating
> data. The backslash and newline characters were getting dropped when the
> algorithm introduced a backslash at the end of a line. This is yet one
> more reason that one should always use parameteritized SQL statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:OAfNhcAgGHA.1456@.TK2MSFTNGP04.phx.gbl...
>|||> And if I type ' ' as the last character in the line in a multi line
> Textbox it will NOT ignore it and I will get
> A\
> B
I would expect that behavior if you using a parameterized SQL Statement.
However, if the SQL statement string is constructed like the example below,
you should get 'AB':
strSql = "INSERT INTO MyTable VALUES('" & _
Request("textBoxValue") & _
"')")
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
> So you never need to escape a letter except the single quote and except
> wildcard character when using LIKE
> What I still don't understand why if I type "A\" & VBCR & "B" I get
> A
> B
> And if I type ' ' as the last character in the line in a multi line
> Textbox it will NOT ignore it and I will get
> A\
> B
> Why is that?
> Thanks,
> Samuel Shulman
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:e2gVaHLgGHA.2476@.TK2MSFTNGP03.phx.gbl...
>|||Can you please define what parameterized statement
Does is matter how the variable is assigned the value?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23Pg3pPMgGHA.1856@.TK2MSFTNGP03.phx.gbl...
> I would expect that behavior if you using a parameterized SQL Statement.
> However, if the SQL statement string is constructed like the example
> below, you should get 'AB':
> strSql = "INSERT INTO MyTable VALUES('" & _
> Request("textBoxValue") & _
> "')")
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:Ou3pyzLgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>|||> Can you please define what parameterized statement
A parameterized statement contains parameter markers instead of literal
values. Actual parameter values are substituted for parameter markers at
query execution time. Parameterized statements have several advantages,
such as improved security, no need for special quote handling and execution
plan reuse.
Below is an ADO example. ADO.NET has a slightly different object model but
the basic principle is the same and you can use named parameter markers with
the ADO.NET SqlClient provider.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = "INSERT INTO MyTable VALUES(?)"
Set textBoxParameter = command.CreateParameter( _
"@.textBoxParameter", adVarchar, adParamInput, 50,
Request("textBoxValue"))
command.Parameters.Append textBoxParameter
Set Rs = command.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OEqmw%23MgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Can you please define what parameterized statement
> Does is matter how the variable is assigned the value?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23Pg3pPMgGHA.1856@.TK2MSFTNGP03.phx.gbl...
>sql