c# - String field with single quotation mark is causing an error when inserting record in table

Question

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?

Answer 1

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.