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.

Answer 1

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