Tackling Apostrophes in SQL String with C#

Apostrophes always screws up SQL string that contains inputs from user. The easiest way to deal with this is to checkout each string input from user and replace single apostrophes with two apostrophes.

Utility.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ApostrphesTrial
{
public static class Utility
{
public static string EscapeApostrophes(string input)
{
return input.Replace("'", "''");
}
}
}

Use this “EscapeApostrophes” Method each time with user input (string) to create a SQL string for database interaction.

string userName = Utility.EscapeApostrophes(userNameTextBox.Text);

Thanks
A Rahim Khan

Advertisements
    • Jon
    • August 27th, 2010

    Hi Rahim,

    How would I use the EscapeApostrophes in the following code for the ProductName and ProdDescription? I can not make it work. Thanks ahead.

    ————-

    private void UpdateMySQL(string strMPTProductID)
    {

    SqlConnection objConnection = new SqlConnection();
    SqlDataReader objDataReader = null;
    SqlCommand objCommand = new SqlCommand();

    MySqlConnection objMySqlConnection = new MySql.Data.MySqlClient.MySqlConnection();
    MySqlCommand objMySqlCommand = new MySqlCommand();

    int intTotRecsFound = 0;
    byte byInactive = 0;
    byte byAllowPurchases = 0;

    string strSQL = ” SELECT ProductName” +
    ” product_sku, ” +
    ” ProductDesc, ” +
    ” ProductPrice, ” +
    ” ProductPrice, ” +
    ” ProductPrice, ” +
    ” Inactive, ” +
    ” product_weight, ” +
    ” product_width, ” +
    ” product_height, ” +
    ” ManufacturerID, ” +
    ” ProductName, ” +
    ” ProductDesc, ” +
    ” NotForSaleOnline ” +
    ” From Products ” +
    ” Where product_sku = ‘” + strMPTProductID + “‘”;

    try
    {

    objMySqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings[“MySqlConnection”].ConnectionString;
    objMySqlConnection.Open();

    objConnection.ConnectionString = ConfigurationManager.ConnectionStrings[“SQLConnection”].ConnectionString;
    objConnection.Open();

    objCommand = new SqlCommand(strSQL, objConnection);

    objDataReader = objCommand.ExecuteReader();

    if (objDataReader.HasRows)
    {
    while (objDataReader.Read())
    {
    strSQL = “select count(productid) from isc_products where prodcode = ‘” + objDataReader[“product_sku”].ToString() + “‘”;

    //Response.Write(“strSQL = ” + strSQL);

    objMySqlCommand = new MySqlCommand(strSQL, objMySqlConnection);

    intTotRecsFound = Convert.ToInt32(objMySqlCommand.ExecuteScalar());

    if (Convert.ToBoolean(objDataReader[“Inactive”]) == true)
    {
    byInactive = 0;
    }
    else
    {
    byInactive = 1;
    }

    if (Convert.ToBoolean(objDataReader[“NotForSaleOnline”]) == true)
    {
    byAllowPurchases = 0;
    }
    else
    {
    byAllowPurchases = 1;
    }

    if (intTotRecsFound > 0)
    {

    strSQL = ” update isc_products set ” +
    ” prodname = ‘” + objDataReader[“ProductName”].ToString() + “‘,” +
    ” prodtype = ‘1’,” +
    ” proddesc = ‘” + objDataReader[“ProductDesc”].ToString() + “‘,” +
    ” prodvendorid = ‘1’,” +
    ” prodprice = ” + objDataReader[“ProductPrice”].ToString() + “,” +
    ” prodretailprice = ” + objDataReader[“ProductPrice”].ToString() + “,” +
    ” prodsaleprice = ” + objDataReader[“ProductPrice”].ToString() + “,” +
    ” prodistaxable = ‘1’,” +
    ” prodvisible = ” + byInactive.ToString() + “,” +
    ” prodrelatedproducts = ‘-1’,” +
    ” prodweight = ” + objDataReader[“product_weight”].ToString() + “,” +
    ” prodallowpurchases = ” + byAllowPurchases.ToString() + “,” +
    ” prodwrapoptions = ‘0’,” +
    ” prodeventdaterequired = ‘0’,” +
    ” prodeventdatefieldname = ‘Delivery Date’,” +
    ” prodeventdatelimited = ‘0’,” +
    ” prodeventdatelimitedtype = ‘0’,” +
    ” prodeventdatelimitedstartdate = ‘0’,” +
    ” prodeventdatelimitedenddate = ‘0’,” +
    ” import_status = ‘2’” +

    ” where prodcode = ‘” + objDataReader[“product_sku”].ToString() + “‘”;

    Response.Write(strSQL);

    }
    else
    {
    strSQL = ” insert into isc_products (” +
    “prodname, prodtype, prodcode, proddesc, prodvendorid, prodprice, prodretailprice, prodsaleprice, prodistaxable, prodvisible, ” +
    “prodrelatedproducts, prodweight, prodallowpurchases, prodwrapoptions, prodeventdaterequired, prodeventdatefieldname, ” +
    “prodeventdatelimited, prodeventdatelimitedtype, prodeventdatelimitedstartdate, prodeventdatelimitedenddate, import_status) ” +
    ” values (” +
    “‘” + objDataReader[“ProductName”].ToString() + “‘,” +
    “1” + “,” +
    “‘” + objDataReader[“product_sku”].ToString() + “‘,” +
    “‘” + objDataReader[“ProductDesc”].ToString() + “‘,” +
    “1” + “,” +
    objDataReader[“ProductPrice”].ToString() + “,” +
    objDataReader[“ProductPrice”].ToString() + “,” +
    objDataReader[“ProductPrice”].ToString() + “,” +
    “1” + “,” +
    byInactive.ToString() + “,” +
    “-1” + “,” +
    objDataReader[“product_weight”].ToString() + “,” +
    byAllowPurchases.ToString() + “,” +
    “0” + “,” +
    “0” + “,” +
    “‘Delivery Date’,” +
    “0” + “,” +
    “0” + “,” +
    “0” + “,” +
    “0” + “,” +
    “1” + “)”;

    Response.Write(strSQL);

    }

    objMySqlCommand = new MySqlCommand(strSQL, objMySqlConnection);
    objMySqlCommand.ExecuteNonQuery();

    }
    }
    }
    catch (Exception eUpdateMySQL)
    {
    lblAlertMessage.Text = “Error: UpdateMySQL=” + eUpdateMySQL.Message.ToString();
    }
    finally
    {
    objConnection.Close();
    }

    objConnection = null;
    }

    }

    • try it like

      
      Utility.EscapeApostrophes(objDataReader["ProductName"].ToString()) + "," + Utility.EscapeApostrophes(objDataReader["ProductDesc"].ToString())
      

      One more thing, MySQL has no problem with Apostrophe Character. hope this will work for you.

    • Jon
    • August 28th, 2010

    Thanks Rahim. I can’t seem to make it work. It keeps giving me an error about the apostrophe when it tries to build the SQL insert statement. :( I’ll be glad to make a donation to you if you can help me get this working! :)

    I’m mainly concerned about the ProductName.

    Do I have to write this in a different way because the insert statement is being built from a query instead of user input?

    public static class Utility
    {
    public static string EscapeApostrophes(string input)
    {
    return input.Replace(“‘”, “””);
    }
    }

    • Jon
      No, you don’t have to change anything in Utility Class. What it does is just replace each Apostrophe Character with two.For example, objDataReader[“ProductName”] has value like “APPLE’S”, Utility.EscapeApostrophes(objDataReader[“ProductName”].ToString()) will change it into “APPLE”S”.

      you may give try on direct approach like

      objDataReader[“ProductName”].ToString().Replace(“‘”,”””)

      hope you will have it working now.

      Download source code. It is done in Visual Studio 2008.

      Thanks

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: