为什么我不应该在 PHP 中使用 mysql_* 函数？

Why shouldn't I use mysql_* functions in PHP?

提问by Madara's Ghost

What are the technical reasons for why one shouldn't use mysql_*functions? (e.g. mysql_query(), mysql_connect()or mysql_real_escape_string())?

为什么不应该使用mysql_*函数的技术原因是什么？（例如mysql_query()，mysql_connect()或mysql_real_escape_string()）？

Why should I use something else even if they work on my site?

为什么我要使用其他东西，即使它们在我的网站上工作？

If they don't work on my site, why do I get errors like

如果它们在我的网站上不起作用，为什么我会收到类似的错误

Warning: mysql_connect(): No such file or directory

警告：mysql_connect()：没有那个文件或目录

采纳答案by Quentin

The MySQL extension:

MySQL 扩展：

• Is not under active development
• Is officially deprecatedas of PHP 5.5 (released June 2013).
• Has been removedentirelyas of PHP 7.0 (released December 2015)
  • This means that as of 31 Dec 2018it does not exist in any supported version of PHP. If you are using a version of PHP which supports it, you are using a version which doesn't get security problems fixed.
• Lacks an OO interface
• Doesn't support:
  • Non-blocking, asynchronous queries
  • Prepared statementsor parameterized queries
  • Stored procedures
  • Multiple Statements
  • Transactions
  • The "new" password authentication method (on by default in MySQL 5.6; required in 5.7)
  • Any of the new functionality in MySQL 5.1 or later

• 未在积极开发中
• 被正式弃用的PHP 5.5（发布2013年6月）。
• 自 PHP 7.0起已完全删除（2015 年 12 月发布）
  • 这意味着截至 2018 年 12 月 31 日，它不存在于任何受支持的 PHP 版本中。如果您使用的 PHP 版本支持它，那么您使用的版本不会解决安全问题。
• 缺少 OO 接口
• 不支持：
  • 非阻塞、异步查询
  • 准备好的语句或参数化查询
  • 存储过程
  • 多条语句
  • 交易
  • “新”密码身份验证方法（在 MySQL 5.6 中默认启用；在 5.7 中需要）
  • MySQL 5.1 或更高版本中的任何新功能

Since it is deprecated, using it makes your code less future proof.

由于它已被弃用，因此使用它会使您的代码不那么具有前瞻性。

Lack of support for prepared statements is particularly important as they provide a clearer, less error-prone method of escaping and quoting external data than manually escaping it with a separate function call.

缺少对准备好的语句的支持尤其重要，因为与使用单独的函数调用手动转义外部数据相比，它们提供了一种更清晰、更不容易出错的转义和引用外部数据的方法。

See the comparison of SQL extensions.

请参阅SQL 扩展的比较。

回答by NullPoiиteя

PHP offers three different APIs to connect to MySQL. These are the mysql(removed as of PHP 7), mysqli, and PDOextensions.

PHP 提供了三种不同的 API 来连接到 MySQL。这些是mysql（从 PHP 7 开始删除）mysqli、 和PDO扩展。

The mysql_*functions used to be very popular, but their use is not encouraged anymore. The documentation team is discussing the database security situation, and educating users to move away from the commonly used ext/mysql extension is part of this (check php.internals: deprecating ext/mysql).

这些mysql_*功能曾经非常流行，但不再鼓励使用它们。文档团队正在讨论数据库安全情况，教育用户远离常用的 ext/mysql 扩展是其中的一部分（检查php.internals: deprecating ext/mysql）。

And the later PHP developer team has taken the decision to generate E_DEPRECATEDerrors when users connect to MySQL, whether through mysql_connect(), mysql_pconnect()or the implicit connection functionality built into ext/mysql.

而后来的PHP开发团队已产生决定E_DEPRECATED当用户连接到MySQL的错误，无论是通过mysql_connect()，mysql_pconnect()或内置于隐式连接功能ext/mysql。

ext/mysqlwas officially deprecated as of PHP 5.5and has been removed as of PHP 7.

ext/mysql被正式弃用PHP 5.5的，并已为PHP 7的去除。

See the Red Box?

看到红框了吗？

When you go on any mysql_*function manual page, you see a red box, explaining it should not be used anymore.

当您进入任何mysql_*功能手册页时，您会看到一个红色框，说明不应再使用它。

Why

为什么

---

---

Moving away from ext/mysqlis not only about security, but also about having access to all the features of the MySQL database.

远离ext/mysql不仅关乎安全性，还关乎访问 MySQL 数据库的所有功能。

ext/mysqlwas built for MySQL 3.23and only got very few additions since then while mostly keeping compatibility with this old version which makes the code a bit harder to maintain. Missing features that is not supported by ext/mysqlinclude: (from PHP manual).

ext/mysql是为MySQL 3.23构建的，从那时起只增加了很少的内容，同时主要保持与这个旧版本的兼容性，这使得代码更难维护。ext/mysql包含不支持的缺失功能：（来自 PHP 手册）。

• Stored procedures(can't handle multiple result sets)
• Prepared statements
• Encryption (SSL)
• Compression
• Full Charset support

• 存储过程（不能处理多个结果集）
• 准备好的报表
• 加密 (SSL)
• 压缩
• 全字符集支持

Reason to not use mysql_*function:

不使用mysql_*函数的原因：

• Not under active development
• Removed as of PHP 7
• Lacks an OO interface
• Doesn't support non-blocking, asynchronous queries
• Doesn't support prepared statements or parameterized queries
• Doesn't support stored procedures
• Doesn't support multiple statements
• Doesn't support transactions
• Doesn't support all of the functionality in MySQL 5.1

• 未在积极开发中
• 从 PHP 7 开始移除
• 缺少 OO 接口
• 不支持非阻塞、异步查询
• 不支持准备好的语句或参数化查询
• 不支持存储过程
• 不支持多条语句
• 不支持交易
• 不支持 MySQL 5.1 中的所有功能

Above point quoted from Quentin's answer

以上观点引自昆汀的回答

Lack of support for prepared statements is particularly important as they provide a clearer, less error prone method of escaping and quoting external data than manually escaping it with a separate function call.

缺乏对准备好的语句的支持尤其重要，因为与使用单独的函数调用手动转义外部数据相比，它们提供了一种更清晰、更不容易出错的转义和引用外部数据的方法。

See the comparison of SQL extensions.

请参阅SQL 扩展的比较。

---

---

Suppressing deprecation warnings

抑制弃用警告

While code is being converted to MySQLi/PDO, E_DEPRECATEDerrors can be suppressed by setting error_reportingin php.inito exclude E_DEPRECATED:

当代码被转换为MySQLi/ 时PDO，E_DEPRECATED可以通过error_reporting在php.ini 中设置为 exclude来抑制错误E_DEPRECATED:

error_reporting = E_ALL ^ E_DEPRECATED

Note that this will also hide other deprecation warnings, which, however, may be for things other than MySQL. (from PHP manual)

请注意，这也会隐藏其他弃用警告，但是，这些警告可能适用于 MySQL 以外的其他内容。（来自 PHP 手册）

The article PDO vs. MySQLi: Which Should You Use?by Dejan Marjanovicwill help you to choose.

文章PDO 与 MySQLi：你应该使用哪个？by Dejan Marjanovic将帮助您选择。

And a better way is PDO, and I am now writing a simple PDOtutorial.

更好的方法是PDO，我现在正在编写一个简单的PDO教程。

---

---

A simple and short PDO tutorial

一个简单而简短的 PDO 教程

---

---

Q. First question in my mind was: what is `PDO`?

问：我想到的第一个问题是：什么是“PDO”？

A. “PDO – PHP Data Objects– is a database access layer providing a uniform method of access to multiple databases.”

A. “ PDO – PHP 数据对象– 是一个数据库访问层，提供访问多个数据库的统一方法。”

---

---

Connecting to MySQL

连接到 MySQL

With mysql_*function or we can say it the old way (deprecated in PHP 5.5 and above)

使用mysql_*函数或者我们可以用旧的方式说它（在 PHP 5.5 及更高版本中弃用）

$link = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('testdb', $link);
mysql_set_charset('UTF-8', $link);

With PDO: All you need to do is create a new PDOobject. The constructor accepts parameters for specifying the database source PDO's constructor mostly takes four parameters which are DSN(data source name) and optionally username, password.

使用PDO：您需要做的就是创建一个新PDO对象。构造函数接受用于指定数据库源PDO的构造函数的参数，主要采用四个参数，它们是DSN（数据源名称）和可选的username, password。

Here I think you are familiar with all except DSN; this is new in PDO. A DSNis basically a string of options that tell PDOwhich driver to use, and connection details. For further reference, check PDO MySQL DSN.

在这里，我想您对所有内容都很熟悉，除了DSN；这是新的PDO。ADSN基本上是一串选项，用于说明PDO要使用的驱动程序和连接详细信息。如需进一步参考，请查看PDO MySQL DSN。

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

Note:you can also use charset=UTF-8, but sometimes it causes an error, so it's better to use utf8.

注意：您也可以使用charset=UTF-8，但有时会导致错误，因此最好使用utf8。

If there is any connection error, it will throw a PDOExceptionobject that can be caught to handle Exceptionfurther.

如果有任何连接错误，它将抛出一个PDOException可以被捕获以Exception进一步处理的对象。

Good read: Connections and Connection management ?

好读物：连接和连接管理？

You can also pass in several driver options as an array to the fourth parameter. I recommend passing the parameter which puts PDOinto exception mode. Because some PDOdrivers don't support native prepared statements, so PDOperforms emulation of the prepare. It also lets you manually enable this emulation. To use the native server-side prepared statements, you should explicitly set it false.

您还可以将多个驱动程序选项作为数组传递给第四个参数。我建议传递PDO进入异常模式的参数。由于某些PDO驱动程序不支持本机准备语句，因此PDO执行准备的模拟。它还允许您手动启用此仿真。要使用本机服务器端准备好的语句，您应该显式设置它false。

The other is to turn off prepare emulation which is enabled in the MySQLdriver by default, but prepare emulation should be turned off to use PDOsafely.

另一种是关闭MySQL驱动程序中默认启用的准备仿真，但准备仿真应该关闭才能PDO安全使用。

I will later explain why prepare emulation should be turned off. To find reason please check this post.

稍后我将解释为什么应关闭准备仿真。要查找原因，请查看此帖子。

It is only usable if you are using an old version of MySQLwhich I do not recommended.

仅当您使用MySQL我不推荐的旧版本时才可用。

Below is an example of how you can do it:

下面是一个如何做到这一点的例子：

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=UTF-8', 
              'username', 
              'password',
              array(PDO::ATTR_EMULATE_PREPARES => false,
              PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

Can we set attributes after PDO construction?

我们可以在 PDO 构建后设置属性吗？

Yes, we can also set some attributes after PDO construction with the setAttributemethod:

是的，我们还可以在 PDO 构建后使用以下setAttribute方法设置一些属性：

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=UTF-8', 
              'username', 
              'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Error Handling

错误处理

---

---

Error handling is much easier in PDOthan mysql_*.

错误处理PDO比mysql_*.

A common practice when using mysql_*is:

使用时的常见做法mysql_*是：

//Connected to MySQL
$result = mysql_query("SELECT * FROM table", $link) or die(mysql_error($link));

OR die()is not a good way to handle the error since we can not handle the thing in die. It will just end the script abruptly and then echo the error to the screen which you usually do NOT want to show to your end users, and let bloody hackers discover your schema. Alternately, the return values of mysql_*functions can often be used in conjunction with mysql_error()to handle errors.

OR die()不是处理错误的好方法，因为我们无法处理die. 它只会突然结束脚本，然后将错误回显到您通常不想向最终用户显示的屏幕上，并让该死的黑客发现您的架构。或者，mysql_*函数的返回值通常可以与mysql_error()结合使用来处理错误。

PDOoffers a better solution: exceptions. Anything we do with PDOshould be wrapped in a try-catchblock. We can force PDOinto one of three error modes by setting the error mode attribute. Three error handling modes are below.

PDO提供了更好的解决方案：异常。我们所做的任何事情都PDO应该包含在try-catch块中。我们可以PDO通过设置错误模式属性强制进入三种错误模式之一。三种错误处理模式如下。

• PDO::ERRMODE_SILENT. It's just setting error codes and acts pretty much the same as mysql_*where you must check each result and then look at $db->errorInfo();to get the error details.
• PDO::ERRMODE_WARNINGRaise E_WARNING. (Run-time warnings (non-fatal errors). Execution of the script is not halted.)
• PDO::ERRMODE_EXCEPTION: Throw exceptions. It represents an error raised by PDO. You should not throw a PDOExceptionfrom your own code. See Exceptionsfor more information about exceptions in PHP. It acts very much like or die(mysql_error());, when it isn't caught. But unlike or die(), the PDOExceptioncan be caught and handled gracefully if you choose to do so.

• PDO::ERRMODE_SILENT. 它只是设置错误代码，其行为与mysql_*您必须检查每个结果然后查看$db->errorInfo();以获取错误详细信息的方式几乎相同。
• PDO::ERRMODE_WARNING提高E_WARNING。（运行时警告（非致命错误）。脚本的执行不会停止。）
• PDO::ERRMODE_EXCEPTION: 抛出异常。它代表 PDO 引发的错误。你不应该PDOException从你自己的代码中抛出 a 。有关PHP 中异常的更多信息，请参阅异常。or die(mysql_error());当它没有被抓住时，它的行为非常像。但与 不同or die()的PDOException是，如果您选择这样做，可以优雅地捕获和处理 。

Good read:

好读：

• Errors and error handling ?
• The PDOException class ?
• Exceptions ?

• 错误和错误处理 ?
• PDOException 类？
• 例外？

Like:

喜欢：

$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$stmt->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

And you can wrap it in try-catch, like below:

您可以将其包装在try- 中catch，如下所示：

try {
    //Connect as appropriate as above
    $db->query('hi'); //Invalid query!
} 
catch (PDOException $ex) {
    echo "An Error occured!"; //User friendly message/message you want to show to user
    some_logging_function($ex->getMessage());
}

You do not have to handle with try-catchright now. You can catch it at any time appropriate, but I strongly recommend you to use try-catch. Also it may make more sense to catch it at outside the function that calls the PDOstuff:

你不必处理try-catch现在。您可以在任何合适的时间捕获它，但我强烈建议您使用try- catch。此外，在调用这些PDO东西的函数之外捕获它可能更有意义：

function data_fun($db) {
    $stmt = $db->query("SELECT * FROM table");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

//Then later
try {
    data_fun($db);
}
catch(PDOException $ex) {
    //Here you can handle error and show message/perform action you want.
}

Also, you can handle by or die()or we can say like mysql_*, but it will be really varied. You can hide the dangerous error messages in production by turning display_errors offand just reading your error log.

此外，您可以处理 byor die()或我们可以说 like mysql_*，但它会非常多样化。您可以通过转动display_errors off并阅读错误日志来隐藏生产中的危险错误消息。

Now, after reading all the things above, you are probably thinking: what the heck is that when I just want to start leaning simple SELECT, INSERT, UPDATE, or DELETEstatements? Don't worry, here we go:

现在，在阅读完以上所有内容后，您可能会想：当我只想开始学习简单的SELECT, INSERT, UPDATE, 或DELETE语句时，这到底是怎么回事？别着急，我们来：

---

---

Selecting Data

选择数据

So what you are doing in mysql_*is:

所以你正在做的mysql_*是：

<?php
$result = mysql_query('SELECT * from table') or die(mysql_error());

$num_rows = mysql_num_rows($result);

while($row = mysql_fetch_assoc($result)) {
    echo $row['field1'];
}

Now in PDO, you can do this like:

现在PDO，您可以这样做：

<?php
$stmt = $db->query('SELECT * FROM table');

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['field1'];
}

Or

或者

<?php
$stmt = $db->query('SELECT * FROM table');
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

//Use $results

Note: If you are using the method like below (query()), this method returns a PDOStatementobject. So if you want to fetch the result, use it like above.

注意：如果您使用如下所示的方法 ( query())，则此方法返回一个PDOStatement对象。因此，如果您想获取结果，请像上面一样使用它。

<?php
foreach($db->query('SELECT * FROM table') as $row) {
    echo $row['field1'];
}

In PDO Data, it is obtained via the ->fetch(), a method of your statement handle. Before calling fetch, the best approach would be telling PDO how you'd like the data to be fetched. In the below section I am explaining this.

在 PDO Data 中，它是通过->fetch(), 语句句柄的方法获得的。在调用 fetch 之前，最好的方法是告诉 PDO 您希望如何获取数据。在下面的部分中，我将解释这一点。

Fetch Modes

获取模式

Note the use of PDO::FETCH_ASSOCin the fetch()and fetchAll()code above. This tells PDOto return the rows as an associative array with the field names as keys. There are many other fetch modes too which I will explain one by one.

使用注意事项PDO::FETCH_ASSOC在fetch()和fetchAll()上面的代码。这告诉PDO将行作为关联数组返回，字段名称作为键。还有很多其他的获取模式，我将一一解释。

First of all, I explain how to select fetch mode:

首先，我解释一下如何选择fetch模式：

 $stmt->fetch(PDO::FETCH_ASSOC)

In the above, I have been using fetch(). You can also use:

在上面，我一直在使用fetch(). 您还可以使用：

• PDOStatement::fetchAll()- Returns an array containing all of the result set rows
• PDOStatement::fetchColumn()- Returns a single column from the next row of a result set
• PDOStatement::fetchObject()- Fetches the next row and returns it as an object.
• PDOStatement::setFetchMode()- Set the default fetch mode for this statement

• PDOStatement::fetchAll()- 返回一个包含所有结果集行的数组
• PDOStatement::fetchColumn()- 从结果集的下一行返回单列
• PDOStatement::fetchObject()- 获取下一行并将其作为对象返回。
• PDOStatement::setFetchMode()- 设置这条语句的默认获取模式

Now I come to fetch mode:

现在我来获取模式：

• PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set
• PDO::FETCH_BOTH(default): returns an array indexed by both column name and 0-indexed column number as returned in your result set

• PDO::FETCH_ASSOC: 返回结果集中返回的按列名索引的数组
• PDO::FETCH_BOTH（默认）：返回由结果集中返回的列名和 0 索引列号索引的数组

There are even more choices! Read about them all in PDOStatementFetch documentation..

还有更多选择！在PDOStatementFetch 文档中阅读有关它们的全部信息。.

Getting the row count:

获取行数：

Instead of using mysql_num_rowsto get the number of returned rows, you can get a PDOStatementand do rowCount(), like:

mysql_num_rows您可以获取 aPDOStatement和 do rowCount()，而不是用于获取返回的行数，例如：

<?php
$stmt = $db->query('SELECT * FROM table');
$row_count = $stmt->rowCount();
echo $row_count.' rows selected';

Getting the Last Inserted ID

获取最后插入的 ID

<?php
$result = $db->exec("INSERT INTO table(firstname, lastname) VAULES('John', 'Doe')");
$insertId = $db->lastInsertId();

---

---

Insert and Update or Delete statements

插入和更新或删除语句

What we are doing in mysql_*function is:

我们在mysql_*函数中所做的是：

<?php
$results = mysql_query("UPDATE table SET field='value'") or die(mysql_error());
echo mysql_affected_rows($result);

And in pdo, this same thing can be done by:

在 pdo 中，同样的事情可以通过以下方式完成：

<?php
$affected_rows = $db->exec("UPDATE table SET field='value'");
echo $affected_rows;

In the above query PDO::execexecute an SQL statement and returns the number of affected rows.

在上面的查询中PDO::exec执行一条 SQL 语句并返回受影响的行数。

_{Insert and delete will be covered later.}

_{插入和删除将在后面介绍。}

The above method is only useful when you are not using variable in query. But when you need to use a variable in a query, do not ever ever try like the above and there for prepared statement or parameterized statementis.

上述方法仅在查询中不使用变量时才有用。但是当你需要在查询中使用一个变量时，千万不要像上面那样尝试 准备语句或参数化语句。

---

---

Prepared Statements

准备好的报表

Q.What is a prepared statement and why do I need them?
A.A prepared statement is a pre-compiled SQL statement that can be executed multiple times by sending only the data to the server.

问：什么是准备好的声明，为什么我需要它们？
A.准备好的语句是预编译的 SQL 语句，可以通过只向服务器发送数据来执行多次。

The typical workflow of using a prepared statement is as follows (quoted from Wikipedia three 3 point):

使用prepared statement的典型工作流程如下（引用自维基百科三三点）：

1. Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled ?below):

   INSERT INTO PRODUCT (name, price) VALUES (?, ?)

2. The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.

3. Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply 'Bread' for the first parameter and 1.00for the second parameter.

1. Prepare：语句模板由应用程序创建并发送到数据库管理系统（DBMS）。某些值未指定，称为参数、占位符或绑定变量（标记?如下）：

   INSERT INTO PRODUCT (name, price) VALUES (?, ?)

2. DBMS 对语句模板进行解析、编译和查询优化，结果存储而不执行。

3. 执行：稍后，应用程序为参数提供（或绑定）值，然后 DBMS 执行语句（可能返回结果）。应用程序可以根据需要使用不同的值多次执行该语句。在此示例中，它可能为第一个参数和1.00第二个参数提供“面包” 。

You can use a prepared statement by including placeholders in your SQL. There are basically three ones without placeholders (don't try this with variable its above one), one with unnamed placeholders, and one with named placeholders.

您可以通过在 SQL 中包含占位符来使用准备好的语句。基本上有三个没有占位符的（不要在变量上面尝试这个），一个有未命名的占位符，一个有命名的占位符。

Q.So now, what are named placeholders and how do I use them?
A.Named placeholders. Use descriptive names preceded by a colon, instead of question marks. We don't care about position/order of value in name place holder:

问：那么现在，什么是命名占位符，我该如何使用它们？
A.命名占位符。使用以冒号开头的描述性名称，而不是问号。我们不关心名称占位符中的位置/值顺序：

 $stmt->bindParam(':bla', $bla);

bindParam(parameter,variable,data_type,length,driver_options)

bindParam(parameter,variable,data_type,length,driver_options)

You can also bind using an execute array as well:

您还可以使用执行数组进行绑定：

<?php
$stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
$stmt->execute(array(':name' => $name, ':id' => $id));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Another nice feature for OOPfriends is that named placeholders have the ability to insert objects directly into your database, assuming the properties match the named fields. For example:

对于OOP朋友来说，另一个不错的功能是命名占位符能够将对象直接插入到数据库中，假设属性与命名字段匹配。例如：

class person {
    public $name;
    public $add;
    function __construct($a,$b) {
        $this->name = $a;
        $this->add = $b;
    }

}
$demo = new person('john','29 bla district');
$stmt = $db->prepare("INSERT INTO table (name, add) value (:name, :add)");
$stmt->execute((array)$demo);

Q.So now, what are unnamed placeholders and how do I use them?
A.Let's have an example:

问：那么现在，什么是未命名占位符，我该如何使用它们？
A.让我们举个例子：

<?php
$stmt = $db->prepare("INSERT INTO folks (name, add) values (?, ?)");
$stmt->bindValue(1, $name, PDO::PARAM_STR);
$stmt->bindValue(2, $add, PDO::PARAM_STR);
$stmt->execute();

and

和

$stmt = $db->prepare("INSERT INTO folks (name, add) values (?, ?)");
$stmt->execute(array('john', '29 bla district'));

In the above, you can see those ?instead of a name like in a name place holder. Now in the first example, we assign variables to the various placeholders ($stmt->bindValue(1, $name, PDO::PARAM_STR);). Then, we assign values to those placeholders and execute the statement. In the second example, the first array element goes to the first ?and the second to the second ?.

在上面，您可以看到那些?而不是名称占位符中的名称。现在在第一个示例中，我们将变量分配给各种占位符 ( $stmt->bindValue(1, $name, PDO::PARAM_STR);)。然后，我们为这些占位符赋值并执行语句。在第二个示例中，第一个数组元素转到第一个?，第二个转到第二个?。

NOTE: In unnamed placeholderswe must take care of the proper order of the elements in the array that we are passing to the PDOStatement::execute()method.

注意：在未命名的占位符中，我们必须注意传递给PDOStatement::execute()方法的数组中元素的正确顺序。

---

---

SELECT, INSERT, UPDATE, DELETEprepared queries

SELECT, INSERT, UPDATE,DELETE准备查询

1. SELECT:

   $stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
   $stmt->execute(array(':name' => $name, ':id' => $id));
   $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
   

2. INSERT:

   $stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
   $stmt->execute(array(':field1' => $field1, ':field2' => $field2));
   $affected_rows = $stmt->rowCount();
   

3. DELETE:

   $stmt = $db->prepare("DELETE FROM table WHERE id=:id");
   $stmt->bindValue(':id', $id, PDO::PARAM_STR);
   $stmt->execute();
   $affected_rows = $stmt->rowCount();
   

4. UPDATE:

   $stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
   $stmt->execute(array($name, $id));
   $affected_rows = $stmt->rowCount();
   

1. SELECT：

   $stmt = $db->prepare("SELECT * FROM table WHERE id=:id AND name=:name");
   $stmt->execute(array(':name' => $name, ':id' => $id));
   $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
   

2. INSERT：

   $stmt = $db->prepare("INSERT INTO table(field1,field2) VALUES(:field1,:field2)");
   $stmt->execute(array(':field1' => $field1, ':field2' => $field2));
   $affected_rows = $stmt->rowCount();
   

3. DELETE：

   $stmt = $db->prepare("DELETE FROM table WHERE id=:id");
   $stmt->bindValue(':id', $id, PDO::PARAM_STR);
   $stmt->execute();
   $affected_rows = $stmt->rowCount();
   

4. UPDATE：

   $stmt = $db->prepare("UPDATE table SET name=? WHERE id=?");
   $stmt->execute(array($name, $id));
   $affected_rows = $stmt->rowCount();
   

---

---

NOTE:

笔记：

However PDOand/or MySQLiare not completely safe. Check the answer Are PDO prepared statements sufficient to prevent SQL injection?by ircmaxell. Also, I am quoting some part from his answer:

但是PDO和/或MySQLi不是完全安全的。检查答案PDO 准备好的语句是否足以防止 SQL 注入？通过ircmaxell。另外，我引用了他的回答中的一部分：

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->query('SET NAMES GBK');
$stmt = $pdo->prepare("SELECT * FROM test WHERE name = ? LIMIT 1");
$stmt->execute(array(chr(0xbf) . chr(0x27) . " OR 1=1 /*"));

回答by Madara's Ghost

First, let's begin with the standard comment we give everyone:

首先，我们先从我们给大家的标准评论开始：

Please, don't use mysql_*functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statementsinstead, and use PDOor MySQLi- this articlewill help you decide which. If you choose PDO, here is a good tutorial.

请不要mysql_*在新代码中使用函数。它们不再被维护并被正式弃用。看到红框了吗？了解准备好的语句，并使用PDO或MySQLi-本文将帮助您决定哪个。如果您选择 PDO，这里有一个很好的教程。

Let's go through this, sentence by sentence, and explain:

让我们逐句逐句解释一下：

• They are no longer maintained, and are officially deprecated

  This means that the PHP community is gradually dropping support for these very old functions. They are likely to not exist in a future (recent) version of PHP! Continued use of these functions may break your code in the (not so) far future.

  NEW! - ext/mysql is now officially deprecated as of PHP 5.5!

  Newer! ext/mysql has been removed in PHP 7.

• Instead, you should learn of prepared statements

  mysql_*extension does not support prepared statements, which is (among other things) a very effective countermeasure against SQL Injection. It fixed a very serious vulnerability in MySQL dependent applications which allows attackers to gain access to your script and perform any possible queryon your database.

  For more information, see How can I prevent SQL injection in PHP?

• See the Red Box?

  When you go to any mysqlfunction manual page, you see a red box, explaining it should not be used anymore.

• Use either PDO or MySQLi

  There are better, more robust and well-built alternatives, PDO - PHP Database Object, which offers a complete OOP approach to database interaction, and MySQLi, which is a MySQL specific improvement.

• 它们不再被维护，并被正式弃用

  这意味着 PHP 社区正在逐渐放弃对这些非常旧的功能的支持。它们很可能不存在于未来（最近）的 PHP 版本中！继续使用这些函数可能会在（并非如此）遥远的将来破坏您的代码。

  新的！- ext/mysql从 PHP 5.5 开始正式弃用！

  更新！ext/mysql已在 PHP 7 中删除。

• 相反，您应该了解准备好的语句

  mysql_*扩展不支持准备好的语句，这是（除其他外）一个非常有效的对抗SQL 注入的对策。它修复了 MySQL 相关应用程序中的一个非常严重的漏洞，该漏洞允许攻击者访问您的脚本并对您的数据库执行任何可能的查询。

  有关更多信息，请参阅如何防止 PHP 中的 SQL 注入？

• 看到红框了吗？

  当您转到任何mysql功能手册页时，您会看到一个红色框，说明不应再使用它。

• 使用 PDO 或 MySQLi

  有更好、更健壮和构建良好的替代方案，PDO-PHP 数据库对象，它为数据库交互提供了完整的 OOP 方法，以及MySQLi，它是 MySQL 的特定改进。

回答by mario

Ease of use

便于使用

The analytic and synthetic reasons were already mentioned. For newcomers there's a more significant incentive to stop using the dated mysql_ functions.

分析和综合原因已经提到。对于新人来说，停止使用过时的 mysql_ 函数有更大的动机。

Contemporary database APIs are just easierto use.

现代数据库 API更易于使用。

It's mostly the bound parameterswhich can simplify code. And with excellent tutorials (as seen above)the transition to PDOisn't overly arduous.

它主要是可以简化代码的绑定参数。有了优秀的教程（如上所示），向PDO的过渡并不会过于艰巨。

Rewriting a larger code base at once however takes time. Raison d'être for this intermediate alternative:

然而，一次重写更大的代码库需要时间。这个中间选择的存在理由：

Equivalent pdo_* functions in place of mysql_*

等效的 pdo_* 函数代替mysql_*

Using <pdo_mysql.php>you can switch from the old mysql_ functions with minimal effort. It adds pdo_function wrappers which replace their mysql_counterparts.

使用< pdo_mysql.php>您可以用旧mysql_功能切换最小的努力。它添加了pdo_函数包装器来替换它们的mysql_对应物。

1. Simply include_once("pdo_mysql.php");in each invocation script that has to interact with the database.
   

2. Remove the mysql_function prefix everywhereand replace it with pdo_.

   • mysql_connect()becomes pdo_connect()
   • mysql_query()becomes pdo_query()
   • mysql_num_rows()becomes pdo_num_rows()
   • mysql_insert_id()becomes pdo_insert_id()
   • mysql_fetch_array()becomes pdo_fetch_array()
   • mysql_fetch_assoc()becomes pdo_fetch_assoc()
   • mysql_real_escape_string()becomes pdo_real_escape_string()
   • and so on...
     
     

3. Your code will work alike and still mostly look the same:

   include_once("pdo_mysql.php"); 
   
   pdo_connect("localhost", "usrABC", "pw1234567");
   pdo_select_db("test");
   
   $result = pdo_query("SELECT title, html FROM pages");  
   
   while ($row = pdo_fetch_assoc($result)) {
       print "$row[title] - $row[html]";
   }
   

1. 只需在每个必须与数据库交互的调用脚本中。 include_once("pdo_mysql.php");
   

2. 删除无处不在的mysql_函数前缀并将其替换为.pdo_

   • mysql_connect()变成 pdo_connect()
   • mysql_query()变成 pdo_query()
   • mysql_num_rows()变成 pdo_num_rows()
   • mysql_insert_id()变成 pdo_insert_id()
   • mysql_fetch_array()变成 pdo_fetch_array()
   • mysql_fetch_assoc()变成 pdo_fetch_assoc()
   • mysql_real_escape_string()变成 pdo_real_escape_string()
   • 等等...
     
     

3. 您的代码将类似地工作，但看起来仍然大致相同：

   include_once("pdo_mysql.php"); 
   
   pdo_connect("localhost", "usrABC", "pw1234567");
   pdo_select_db("test");
   
   $result = pdo_query("SELECT title, html FROM pages");  
   
   while ($row = pdo_fetch_assoc($result)) {
       print "$row[title] - $row[html]";
   }
   

Et voilà.
Your code is usingPDO.
Now it's time to actually utilizeit.

等等。
您的代码正在使用PDO。
现在是实际使用它的时候了。

Bound parameters can be easy to use

绑定参数易于使用

You just need a less unwieldy API.

您只需要一个不那么笨拙的 API。

pdo_query()adds very facile support for bound parameters. Converting old code is straightforward:

pdo_query()为绑定参数添加了非常轻松的支持。转换旧代码很简单：

Move your variables out of the SQL string.

将变量移出 SQL 字符串。

• Add them as comma delimited function parameters to pdo_query().
• Place question marks ?as placeholders where the variables were before.
• Get rid of 'single quotes that previously enclosed string values/variables.

• 将它们作为逗号分隔的函数参数添加到pdo_query().
• 将问号?作为占位符放在变量之前的位置。
• 去掉'以前包含字符串值/变量的单引号。

The advantage becomes more obvious for lengthier code.

对于较长的代码，优势变得更加明显。

Often string variables aren't just interpolated into SQL, but concatenated with escaping calls in between.

通常，字符串变量不仅会插入到 SQL 中，还会与中间的转义调用连接在一起。

pdo_query("SELECT id, links, html, title, user, date FROM articles
   WHERE title='" . pdo_real_escape_string($title) . "' OR id='".
   pdo_real_escape_string($title) . "' AND user <> '" .
   pdo_real_escape_string($root) . "' ORDER BY date")

With ?placeholders applied you don't have to bother with that:

随着?占位符加你不必与理会：

pdo_query("SELECT id, links, html, title, user, date FROM articles
   WHERE title=? OR id=? AND user<>? ORDER BY date", $title, $id, $root)

Remember that pdo_* still allows either or.
Just don't escape a variable andbind it in the same query.

请记住， pdo_* 仍然允许或。
只是不要转义变量并将其绑定在同一个查询中。

• The placeholder feature is provided by the real PDO behind it.
• Thus also allowed :namedplaceholder lists later.

• 占位符功能由其背后的真实 PDO 提供。
• 因此，:named稍后也允许占位符列表。

More importantly you can pass $_REQUEST[] variables safely behind any query. When submitted <form>fields match the database structure exactly it's even shorter:

更重要的是，您可以在任何查询之后安全地传递 $_REQUEST[] 变量。当提交的<form>字段与数据库结构完全匹配时，它甚至更短：

pdo_query("INSERT INTO pages VALUES (?,?,?,?,?)", $_POST);

So much simplicity. But let's get back to some more rewriting advises and technical reasons on why you may want to get rid of mysql_and escaping.

如此简单。但是让我们回到一些关于为什么你可能想要摆脱mysql_和逃避的重写建议和技术原因。

Fix or remove any oldschool sanitize()function

修复或删除任何旧学校sanitize()功能

Once you have converted all mysql_calls to pdo_querywith bound params, remove all redundant pdo_real_escape_stringcalls.

将所有mysql_调用转换为pdo_query绑定参数后，删除所有冗余pdo_real_escape_string调用。

In particular you should fix any sanitizeor cleanor filterThisor clean_datafunctions as advertised by dated tutorials in one form or the other:

特别是，您应该以一种或另一种形式修复过时教程所宣传的任何sanitize或clean或filterThis或clean_data功能：

function sanitize($str) {
   return trim(strip_tags(htmlentities(pdo_real_escape_string($str))));
}

Most glaring bug here is the lack of documentation. More significantly the order of filtering was in exactly the wrong order.

这里最明显的错误是缺乏文档。更重要的是，过滤的顺序完全错误。

• Correct order would have been: deprecatedly stripslashesas the innermost call, then trim, afterwards strip_tags, htmlentitiesfor output context, and only lastly the _escape_stringas its application should directly preceed the SQL intersparsing.

• But as first step just get rid of the _real_escape_stringcall.

• You may have to keep the rest of your sanitize()function for now if your database and application flow expect HTML-context-safe strings. Add a comment that it applies only HTML escaping henceforth.

• String/value handling is delegated to PDO and its parameterized statements.

• If there was any mention of stripslashes()in your sanitize function, it may indicate a higher level oversight.

  • That was commonly there to undo damage (double escaping) from the deprecated magic_quotes. Which however is best fixed centrally, not string by string.

  • Use one of the userland reversalapproaches. Then remove the stripslashes()in the sanitizefunction.

  Historic note on magic_quotes.That feature is rightly deprecated. It's often incorrectly portrayed as failed securityfeature however. But magic_quotes are as much a failed security feature as tennis balls have failed as nutrition source. That simply wasn't their purpose.

  The original implementation in PHP2/FI introduced it explicitly with just "quotes will be automatically escaped making it easier to pass form data directly to msql queries". Notably it was accidentially safe to use with mSQL, as that supported ASCII only.
  Then PHP3/Zend reintroduced magic_quotes for MySQL and misdocumented it. But originally it was just a convenience feature, not intend for security.

• 正确的顺序应该是：不推荐使用stripslashes最里面的调用，然后trim，之后strip_tags，htmlentities用于输出上下文，最后，_escape_string因为它的应用程序应该直接在 SQL 解析之前。

• 但作为第一步，只需摆脱_real_escape_string电话。

• sanitize()如果您的数据库和应用程序流需要 HTML 上下文安全字符串，则您可能必须暂时保留其余函数。添加一条注释，说明此后仅适用 HTML 转义。

• 字符串/值处理委托给 PDO 及其参数化语句。

• 如果stripslashes()在您的消毒功能中有任何提及，则可能表明存在更高级别的监督。

  • 这通常用于从已弃用的magic_quotes. 然而，最好集中固定，而不是一串串。

  • 使用用户空间反转方法之一。然后取出stripslashes()的sanitize功能。

  关于magic_quotes 的历史记录。该功能已被正确弃用。然而，它经常被错误地描述为失败的安全功能。但是，magic_quotes 是一个失败的安全功能，就像网球作为营养源失败一样。那根本就不是他们的目的。

  PHP2/FI 中的原始实现仅使用“引号将被自动转义，从而更容易将表单数据直接传递给 msql 查询”。值得注意的是，与mSQL一起使用是意外安全的，因为它仅支持 ASCII。
  然后 PHP3/Zend 为 MySQL 重新引入了 magic_quotes 并对其进行了错误记录。但最初它只是一个方便的功能，而不是为了安全。

How prepared statements differ

准备好的报表有何不同

When you scramble string variables into the SQL queries, it doesn't just get more intricate for you to follow. It's also extraneous effort for MySQL to segregate code and data again.

当您将字符串变量加扰到 SQL 查询中时，它不仅会变得更加复杂。对于 MySQL 来说，再次分离代码和数据也是多余的工作。

SQL injections simply are when data bleeds into codecontext. A database server can't later spot where PHP originally glued variables inbetween query clauses.

SQL 注入只是当数据渗入代码上下文时。数据库服务器以后无法发现 PHP 最初在查询子句之间粘合变量的位置。

With bound parameters you separate SQL code and SQL-context values in your PHP code. But it doesn't get shuffled up again behind the scenes (except with PDO::EMULATE_PREPARES). Your database receives the unvaried SQL commands and 1:1 variable values.

使用绑定参数，您可以在 PHP 代码中分离 SQL 代码和 SQL 上下文值。但它不会在幕后再次改组（除了 PDO::EMULATE_PREPARES）。您的数据库接收不变的 SQL 命令和 1:1 的变量值。

While this answer stresses that you should care about the readability advantages of dropping mysql_. There's occasionally also a performance advantage (repeated INSERTs with just differing values) due to this visible and technical data/code separation.

虽然这个答案强调你应该关心 drop 的可读性优势mysql_。由于这种可见的技术数据/代码分离，偶尔也会有性能优势（重复插入只是不同的值）。

Beware that parameter binding still isn't a magic one-stop solution against allSQL injections. It handles the most common use for data/values. But can't whitelist column name / table identifiers, help with dynamic clause construction, or just plain array value lists.

请注意，参数绑定仍然不是针对所有SQL 注入的神奇一站式解决方案。它处理数据/值的最常见用途。但是不能将列名/表标识符列入白名单，帮助动态子句构造，或者只是简单的数组值列表。

Hybrid PDO use

混合 PDO 使用

These pdo_*wrapper functions make a coding-friendly stop-gap API. (It's pretty much what MYSQLIcould have been if it wasn't for the idiosyncratic function signature shift). They also expose the real PDO at most times.
Rewriting doesn't have to stop at using the new pdo_ function names. You could one by one transition each pdo_query() into a plain $pdo->prepare()->execute() call.

这些pdo_*包装函数构成了一个对编码友好的临时 API。（MYSQLI如果不是特殊的函数签名转换，这几乎是什么情况）。大多数时候，它们也会暴露真正的 PDO。
重写不必停止使用新的 pdo_ 函数名称。您可以将每个 pdo_query() 一一转换为普通的 $pdo->prepare()->execute() 调用。

It's best to start at simplifying again however. For example the common result fetching:

然而，最好从简化开始。例如常见的结果获取：

$result = pdo_query("SELECT * FROM tbl");
while ($row = pdo_fetch_assoc($result)) {

Can be replaced with just an foreach iteration:

可以只用 foreach 迭代替换：

foreach ($result as $row) {

Or better yet a direct and complete array retrieval:

或者更好的是直接和完整的数组检索：

$result->fetchAll();

You'll get more helpful warnings in most cases than PDO or mysql_ usually provide after failed queries.

在大多数情况下，您会得到比 PDO 或 mysql_ 通常在查询失败后提供的更多有用的警告。

Other options

其他选项

So this hopefully visualized some practicalreasons and a worthwile pathway to drop mysql_.

所以这有希望地形象化了一些实际的原因和一个有价值的下降途径mysql_。

Just switching to pdodoesn't quite cut it. pdo_query()is also just a frontend onto it.

只是切换到pdo并不能完全解决它。pdo_query()也只是它的前端。

Unless you also introduce parameter binding or can utilize something else from the nicer API, it's a pointless switch. I hope it's portrayed simple enough to not further the discouragement to newcomers. (Education usually works better than prohibition.)

除非您还引入了参数绑定或可以利用更好的 API 中的其他内容，否则这是一个毫无意义的切换。我希望它被描绘得足够简单，不要让新人更加沮丧。（教育通常比禁止更有效。）

While it qualifies for the simplest-thing-that-could-possibly-work category, it's also still very experimental code. I just wrote it over the weekend. There's a plethora of alternatives however. Just google for PHP database abstractionand browse a little. There always have been and will be lots of excellent libraries for such tasks.

虽然它符合最简单的可能工作类别，但它仍然是非常实验性的代码。我周末刚写的。然而，有很多选择。只需谷歌搜索PHP 数据库抽象并浏览一下。一直以来都会有很多优秀的库用于此类任务。

If you want to simplify your database interaction further, mappers like Paris/Idiormare worth a try. Just like nobody uses the bland DOM in JavaScript anymore, you don't have to babysit a raw database interface nowadays.

如果你想进一步简化你的数据库交互，像Paris/Idiorm这样的映射器值得一试。就像再也没有人在 JavaScript 中使用平淡无奇的 DOM 一样，如今您不必照看原始数据库接口。

回答by Alnitak

The mysql_functions:

该mysql_功能：

1. are out of date - they're not maintained any more
2. don't allow you to move easily to another database backend
3. don't support prepared statements, hence
4. encourage programmers to use concatenation to build queries, leading to SQL injection vulnerabilities

1. 已过时 - 它们不再维护
2. 不允许您轻松移动到另一个数据库后端
3. 不支持准备好的语句，因此
4. 鼓励程序员使用concatenation来构建查询，导致SQL注入漏洞

回答by Your Common Sense

Speaking of technicalreasons, there are only a few, extremely specific and rarely used. Most likely you will never ever use them in your life.
Maybe I am too ignorant, but I never had an opportunity to use them things like

说到技术原因，只有几个，极其具体，很少用到。很可能你永远不会在你的生活中使用它们。
也许我太无知了，但我从来没有机会使用它们，比如

• non-blocking, asynchronous queries
• stored procedures returning multiple resultsets
• Encryption (SSL)
• Compression

• 非阻塞、异步查询
• 返回多个结果集的存储过程
• 加密 (SSL)
• 压缩

If you need them - these are no doubt technical reasons to move away from mysql extension toward something more stylish and modern-looking.

如果您需要它们 - 这些无疑是从 mysql 扩展转向更时尚和现代的东西的技术原因。

Nevertheless, there are also some non-technical issues, which can make your experience a bit harder

然而，也有一些非技术问题，这可能会让你的体验有点困难

• further use of these functions with modern PHP versions will raise deprecated-level notices. They simply can be turned off.
• in a distant future, they can be possibly removed from the default PHP build. Not a big deal too, as mydsql ext will be moved into PECL and every hoster will be happy to compile PHP with it, as they don't want to lose clients whose sites were working for decades.
• strong resistance from Stackoverflow community. Еverytime you mention these honest functions, you being told that they are under strict taboo.
• being an average PHP user, most likely your idea of using these functions is error-prone and wrong. Just because of all these numerous tutorials and manuals which teach you the wrong way. Not the functions themselves - I have to emphasize it - but the way they are used.

• 在现代 PHP 版本中进一步使用这些函数将引发弃用级别的通知。它们可以简单地关闭。
• 在遥远的将来，它们可能会从默认的 PHP 构建中删除。也没什么大不了的，因为 mydsql ext 将被转移到 PECL 中，每个托管商都会很乐意用它编译 PHP，因为他们不想失去那些网站已经工作了几十年的客户。
• Stackoverflow 社区的强烈抵制。Е每次你提到这些诚实的功能时，你都会被告知它们是严格的禁忌。
• 作为一个普通的 PHP 用户，您使用这些函数的想法很可能容易出错并且是错误的。只是因为所有这些无数的教程和手册教你错误的方式。不是函数本身——我必须强调它——而是它们的使用方式。

This latter issue is a problem.
But, in my opinion, the proposed solution is no better either.
It seems to me too idealistica dream that all those PHP users will learn how to handle SQL queries properly at once. Most likely they would just change mysql_* to mysqli_* mechanically, leaving the approach the same. Especially because mysqli makes prepared statements usage incredible painful and troublesome.
Not to mention that nativeprepared statements aren't enough to protectfrom SQL injections, and neither mysqli nor PDO offers a solution.

后一个问题是一个问题。
但是，在我看来，提议的解决方案也好不到哪里去。
在我看来，所有这些 PHP 用户都将学习如何正确处理 SQL 查询是一个过于理想化的梦想。他们很可能只是机械地将 mysql_* 更改为 mysqli_* ，而保持方法不变。特别是因为 mysqli 使准备好的语句使用令人难以置信的痛苦和麻烦。
更不用说原生准备好的语句不足以防止SQL 注入，而且 mysqli 和 PDO 都没有提供解决方案。

So, instead of fighting this honest extension, I'd prefer to fight wrong practices and educate people in the right ways.

因此，与其与这种诚实的扩展作斗争，我更愿意与错误的做法作斗争并以正确的方式教育人们。

Also, there are some false or non-significant reasons, like

此外，还有一些虚假或不重要的原因，例如

• Doesn't support Stored Procedures (we were using mysql_query("CALL my_proc");for ages)
• Doesn't support Transactions (same as above)
• Doesn't support Multiple Statements (who need them?)
• Not under active development (so what? does it affect youin any practical way?)
• Lacks an OO interface (to create one is a matter of several hours)
• Doesn't support Prepared Statements or Parametrized Queries

• 不支持存储过程（我们使用mysql_query("CALL my_proc");了很长时间）
• 不支持交易（同上）
• 不支持多语句（谁需要它们？）
• 未在积极开发中（那又怎样？它对您有任何实际影响吗？）
• 缺少 OO 接口（创建一个需要几个小时）
• 不支持准备好的语句或参数化查询

The last one is an interesting point. Although mysql ext do not support nativeprepared statements, they aren't required for the safety. We can easily fake prepared statements using manually handled placeholders (just like PDO does):

最后一点很有趣。尽管 mysql ext 不支持本机准备好的语句，但出于安全考虑，它们不是必需的。我们可以使用手动处理的占位符轻松伪造准备好的语句（就像 PDO 一样）：

function paraQuery()
{
    $args  = func_get_args();
    $query = array_shift($args);
    $query = str_replace("%s","'%s'",$query); 

    foreach ($args as $key => $val)
    {
        $args[$key] = mysql_real_escape_string($val);
    }

    $query  = vsprintf($query, $args);
    $result = mysql_query($query);
    if (!$result)
    {
        throw new Exception(mysql_error()." [$query]");
    }
    return $result;
}

$query  = "SELECT * FROM table where a=%s AND b LIKE %s LIMIT %d";
$result = paraQuery($query, $a, "%$b%", $limit);

voila, everything is parameterized and safe.

瞧，一切都是参数化和安全的。

But okay, if you don't like the red box in the manual, a problem of choice arises: mysqli or PDO?

但是好吧，如果你不喜欢手册中的红框，就会出现一个选择问题：mysqli 还是 PDO？

Well, the answer would be as follows:

那么，答案如下：

• If you understand the necessity of using a database abstraction layerand looking for an API to create one, mysqliis a very good choice, as it indeed supports many mysql-specific features.

• If, like vast majority of PHP folks, you are using raw API calls right in the application code (which is essentially wrong practice) - PDO is the only choice, as this extension pretends to be not just API but rather a semi-DAL, still incomplete but offers many important features, with two of them makes PDO critically distinguished from mysqli:

  • unlike mysqli, PDO can bind placeholders by value, which makes dynamically built queries feasible without several screens of quite messy code.
  • unlike mysqli, PDO can always return query result in a simple usual array, while mysqli can do it only on mysqlnd installations.

• 如果您了解使用数据库抽象层并寻找 API 来创建一个数据库抽象层的必要性，mysqli是一个非常好的选择，因为它确实支持许多 mysql 特定的功能。

• 如果像绝大多数 PHP 人员一样，您在应用程序代码中使用原始 API 调用（这本质上是错误的做法） - PDO 是唯一的选择，因为这个扩展伪装成不仅仅是 API 而是半 DAL，仍然不完整，但提供了许多重要的功能，其中两个使 PDO 与 mysqli 区别开来：

  • 与 mysqli 不同，PDO 可以按值绑定占位符，这使得动态构建的查询变得可行，而无需几个屏幕非常混乱的代码。
  • 与 mysqli 不同，PDO 总是可以在一个简单的普通数组中返回查询结果，而 mysqli 只能在 mysqlnd 安装上这样做。

So, if you are an average PHP user and want to save yourself a ton of headaches when using native prepared statements, PDO - again - is the only choice.
However, PDO is not a silver bullet too and has its hardships.
So, I wrote solutions for all the common pitfalls and complex cases in the PDO tag wiki

因此，如果您是一个普通的 PHP 用户并且想要在使用原生准备好的语句时为自己省去一大堆麻烦，那么 PDO - 再次 - 是唯一的选择。
然而，PDO 也不是灵丹妙药，也有它的困难。
因此，我为PDO 标签 wiki中的所有常见陷阱和复杂情况编写了解决方案

Nevertheless, everyone talking about extensions always missing the 2 important factsabout Mysqli and PDO:

然而，每个谈论扩展的人总是忽略了关于 Mysqli 和 PDO的两个重要事实：

1. Prepared statement isn't a silver bullet. There are dynamical identifiers which cannot be bound using prepared statements. There are dynamical queries with an unknown number of parameters which makes query building a difficult task.

2. Neither mysqli_* nor PDO functions should have appeared in the application code.
   There ought to be an abstraction layerbetween them and application code, which will do all the dirty job of binding, looping, error handling, etc. inside, making application code DRY and clean. Especially for the complex cases like dynamical query building.

1. 准备好的声明不是灵丹妙药。存在不能使用准备好的语句绑定的动态标识符。存在具有未知数量参数的动态查询，这使得查询构建成为一项艰巨的任务。

2. mysqli_* 和 PDO 函数都不应该出现在应用程序代码中。它们和应用程序代码之间
   应该有一个抽象层，它将在内部完成绑定、循环、错误处理等所有肮脏的工作，使应用程序代码干干净净。特别是对于动态查询构建等复杂情况。

So, just switching to PDO or mysqli is not enough. One has to use an ORM, or a query builder, or whatever database abstraction class instead of calling raw API functions in their code.
And contrary - if you have an abstraction layer between your application code and mysql API - it doesn't actually matter which engine is used.You can use mysql ext until it goes deprecated and then easily rewrite your abstraction class to another engine, having all the application code intact.

所以，仅仅切换到 PDO 或 mysqli 是不够的。必须使用 ORM、查询构建器或任何数据库抽象类，而不是在其代码中调用原始 API 函数。
相反 - 如果您的应用程序代码和 mysql API 之间有一个抽象层 -实际上使用哪个引擎并不重要。您可以使用 mysql ext 直到它被弃用，然后轻松地将您的抽象类重写为另一个引擎，使所有应用程序代码保持完整。

Here are some examples based on my safemysql classto show how such an abstraction class ought to be:

以下是一些基于我的safemysql 类的示例，以展示此类抽象类应该如何：

$city_ids = array(1,2,3);
$cities   = $db->getCol("SELECT name FROM cities WHERE is IN(?a)", $city_ids);

Compare this one single line with amount of code you will need with PDO.
Then compare with crazy amount of codeyou will need with raw Mysqli prepared statements. Note that error handling, profiling, query logging already built in and running.

将这一行与 PDO 所需的代码量进行比较。
然后将您需要的大量代码与原始 Mysqli 准备好的语句进行比较。请注意，错误处理、分析、查询日志已经内置并正在运行。

$insert = array('name' => 'John', 'surname' => "O'Hara");
$db->query("INSERT INTO users SET ?u", $insert);

Compare it with usual PDO inserts, when every single field name being repeated six to ten times - in all these numerous named placeholders, bindings, and query definitions.

将它与通常的 PDO 插入进行比较，当每个字段名称重复六到十次时 - 在所有这些众多命名的占位符、绑定和查询定义中。

Another example:

另一个例子：

$data = $db->getAll("SELECT * FROM goods ORDER BY ?n", $_GET['order']);

You can hardly find an example for PDO to handle such practical case.
And it will be too wordy and most likely unsafe.

您几乎找不到 PDO 处理此类实际案例的示例。
它会过于冗长，而且很可能是不安全的。

So, once more - it is not just raw driver should be your concern but abstraction class, useful not only for silly examples from beginner's manual but to solve whatever real-life problems.

所以，再一次 - 不仅仅是原始驱动程序应该是您关注的问题，而且是抽象类，不仅对于初学者手册中的愚蠢示例有用，而且对于解决任何现实生活中的问题都很有用。

回答by Trott

There are many reasons, but perhaps the most important one is that those functions encourage insecure programming practices because they do not support prepared statements. Prepared statements help prevent SQL injection attacks.

原因有很多，但也许最重要的一个是这些函数鼓励不安全的编程实践，因为它们不支持准备好的语句。准备好的语句有助于防止 SQL 注入攻击。

When using mysql_*functions, you have to remember to run user-supplied parameters through mysql_real_escape_string(). If you forget in just one place or if you happen to escape only part of the input, your database may be subject to attack.

使用mysql_*函数时，您必须记住通过 运行用户提供的参数mysql_real_escape_string()。如果您只忘记一处，或者您碰巧只逃脱了部分输入，您的数据库可能会受到攻击。

Using prepared statements in PDOor mysqliwill make it so that these sorts of programming errors are more difficult to make.

在PDOor 中使用准备好的语句mysqli会使这些类型的编程错误更难发生。

回答by enhzflep

Because (amongst other reasons) it's much harder to ensure the input data is sanitized. If you use parametrized queries, as one does with PDO or mysqli you can entirely avoid the risk.

因为（除其他原因外）确保输入数据经过消毒要困难得多。如果您使用参数化查询，就像使用 PDO 或 mysqli 一样，您可以完全避免风险。

As an example, someone could use "enhzflep); drop table users"as a username. The old functions will allow executing multiple statements per query, so something like that nasty bugger can delete a whole table.

例如，某人可以用作用"enhzflep); drop table users"户名。旧函数将允许每个查询执行多个语句，因此像那个讨厌的虫子之类的东西可以删除整个表。

If one were to use PDO of mysqli, the user-name would end-up being "enhzflep); drop table users".

如果要使用 mysqli 的 PDO，用户名最终将是"enhzflep); drop table users".

See bobby-tables.com.

请参阅bobby-tables.com。

回答by Fluffeh

This answer is written to show just how trivial it is to bypass poorly written PHP user-validation code, how (and using what) these attacks work and how to replace the old MySQL functions with a secure prepared statement - and basically, why StackOverflow users (probably with a lot of rep) are barking at new users asking questions to improve their code.

这个答案是为了展示绕过写得不好的 PHP 用户验证代码是多么的简单，这些攻击是如何（以及使用什么）工作的，以及如何用安全的准备好的语句替换旧的 MySQL 函数 - 基本上，为什么 StackOverflow 用户（可能有很多代表）正在对新用户咆哮，提出问题以改进他们的代码。

First off, please feel free to create this test mysql database (I have called mine prep):

首先，请随意创建这个测试 mysql 数据库（我已经调用了我的 prep）：

mysql> create table users(
    -> id int(2) primary key auto_increment,
    -> userid tinytext,
    -> pass tinytext);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into users values(null, 'Fluffeh', 'mypass');
Query OK, 1 row affected (0.04 sec)

mysql> create user 'prepared'@'localhost' identified by 'example';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on prep.* to 'prepared'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

With that done, we can move to our PHP code.

完成后，我们可以转到我们的 PHP 代码。

Lets assume the following script is the verification process for an admin on a website (simplified but working if you copy and use it for testing):

让我们假设以下脚本是网站管理员的验证过程（简化但如果您复制并使用它进行测试，则可以工作）：

<?php 

    if(!empty($_POST['user']))
    {
        $user=$_POST['user'];
    }   
    else
    {
        $user='bob';
    }
    if(!empty($_POST['pass']))
    {
        $pass=$_POST['pass'];
    }
    else
    {
        $pass='bob';
    }

    $database='prep';
    $link=mysql_connect('localhost', 'prepared', 'example');
    mysql_select_db($database) or die( "Unable to select database");

    $sql="select id, userid, pass from users where userid='$user' and pass='$pass'";
    //echo $sql."<br><br>";
    $result=mysql_query($sql);
    $isAdmin=false;
    while ($row = mysql_fetch_assoc($result)) {
        echo "My id is ".$row['id']." and my username is ".$row['userid']." and lastly, my password is ".$row['pass']."<br>";
        $isAdmin=true;
        // We have correctly matched the Username and Password
        // Lets give this person full access
    }
    if($isAdmin)
    {
        echo "The check passed. We have a verified admin!<br>";
    }
    else
    {
        echo "You could not be verified. Please try again...<br>";
    }
    mysql_close($link);

?>

<form name="exploited" method='post'>
    User: <input type='text' name='user'><br>
    Pass: <input type='text' name='pass'><br>
    <input type='submit'>
</form>

Seems legit enough at first glance.

乍一看似乎足够合法。

The user has to enter a login and password, right?

用户必须输入登录名和密码，对吗？

Brilliant, not enter in the following:

太棒了，不录入以下内容：

user: bob
pass: somePass

and submit it.

并提交。

The output is as follows:

输出如下：

You could not be verified. Please try again...

Super! Working as expected, now lets try the actual username and password:

极好的！按预期工作，现在让我们尝试实际的用户名和密码：

user: Fluffeh
pass: mypass

Amazing! Hi-fives all round, the code correctly verified an admin. It's perfect!

惊人的！大家好，代码正确地验证了管理员。这是完美的！

Well, not really. Lets say the user is a clever little person. Lets say the person is me.

嗯，不是真的。假设用户是一个聪明的小人物。让我们说这个人是我。

Enter in the following:

输入以下内容：

user: bob
pass: n' or 1=1 or 'm=m

And the output is:

输出是：

The check passed. We have a verified admin!

Congrats, you just allowed me to enter your super-protected admins only section with me entering a false username and a false password. Seriously, if you don't believe me, create the database with the code I provided, and run this PHP code - which at glance REALLY does seem to verify the username and password rather nicely.

恭喜，你刚刚允许我进入你的超级保护管理员部分，我输入了一个假用户名和一个假密码。说真的，如果您不相信我，请使用我提供的代码创建数据库，然后运行此 PHP 代码 - 乍一看，它确实可以很好地验证用户名和密码。

So, in answer, THAT IS WHY YOU ARE BEING YELLED AT.

所以，作为回答，这就是你被大吼大叫的原因。

So, lets have a look at what went wrong, and why I just got into your super-admin-only-bat-cave. I took a guess and assumed that you weren't being careful with your inputs and simply passed them to the database directly. I constructed the input in a way tht would CHANGE the query that you were actually running. So, what was it supposed to be, and what did it end up being?

那么，让我们看看出了什么问题，以及为什么我刚刚进入您的超级管理员专用蝙蝠洞。我进行了猜测并假设您对输入不小心，而是直接将它们传递给数据库。我以一种会改变您实际运行的查询的方式构建输入。那么，它应该是什么，它最终是什么？

select id, userid, pass from users where userid='$user' and pass='$pass'

That's the query, but when we replace the variables with the actual inputs that we used, we get the following:

这就是查询，但是当我们用我们使用的实际输入替换变量时，我们得到以下结果：

select id, userid, pass from users where userid='bob' and pass='n' or 1=1 or 'm=m'

See how I constructed my "password" so that it would first close the single quote around the password, then introduce a completely new comparison? Then just for safety, I added another "string" so that the single quote would get closed as expected in the code we originally had.

看看我如何构建我的“密码”，以便它首先关闭密码周围的单引号，然后引入一个全新的比较？然后为了安全起见，我添加了另一个“字符串”，以便在我们最初拥有的代码中按预期关闭单引号。

However, this isn't about folks yelling at you now, this is about showing you how to make your code more secure.

但是，这不是关于人们现在对您大喊大叫，而是向您展示如何使您的代码更安全。

Okay, so what went wrong, and how can we fix it?

好的，那么出了什么问题，我们该如何解决？

This is a classic SQL injection attack. One of the simplest for that matter. On the scale of attack vectors, this is a toddler attacking a tank - and winning.

这是典型的 SQL 注入攻击。最简单的方法之一。在攻击向量的规模上，这是一个蹒跚学步的坦克攻击 - 并获胜。

So, how do we protect your sacred admin section and make it nice and secure? The first thing to do will be to stop using those really old and deprecated mysql_*functions. I know, you followed a tutorial you found online and it works, but it's old, it's outdated and in the space of a few minutes, I have just broken past it without so much as breaking a sweat.

那么，我们如何保护您神圣的管理部分并使其安全可靠？首先要做的是停止使用那些非常旧的和不推荐使用的mysql_*功能。我知道，您遵循了您在网上找到的教程并且它有效，但是它很旧，已经过时，并且在几分钟内，我刚刚通过它而没有出汗。

Now, you have the better options of using mysqli_or PDO. I am personally a big fan of PDO, so I will be using PDO in the rest of this answer. There are pro's and con's, but personally I find that the pro's far outweigh the con's. It's portable across multiple database engines - whether you are using MySQL or Oracle or just about bloody anything - just by changing the connection string, it has all the fancy features we want to use and it is nice and clean. I like clean.

现在，您有更好的选择来使用mysqli_或PDO。我个人是 PDO 的忠实粉丝，所以我将在本答案的其余部分使用 PDO。有优点和缺点，但我个人认为优点远远超过缺点。它可以跨多个数据库引擎移植——无论您使用的是 MySQL 还是 Oracle 或几乎任何血腥的东西——只需更改连接字符串，它就拥有我们想要使用的所有奇特功能，而且它既漂亮又干净。我喜欢干净。

Now, lets have a look at that code again, this time written using a PDO object:

现在，让我们再看看那段代码，这次是使用 PDO 对象编写的：

<?php 

    if(!empty($_POST['user']))
    {
        $user=$_POST['user'];
    }   
    else
    {
        $user='bob';
    }
    if(!empty($_POST['pass']))
    {
        $pass=$_POST['pass'];
    }
    else
    {
        $pass='bob';
    }
    $isAdmin=false;

    $database='prep';
    $pdo=new PDO ('mysql:host=localhost;dbname=prep', 'prepared', 'example');
    $sql="select id, userid, pass from users where userid=:user and pass=:password";
    $myPDO = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
    if($myPDO->execute(array(':user' => $user, ':password' => $pass)))
    {
        while($row=$myPDO->fetch(PDO::FETCH_ASSOC))
        {
            echo "My id is ".$row['id']." and my username is ".$row['userid']." and lastly, my password is ".$row['pass']."<br>";
            $isAdmin=true;
            // We have correctly matched the Username and Password
            // Lets give this person full access
        }
    }

    if($isAdmin)
    {
        echo "The check passed. We have a verified admin!<br>";
    }
    else
    {
        echo "You could not be verified. Please try again...<br>";
    }

?>

<form name="exploited" method='post'>
    User: <input type='text' name='user'><br>
    Pass: <input type='text' name='pass'><br>
    <input type='submit'>
</form>

The major differences are that there are no more mysql_*functions. It's all done via a PDO object, secondly, it is using a prepared statement. Now, what's a prepred statement you ask? It's a way to tell the database ahead of running a query, what the query is that we are going to run. In this case, we tell the database: "Hi, I am going to run a select statement wanting id, userid and pass from the table users where the userid is a variable and the pass is also a variable.".

主要的区别是没有更多的mysql_*功能。这一切都是通过 PDO 对象完成的，其次，它使用的是准备好的语句。现在，你要问什么是预先声明？这是一种在运行查询之前告诉数据库我们将要运行的查询的方法。在这种情况下，我们告诉数据库：“嗨，我将运行一个 select 语句，想要从表 users 中获取 id、userid 和 pass，其中 userid 是一个变量，pass 也是一个变量。”。

Then, in the execute statement, we pass the database an array with all the variables that it now expects.

然后，在执行语句中，我们将一个包含它现在期望的所有变量的数组传递给数据库。

The results are fantastic. Lets try those username and password combinations from before again:

结果太棒了。让我们再次尝试那些以前的用户名和密码组合：

user: bob
pass: somePass

User wasn't verified. Awesome.

用户未经验证。惊人的。

How about:

怎么样：

user: Fluffeh
pass: mypass

Oh, I just got a little excited, it worked: The check passed. We have a verified admin!

哦，我只是有点兴奋，它奏效了：支票通过了。我们有经过验证的管理员！

Now, lets try the data that a clever chap would enter to try to get past our little verification system:

现在，让我们尝试一个聪明的家伙输入的数据，试图通过我们的小验证系统：

user: bob
pass: n' or 1=1 or 'm=m

This time, we get the following:

这一次，我们得到以下信息：

You could not be verified. Please try again...

This is why you are being yelled at when posting questions - it's because people can see that your code can be bypassed wihout even trying. Please, do use this question and answer to improve your code, to make it more secure and to use functions that are current.

这就是为什么您在发布问题时会被大吼大叫 - 这是因为人们可以看到即使不尝试也可以绕过您的代码。请务必使用此问题和答案来改进您的代码，使其更安全并使用最新的功能。

Lastly, this isn't to say that this is PERFECT code. There are many more things that you could do to improve it, use hashed passwords for example, ensure that when you store sensetive information in the database, you don't store it in plain text, have multiple levels of verification - but really, if you just change your old injection prone code to this, you will be WELL along the way to writing good code - and the fact that you have gotten this far and are still reading gives me a sense of hope that you will not only implement this type of code when writing your websites and applications, but that you might go out and research those other things I just mentioned - and more. Write the best code you can, not the most basic code that barely functions.

最后，这并不是说这是完美的代码。您还可以做很多事情来改进它，例如使用散列密码，确保在数据库中存储敏感信息时，不要以纯文本形式存储它，有多个级别的验证 - 但实际上，如果您只需将旧的易于注入的代码更改为此，您就会很好地编写好的代码 - 而且您已经走到这一步并且仍在阅读的事实让我感到希望您不仅会实现这种类型编写您的网站和应用程序时的代码，但您可能会出去研究我刚刚提到的其他事情 - 以及更多。尽可能编写最好的代码，而不是几乎无法运行的最基本的代码。

回答by Alexander

The MySQL extension is the oldest of the three and was the original way that developers used to communicate with MySQL. This extension is now being deprecatedin favor of the other twoalternativesbecause of improvements made in newer releases of both PHP and MySQL.

MySQL 扩展是三者中最古老的，是开发人员用来与 MySQL 通信的原始方式。由于在 PHP 和 MySQL 的较新版本中进行了改进，此扩展现在已被弃用，取而代之的是其他两种替代方案。

• MySQLiis the 'improved' extension for working with MySQL databases. It takes advantage of features that are available in newer versions of the MySQL server, exposes both a function-oriented and an object-oriented interface to the developer and a does few other nifty things.

• PDOoffers an API that consolidates most of the functionality that was previously spread across the major database access extensions, i.e. MySQL, PostgreSQL, SQLite, MSSQL, etc. The interface exposes high-level objects for the programmer to work with database connections, queries and result sets, and low-level drivers perform communication and resource handling with the database server. A lot of discussion and work is going into PDO and it's considered the appropriate method of working with databases in modern, professional code.

• MySQLi是用于处理 MySQL 数据库的“改进”扩展。它利用了较新版本的 MySQL 服务器中可用的功能，向开发人员公开了面向函数和面向对象的接口，并做了一些其他漂亮的事情。

• PDO提供了一个 API，该 API 整合了以前分布在主要数据库访问扩展（即 MySQL、PostgreSQL、SQLite、MSSQL 等）中的大部分功能。该接口为程序员公开高级对象以处理数据库连接、查询和结果集和低级驱动程序执行与数据库服务器的通信和资源处理。许多讨论和工作都在 PDO 中进行，它被认为是使用现代专业代码处理数据库的合适方法。