Escape special characters in SQL INSERT INTO via C#

Question

I have searched google and haven't found any solution for my issue yet. Basically I have a comments feed that is setup within an image gallery (similar to facebook or stackoverflow comments). Users can post comments and read comments posted by other users. This is working fine. However, if a user tries to post a comment with an apostrophe, I get a nice little web application error:

Incorrect syntax near 's'. Unclosed quotation mark after the character string ')'.

The comment that I'm posting to SQL is 81's. I'm wanting a solution that will escape all special characters so that whatever the user types in, no matter what, doesn't error out.

Code Behind

Fetcher.postUserComments(connectionString, imagePath, comments.ToString(), userId);

Fetcher

sqlCom.CommandText = "INSERT INTO dbo.Table(userId, imagePath, userComments, dateCommented) VALUES ('" + userId + "', '" + imagePath + "', '" + comments + "', '" + theDate + "')";

The data type is string and I've also tried doing a .ToString() but no luck. Thanks in advance for any helpful input.

Do not do this! You are opening yourself to [SQL Injection attacks](http://msdn.microsoft.com/en-us/library/ff648339.aspx). Change your code so that you parameterize all of your queries. You will be sorry otherwise. — Jim Mischel, Jun 20 '14 at 03:51

score 17 · Accepted Answer · answered Jun 20 '14 at 03:54

You should always use parameterized querys. They help you avoid situations like the one you are having, as well as SQL Injection attacks

sqlCom.CommandText = "INSERT INTO dbo.Table(userId, imagePath, userComments, dateCommented) VALUES (@userId, @imagePath, @userComments, @dateCommented)";

sqlCom.Parameters.AddWithValue("@userId", userId);
sqlCom.Parameters.AddWithValue("@imagePath", imagePath);
sqlCom.Parameters.AddWithValue("@userComments", comments);
sqlCom.Parameters.AddWithValue("@dateCommented", theDate);

Thank you for the information and corrections. Everything is working good now. — frank billy, Jun 20 '14 at 04:22

score 4 · Answer 2 · answered Jun 20 '14 at 03:49

4

You need to duplicate the ' character in comments

comments = comments.Replace("'", "''");

Alternatively, but more safety, is to use Sql parameter, example :

cmd.CommandText = "SELECT * FROM Client, Project WHERE Client.ClientName = @ClientName AND Project.ProjectName = @ProjectName";

cmd.Parameters.Add(new SqlParameter("@ClientName",client.SelectedValue));

cmd.Parameters.Add(new SqlParameter("@ProjectName",projnametxt.Text));

answered Jun 20 '14 at 03:49

NeedAnswers

1,411
3
19
43

That would be a band-aid fix. I would then have to do that for all special characters. I'm looking for a more permanent fix. Thank you for the advice though. I need to update the question to state "all special characters" – frank billy Jun 20 '14 at 03:50
***Like*** has special characters. Maybe escape too. – Kiquenet Dec 11 '17 at 11:01

score 3 · Answer 3 · answered Jun 20 '14 at 03:55

You should NEVER do this...because it allows for easy SQL injection. I could inject malicious sql queries through a comment, something like...

 ;drop database master;

use parameters instead to avoid sql injection

command.Parameters.Add(new SqlParameter("@Param", value));

Escape special characters in SQL INSERT INTO via C#

3 Answers3

Linked