Data from WebApi to sql database

Question

I get Invoices from a WebApi and I insert these Invoices into a SQL table. I use C# to do this operation. I use System.Data.SqlClient where I generate this SQL query:

string sql = String.Format("insert into [dbo].[InvoiceHeader] values('{0}','{1}','{2}','{3}')", inv.logNo, inv.locationCode, inv.invSeq,inv.reference);

command = new SqlCommand(sql, cnn);

adapter.InsertCommand.ExecuteNonQuery();

The problem is that one of the Invoices has an inv.reference = "Box 20' Cont" and that ' is a problem for the InsertCommand. How do I insert text into a sql database when the variable contains an ' in the text?.

Mithrandir · Accepted Answer · 2021-04-13 08:19:34Z

2

You should switch to using parameters. This will make your code safer and cases like the one your question is about will resolve quite easy:

SqlCommand cmd = new SqlCommand(
    "insert into [dbo].[InvoiceHeader] values (@logoNo,@locationCode,@ref)", conn);

SqlParameter param1  = new SqlParameter();
param.ParameterName = "@logoNo";
param.Value         = inv.logNo;

SqlParameter param2  = new SqlParameter();
param.ParameterName = "@locationCode";
param.Value         = inv.locationCode;

SqlParameter param3  = new SqlParameter();
param.ParameterName = "@ref";
param.Value         = inv.invSeq,inv.reference;

cmd.Parameters.Add(param1);
cmd.Parameters.Add(param2);
cmd.Parameters.Add(param3);

All the necessary quoting and protection against SQL-injection will be taken care of. You still need to handle exceptions and general errors.

edited Apr 13, 2021 at 8:19

answered Apr 9, 2021 at 8:38

Mithrandir

25.5k6 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel Dahl Over a year ago

I tried to use this approach but I got an error. What do I do when one of the parameters is Null?

Mithrandir Over a year ago

That depends on your table/column definition. If NULL is not allowed for a column, then the parameters can't be NULL. Your previous method must have run into the same problem.

Mithrandir Over a year ago

I have edited the answer to make it clearer how to go about it. Have you added the parameters to your command before?

Daniel Dahl · Accepted Answer · 2021-04-09 08:39:54Z

-1

It was actually easy.

I just did it like this.

inv.reference.Replace("'", "''")

Worked like a charm

answered Apr 9, 2021 at 8:39

Daniel Dahl

215 bronze badges

Collectives™ on Stack Overflow

Data from WebApi to sql database

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related