Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail From: "Bob Barrows" Newsgroups: comp.databases.ms-sqlserver Subject: Re: Escape Characters in Strings Date: Thu, 23 Aug 2012 07:05:39 -0400 Organization: A noiseless patient Spider Lines: 62 Message-ID: References: <2s0a38toaa3n1th1e42kmat8n0ei6v9bah@4ax.com> Injection-Date: Thu, 23 Aug 2012 11:05:35 +0000 (UTC) Injection-Info: mx04.eternal-september.org; posting-host="bd099084a2caba1d0ebe690afacb9523"; logging-data="29068"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19QNnivrDS6I8iG5ourn4o3GZxY/fij0os=" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157 X-RFC2646: Format=Flowed; Original X-Newsreader: Microsoft Outlook Express 6.00.2900.5931 Cancel-Lock: sha1:QekulhunHuwo6s92hrl5EeSUcq8= X-Priority: 3 X-MSMail-Priority: Normal Xref: csiph.com comp.databases.ms-sqlserver:1232 Gene Wirchenko wrote: > On Wed, 22 Aug 2012 21:14:55 +0200, Erland Sommarskog > 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