F
Fred Chateau
What is the proper way of handling apostrophes in parameter strings for
stored procedures?
stored procedures?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Roy Harvey
Replace each single quote character ' the is embedded in the string
with TWO of that character.
SELECT 'O''Hara'
with TWO of that character.
SELECT 'O''Hara'
E
Erland Sommarskog
Fred said:What is the proper way of handling apostrophes in parameter strings for
stored procedures?
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, (e-mail address removed)
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
W
William \(Bill\) Vaughn
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)
-----------------------------------------------------------------------------------------------------------------------
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)
-----------------------------------------------------------------------------------------------------------------------