How to Avoid Single Quote Exception in SQL

Avoid Single Quote Issue

   Most of Beginner facing problem in executing SQL query that contains varchar type fields..
for example : inserting string value to varchar type field in Sql query.. will raise "Unclosed quotation mark......." error.
-------Code ------------------------
string frstName="c-sharp's';
string lastName="corner";
//Here frstName contains single quote character.
string sqlQuery= "insert into TestTable(Firstname,lastname) value(frstName,lastName)";
//Excute Sql Query..


While Executing the above query will raise "Unclosed quotation mark after the character string" exception. To avoid this type of error replace ' with ''.

string sqlQuery= "insert into TestTable(Firstname,lastname) value(frstName.Replace("'", "''"),lastNameReplace("'", "''"))";

We can generalize the code to single Extension Method.  Here is the Extension Method for replacing ' with ''.

-----Code----------
 /// <summary>
/// Class use for replace single qoutes ' to ''
/// </summary>
public static class SqlString
{
/// <summary>
/// This Extension method will replace the single qoutes ' to ''..
/// So Sql single qoutes exception will never raise
/// </summary>
/// <param name="fieldvalue"></param>
/// <returns></returns>
public static string GetSqlString(this string fieldvalue)
{
if (fieldvalue != null)
return fieldvalue.Replace("'", "''");
return fieldvalue;
}
}


Code Implementation:

  string sqlQuery= "insert into TestTable(Firstname.GetSqlString(),lastname.GetSqlString()) value(frstName,lastName)";



Next Recommended Reading Single Responsibility Principle – C#