Sql ce escape '(single quote) C #

Question

I am writing code to store names in a database. I restrict names to specific characters only, but surnames are a challenge. Since some people have single quotes in their name (O'Brian example), I need to resolve this. So I wrote a replacement for the regular expression to replace "with a", which, as I supposed, should make it a "literal". It works before replacement, but it still marks the end of the line, and I get an error

An error occurred while parsing the request. [Token line number = 1, line token - 71, error token = Brian]

I understand the error, a single quotation mark marks the end of the entered string, leaving the rest of the Brian string outside the quotes.

The code I'm using is:

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

Then the selection request is created using 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 title.

Answer 1

It is better to use parameterized sql queries rather than building them using string concatenation. Documentation and example can be found on MSDN .

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

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