Post
Ask Question

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