SQL语法错误：删除/废除撇号？

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 ';'.

我使用以下代码更新我的一个Windows窗体上的业务信息。当用户将商业名称放在txtBusName中作为“Sandy's Place”之类的东西时，我会在';'附近收到错误的语法。字符串';'后面的未闭合引号。

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

4 个解决方案

#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

上面链接的文章值得从头到尾阅读，但总而言之，使用参数化查询，您可以将单引号（以及数字小数和日期文字）格式化为比我更了解的框架代码。你如何处理这些字符串，并以这种方式避免可怕的Sql注入问题，可能会使您的数据库暴露黑客

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.

注意：我不知道应该设置为null的列的实际数据类型，所以我假设它们都是NVARCHAR。如果不是这种情况，那么您应该用适当的值替换SqlDbType。

#2

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

通过使用双单引号来逃避SQL中的单引号，因此Sandy的位置应该有效。

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.

我强烈建议使用查询参数而不是将您自己的查询串在一起，这可以修复潜在的安全风险（SQL注入），并且很可能也会出现像引号这样的问题。

#3

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

请使用SqlParameter对象而不是像这样的字符串连接。就像是：

    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.

使用这种方法将为您提供像撇号这样的特殊字符，并使代码看起来更干净，更易读，更安全。

#4

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

例如：http：//www.aspsnippets.com/articles/Using-Parameterized-queries-to-prevent-SQL-Injection-Attacks-in-SQL-Server.aspx

#1