mysqli 准备好的语句和 mysqli_real_escape_string
我目前正在使用 mysqli php 扩展。
传统上我使用 mysqli_real_escape_string 来转义用户输入。不过，我正在考虑更改代码（希望尽可能少的步骤）以使用准备好的语句。
我想澄清这一点 - 如果我使用准备好的语句来绑定所有变量，我可以确信 sql 注入是不可能的吗？ （并完全免除 mysqli_real_escape_string？）
谢谢
I'm currently using the mysqli php extension.
Traditionally I have used mysqli_real_escape_string to escape user input. However I am looking at changing over the code (hopefully in as few steps as possible) to use prepared statements.
I want to be clear on this - provided I use prepared statements to bind all of my variables, can I be confident that sql injection is impossible? (And dispense completely with mysqli_real_escape_string?)
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果正确绑定所有变量，则可以显着降低 SQL 注入的风险。例如，如果您动态创建 SQL，仍然有可能发生 SQL 注入：
但是如果您避免这样的事情，则不太可能出现问题。
If you correctly bind all your variables you can dramatically reduce the risk of SQL injection. It is still possible to get an SQL injection if you create SQL dynamically for example:
But if you avoid things like this it is unlikely you will have problems.
说到安全性，如果您正确绑定或格式化变量，这两种方法之间没有区别。
绑定更简单，因为它可以用于任何情况，而转义则不能（因此，您必须强制转换一些变量而不是转义/引用）。
另外，请记住，任何绑定或转义都不能使标识符安全。因此，如果您必须在查询中使用字段名称或运算符，则必须使用在脚本中硬编码的值。
Speaking of security, there is no difference between both methods, if you correctly bind or format your variables.
Binding is just simpler, because it can be used just for any case, while escaping can't (so, you have to cast some variables instead of escaping/quoting).
Also, bear in mind that no binding nor escaping can make identifier safe. So, if you have to use a field name or operator in your query, you have to use a value, hardcoded in your script.
这是我对该主题的高级看法。
当使用动态 SQL 字符串时，您依赖于转义函数的正常工作。不幸的是，情况并非总是如此，正如这个（诚然是旧的）示例中所示：
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-22.html
一旦你的数据值被转义， SQL 字符串必须由数据库服务器解析和编译。如果转义函数没有正确完成其工作，或者发现了一种巧妙的新 SQL 注入攻击，服务器就有可能将数据误认为是 SQL 语句。
如果您使用带有参数的预准备语句，则首先会解析和编译该语句。执行时，数据值会与已编译的语句结合起来。这将 SQL 逻辑与数据值分开 - 永远不应该出现混淆两者的机会。
所以，是的，您可以省去
mysqli_real_escape_string，但我不会说使用带有参数的准备好的语句会使 SQL 注入变得不可能。这使得事情变得更加困难，但与 mysqli_real_escape_string 错误一样，我猜总有可能一个尚未发现（或新创建）的错误会让看似不可能的事情变成可能。Here's my high-level view on the topic.
When using dynamic SQL strings, you are relying on the escaping function working correctly. Unfortunately, this is not always the case, as can be seen in this (admittedly old) example:
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-22.html
Once your data values have been escaped, the SQL string has to be parsed and compiled by the database server. If the escaping function has not done its job properly, or a clever new SQL injection attack has been discovered, there is a chance that the server will mistake data for SQL statements.
If you use prepared statements with parameters, the statement is first parsed and compiled. The data values are combined with the compiled statement when it is executed. This separates the SQL logic from the data values - the opportunity to confuse the two should never occur.
So, yes, you can dispense with
mysqli_real_escape_string, but I would not go so far as to say that using prepared statements with parameters makes SQL injection impossible. It makes it significantly harder, but as with themysqli_real_escape_stringbug, I guess there's always the chance that a yet to be discovered (or newly created) bug will make the seemingly impossible, possible.