Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1224 > unrolled thread

Escape Characters in Strings

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1224 — Escape Characters in Strings

Dear SQLers:

     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.

     While I am at it, I want to handle any other special character
sequences.

     I am referring to plain string values as would be stored in a
column, not LIKE strings, etc.

     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.

Sincerely,

Gene Wirchenko

[toc] | [next] | [standalone]

#1225

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

[toc] | [prev] | [next] | [standalone]

#1226

On Wed, 22 Aug 2012 07:33:53 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

>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.

     My question was really whether there are any other escape
characters?  Are there?
>
>>      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.

     No.  I will be passing parameters, but I need to be sure that
they are properly delimited and escaped.  For example, if I do not
escape quotes, it may allow trouble.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#1227

Gene Wirchenko (genew@ocis.net) writes:
>      My question was really whether there are any other escape
> characters?  Are there?

No.

>      No.  I will be passing parameters, but I need to be sure that
> they are properly delimited and escaped.  For example, if I do not
> escape quotes, it may allow trouble.
 
As long as you don't build SQL strings from input data, there is no trouble. 
No need to delimit, no need to escape. Again from a strict SQL perspective. 
There may be business rules requiring you to deal with certain characters. 
But given that the apostrophe is an essential character in English 
ortography, I don't think that the single quote is one these characters.


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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1228

On Wed, 22 Aug 2012 21:14:55 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      My question was really whether there are any other escape
>> characters?  Are there?
>
>No.

     That is good to know.  It has been hard to find such an answer
since my question is about a negative.  Thank you very much.

>>      No.  I will be passing parameters, but I need to be sure that
>> they are properly delimited and escaped.  For example, if I do not
>> escape quotes, it may allow trouble.
> 
>As long as you don't build SQL strings from input data, there is no trouble. 

     I will be building only statements that execute stored
procedures.  e.g.
           execute ExampleProc 'abc',1,2,3
or
           execute ExampleProc theString='abc',foo=1,bar=2,baz=3
Does that count?

     I will not be building any other type of statement.  No selects,
no inserts, no updates, etc.

>No need to delimit, no need to escape. Again from a strict SQL perspective. 
>There may be business rules requiring you to deal with certain characters. 
>But given that the apostrophe is an essential character in English 
>ortography, I don't think that the single quote is one these characters.

     Eh?  Would that not be exactly why I need to concern myself with
it?

     I had an computing instructor with the family name "O'Neil".  He
had words about companies that messed up orders as a result of his
name.  It was quite appropriate in an algorithms and data structures
course.  Sadly, I have seen many HTML books that show how to build
forms really easily and totally skip this gotcha.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#1229

Gene Wirchenko (genew@ocis.net) writes:
>      I will be building only statements that execute stored
> procedures.  e.g.
>            execute ExampleProc 'abc',1,2,3
> or
>            execute ExampleProc theString='abc',foo=1,bar=2,baz=3
> Does that count?
 
Yes, that counts, and you should not be doing it. Assuming that you are
using C#, it should look like this:

  cmd.CommandType = CommandType.StoredProcedure;
  cmd.CommandText = "dbo.ExampleProc";    // Always include schema!
  cmd.Parameters.Add("@theString", SqlDBTypes.VarChar, 20);
  cmd.Parameters["@theString"].Value = "abc";

This results in an RPC call which is more efficient. And there is no risk 
for SQL Injection.

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1230

On Wed, 22 Aug 2012 23:35:15 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      I will be building only statements that execute stored
>> procedures.  e.g.
>>            execute ExampleProc 'abc',1,2,3
>> or
>>            execute ExampleProc theString='abc',foo=1,bar=2,baz=3
>> Does that count?
> 
>Yes, that counts, and you should not be doing it. Assuming that you are
>using C#, it should look like this:

     Rats!  I would be using VBScript.  I hope the syntax is similar.

>  cmd.CommandType = CommandType.StoredProcedure;
>  cmd.CommandText = "dbo.ExampleProc";    // Always include schema!
>  cmd.Parameters.Add("@theString", SqlDBTypes.VarChar, 20);
>  cmd.Parameters["@theString"].Value = "abc";

     There appear to be pieces missing.  Presumably, the other
parameters get similar treatment in their declared order, and
something must state to go.

>This results in an RPC call which is more efficient. And there is no risk 
>for SQL Injection.

     I will do some hunting for SQL Server and RPC and see what it
gets me.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#1231

Gene Wirchenko (genew@ocis.net) writes:
>      Rats!  I would be using VBScript.  I hope the syntax is similar.

Yes. You can to the same thing in old ADO. The syntax and the names are 
somewhat different though.

Then again, I know we have discussed before, but VBscript and old ADO in 
2012 for new development is just crazy.

>      There appear to be pieces missing.  Presumably, the other
> parameters get similar treatment in their declared order, and
> something must state to go.

Yes, I only included the first parameter for brevity. You don't have to 
create them in the order they are declared in the procedure since you 
provide the parameter names.

And, yes, you need code to execute the command, but that is the same thing 
as when you use command type text; there is no difference in that regard.



-- 
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

[toc] | [prev] | [next] | [standalone]

#1232

Gene Wirchenko wrote:
> On Wed, 22 Aug 2012 21:14:55 +0200, Erland Sommarskog
> <esquel@sommarskog.se> wrote:
>
>> Gene Wirchenko (genew@ocis.net) writes:
>>>      My question was really whether there are any other escape
>>> characters?  Are there?
>>
>> No.
>
>     That is good to know.  It has been hard to find such an answer
> since my question is about a negative.  Thank you very much.
>
>>>      No.  I will be passing parameters, but I need to be sure that
>>> they are properly delimited and escaped.  For example, if I do not
>>> escape quotes, it may allow trouble.
>>
>> As long as you don't build SQL strings from input data, there is no
>> trouble.
>
>     I will be building only statements that execute stored
> procedures.  e.g.
>           execute ExampleProc 'abc',1,2,3

Then you're not using parameters. You are using dynamic sql to execute 
stored procedures. Very bizarre. Why not use ADO's innate ability to pass 
parameter values? I see from you're next message that you are using 
vbscript. So, assuming the procedure above returns no records, and that your 
opened connection variable is called "cn", the vbscript to execute the above 
procedure would be:

cn.ExampleProc "abc",1,2,3

Explanation: ADO (2.5 and higher) allows stored procedures to be treated as 
if they are methods of the connection object, allowing the parameter values 
to passed as if you were plassing arguments to a builtin method. No need to 
worry about escaping delimiters, etc.
If the procedure returns records, you simply add an instantiated recordset 
variable as the last argument:

set rs=createobject("adodb.recordset")
cn.ExampleProc "abc",1,2,3, rs
if not rs.eof then
etc.

So much simpler than building dynamic sql strings.

If the procedure has output parameters, or you wish to read the Return 
parameter, then use a command object, append parameter objects (look up the 
CreateParameter method) with the appropriate parameter-drection arguments, 
setting the parameter values as you add them, execute the procedure, consume 
any resultsets returned by the procedure, then read the values of the output 
and/or return values as needed. ADODB seems to ignore the names of the 
procedure's parameters, so you do need to add them in the correct order (the 
Return parameter must always be added first, followed by the user-defined 
parameters), as opposed to the ADO.Net method described by Erland.
I wrote a utility asp page to build the vbscript statements to create a 
command object and add parameter objects to for me. It is available at
http://common.mvps.org/barrowsb/ClassicASP_sp_code_generator.zip

[toc] | [prev] | [next] | [standalone]

#1233

Bob Barrows (reb01501@NOSPAMyahoo.com) writes:
> Very bizarre. Why not use ADO's innate ability to pass parameter values?
> I see from you're next message that you are using vbscript. So, assuming
> the procedure above returns no records, and that your opened connection
> variable is called "cn", the vbscript to execute the above procedure
> would be: 
> 
> cn.ExampleProc "abc",1,2,3
> 
> Explanation: ADO (2.5 and higher) allows stored procedures to be treated
> as if they are methods of the connection object, allowing the parameter
> values to passed as if you were plassing arguments to a builtin method.
> No need to worry about escaping delimiters, etc. 

Egads! I didn't know of that one. Unfortunately, I don't have an VB 
environment here at home, so I cannot try it.

But how does it work under the covers? I would guess that it runs .Refresh 
under the covers, but I found in the MDAC Books Online that it says:
"ADO will make a 'best guess' of parameter types.".

Neither of these two strategies are really appealing. .Refresh would be 
appealing if it cached the parameter profile, so that the metadata query 
was executed only once, but ADO does not seem to do that. And "best guess"
are like to cause problems when the guesses go wrong.

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1234

Erland Sommarskog wrote:
> Bob Barrows (reb01501@NOSPAMyahoo.com) writes:
>> Very bizarre. Why not use ADO's innate ability to pass parameter
>> values? I see from you're next message that you are using vbscript.
>> So, assuming the procedure above returns no records, and that your
>> opened connection variable is called "cn", the vbscript to execute
>> the above procedure would be:
>>
>> cn.ExampleProc "abc",1,2,3
>>
>> Explanation: ADO (2.5 and higher) allows stored procedures to be
>> treated as if they are methods of the connection object, allowing
>> the parameter values to passed as if you were plassing arguments to
>> a builtin method. No need to worry about escaping delimiters, etc.
>
> Egads! I didn't know of that one. Unfortunately, I don't have an VB
> environment here at home, so I cannot try it.
>
> But how does it work under the covers? I would guess that it runs
> .Refresh under the covers, but I found in the MDAC Books Online that
> it says: "ADO will make a 'best guess' of parameter types.".

I don't believe it runs Refresh, I've never seen any evidence of it when 
using Profiler.
Under the covers, a command object is created (similarly as to when the 
connection's Execute method is called) and parameter objects are appended, 
typed based on that "best guess", which uses the types (or subtypes given 
vbscript's restriction to Variant datatypes) of the passed values in making 
that guess. Yes, theoretically, the best guess could be wrong, but in my 
experience it never has been - at worst, an implicit conversion might be 
required. Of course, my environment has always been very simple. And as long 
as you properly type your VB objects, the chance of a bad guess that 
requires more than a conversion from varchar to nvarchar, or float to 
integer is slim.

[toc] | [prev] | [next] | [standalone]

#1235

Bob Barrows (reb01501@NOSPAMyahoo.com) writes:
> Under the covers, a command object is created (similarly as to when the 
> connection's Execute method is called) and parameter objects are
> appended, typed based on that "best guess", which uses the types (or
> subtypes given vbscript's restriction to Variant datatypes) of the
> passed values in making that guess. Yes, theoretically, the best guess
> could be wrong, but in my experience it never has been - at worst, an
> implicit conversion might be required. Of course, my environment has
> always been very simple. And as long as you properly type your VB
> objects, the chance of a bad guess that requires more than a conversion
> from varchar to nvarchar, or float to integer is slim. 
 
The area that would make me the most worried are dates, particularly if you 
have them in string fields. If they are passed as (n)varchar, conversion 
happens in SQL Server which can give a headache.

-- 
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

[toc] | [prev] | [next] | [standalone]

#1236

Erland Sommarskog wrote:
> Bob Barrows (reb01501@NOSPAMyahoo.com) writes:
>> Under the covers, a command object is created (similarly as to when
>> the connection's Execute method is called) and parameter objects are
>> appended, typed based on that "best guess", which uses the types (or
>> subtypes given vbscript's restriction to Variant datatypes) of the
>> passed values in making that guess. Yes, theoretically, the best
>> guess could be wrong, but in my experience it never has been - at
>> worst, an implicit conversion might be required. Of course, my
>> environment has always been very simple. And as long as you properly
>> type your VB objects, the chance of a bad guess that requires more
>> than a conversion from varchar to nvarchar, or float to integer is
>> slim.
>
> The area that would make me the most worried are dates, particularly
> if you have them in string fields. If they are passed as (n)varchar,
> conversion happens in SQL Server which can give a headache.

Right. They need to be passed as dates, either as the result of using 
CDate() or Dateserial() or literal dates (delimited with hash marks in 
vb/vbscript). 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web