SQL server - inserting a string with a single quotation mark

Question

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)?

Using parametrized queries? – It'sNotALie. Jun 09 '13 at 08:26 — It'sNotALie., Jun 09 '13 at 08:26

score 2 · Answer 1 · answered Jun 09 '13 at 08:27

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 });

score 1 · Answer 2 · answered Jun 09 '13 at 08:27

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/

SQL server - inserting a string with a single quotation mark

2 Answers2