SQL Syntax Error: Remove/Disclude Apostrophe's?

Question

I use the below code to update a Business' information on one of my Windows Forms. When a user puts the Business name in txtBusName as something like "Sandy's Place" I receive Incorrect Syntax near ';'. Unclosed quotation mark after the character string ';'.

What is the best way to handle this issue?

conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();

mskZip.TextMaskFormat = MaskFormat.ExcludePromptAndLiterals;
string zip = mskZip.Text;
mskZip.TextMaskFormat = MaskFormat.IncludeLiterals;
mskMailZip.TextMaskFormat = MaskFormat.ExcludePromptAndLiterals;
string mailzip = mskMailZip.Text;
mskMailZip.TextMaskFormat = MaskFormat.IncludeLiterals;
mskPhone.TextMaskFormat = MaskFormat.ExcludePromptAndLiterals;
string phone = mskPhone.Text;
mskPhone.TextMaskFormat = MaskFormat.IncludeLiterals;
mskFax.TextMaskFormat = MaskFormat.ExcludePromptAndLiterals;
string fax = mskFax.Text;
mskFax.TextMaskFormat = MaskFormat.IncludeLiterals;


cmd.CommandText = "Update Business SET Name='" + txtBusName.Text + "', ContactName='" + txtContName.Text +
                "', Address='" + txtAddr1.Text + "', City='" + txtCity.Text + "', State='" + cmbState.Text + "', Zip=" + ((zip=="")?"NULL":zip) + ", " +
                "MailAddress='" + txtMailAddr1.Text + "', MailCity='" + txtMailCity.Text + "', MailState='" + cmbMailState.Text +
                "', MailZipcode=" + ((mailzip == "") ? "NULL" : mailzip) + ", Latitude=" + ((txtLat.Text == "") ? "NULL" : txtLat.Text) + ", Longitude=" + ((txtLong.Text == "") ? "NULL" : txtLong.Text) + ", Phone=" +
                ((phone == "") ? "NULL" : phone) + ", Fax=" + ((fax == "") ? "NULL" : fax) + ", Email='" + txtEmail.Text + "' " +
                "WHERE BusinessID=" + busID + " AND Status='A';";

cmd.ExecuteNonQuery();

MessageBox.Show("Database updated successfully.");
this.Close();

Answer 1

You need to use a parameterized query like this

cmd.CommandText = 
        "Update Business SET Name=@name, ContactName=@contact, Address=@address, " + 
                "City=@city, State=@state, Zip=@zip, " +
                "MailAddress=@mail, MailCity=@ecity, MailState=@estate, " +
                "MailZipcode=@ezip, Latitude=@lat, Longitude=@lng, Phone=@phone, " +
                "Fax=@fax, Email=@email " +
                "WHERE BusinessID=@busID AND Status='A'";

cmd.Parameters.AddWithValue("@name", txtBusName.Text);
cmd.Parameters.AddWithValue("@contact", txtContName.Text); 
cmd.Parameters.AddWithValue("@address", txtAddr1.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text);
cmd.Parameters.AddWithValue("@state", cmbState.Text);

SqlParameter p1 = cmd.Parameters.Add("@zip", SqlDbType.NVarChar);
if(zip == "") p1.Value = DBNull.Value; else p1.Value = zip;

cmd.Parameters.AddWithValue("@mail",  txtMailAddr1.Text);
cmd.Parameters.AddWithValue("@ecity", txtMailCity.Text);
cmd.Parameters.AddWithValue("@estate", cmbMailState.Text);

p1 = cmd.Parameters.Add("@ezip", SqlDbType.NVarChar);
if (mailzip == "") p1.Value = DBNull.Value; else p1.Value = mailzip;

p1 = cmd.Parameters.Add("@lat", SqlDbType.NVarChar);
if (txtLat.Text == "") p1.Value = DBNull.Value; else p1.Value = txtLat.Text;

p1 = cmd.Parameters.Add("@lng", SqlDbType.NVarChar);
if (txtLong.Text == "") p1.Value = DBNull.Value; else p1.Value = txtLong.Text;

p1 = cmd.Parameters.Add("@phone", SqlDbType.NVarChar);
if (phone == "") p1.Value = DBNull.Value; else p1.Value = phone;

p1 = cmd.Parameters.Add("@fax", SqlDbType.NVarChar);
if (fax == "") p1.Value = DBNull.Value; else p1.Value = fax;

cmd.Parameters.AddWithValue("@email", txtEmail.Text );
cmd.Parameters.AddWithValue("@busID", busID); 

The article linked above is worth to read from start to end, however, to summarize, using a parameterized query you let the work to format the single quotes (and also numeric decimals and date literals) to the framework code that knows better than me and you how to deal with that strings and also in this way you avoid the dreaded Sql Injection problem that could expose your database to hacking

NOTE: I don't know the actual datatype for the columns that should be set to null, so I have assumed that they are all NVARCHAR. If this is not the case then you should replace the SqlDbType with the appropriate value.

Answer 2

The best way is to use a parameterised query.

eg: http://www.aspsnippets.com/articles/Using-Parameterized-queries-to-prevent-SQL-Injection-Attacks-in-SQL-Server.aspx

Answer 3

Please use SqlParameter object and not a string concatenation like that. Something like:

    string sql = "Update Business SET Name=@Name, ContactName=@ContactName, Address=@Address WHERE BusinessID=@BusinessID AND Status='A';";
    System.Data.SqlClient.SqlParameter[] par = new System.Data.SqlClient.SqlParameter[4];
    par[0] = new System.Data.SqlClient.SqlParameter("@Name", txtBusName.Text);
    par[1] = new System.Data.SqlClient.SqlParameter("@ContactName", txtContName.Text);
    par[2] = new System.Data.SqlClient.SqlParameter("@Address", txtAddr1.Text);
    par[3] = new System.Data.SqlClient.SqlParameter("@BusinessID", busID);

        System.Data.SqlClient.SqlCommand com = new System.Data.SqlClient.SqlCommand(sql, SQL_CONNECTION);
        com.Parameters.AddRange(par);
        com.ExecuteNonQuery();

Too many parameters you had so didn't write them all :)

Using this approach will take care of special characters like the apostrophe for you plus make code look cleaner, more readable and more secure.

Answer 4

Escape single quotes in SQL by using double single quotes, so Sandy''s place should work.

I would strongly advice on using query parameters instead of stringing together your own query, this fixes a potential security risk (SQL injection) and most probably problems like your quotes as well.