PHP PDO：连接 MySQL 并使用预处理语句

PDO（PHP Data Objects）是 PHP 内置的数据库访问方式。用 new PDO($dsn, $user, $password) 建立连接，然后凡是包含值的查询，都用 prepare() 和 execute() 执行，这样值会与 SQL 分开发送：

<?php
$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'shop_user', 'secret', [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = ?');
$stmt->execute([$email]);
$user = $stmt->fetch();      // an array, or false if no row matched

本页的示例需要数据库服务器，所以以普通代码的形式展示。下面的 SQL 注入示例可以运行。

用 PDO 连接 MySQL

第一个参数是 DSN：先写驱动名称，再写连接信息。把连接代码放在一个地方，在需要的地方引入它。

<?php
// db.php
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // the default since PHP 8.0
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,        // rows as ['id' => 1, ...]
    PDO::ATTR_EMULATE_PREPARES   => false,                   // real prepared statements, real int types
];

try {
    $pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASS'), $options);
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    exit('Database unavailable.');
}

return $pdo;

• charset=utf8mb4 要写在 DSN 里。没有它，带重音的字母和 emoji 可能会变成 ? 或乱码。
• 永远不要把 $e->getMessage() 输出给访问者：它可能包含主机名和用户名。把它写进日志。
• 不要把密码写在代码里，放在环境变量或网站根目录之外的配置文件中。

换成其他数据库只需要改 DSN：PostgreSQL 用 pgsql:host=localhost;dbname=shop，SQLite 文件用 sqlite:/var/data/app.db。每种数据库都需要安装对应的 PDO 驱动（pdo_mysql、pdo_pgsql、pdo_sqlite）；php -m 会列出已安装的驱动。

用 ? 和 :name 写预处理语句

占位符标出值要放的位置。位置占位符 ? 配合列表使用，命名占位符配合关联数组使用：

<?php
// Positional
$stmt = $pdo->prepare('SELECT * FROM products WHERE category = ? AND price < ?');
$stmt->execute(['books', 20]);

// Named: easier to read when there are many values
$stmt = $pdo->prepare('SELECT * FROM products WHERE category = :cat AND price < :max');
$stmt->execute(['cat' => 'books', 'max' => 20]);

// bindValue when you want to set the type explicitly
$stmt = $pdo->prepare('SELECT * FROM products ORDER BY id LIMIT :limit');
$stmt->bindValue('limit', 10, PDO::PARAM_INT);
$stmt->execute();

不要给占位符加引号：WHERE name = '?' 比较的是字面文本 ?。预处理语句还可以用不同的值执行多次，所以在循环之前准备一次即可，不要放在循环里面。

为什么在 SQL 中拼接字符串很危险

这是本页唯一可以运行的代码块：它对同一个输入，分别用错误的方式和正确的方式构建查询。修改 $email，再运行一次。

<?php
$email = "nobody@example.com' OR '1'='1";

// Wrong: the input becomes part of the SQL
$sql = "SELECT * FROM users WHERE email = '" . $email . "'";
echo "Concatenated:\n$sql\n\n";

// Right: the SQL stays fixed, the value travels separately
$sql = 'SELECT * FROM users WHERE email = ?';
$params = [$email];
echo "Prepared:\n$sql\n";
echo 'Values: ', json_encode($params), "\n";

在拼接出来的查询中，输入里的引号结束了字符串，OR '1'='1' 让条件对每一行都成立：查询返回了所有用户。在登录表单上，这意味着不知道密码也能登录；如果驱动允许一次调用执行多条语句，像 '; DROP TABLE users; -- 这样的输入还会执行攻击者任意指定的第二条语句。在预处理版本中，数据库分别收到 SQL 和值，所以引号只是邮箱里的一个普通字符，这个邮箱匹配不到任何人。

addslashes() 这类转义函数解决不了问题：它们会漏掉一些取决于连接字符集的情况。每个值都要用占位符，包括数字和“来自你自己代码”的值。

获取行：fetch、fetchAll 和 fetchColumn

<?php
// One row
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([42]);
$user = $stmt->fetch();                 // ['id' => 42, 'name' => 'Ada', ...] or false
if ($user === false) {
    http_response_code(404);
}

// All rows
$rows = $pdo->query('SELECT id, name FROM users ORDER BY name')->fetchAll();

// Loop row by row without building an array of all rows
$stmt = $pdo->query('SELECT id, name FROM users');
foreach ($stmt as $row) {
    echo $row['name'], "\n";
}

// One value
$count = $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();

// Two columns as key => value
$names = $pdo->query('SELECT id, name FROM users')->fetchAll(PDO::FETCH_KEY_PAIR);  // [42 => 'Ada', ...]

SQL 中不包含任何外部值时，用 query() 没问题。只要涉及变量，就改用 prepare()。

插入、更新和删除

<?php
$stmt = $pdo->prepare('INSERT INTO users (email, name, password_hash) VALUES (?, ?, ?)');
$stmt->execute([$email, $name, password_hash($password, PASSWORD_DEFAULT)]);
$id = (int) $pdo->lastInsertId();

$stmt = $pdo->prepare('UPDATE users SET name = ? WHERE id = ?');
$stmt->execute([$newName, $id]);
echo $stmt->rowCount();                 // rows changed

$stmt = $pdo->prepare('DELETE FROM sessions WHERE expires_at < NOW()');
$stmt->execute();

lastInsertId() 返回的是字符串，所以要做类型转换。在 MySQL 中，UPDATE 之后的 rowCount() 统计的是真正发生变化的行，所以把名字设成它原本的值会得到 0。存储密码要用 password_hash，永远不要存明文。

事务

事务让多条语句要么一起成功，要么一起失败。只要有任何一步抛出异常，就回滚，什么都不会保存：

<?php
try {
    $pdo->beginTransaction();

    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([100, $from]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([100, $to]);
    $pdo->prepare('INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)')->execute([$from, $to, 100]);

    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}

这依赖于出错时抛出异常（PHP 8.0 起的默认行为），这样失败的语句会跳到 catch。在 MySQL 中，表必须使用 InnoDB；MyISAM 会忽略事务。Throwable 和重新抛出异常的内容见 try/catch 页面。

用数组做 WHERE IN

一个 ? 只能放一个值，所以 IN (?) 配合数组是行不通的。为每个值生成一个占位符：

<?php
$ids = [3, 8, 15];

if ($ids) {
    $placeholders = implode(',', array_fill(0, count($ids), '?'));   // "?,?,?"
    $stmt = $pdo->prepare("SELECT * FROM products WHERE id IN ($placeholders)");
    $stmt->execute($ids);
    $products = $stmt->fetchAll();
} else {
    $products = [];                     // IN () would be a syntax error
}

插入到 SQL 中的字符串只有问号和逗号，所以查询仍然是安全的。

使用占位符的常见错误

• 表名和列名。 ORDER BY ? 按一个常量字符串排序，而不是按列排序。从允许列表中挑选标识符：$sort = in_array($_GET['sort'] ?? '', ['name', 'price'], true) ? $_GET['sort'] : 'name';，然后再把 $sort 放进 SQL。
• 模拟预处理下的 LIMIT ?。 MySQL 默认开启 ATTR_EMULATE_PREPARES，此时 execute(['10']) 发送的是 LIMIT '10'，这是语法错误。用 PDO::PARAM_INT 绑定，或者关闭模拟。
• 同一个命名占位符用了两次。 WHERE first = :q OR last = :q 只在模拟预处理下有效。使用真正的预处理时会得到 SQLSTATE[HY093]: Invalid parameter number；改用 :q1 和 :q2。
• 忘了 fetch() 会返回 false。 对 false 取 $user['name'] 会产生警告并得到 null；先检查结果。