参数化语句可以停止所有SQL注入吗？ [英] Can parameterized statement stop all SQL injection?
问题描述
如果是，为什么仍然有那么多成功的SQL注入？仅仅是因为某些开发人员太笨了，无法使用参数化语句？
If yes, why are there still so many successful SQL injections? Just because some developers are too dumb to use parameterized statements?
推荐答案
我在问题注释中张贴的链接解释了这个问题很好。下面总结了我对问题仍然存在的原因的看法：
The links that I have posted in my comments to the question explain the problem very well. I've summarised my feelings on why the problem persists, below:
-
刚开始的人可能不了解SQL注入
Those just starting out may have no awareness of SQL injection.
有些人知道SQL注入，但是认为转义是（唯一的）解决方案。如果您对 php mysql查询进行快速Google搜索，则显示的第一页是 mysql_query 页，在该页上有一个示例，显示了将转义的用户输入插入到查询中。没有提到（至少我看不到）使用准备好的语句。就像其他人所说的那样，那里有太多使用参数插值的教程，因此使用频率仍然不足为奇。
Some are aware of SQL injection, but think that escaping is the (only?) solution. If you do a quick Google search for php mysql query, the first page that appears is the mysql_query page, on which there is an example that shows interpolating escaped user input into a query. There's no mention (at least not that I can see) of using prepared statements instead. As others have said, there are so many tutorials out there that use parameter interpolation, that it's not really surprising how often it is still used.
缺乏了解参数化语句的工作方式。有人认为这只是逃避价值的一种幻想手段。
A lack of understanding of how parameterized statements work. Some think that it is just a fancy means of escaping values.
其他人知道参数化的语句，但不要使用它们，因为他们听说它们太慢了。我怀疑许多人听到过如此缓慢的参数化陈述，但实际上并没有对他们自己进行任何测试。正如比尔·卡文（Bill Karwin）在演讲中指出的那样，在考虑使用准备好的陈述时，应该很少将绩效差异作为因素。 一次执行，多次执行的好处，似乎常常被忘记，安全性和代码可维护性方面的改善也是如此。
Others are aware of parameterized statements, but don't use them because they have heard that they are too slow. I suspect that many people have heard how incredibly slow paramterized statements are, but have not actually done any testing of their own. As Bill Karwin pointed out in his talk, the difference in performance should rarely be used as a factor when considering the use of prepared statements. The benefits of prepare once, execute many, often appear to be forgotten, as do the improvements in security and code maintainability.
有些在各处都使用参数化语句，但会插值未检查的值，例如表和列名称，关键字和条件运算符。动态搜索（例如允许用户指定许多不同搜索字段，比较条件和排序顺序的搜索）就是此类的主要示例。
Some use parameterized statements everywhere, but with interpolation of unchecked values such as table and columns names, keywords and conditional operators. Dynamic searches, such as those that allow users to specify a number of different search fields, comparison conditions and sort order, are prime examples of this.
错误的理解使用ORM时的安全性。 ORM仍然允许内插SQL语句部分-参见5。
False sense of security when using an ORM. ORMs still allow interpolation of SQL statement parts - see 5.
编程是一个大而复杂的主题，数据库管理是一个大而复杂的主题，安全性是大而复杂的主题。开发安全的数据库应用程序并不容易-即便是经验丰富的开发人员也可能会陷入困境。
Programming is a big and complex subject, database management is a big and complex subject, security is a big and complex subject. Developing a secure database application is not easy - even experienced developers can get caught out.
关于stackoverflow的许多答案都无济于事。当人们编写使用动态SQL和参数插值的问题时，通常缺少建议使用参数化语句的答案。在某些情况下，我曾有人反对我使用准备好的语句的建议-通常是因为人们认为性能开销不可接受。我严重怀疑那些问大多数这些问题的人所处的位置，即准备参数化语句所花费的额外几毫秒会对其应用造成灾难性的影响。
Many of the answers on stackoverflow don't help. When people write questions that use dynamic SQL and parameter interpolation, there is often a lack of responses that suggest using parameterized statements instead. On a few occasions, I've had people rebut my suggestion to use prepared statements - usually because of the perceived unacceptable performance overhead. I seriously doubt that those asking most of these questions are in a position where the extra few milliseconds taken to prepare a parameterized statement will have a catastrophic effect on their application.
这篇关于参数化语句可以停止所有SQL注入吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
