为 SQL WHERE 子句构建安全搜索条件
我需要构建与 WHERE 子句一起使用的搜索条件。然后，此搜索条件会传递到不同的应用程序，作为 SQL 查询的一部分来执行。因为搜索条件可能非常复杂（包括子查询），所以我不相信接收应用程序可以智能地解析它们以防止 SQL 注入攻击。
最佳实践表明应使用参数化查询。当您使用命令对象自己执行查询时，效果很好。就我而言，我希望获取带有合并参数的查询字符串，并解析出我感兴趣的搜索子句。有没有办法做到这一点？
我使用 MS SQL Server，目前只需将我从调用者收到的字符串中的所有单引号替换为两个单引号。是否有更好的方法来实现一定程度的 SQL 注入攻击防护？
I need to build search conditions to be used with WHERE clause. This search condition is then passed to a different application to be executed as a part of SQL query. Because there search conditions can be quite complex (including sub-queries) I don't believe receiving application can intelligently parse them to prevent SQL injection attacks.
Best practices state that parametrized queries should be used. That works fine when you use command object to execute the query yourself. In my case I wish to obtain that query string with parameters merged into it, and parse out where search clause I am interested in. Is there a way to do this?
I work with MS SQL Server and currently simply replace all single quotes with two single quotes in string I receive from a caller. Is there a better way to achieve some level of protection from SQL injection attacks?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
查看这两个链接
此代码是否可以防止 SQL 注入？
和
< a href="https://stackoverflow.com/questions/1834396/proving-sql-injection">证明 SQL 注入
Have a look at these 2 links
Does this code prevent SQL injection?
and
Proving SQL Injection
OWASP 的一些指南
some guidelines from OWASP
我认为你很好：根据 SQL Server在线图书，单个单引号似乎是退出以单引号开头的带引号字符串的唯一方法。因此，将
'替换为''应该足以避免通过string变量进行 SQL 注入。我想不出任何方法可以通过其他非字符串本机C# 数据类型注入 SQL，如果它们正确（区域设置不变）转换为字符串。
尽管如此，参数化查询是“推荐”的解决方案。目前，您的应用程序似乎是这样组织的：
是否可以选择像这样重写您的应用程序？
我遇到了类似的情况，并通过创建一个 SubSQL 类来解决它，该类基本上包含一个带有 CommandText 的参数化字符串和一个带有参数的哈希表。然后，您可以将其用作
mySubSQL.CommandText += ...、mySubSQL.Parameters("@myfield") = myValue和mySubSQL.MergeInto(myCommand)（实现应该是明显且直接的）。I think you are fine: According to the SQL Server Books Online, a solitary single quote seems to be the only way to exit a quoted string that was started with a single quote. Thus, replacing
'with''should suffice to avoid SQL injection throughstringvariables.I cannot think of any way to inject SQL through other, non-string native C# data types, if they are properly (locale-invariant) converted to strings.
Nevertheless, parameterized queries are the "recommended" solution. At the moment, your application seems to be organized like this:
Would it be an option to rewrite your application like this?
I was in a similar situation and solved it by creating a
SubSQLclass, which basically contains a parameterized string with the CommandText and a hash table with the parameters. You could then use this asmySubSQL.CommandText += ...,mySubSQL.Parameters("@myfield") = myValueandmySubSQL.MergeInto(myCommand)(the implementation should be obvious and straight-forward).