PDO 与 MYSQLI，准备好的语句和绑定参数

2021-12-25 00:00:00 parameters php pdo mysqli prepared-statement

我有这个问题需要澄清.我阅读了一些文档和评论，但仍有一些不够清楚.

I have this very question to clear things up. I read some documentation and comments around but still somethings are just not clear enough.

  • 我知道 PDO 提供了更多驱动程序，如果您更改数据库类型，这当然是一个加分项.
  • 正如在另一篇文章中所说，PDO 不提供真正的预处理语句，但 mysqli 提供，因此使用 MYSQLI 会更安全
  • 基准看起来很相似，(没有自己测试，而是在网上检查了一些基准)
  • 面向对象对我来说不是问题，因为 mysqli 正在迎头赶上.但是对程序 mysqli 与 PDO 进行基准测试会很好，因为程序应该稍微快一点.

但这是我的问题，对于准备好的语句，我们是否必须对我们在语句中使用的数据使用参数绑定?好的做法还是必须的?我知道如果您多次运行相同的查询，准备好的语句在性能方面是很好的，但这足以保护查询本身吗?或绑定参数是必须的?绑定参数究竟是什么以及它如何保护数据免受 sql 注入?如果您指出我们对我上述陈述的任何误解，我们也将不胜感激.

But here is my question, with prepared statement, do we have to use parameter binding with the data we use in our statement? good practice or have to? I understand prepared statements are good perfermance-wise if you run the same query multiple times but it is enough to secure the query itself? or binding parameters is a must? What exactly do the binding parameters and how it works to protect the data from sql injection? Also would be appreciated if you point our any misunderstanding about the statements I made above.

推荐答案

简而言之，

  • 绑定是必须的，它是保护的基石，无论原生驱动是否支持.重要的是替代的想法.
  • 差异在安全性和性能方面可以忽略不计.
  • 性能是最后要考虑的事情.没有比其他 API 慢得多的 API.它不是可能导致任何性能问题的类或函数，而是数据操作或糟糕的算法.优化您的查询，而不仅仅是调用它们的函数.
  • 如果您打算使用原始的裸 API，那么 PDO 是唯一的选择.虽然包含在更高级别的类中，但 mysqli 似乎更适合 mysql.
  • mysqli 和 PDO 都缺少标识符和关键字的绑定.在这种情况下，必须实施基于白名单的保护.这是我的文章，带有现成的示例，将字段名称动态添加到 SQL 查询
  • Binding is a must, being a cornerstone of protection, no matter if it is supported by a native driver or not. It's the idea of substitution that matters.
  • The difference is negligible in either safety and performance.
  • Performance is the last thing to consider. There is NO API that is considerable slower than other. It is not a class or a function that may cause whatever performance problem but a data manipulation or a bad algorithm. Optimize your queries, not mere functions to call them.
  • If you are going to use a raw bare API, then PDO is the only choice. While wrapped in a higher level class, mysqli seems more preferable for mysql.
  • Both mysqli and PDO lack bindings for the identifiers and keywords. In this case a whitelist-based protection must be implemented. Here is my article with the ready made example, Adding a field name to the SQL query dynamically

相关文章