0

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?

Ry-
  • 218,210
  • 55
  • 464
  • 476
Somebody
  • 2,667
  • 14
  • 60
  • 100

1 Answers1

12

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.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • Just so you know, for example when you're testing an `insert` statement manually in SSMS, you specify a single quote using two consecutive single quotes. But for anything that isn't just manual that you're doing, parameters is the approach you should take. – sasfrog Oct 11 '13 at 20:52