在表中插入记录时使用单引号字符串字段导致错误 [英] String field with single quotation mark is causing an error when inserting record in table

问题描述

下面我有code：

 查询=插入tblB2B_OrderStatusTopStillInRB（LSRNbr，货主名称，单位，DroppedInRB，EPT，状态，OnTimeStatus，SHIPVIA，DroppedInRB_Order，RealEPT）;
查询+ =值（'
                    + ListOrdStatusTopInRB [I] .LSRNbr +'，'
                    + ListOrdStatusTopInRB [I] .ShipName +'
                    + ListOrdStatusTopInRB [I] .Units +，'
                    + ListOrdStatusTopInRB [I] .DroppedInRB +'，'
                    + ListOrdStatusTopInRB [I] .EPT +'，'
                    + ListOrdStatusTopInRB [I] .STATUS +'，'
                    + ListOrdStatusTopInRB [I] .OnTimeStatus +'，'
                    + ListOrdStatusTopInRB [I] .ShipVia +'，'
                    + ListOrdStatusTopInRB [I] .DroppedInRB_Order +'，'
                    + ListOrdStatusTopInRB [I] .RealEPT +'）;cmd.CommandText =查询;
cmd.ExecuteNonQuery（）;
 

和我才意识到，当货主有一个单引号的值，在INSERT语句导致错误，例如：国际运输。

有没有什么办法来解决这个问题，而不从字符串去除单引号？

我用尝试以下，但没有工作：

  cmd.CommandText = @query
+ @ListOrdStatusTopInRB [I] .ShipName +'
 

任何想法？

解决方案

  

有没有什么办法来解决这个问题，而不从字符串去除单引号？

是 - 使用参数化的SQL来代替。你应该的从不的直接在这样的SQL使用变量值。它可以让 SQL注入攻击的，事业转换古怪，一般使SQL更加混乱阅读。

请参阅为 SqlCommand.Parameters文档 以参数化的SQL的例子。

基本上，这个想法是，你的SQL包括对参数的引用，例如

  INSERT INTO SomeTable（美孚，酒吧）VALUES（@foo，@Bar）
 

然后指定 @foo 和 @Bar 单独的值。然后该值不是SQL本身的一部分，因此也无所谓无论它们是否含有这将具有SQL内特殊含义的字符

I have below code:

query = "insert into tblB2B_OrderStatusTopStillInRB (LSRNbr, ShipName, Units, DroppedInRB, EPT, Status, OnTimeStatus, ShipVia, DroppedInRB_Order, RealEPT) ";
query += "values ('"
                    + ListOrdStatusTopInRB[i].LSRNbr + "','"
                    + ListOrdStatusTopInRB[i].ShipName + "',"
                    + ListOrdStatusTopInRB[i].Units + ",'"
                    + ListOrdStatusTopInRB[i].DroppedInRB + "','"
                    + ListOrdStatusTopInRB[i].EPT + "','"
                    + ListOrdStatusTopInRB[i].Status + "','"
                    + ListOrdStatusTopInRB[i].OnTimeStatus + "','"
                    + ListOrdStatusTopInRB[i].ShipVia + "','"
                    + ListOrdStatusTopInRB[i].DroppedInRB_Order + "','"
                    + ListOrdStatusTopInRB[i].RealEPT + "')";

cmd.CommandText = query;
cmd.ExecuteNonQuery();

And I just realized, that when the ShipName has a value with a single quotation mark, is causing an error in the insert statement, for instance: int'l Transp.

Is there any way to fix that, without removing the single quotation mark from the string?

I was trying using the following but didn't work:

cmd.CommandText = @query
+ @ListOrdStatusTopInRB[i].ShipName + "',"

Any ideas?

解决方案

Is there any way to fix that, without removing the single quotation mark from the string?

Yes - use parameterized SQL instead. You should never use variable values directly in your SQL like this. It can allow SQL injection attacks, cause conversion oddities, and generally make the SQL more confusing to read.

See the documentation for SqlCommand.Parameters for an example of parameterized SQL.

Basically, the idea is that your SQL includes references to parameters, e.g.

INSERT INTO SomeTable(Foo, Bar) VALUES (@Foo, @Bar)

and then you specify the values for @Foo and @Bar separately. The values then aren't part of the SQL itself, so it doesn't matter whether or not they contain characters which would have special meaning within the SQL.

这篇关于在表中插入记录时使用单引号字符串字段导致错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！