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)";