SQL Server - escaping single quote

Asked By Andy

26-Oct-07 08:47 PM

Hi,
I call sql statement from c++ code using ADO.
Is there a way to prepare sql query before execution
to be sure there are no single quotes etc?
thanks

26-Oct-07 09:45 PM

Your best bet is to use parameterized SQL statements and procs.  This
eliminates the need to escape quotes, format dates and is more secure than
building and executing SQL statement strings.

--
Hope this helps.

Dan Guzman
SQL Server MVP

27-Oct-07 12:09 AM

so when I use parameterized query (but without stored proc) will it take
care of escaping problem?
thanks

27-Oct-07 03:41 AM

Yep. And you also get better protection against SQL Injection. And less cache bloating (better
re-use of your execution plans).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

27-Oct-07 06:39 AM

Andy (kc2ine@yahoo.com) writes:

There will not be any escaping problem, because there is nothing to escape.




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

27-Oct-07 07:53 AM

Yes, no escaping problem.  With a parameterized query, the client API
inserts the actual values into the TDS protocol stream in native format.  A
regular query needs to be parsed by SQL Server and that's where the escaping
and dateformat issues are.

--
Hope this helps.

Dan Guzman
SQL Server MVP

27-Oct-07 10:21 AM

the only problem I think with that is that I generate query automatically in
a loop concatenating all these fields which there is huge amount, so with
you solution I'd have to go through each of the field and prepare parameter
for...

How about using "replace" statement in query for single quotes?

27-Oct-07 10:46 AM

The replace needs to be done on the client side before the query is sent to
SQL Server.  You can't use a Transact-SQL REPLACE function to escape quotes
because it's a catch-22; the quotes need to be escaped/replaced before the
REPLACE function can be parsed.

I wouldn't think it would be much more work to create parameters in the
loop.  You can find a simple C++ ADO parameterize command example at
http://support.microsoft.com/kb/181734.

--
Hope this helps.

Dan Guzman
SQL Server MVP

28-Oct-07 10:19 PM

Andy (kc2ine@yahoo.com) writes:

It's a bad idea, because it only addresses one problem. Another problem
with inlining parameter is that each query will be its own entry in the
procedure cache, and each query will be compiled each time. Parameterised
queries give you better performance.

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

27-Oct-07 10:10 PM

thanks fo samples, but looks like I'd need to buld parameter for each of the
fields and I have more then 200 of them...
// Create Parameter Object.
Param1 = Cmd1->CreateParameter( _bstr_t(L""),
ADODB::adInteger,
ADODB::adParamInput,
-1,
_variant_t( (long) 5) );
Param1->Value = _variant_t( (long) 5 );
Cmd1->Parameters->Append( Param1 );

28-Oct-07 06:02 AM

That's correct. Why would that be more difficult than concatenating
200 values into a string (including the necessary validation, type
conversions, quoting and other issues)?

I would still question the wisdom of a 200 column design. Are you
familiar with the principles of normalization? Are you sure they have
been applied correctly in your case?

--
David Portas

28-Oct-07 10:19 PM

Andy (kc2ine@yahoo.com) writes:

And? Either the parameters are similar in kind, in which you can do this
in a loop.

Or they are of different data types, in which case you will need to treat
all 200 parameters differently, no matter which method you use. If you
interpolate the parameter values into the string, you need to know which
to put quotes around and which not. You need to know which are dates, so
that you convert them to a safe format (YYYYMMDD). You need to convert
integer and binary values to string etc.

And if you use parameters, you may be better of to put the parameter
information in an array, and then run a loop that runs CreateParameter
taking the values from the array.

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

28-Oct-07 09:25 AM

Since you are currently concatenating string values 200 times, I would think
you could create parameters in a some sort of iterative fashion too.
Perhaps some helper functions/classes can facilitate this.

--
Hope this helps.

Dan Guzman
SQL Server MVP

28-Oct-07 12:43 PM

yes, you're guys right. I treat the values anyway as strings and I let the
SQL Server to convert them as needed so I can really in fact use one
parameter formula for all of them.
I comletely agree that 200 columns table is a very bad idea but nothing I
can do about it, it's old system.

thanks for tips