通过C＃在SQL INSERT INTO中转义特殊字符 [英] Escape special characters in SQL INSERT INTO via C#

问题描述

我已经搜索过Google，还没有找到任何解决方案。基本上我有一个在图像库中设置的注释源（类似于Facebook或者stackoverflow注释）。用户可以发表评论并阅读其他用户发表的评论。这工作正常但是，如果用户试图用撇号发表评论，我会收到一个很好的小型Web应用程序错误：

。在字符
string'）之后取消闭合的引号。

我发布到SQL的评论是 81的
$ b

后面的代码

  Fetcher.postUserComments（connectionString，imagePath， comments.ToString（），userId）; 
  

Fetcher

  sqlCom.CommandText =INSERT INTO dbo.Table（userId，imagePath，userComments，dateCommented）VALUES（'+ userId +'，'+ imagePath +'，' + comments +'，'+ theDate +'）; 
  

数据类型是字符串，我也试过做一个 .ToString （）但没有运气。感谢您的任何有用的输入。

解决方案

您应该始终使用参数化查询。它们可以帮助您避免您遇到的情况，以及 SQL注入攻击

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

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.

解决方案

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

这篇关于通过C＃在SQL INSERT INTO中转义特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！