php 为什么 PDO 比 mysql_real_escape_string 更适合转义 MySQL 查询/查询字符串？

Why is PDO better for escaping MySQL queries/querystrings than mysql_real_escape_string?

提问by BlissC

I've been told that I'd be better using PDOfor MySQL escaping, rather than mysql_real_escape_string.

有人告诉我，我最好使用PDOMySQL 转义，而不是mysql_real_escape_string.

Maybe I'm having a brain-dead day (or it may be the fact I'm by no stretch of the imagination a natural programmer, and I'm still very much at the newbie stage when it comes to PHP), but having checked out the PHP manual and read the entry on PDO, I'm still no clearer as to what PDO actually is and why it's better than using mysql_real_escape_string. This may be because I've not really got to grips with the complexities of OOP yet (I'm assuming it's something to do with OOP), but other than the fact that variables and array values seem to have a colon infront of them, I'm still not sure what it actually is and how you use it (and why it's better than mysql_real_escape_string. (It also may have something to do with the fact that I don't really have a clear understanding of what 'classes' are, so when I read "PDO class" I'm none the wiser really).

也许我正在度过一个脑残的一天（或者可能是因为我绝对不是一个天生的程序员，而且我在 PHP 方面仍然处于新手阶段），但是有查看了 PHP 手册并阅读了 PDO 上的条目，我仍然不清楚 PDO 到底是什么以及为什么它比使用mysql_real_escape_string. 这可能是因为我还没有真正掌握 OOP 的复杂性（我假设它与 OOP 有关），但除了变量和数组值似乎在它们前面有一个冒号这一事实之外，我仍然不确定它到底是什么以及你如何使用它（以及为什么它比mysql_real_escape_string. （这也可能与我对什么是“类”并没有清楚的了解有关，所以当我阅读“PDO 类”时，我真的一点也不聪明）。

Having read an articleor two on the 'Developer Zone' bit of the MySQL website, I'm still no clearer. As I can't even figure out what it is at the moment, I think probably using it is a bit beyond me right now, but I'm still interested in broadening my education and finding out how I could improve things.

在MySQL 网站的“开发人员专区”部分阅读了一两篇文章后，我仍然不清楚。由于目前我什至无法弄清楚它是什么，我认为现在使用它可能有点超出我的范围，但我仍然有兴趣扩大我的教育范围并找出我可以改进的方法。

Could anyone explain to me in 'plain English' what PDO is (or point me in the direction of something on the subject written in plain English), and how you'd go about using it?

任何人都可以用“普通英语”向我解释什么是 PDO（或向我指出用普通英语编写的主题的方向），以及您将如何使用它？

回答by Henrik Opel

As the current answers go into details while your question is more aimed at a general overview, I'll give it a try:

由于当前的答案详细说明，而您的问题更侧重于一般概述，我会尝试一下：

The PDO classes aim to encapsulate all the functionality needed to interact with a database. They do this by defining 'methods' (OO parlor for functions) and 'properties' (OO parlor for variables). You'd use them as a complete replacementfor all the 'standard' functions you are using now for talking to a database.

PDO 类旨在封装与数据库交互所需的所有功能。他们通过定义“方法”（函数的 OO 客厅）和“属性”（变量的 OO 客厅）来做到这一点。您可以将它们用作您现在用于与数据库对话的所有“标准”功能的完整替代品。

So instead of calling a series of the 'mysql_doSomething()' functions, storing their results in your own variables, you would 'instantiate' an object from the PDO class ('class' = abstract definition, 'object' = concrete, usable instance of a class) and call methods on that object to do the same.

因此，与其调用一系列“mysql_doSomething()”函数，将它们的结果存储在您自己的变量中，不如从 PDO 类中“实例化”一个对象（“类”=抽象定义，“对象”=具体的、可用的实例类）并调用该对象上的方法来执行相同的操作。

As an example, without PDO, you'd do something like this:

例如，如果没有 PDO，您将执行以下操作：

// Get a db connection
$connection = mysql_connect('someHost/someDB', 'userName', 'password');
// Prepare a query
$query = "SELECT * FROM someTable WHERE something = " . mysql_real_escape_string($comparison) . "'";
// Issue a query
$db_result = mysql_query($query);
// Fetch the results
$results = array();
while ($row = mysql_fetch_array($db_result)) {
  $results[] = $row;
}

while this would be the equivalent using PDO:

虽然这与使用 PDO 是等效的：

// Instantiate new PDO object (will create connection on the fly)
$db = new PDO('mysql:dbname=someDB;host=someHost');
// Prepare a query (will escape on the fly)
$statement = $db->prepare('SELECT * FROM someTable WHERE something = :comparison');
// $statement is now a PDOStatement object, with its own methods to use it, e.g.
// execute the query, passing in the parameters to replace
$statement->execute(array(':comparison' => $comparison));
// fetch results as array
$results = $statement->fetchAll();

So on first glance, there is not much difference, except in syntax. But the PDO version has some advantages, the biggest one being database independence:

所以乍一看，除了语法之外，没有太大区别。但是 PDO 版本有一些优点，最大的一个是数据库独立性：

If you need to talk to a PostgreSQL database instead, you'd only change mysql:to pgsql:in the instantiating call new PDO(). With the old method, you'd have to go through all your code, replacing all 'mysql_doSomething()' functions with their 'pg_doSomthing()' counterpart (always checking for potential differences in parameter handling). The same would be the case for many other supported database engines.

如果您需要与 PostgreSQL 数据库交谈，则只需在实例化调用中更改mysql:为。使用旧方法，您必须遍历所有代码，将所有 'mysql_doSomething()' 函数替换为其对应的 'pg_doSomthing()' 函数（始终检查参数处理中的潜在差异）。许多其他受支持的数据库引擎也是如此。pgsql:new PDO()

So to get back to your question, PDO basically just gives you a different way to achieve the same things, while offering some shortcuts/improvements/advantages. For example, escaping would happen automatically in the proper way needed for the database engine you are using. Also parameter substitution (prevents SQL Injections, not shown in example) is much easier, making it less error prone.

所以回到你的问题，PDO 基本上只是给你一种不同的方式来实现同样的事情，同时提供一些捷径/改进/优势。例如，转义会以您正在使用的数据库引擎所需的正确方式自动发生。此外，参数替换（防止 SQL 注入，示例中未显示）要容易得多，因此更不容易出错。

You should read up on some OOP basicsto get an idea of other advantages.

您应该阅读一些 OOP 基础知识以了解其他优势。

回答by Nathan Long

I'm not super familiar with PDO, but there is a distinction between "prepared statements" and escaped strings. Escaping is about removing disallowed character stringsfrom the query, but prepared statements are about telling the database what kind of query to expect.

我对 PDO 不是很熟悉，但是“准备好的语句”和转义的字符串之间是有区别的。转义是关于从查询中删除不允许的字符串，但准备好的语句是关于告诉数据库期望什么样的查询。

A query has multiple parts

一个查询有多个部分

Think of it this way: when you give a query to the database, you're telling it several separate things. One thing might be, for example, "I want you to do a select." Another might be "limit it to rows WHERE the user name is the following value."

可以这样想：当您向数据库发出查询时，您是在告诉它几件不同的事情。例如，一件事可能是“我想让你做一个选择”。另一个可能是“将其限制为用户名是以下值的行”。

If you build up a query as a string and hand it to the database, it doesn't know about either part until it gets the completed string. You might do this:

如果您将查询构建为字符串并将其传递给数据库，则在获得完整字符串之前，它不知道任何部分。你可以这样做：

'SELECT * FROM transactions WHERE username=$username'

When it gets that string, it has to parse it and decide "this is a SELECTwith a WHERE".

当它得到那个字符串时，它必须解析它并决定“这是一个SELECT带一个WHERE”。

Getting the parts mixed up

把零件弄混

Suppose a malicious user inputs their user name as billysmith OR 1=1. If you're not careful, you might put that into your string, resulting in:

假设一个恶意用户将他们的用户名输入为billysmith OR 1=1。如果您不小心，您可能会将其放入字符串中，从而导致：

'SELECT * FROM transactions WHERE username=billysmith OR 1=1'

...which would return all the transactions for all users, because 1 always equals 1. Whoops, you've been hacked!

...这将返回所有用户的所有交易，因为 1 总是等于 1。哎呀，你被黑了！

See what happened? The database didn't know what parts to expect in your query, so it just parsed the string. It wasn't surprised that the WHEREhad an OR, with two conditions that could satisfy it.

看看发生了什么？数据库不知道您的查询中需要哪些部分，因此它只是解析了字符串。WHERE有一个OR，有两个条件可以满足它并不奇怪。

Keeping the parts straight

保持零件笔直

If only it had known what to expect, namely, a SELECTwhose WHEREhad only one condition, the malicious user couldn't have tricked it.

如果只有它早知道会发生什么，即，一SELECT，其WHERE只有一个条件下，恶意用户可能不会欺骗它。

With a prepared statement, you can give it that correct expectation. You you can tell the database "I'm about to send you a SELECT, and it's going to be limited to rows WHERE username =a string that I'm about to give you. That's all - there are no other parts to the query. Are you ready? OK, here comes the string to compare to the username."

使用准备好的语句，您可以给它正确的期望。您可以告诉数据库“我将要向您发送一个SELECT，并且它将仅限于行WHERE username =我将要给您的字符串。仅此而已- 查询没有其他部分。您准备好了吗？ ? 好的，要与用户名进行比较的字符串来了。”

With that expectation, the database wouldn't be fooled: it would only return rows where the usernamecolumn contains the actual string 'billysmith OR 1=1.' If nobody has that user name, it would return nothing.

有了这个期望，数据库就不会被愚弄了：它只会返回username列包含实际字符串“billysmith OR 1=1”的行。如果没有人拥有该用户名，则不会返回任何内容。

Other benefits of prepared statements

准备好的报表的其他好处

In addition to security benefits, prepared statements have a couple of speed benefits:

除了安全优势之外，准备好的语句还有几个速度优势：

• They can be reused with different parameters, which should be faster than building a new query from scratch, because the database already knows basically what you're about to ask for. It has already built its "query plan".
• Some databases (Postgres is one, I think) will start making a query plan as soon as they get the prepared statement - before you've actually sent the parameters to use with it. So you may see a speedup even on the first query.

• 它们可以与不同的参数一起重用，这应该比从头开始构建新查询更快，因为数据库基本上已经知道您将要请求什么。它已经建立了它的“查询计划”。
• 某些数据库（我认为 Postgres 就是其中之一）将在获得准备好的语句后立即开始制定查询计划 - 在您实际发送要使用的参数之前。因此，即使在第一次查询时，您也可能会看到加速。

For another explanation, see Theo's answer here.

有关另一种解释，请参阅 Theo 的回答here。

回答by Cory House

Unlike mysql_real_escape_string, PDO allows you to enforce a datatype.

与 mysql_real_escape_string 不同，PDO 允许您强制执行数据类型。

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>

Note that in the example above, the first parameter, calories, is required to be an integer (PDO::PARAM_INT).

请注意，在上面的示例中，第一个参数卡路里必须是整数 (PDO::PARAM_INT)。

Second, to me, PDO parameterized queries are easier to read. I'd rather read:

其次，对我来说，PDO 参数化查询更容易阅读。我宁愿阅读：

SELECT name FROM user WHERE id = ? AND admin = ? 

than

比

SELECT name FROM user WHERE id = mysql_real_escape_string($id) AND admin = mysql_real_escape_string($admin);

Third, you don't have to make sure you quote parameters properly. PDO takes care of that. For example, mysql_real_query_string:

第三，您不必确保正确引用参数。PDO 负责解决这个问题。例如，mysql_real_query_string：

SELECT * FROM user WHERE name = 'mysql_real_escape_string($name)' //note quotes around param

vs

对比

SELECT * FROM user WHERE name = ?

Finally, PDO allows you to port your app to a different db without changing your PHP data calls.

最后，PDO 允许您将应用程序移植到不同的数据库，而无需更改 PHP 数据调用。

回答by knittl

imagine you write something along the lines of:

想象你写了一些类似的东西：

$query = 'SELECT * FROM table WHERE id = ' . mysql_real_escape_string($id);

this will not save you from injections, because $id could be 1 OR 1=1and you will get all the records from the table. you'd have to cast $id to the right datatype (int in that case)

这不会使您免于注射，因为 $id 可能是1 OR 1=1并且您将从表中获取所有记录。您必须将 $id 转换为正确的数据类型（在这种情况下为 int）

pdo has another advantage, and that is the interchangability of database backends.

pdo 还有一个优势，那就是数据库后端的可互换性。

回答by Wes

In addition to preventing SQL injection, PDO allows you to prepare a query once and execute it multiple times. If your query is executed multiple times (within a loop, for instance), this method should be more efficient (I say "should be", because it looks like that is not always the case on older versions of MySQL). The prepare/bind method is also more in line with other languages I have worked with.

除了防止 SQL 注入之外，PDO 还允许您准备一次查询并多次执行它。如果您的查询被多次执行（例如在一个循环内），这种方法应该更有效（我说“应该”，因为在旧版本的 MySQL 上似乎并非总是如此）。准备/绑定方法也更符合我使用过的其他语言。

回答by Your Common Sense

Why is PDO better for escaping MySQL queries/querystrings than mysql_real_escape_string?

为什么 PDO 比 mysql_real_escape_string 更适合转义 MySQL 查询/查询字符串？

Simply because "escaping" alone makes no sense.
Moreover, it's different incomparablematters.

仅仅因为“逃避”是没有意义的。
而且，是不同的不可比物。

The only problem with escaping is that everyone takes it wrong,assuming it as some sort of "protection".
Everyone says "I escaped my variables" with the meaning "I protected my query".
While escaping alone has nothing to do with protection at all.

逃避的唯一问题是每个人都错误地认为它是某种“保护”。
每个人都说“我逃避了我的变量”，意思是“我保护了我的查询”。
而独自逃跑与保护根本无关。

Protection can be roughly achieved in case of I escaped and quoted my data, but it is not applicable everywhere, for the identifiers for example (As well as PDO, by the way).

如果我转义并引用了我的数据，可以大致实现保护，但它并不适用于所有地方，例如标识符（顺便说一下，还有 PDO）。

So, the answer is:

所以，答案是：

• PDO, when doing escaping for the binded values, applies not only escaping but also quoting - that's why it's better.
• "escaping" is not a synonym for the "protection". "escaping + quoting" roughly is.
• but for some query parts both methods inapplicable.

• PDO 在对绑定值进行转义时，不仅适用于转义，还适用于引用 - 这就是它更好的原因。
• “逃避”不是“保护”的同义词。“转义+引用”大致是。
• 但对于某些查询部分，这两种方法都不适用。