What is the proper way of handling apostrophes in parameter strings for
stored procedures?

--
Regards,

Fred Chateau
fchateauAtComcastDotNet

Replace each single quote character ' the is embedded in the string
with TWO of that character.

SELECT 'O''Hara'

----------
O'Hara

Roy Harvey
Beacon Falls, CT

On Fri, 7 Sep 2007 19:09:59 -0500, "Fred Chateau" <fchateau (AT) 127 (DOT) 0.0.1>
wrote:

Fred Chateau (fchateau (AT) 127 (DOT) 0.0.1) writes:
If the question is how to it in application code, the answer is that it
should never be an issue. You should never send EXEC strings, but use
RPC calls and create parameters with CreateParameter, AddParameter or
whatever method your client API provides.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Ah, no. The Parameters passed to a stored procedure (or even a parameterized
query) can only safely be managed with a Command Object's Parameters
collection. In this case you pass the string (which might contain an
apostrophy) to the Parameter.Value property--ADO (or ADO.NET) handles the
issue (and several others) automatically. If you are in a position to use
the Replace method (changing single apostrophys for two), your code is
subject to SQL injection attacks--a very common failing.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
"Roy Harvey" <roy_harvey (AT) snet (DOT) net> wrote