要回答您的担忧:
MySQL >= 5.1.17 (or >= 5.1.21 for the PREPARE
and EXECUTE
statements) can use prepared statements in the query cache. So your version of MySQL+PHP can use prepared statements with the query cache. However, make careful note of the caveats for caching query results in the MySQL documentation. There are many kinds of queries which cannot be cached or which are useless even though they are cached. In my experience the query cache isn't often a very big win anyway. Queries and schemas need special construction to make maximum use of the cache. Often application-level caching ends up being necessary anyway in the long run.
本地准备对安全性没有任何影响.伪预准备语句仍将转义查询参数值,这将只在PDO库中使用字符串完成,而不是在MySQL服务器上使用二进制协议完成.换句话说,无论您的EMULATE_PREPARES
设置如何,相同的PDO代码都同样易受(或不易受)注入攻击.唯一的区别是参数替换发生在哪里--如果是EMULATE_PREPARES
,则在PDO库中进行;如果没有EMULATE_PREPARES
,则在MySQL服务器上进行.
如果没有EMULATE_PREPARES
,您可能会在准备时而不是执行时出现语法错误;对于EMULATE_PREPARES
,您只会在执行时得到语法错误,因为PDO直到执行时才有查询给MySQL.注意this affects the code you will write!尤其是如果你使用PDO::ERRMODE_EXCEPTION
!
另一个注意事项:
prepare()
的开销是固定的(使用本机预准备语句),因此使用本机预准备语句的prepare();execute()
可能比使用模拟预准备语句发出纯文本查询要慢一些.在许多数据库系统上,prepare()
的查询计划也被缓存,并且可能与多个连接共享,但我认为MySQL不会这样做.因此,如果不对多个查询重用准备好的语句对象,则总体执行速度可能会较慢.
As a final recommendation,我认为在旧版本的MySQL+PHP中,您应该模拟准备好的语句,但是在您的最新版本中,您应该关闭模拟.
在编写了几个使用PDO的应用程序之后,我制作了一个PDO连接函数,它有我认为最好的设置.您可能应该使用类似的方法或调整您的首选设置:
/**
* Return PDO handle for a MySQL connection using supplied settings
*
* Tries to do the right thing with different php and mysql versions.
*
* @param array $settings with keys: host, port, unix_socket, dbname, charset, user, pass. Some may be omitted or NULL.
* @return PDO
* @author Francis Avila
*/
function connect_PDO($settings)
{
$emulate_prepares_below_version = '5.1.17';
$dsndefaults = array_fill_keys(array('host', 'port', 'unix_socket', 'dbname', 'charset'), null);
$dsnarr = array_intersect_key($settings, $dsndefaults);
$dsnarr += $dsndefaults;
// connection options I like
$options = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
// connection charset handling for old php versions
if ($dsnarr['charset'] and version_compare(PHP_VERSION, '5.3.6', '<')) {
$options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES '.$dsnarr['charset'];
}
$dsnpairs = array();
foreach ($dsnarr as $k => $v) {
if ($v===null) continue;
$dsnpairs[] = "{$k}={$v}";
}
$dsn = 'mysql:'.implode(';', $dsnpairs);
$dbh = new PDO($dsn, $settings['user'], $settings['pass'], $options);
// Set prepared statement emulation depending on server version
$serverversion = $dbh->getAttribute(PDO::ATTR_SERVER_VERSION);
$emulate_prepares = (version_compare($serverversion, $emulate_prepares_below_version, '<'));
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, $emulate_prepares);
return $dbh;
}