输入清理和参数化查询是否相互排斥?
我正在更新一些无法正确处理用户输入的遗留代码。该代码确实进行了最少量的清理,但并未涵盖所有已知的威胁。
我们的新代码使用参数化查询。据我了解,查询是预编译的,并且输入被简单地视为无法执行的数据。在这种情况下,不需要进行消毒。是这样吗?
换句话说,如果我参数化此遗留代码中的查询,是否可以消除它当前所做的清理?或者我是否错过了参数化之外的清理的一些额外好处?
I'm working updating some legacy code that does not properly handle user input. The code does do a minimal amount of sanitization, but does not cover all known threats.
Our newer code uses parameterized queries. As I understand it, the queries are precompiled, and the input is treated simply as data which cannot be executed. In that case, sanitization is not necessary. Is that right?
To put it another way, if I parameterize the queries in this legacy code, is it OK to eliminate the sanitization that it currently does? Or am I missing some additional benefit of sanitization on top of parameterization?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
确实,SQL 查询参数可以很好地防御 SQL 注入。嵌入的引号或其他特殊字符不会造成损害。
但 SQL 查询的某些组件无法参数化。例如表名、列名、SQL 关键字。
因此,在插入 SQL 查询之前,您可能需要验证一些动态内容的示例。将值列入白名单也是一种很好的技术。
此外,您还可以拥有列的数据类型允许的值,但这些值是无意义的。对于这些情况,使用应用程序代码进行验证通常比尝试在 SQL 约束中进行验证更容易。
假设您存储了信用卡号。信用卡号码有有效的模式,并且有库可以识别有效的信用卡号码和无效的信用卡号码。
或者当用户定义她的密码时怎么样?您可能想要确保足够的密码强度,或者验证用户是否在两个密码输入字段中输入了相同的字符串。
或者,如果他们订购一定数量的商品,您可能需要将数量存储为整数,但您需要确保它大于零,也许如果它大于 1000,您需要仔细检查用户输入的内容正确。
It's true that SQL query parameters are a good defense against SQL injection. Embedded quotes or other special characters can't make mischief.
But some components of SQL queries can't be parameterized. E.g. table names, column names, SQL keywords.
So there are some examples of dynamic content you may need to validate before interpolating into an SQL query. Whitelisting values is also a good technique.
Also you could have values that are permitted by the data type of a column but would be nonsensical. For these cases, it's often easier to use application code to validate than to try to validate in SQL constraints.
Suppose you store a credit card number. There are valid patterns for credit card numbers, and libraries to recognize a valid one from an invalid one.
Or how about when a user defines her password? You may want to ensure sufficient password strength, or validate that the user entered the same string in two password-entry fields.
Or if they order a quantity of merchandise, you may need to store the quantity as an integer but you'd want to make sure it's greater than zero and perhaps if it's greater than 1000 you'd want to double-check with the user that they entered it correctly.
参数化查询将有助于防止 SQL 注入,但它们不会对跨站点脚本执行起到作用。您需要其他措施(例如 HTML 编码或 HTML 检测/验证)来防止这种情况发生。如果您只关心 SQL 注入,那么参数化查询可能就足够了。
Parameterized queries will help prevent SQL injection, but they won't do diddly against cross-site scripting. You need other measures, like HTML encoding or HTML detection/validation, to prevent that. If all you care about is SQL injection, parameterized queries is probably sufficient.
进行清理和验证的原因有很多,包括防止跨站点脚本编写,以及只是希望字段内容正确(电话号码中没有姓名)。参数化查询消除了手动清理或逃避 SQL 注入的需要。
请参阅我之前对此的回答。
There are many different reasons to sanitize and validate, including preventing cross-site scripting, and simply wanting the correct content for a field (no names in phone numbers). Parameterized queries eliminate the need to manually sanitize or escape against SQL injection.
See one of my previous answers on this.
你是对的,SQL 参数不是可执行代码,所以你不需要担心这一点。
但是,您仍然应该进行一些验证。例如,如果您期望 varchar(10) 并且用户输入的内容比该值长,则最终会出现异常。
You are correct, SQL parameters are not executable code so you don't need to worry about that.
However, you should still do a bit of validation. For example, if you expect a varchar(10) and the user inputs something longer than that, you will end up with an exception.
简而言之,没有。输入清理和参数化查询的使用并不相互排斥,它们是独立的:您可以两者都不使用,也可以单独使用其中之一,或者两者都使用。它们可以防止不同类型的攻击。两者结合使用是最好的方法。
In short no. Input sanitization and the use of parameterized queries are not mutually exclusive, they are independent: you can use neither, either one alone, or both. They prevent different types of attacks. Using both is the best course.
值得注意的是,有时编写包含动态 SQL 的存储过程很有用。在这种情况下,输入参数化这一事实并不能自动防御 SQL 注入。这似乎是一个相当明显的观点,但我经常遇到这样的人,他们认为因为他们的输入是参数化的,所以他们可以不用担心 SQL 注入。
It is important to note, as a minor point, that sometimes it is useful to write stored procedures which contain dynamic SQL. In this case, the fact that the inputs are parameterized is no automatic defense against SQL injection. This may seem a fairly obvious point, but I often run into people who think that because their inputs are parameterized they can just stop worrying about SQL Injection.