这种构建动态 SQL 的方法是否容易受到 SQL 注入或对性能不利？
我想构建一个安全的动态选择语句，可以处理多个 WHERE 子句。
例如，基本 SQL 如下所示：
SELECT * FROM Books Where Type='Novel'
我将传递类似以下内容的函数：
SafeDynamicSQL("author,=,Herman Melville","pages,>,1000");
它将清理输入并连接如下：
SELECT * FROM Books Where Type='Novel' AND author=@author AND pages>@pages
该函数将通过检查预定义列名称的数组来清理列名称。运算符仅允许为 >、<、=。该值将作为普通参数添加。
这仍然容易受到 SQL 注入攻击吗？
会有一些字符串操作和小循环，这会影响性能，但我的想法是，与平均需要 200 毫秒的请求相比，这只会花费几毫秒。如果这些请求大约每秒发出一次，这会对服务器造成比我想象的更多的负担吗？
我知道这无论如何都不是最佳实践，但它将大大加快开发速度。请告诉我为什么这可能是一个坏主意的任何其他原因。
I would like to build a safe dynamic select statement that can handle multiple WHERE clauses.
For example the base SQL would look like:
SELECT * FROM Books Where Type='Novel'
I would pass the function something like:
SafeDynamicSQL("author,=,Herman Melville","pages,>,1000");
Which would sanitize inputs and concatenate like:
SELECT * FROM Books Where Type='Novel' AND author=@author AND pages>@pages
The function would sanitize the column name by checking against an array of predefined column names. The operator would only be allowed to be >,<,=. The value would be added as a normal paramater.
Would this still be vulnerable to SQL injection?
There will be some string manipulation and small loops which will affect performance but my thoughts are that this will only take a few milliseconds compared to the request which on average take 200ms. Would this tax the server more than I am thinking if these requests are made about once a second?
I know this isn't best practice by any means, but it will greatly speed up development. Please give me any other reasons why this could be a bad idea.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
看起来您正在重新发明许多现有的 ORM 解决方案，这些解决方案提供了用于创建 WHERE 子句的类似 API。
您问题的答案取决于您所说的“该值将作为正常参数添加”的含义。如果您的意思是执行字符串连接来生成您显示的字符串，那么是的，这仍然会受到 SQL 注入攻击。如果您的意思是使用实际的参数化查询，那么您会很安全。在后一种情况下，您将生成类似的内容
，然后将其绑定到诸如 ['Herman Melville', 1000] 之类的值列表。它到底是什么样子取决于您使用的编程语言。
最后，如果您选择这条路径，我强烈建议您将逗号分隔的参数更改为三个单独的参数，这样您会节省大量编程时间。
It looks like you're reinventing any number of existing ORM solutions which offer a similar API for creating WHERE clauses.
The answer to your question hinges on what you mean by "The value would be added as a normal paramater." If by that you mean performing string concatenation to produce the string you showed then yes, that would still be subject to SQL injection attack. If you mean using an actual parameterized query then you would be safe. In the later case, you would produce something like
and then bind that to a list of values like ['Herman Melville', 1000]. Exactly what it would look like depends on what programming language you're using.
Finally, if you pursue this path I would strongly recommend changing from comma-delimited arguments to three separate arguments, you'd save yourself a lot of programming time.
从安全角度来看，几乎任何将字符串附加在一起（或插入）来创建 SQL 的代码都是不好的形式，并且可能会受到某些 SQLi 攻击向量的影响。只需使用绑定参数即可避免整个问题；避免 SQL 注入非常简单。
Pretty much any code that appends together (or interpolates) strings to create SQL is bad form from a security point of view, and is probably subject to some SQLi attack vector. Just use bound parameters and avoid the entire problem; avoiding SQL injection is super-easy.