You could easily find out how to escape the apostrophe and other things, for example:
http://www.sqlteam.com/article/apostrophes-and-quotation-marks-in-sql-server[
^], or after all:
http://bit.ly/x5qYJi[
^].
This is almost irrelevant though. You should think about very different thing: how come such text as "O'Connel" can get into your query? I can tell you: it should never appear in a query. What, do you hard-code a person's name in the source code? No? Then you probably compose a command string from during run time, probably even from interactive user input.
You should never do this. You need to use
parametrized queries. Please see:
http://msdn.microsoft.com/en-us/library/ms254953.aspx[
^].
If you use parametrize queries, the problem of the apostrophe won't even come into consideration: you assign actual values to the parameters, which are types. In the case of string, you supply a string value as it is. Even with a null character inside. :-)
If my arguments are not yet convincing to you, think about the security: composing the text of the query from the input is simply prohibitively
dangerous. Please read about the
danger of
SQL Injection and the role of
parametrized statements:
http://en.wikipedia.org/wiki/SQL_injection[
^].
—SA