什么时候*不*使用准备好的语句? [英] When *not* to use prepared statements?

问题描述

我正在重新设计一个使用最少数据库的 PHP 驱动的网站.原始版本使用伪准备语句"(进行引用和参数替换的PHP函数)来防止注入攻击并将数据库逻辑与页面逻辑分离.

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.

用使用 PDO 和真正准备好的语句的对象替换这些临时函数似乎很自然，但是在阅读它们之后，我不太确定.PDO 似乎仍然是一个好主意，但准备好的语句的主要卖点之一是能够重用它们......我永远不会这样做.这是我的设置:

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:

• 这些陈述都很简单.大多数都采用 SELECT foo,bar FROM baz WHERE quux = ?ORDER BY bar LIMIT 1.该批次中最复杂的语句只是三个这样的选择，并用 UNION ALL s 连接在一起.
• 每个页面点击至多执行一个语句并且只执行一次.
• 我在托管环境中，因此不敢亲自进行任何压力测试"来破坏他们的服务器.

• 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 with UNION 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.

鉴于使用准备好的语句至少会使我进行的数据库往返次数增加一倍，我最好避免它们吗?我可以使用 PDO::MYSQL_ATTR_DIRECT_QUERY 来避免多次数据库访问的开销，同时保留参数化和注入防御的好处吗?或者，与执行非准备好的查询相比，准备好的语句 API 使用的二进制调用的性能是否足够好，我不应该担心?

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?

感谢所有好的建议，伙计们.这是一个我希望我可以将不止一个答案标记为已接受"的地方——许多不同的观点.但最终，我必须给 rick 他应得的......如果没有他的回答，即使在遵循每个人的建议之后，我也会幸福地离开并做完全错误的事情.:-)

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. :-)

模拟准备好的语句！

推荐答案

我想你想要 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.

这篇关于什么时候*不*使用准备好的语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！