Groups > comp.databases.ms-sqlserver > #1225

Re: Escape Characters in Strings

Path	csiph.com!usenet.pasdenom.info!goblin3!goblin1!goblin.stu.neva.ru!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver, microsoft.public.sqlserver.programming
Subject	Re: Escape Characters in Strings
Date	Wed, 22 Aug 2012 07:33:53 +0000 (UTC)
Organization	Erland Sommarskog
Lines	34
Message-ID	<XnsA0B7614C3AD91Yazorman@127.0.0.1> (permalink)
References	<n03838l2qs18qe540g4fe6j3stemkeo5pr@4ax.com>
Mime-Version	1.0
Content-Type	text/plain; charset=us-ascii
Content-Transfer-Encoding	7bit
Injection-Date	Wed, 22 Aug 2012 07:33:53 +0000 (UTC)
Injection-Info	mx04.eternal-september.org; posting-host="1bbf89cf6d97a98086f02eab6f51f760"; logging-data="15960"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/jSCnkHuWDCvS+cLVTyy2V"
User-Agent	Xnews/2005.10.03 Mime-proxy/1.4.c.4 (Win32)
Cancel-Lock	sha1:cel+3ZZ4meSP9Qt+AwcGDIm0WbQ=
Xref	csiph.com comp.databases.ms-sqlserver:1225

Cross-posted to 2 groups.

Show key headers only | View raw

Gene Wirchenko (genew@ocis.net) writes:
> 
>      Does SQL Server have any string escape characters besides
> doubling of quotation marks as in
>           'This is a single quotation mark('').'
>           "This is a double quotation mark("")."
> I will have text which could contain both.

Note that " is not a string delimiter, it is an identifier delimiter. 
...unless QUOTED_IDENTIFIER is off, in which case it is a string delimiter,
but that's a legacy setting you should stay away from.

And, no, there is no other choice but double the single quotes. Or double 
the double quotes or right brackets in an identifier.

>      Why, yes, I am sanitising input.  It is from a Web browser so I
> do not see how I can avoid using sanitising.  If there is such a
> solution, please let me know.

It's not clear to me why want to sanitise. You are not wrting code like:

  sSQL = "SELECT ... FROM Orders WHERE CustomerID = " & custid_field

are you? As long as you pass all user input as parameters in a proper way, 
there is no need to modify the user input from a strict SQL perspective.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Escape Characters in Strings Gene Wirchenko <genew@ocis.net> - 2012-08-21 15:39 -0700
  Re: Escape Characters in Strings Erland Sommarskog <esquel@sommarskog.se> - 2012-08-22 07:33 +0000
    Re: Escape Characters in Strings Gene Wirchenko <genew@ocis.net> - 2012-08-22 09:10 -0700
      Re: Escape Characters in Strings Erland Sommarskog <esquel@sommarskog.se> - 2012-08-22 21:14 +0200
        Re: Escape Characters in Strings Gene Wirchenko <genew@ocis.net> - 2012-08-22 13:53 -0700
          Re: Escape Characters in Strings Erland Sommarskog <esquel@sommarskog.se> - 2012-08-22 23:35 +0200
            Re: Escape Characters in Strings Gene Wirchenko <genew@ocis.net> - 2012-08-22 17:38 -0700
              Re: Escape Characters in Strings Erland Sommarskog <esquel@sommarskog.se> - 2012-08-23 10:22 +0000
          Re: Escape Characters in Strings "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-08-23 07:05 -0400
            Re: Escape Characters in Strings Erland Sommarskog <esquel@sommarskog.se> - 2012-08-23 23:13 +0200
              Re: Escape Characters in Strings "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-08-23 18:22 -0400
                Re: Escape Characters in Strings Erland Sommarskog <esquel@sommarskog.se> - 2012-08-24 07:40 +0000
                Re: Escape Characters in Strings "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-08-24 06:16 -0400

csiph-web