CODEWITHSUNDEEP
c#sql-serverdml
Bick
Bick

Reputation: 18521

SQL server - inserting a string with a single quotation mark

I iterate over an external source and get a list of strings. I then insert them into the DB using:

SqlCommand command = new SqlCommand(commandString, connection);
command.ExecuteNonQuery();

Where commandString is an insert into command. i.e. 

insert into MyTable values (1, "Frog")

Sometimes the string contains ' or " or \ and the insert fails.
Is there an elegant way to solve this (i.e. @"" or similar)?

Upvotes: 0

Views: 1853

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1062855

Parameters.

insert into MyTable values (@id, @name)

And

int id = 1;
string name = "Fred";
SqlCommand command = new SqlCommand(commandString, connection);
command.Parameters.AddWithValue("id", id);
command.Parameters.AddWithValue("name", name);
command.ExecuteNonQuery();

Now name can have any number of quotes and it'll work fine. More importantly it is now safe from sql injection.

Tools like "dapper" (freely available on NuGet) make this easier:

int id = 1;
string name = "Fred";
connection.Execute("insert into MyTable values (@id, @name)",
    new { id, name });

Upvotes: 2

TyCobb
TyCobb

Reputation: 9089

You should look into using parameterized queries. This will allow you insert the data no matter the content and also help you avoid possible future SQL injection.

http://csharp-station.com/Tutorial/AdoDotNet/Lesson06

http://www.c-sharpcorner.com/uploadfile/puranindia/parameterized-query-and-sql-injection-attacks/

Upvotes: 1

Related Questions