MySQL PHP PDO准备的语句-性能问题与安全性 [英] MySQL PHP PDO prepared statements - performance issues vs security

问题描述

我正在考虑使用InnoDB(现在为mysql_query和MyISAM)将一些开源应用程序重写为PDO和事务.

I am thinking of rewriting some open-source application for my purposes to PDO and transactions using InnoDB (mysql_query and MyISAM now).

我的问题是:哪些情况下适合使用预准备语句?

My question is: Which cases are reasonable for using prepared statements?

因为我正在阅读的每个地方(甚至在这里的许多帖子中都提到)，由于1.安全性和2.性能，我每次都应该在任何地方使用准备好的语句.甚至PHP手册也建议使用准备好的语句，而不要提及转义.

Because everywhere I am reading is stated (even in many posts here) that I should use prepared statements every time and everywhere because of the 1. security and 2. performance. Even PHP manual recommends using prepared statements and not mentioning the escape-thing.

您不能否认安全机制.但是，一遍又一遍地思考它，使我想到必须每次准备该语句然后使用一次..这没有任何意义.虽然必须在单个语句中插入1000次某些变量，但这很有意义，但这是显而易见的.但这不是普通的eshop或board建立的基础.

You can't deny the security mechanism. But thinking it over and over it comes to mind that having to prepare the statement every time and then use it once.. It doesn't make sense. While having to insert 1000 times some variables in single statement, that makes sense but it is obvious. But this is not what common eshop or board is built upon.

那么如何克服呢?我可以在整个应用程序中准备我的陈述并具体命名吗?我可以准备几个不同的语句并按名称使用它们吗?因为这是我正在考虑的唯一合理的解决方案(1000x除外).

So how to overcome this? May I prepare my statements application-wide and to name them specifically? Can I prepare several different statements and to use them by name? Because this is the only reasonable solution I am thinking of (except the 1000x thing).

我发现也有一个名为$ pdo-> quote的mysql_real_escape，用于单个查询.为什么不使用这个?为什么要打扰准备呢?

I found there is this mysql_real_escape called $pdo->quote as well for the purpose of single query. Why not to use this? Why to bother with preparing?

您如何看待这篇出色的文章? http://blog.ulf-wendel.de/2008/pdo_mysqlnd-准备语句再次/

And what do you think of this excellent article? http://blog.ulf-wendel.de/2008/pdo_mysqlnd-prepared-statements-again/

您是否同意准备报表产生的间接费用?

Do you agree with the overhead caused by preparing the statements?

谢谢

推荐答案

我认为这属于过早优化"类别.

I think this falls in the "premature optimization" category.

间接费用有多大?你测量了吗?它根本不会影响您的服务器性能吗?

How significant is the overhead? Have you measured it? Does it affect your server performance at all?

奇怪的是，没有.

从好的方面来说，您在安全性方面具有不可否认的好处(对于任何基于Internet的商店来说，这都是一个主要问题).

On the plus side, you have an undeniable gain in terms of security (which should be a major concern for any internet-based shop).

不利的一面是，它可能会影响性能.在您提供的链接中，它表明，在某些情况下，实施不佳的PDO准备工作会导致性能略低于未准备好的语句. 5000次运行时的性能差异为0.298秒.

On the downside, you have the risk that it might affect performance. In the link you provided, it shows that poorly implemented PDO preparation results in slightly lower performance than non prepared statement in some circumstances. Performance difference on 5000 runs is 0.298 seconds.

无关紧要.更是如此，当您意识到未准备好"的查询在没有输入的情况下运行 时，要确保它们在实时环境中的安全，就需要执行输入清理例程.如果不使用准备好的查询，则需要某种形式的输入清理以防止SQL攻击，并且根据完成方式的不同，可能需要对结果集进行处理.

Insignificant. Even more so when you realize that the "non prepared" queries are run without the input sanitizing routines that would be required to make them safe in a live environment. If you don't use the prepared queries, you need some form of input sanitizing to prevent SQL attacks, and depending on how it is done, you may need to massage back the result sets.

最重要的是，没有重大的性能问题，但是有很大的安全性好处.因此，正式建议使用准备好的陈述.

Bottom line, there is no significant performance issue, but there is a significant security benefit. Thus the official recommendation of using prepared statements.

在您的问题中，您所说的是普通的网上商店".如果有的话，常见的eshop"将永远不会有足够的流量来担心性能问题.另一端的安全问题...

In your question, you speak of "the common eshop". The "common eshop" will never have enough traffic to worry about the performance issue, if there is one. The security issue on the other end...

这篇关于MySQL PHP PDO准备的语句-性能问题与安全性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！