什么时候*不*使用准备好的语句？
我正在重新设计一个使用最小数据库的 PHP 驱动的网站。 原始版本使用“伪准备语句”（进行引用和参数替换的 PHP 函数）来防止注入攻击并将数据库逻辑与页面逻辑分开。
用使用 PDO 和真正准备好的语句的对象替换这些临时函数似乎很自然，但在阅读它们之后，我不太确定。 PDO 看起来仍然是一个好主意，但准备好的语句的主要卖点之一是能够重用它们……我永远不会。 这是我的设置：
- 这些语句都非常简单。 大多数的形式是
SELECT foo,bar FROM baz WHERE quux = ? ORDER BY bar LIMIT 1。 其中最复杂的语句就是三个这样的选择用 UNION ALL 连接在一起。 - 每个页面点击至多执行一条语句并且仅执行一次。
- 我处于托管环境中，因此对亲自进行任何“压力测试”来攻击他们的服务器持谨慎态度。
鉴于使用准备好的语句至少会使我进行的数据库往返次数增加一倍，我是否最好避免使用它们？ 我可以使用 PDO::MYSQL_ATTR_DIRECT_QUERY 来避免多次数据库访问的开销，同时保留参数化和注入防御的好处吗？ 或者，与执行非准备好的查询相比，准备好的语句 API 使用的二进制调用是否表现得足够好，我不应该担心？
编辑：
感谢各位提供的所有好建议。 我希望我可以将多个答案标记为“已接受”——许多不同的观点。 但最终，我必须给予 rick 他应得的……如果没有他的回答，即使在遵循每个人的建议之后，我也会幸福地离开并做完全错误的事情。 :-)
它是模拟的准备好的语句！
I'm re-engineering a PHP-driven web site which uses a minimal database. The original version used "pseudo-prepared-statements" (PHP functions which did quoting and parameter replacement) to prevent injection attacks and to separate database logic from page logic.
It seemed natural to replace these ad-hoc functions with an object which uses PDO and real prepared statements, but after doing my reading on them, I'm not so sure. PDO still seems like a great idea, but one of the primary selling points of prepared statements is being able to reuse them… which I never will. Here's my setup:
- The statements are all trivially simple. Most are in the form
SELECT foo,bar FROM baz WHERE quux = ? ORDER BY bar LIMIT 1. The most complex statement in the lot is simply three such selects joined together withUNION ALLs. - Each page hit executes at most one statement and executes it only once.
- I'm in a hosted environment and therefore leery of slamming their servers by doing any "stress tests" personally.
Given that using prepared statements will, at minimum, double the number of database round-trips I'm making, am I better off avoiding them? Can I use PDO::MYSQL_ATTR_DIRECT_QUERY to avoid the overhead of multiple database trips while retaining the benefit of parametrization and injection defense? Or do the binary calls used by the prepared statement API perform well enough compared to executing non-prepared queries that I shouldn't worry about it?
EDIT:
Thanks for all the good advice, folks. This is one where I wish I could mark more than one answer as "accepted" — lots of different perspectives. Ultimately, though, I have to give rick his due… without his answer I would have blissfully gone off and done the completely Wrong Thing even after following everyone's advice. :-)
Emulated prepared statements it is!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
当今的软件工程规则：如果它不能为您做任何事情，就不要使用它。
Today's rule of software engineering: if it isn't going to do anything for you, don't use it.
我认为你想要 PDO::ATTR_EMULATE_PREPARES。 这将关闭本机数据库准备好的语句，但仍然允许查询绑定以防止 sql 注入并保持 sql 整洁。 据我了解， PDO::MYSQL_ATTR_DIRECT_QUERY 完全关闭查询绑定。
I think you want PDO::ATTR_EMULATE_PREPARES. That turns off native database prepared statements, but still allows query bindings to prevent sql injection and keep your sql tidy. From what I understand, PDO::MYSQL_ATTR_DIRECT_QUERY turns off query bindings completely.
什么时候不使用准备好的语句？ 当您只想在数据库连接消失之前运行该语句一次时。
什么时候不使用绑定查询参数（这实际上是大多数人使用准备好的语句来获取的）？ 我倾向于说“从不”，而且我真的很想说“从不”，但现实是大多数数据库和一些数据库抽象层都有某些情况不允许您绑定参数，所以您在这些情况下被迫不使用它们。 但在任何其他时候，它都会让您的生活变得更简单，并且您的代码使用它们会更安全。
我不熟悉 PDO，但我敢打赌它提供了一种机制，用于使用同一函数调用中给出的值运行参数化查询（如果您不想准备，然后作为单独的步骤运行）。 （例如，类似于
run_query("SELECT * FROM users WHERE id = ?", 1)或类似内容。）此外，如果您深入了解，大多数数据库抽象层都会准备查询，然后运行它，即使您只是告诉它执行静态 SQL 语句。 因此，无论如何，您可能都不会通过避免显式准备来节省数据库之旅。
When not to use prepared statements? When you're only going to be running the statement once before the db connection goes away.
When not to use bound query parameters (which is really what most people use prepared statements to get)? I'm inclined to say "never" and I'd really like to say "never", but the reality is that most databases and some db abstraction layers have certain circumstances under which they won't allow you to bind parameters, so you're forced to not use them in those cases. Any other time, though, it will make your life simpler and your code more secure to use them.
I'm not familiar with PDO, but I'd bet it provides a mechanism for running parametrized queries with the values given in the same function call if you don't want to prepare, then run as a separate step. (e.g., Something like
run_query("SELECT * FROM users WHERE id = ?", 1)or similar.)Also, if you look under the hood, most db abstraction layers will prepare the query, then run it, even if you just tell it to execute a static SQL statement. So you're probably not saving a trip to the db by avoiding explicit prepares anyhow.
准备好的语句正在被成千上万的人使用，因此经过了充分的测试（因此可以推断它们是相当安全的）。 您的自定义解决方案仅供您使用。
您的自定义解决方案不安全的可能性非常高。 使用准备好的语句。 这样你就必须维护更少的代码。
Prepared statements are being used by thousands of people and are therefore well-tested (and thus one can infer they are reasonably secure). Your custom solution is only used by you.
The chance that your custom solution is insecure is pretty high. Use prepared statements. You have to maintain less code that way.
准备好的语句的好处如下：
但是，准备好的语句仅在每个连接中持久存在。 除非您使用连接池，否则如果您每页只执行一条语句，则不会有任何好处。 简单的查询也不会从更高效的传输格式中受益。
就我个人而言，我不会打扰。 伪准备语句可能对于它们可能提供的安全变量引用很有用。
The benefits of prepared statements are as follows:
However, prepared statements only persist per connection. Unless you're using connection pooling, there would be no benefit if you're only doing one statement per page. Trivially simple queries would not benefit from the more efficient transport format, either.
Personally I wouldn't bother. The pseudo-prepared statements are likely to be useful for the safe variable quoting they presumably provide.
老实说，我认为你不应该为此担心。 不过，我记得许多 PHP 数据访问框架都支持准备语句模式和非准备语句模式。 如果我没记错的话，PEAR:DB 当年就这么做过。
我遇到了和你一样的问题，我也有自己的保留，所以我最终没有使用 PDO，而是编写了自己的轻量级数据库层，该数据库层支持准备和标准语句，并在两者中执行正确的转义（sql 注入预防）案例。 我对准备的另一个抱怨是，有时将一些不可转义的输入附加到诸如 ... WHERE id IN (1, 2, 3...) 之类的语句中会更有效。
我对 PDO 的了解不够，无法告诉您使用它还有哪些其他选项。 然而，我确实知道 PHP 具有可用于它支持的所有数据库供应商的转义函数，并且您可以在您所使用的任何数据访问层之上滚动您自己的小层。
Honestly, I don't think you should worry about it. However, I remember that a number of PHP data access frameworks supported prepare statement modes and non-prepare statement modes. If I remember correctly, PEAR:DB did back in the day.
I have ran into the same issue as you and I had my own reservations, so instead of using PDO I ended up writing my own light-weight database layer that supported prepares and standard statements and performed correct escaping (sql-injection prevention) in both cases. One of my other gripes with prepares is that sometimes it is more efficient to append some non-escapable input to a statement like ... WHERE id IN (1, 2, 3...).
I don't know enough about PDO to tell you what other options you have using it. However, I do know that PHP has escaping functions available for all database vendors it supports and you could roll your own little layer on top of any data access layer you are stuck with.