什么时候不使用准备好的语句? [英] 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连接在一起.
• 每个页面命中最多只能执行 个语句，并且只能执行一次.
• 我处于托管环境中，因此不愿意亲自进行任何压力测试"来猛击服务器.

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

它是模拟的准备好的语句！

Emulated prepared statements it is!

推荐答案

我认为您需要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屋！