How to avoid the single quote within a column value

Question

I have the following code which iterates through a CSV file and writes to the SQL table:

foreach (string line in lines.Skip(1))
{
     var sqlfmt = "INSERT INTO [" + tab + "] values ({0})";
     var insert = string.Format(sqlfmt, line.Replace("\"", "'"));

     MessageBox.Show(insert + "");

     SqlCommand ty = new SqlCommand(insert, myConnection);
     ty.ExecuteNonQuery();
}

The issue I have now is if one of the column has ' in the text my application crashes.

What would be the best way to avoid the issue?

@Cory - You're right. I reopened it. However, as you pointed out in your comment below, using parameterized queries instead of escaping strings is the right answer to this issue, so it's hard to feel too bad about closing it in the first place. — David, Jun 10 '14 at 21:32

Ruslan · Accepted Answer · 2014-06-10T21:20:21.937

1

Just change

var insert = string.Format(sqlfmt, line.Replace("\"", "'"));

To:

var insert = string.Format(sqlfmt, line.Replace("'", "''").Replace("\"", "'"));

The reason for this is that in T-SQL (SQL Server's version of SQL), single-quotes inside a string are escaped by another single quote. For example, if you wanted to properly quote the string Bob's quotes', the properly-escaped SQL string would be 'Bob''s quotes'''.

edited Jun 10 '14 at 21:20

answered Jun 10 '14 at 21:14

Ruslan

2,691
1
19
29

1

Additionally I would like to mention that prepared statements are the preferred way to go. They protect against SQL injection by taking care of all of the character escaping situations for you. See an example here: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare(v=vs.110).aspx – Cᴏʀʏ Jun 10 '14 at 21:23
I will be moving everything to parameter once it's working. Thanks. – SearchForKnowledge Jun 10 '14 at 21:24
@Cory That is a very good point. Stored procedures are even better for the purpose. – Ruslan Jun 10 '14 at 21:25
@SearchForKnowledge, Interesting approach. Start with the hard, complicated way and then change it to the easier way once it is working. – adrianm Jun 11 '14 at 07:33
@adrianm Parameters in this case wouldn't be the easiest way since he's taking the string right out of the CSV file and not breaking it up into separate columns. The easiest and most "proper" way is to use BULK IMPORT in this case (or the equivalent SqlBulkCopy in .NET). – Ruslan Jun 12 '14 at 03:58

How to avoid the single quote within a column value

1 Answers1