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

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

Re: Escape Characters in Strings

Show all headers | View raw

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

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