sql ce escape '(single quote) C#

Question

I am writing some code to store names in a database. I limit the names to only certain characters, but last names are a challenge. Since some people have single quotes in their name (example O'Brian) I need to allow this. So I wrote a regex replace to replace the ' with a \' which I assumed should make the ' a literal. It works as far as replacement goes, but it still marks the end of the string, and I get the error

There was an error parsing the query. [ Token line number=1, token line offeset = 71, token in error=Brian]

I understand the error, the single quote marks the end of the string to be entered leaving the rest of the string Brian outside the quotes.

The code I am using:

Regex reg = new Regex("\'");
firstName = reg.Replace(firstName, "\\'");
lastName = reg.Replace(lastName, "\\'"):

Then the select query is built with string.format

sqlInsertObj.CommandText = string.Format("INSERT INTO childNameId (childFName, childLName) VALUES ('{0}', '{1}')", fName, lName);
 sqlInsertObj.ExecuteNonQuery();

This works for any entry, except when there is a quote in the name.

Answer 1

It's better to use parameterized sql queries, instead of building them with string concatenation. Documentation and an example can be found at MSDN.

If you insist on string concatenation escape it with a double single quote instead of \.

firstName = firstName.Replace("'", "''" );