第 5 章 · Web 开发 · 第 5.3 课

用 PDO 操作数据库

约 20 分钟

网站的数据（用户、文章、订单）都存在数据库里。PHP 用 PDO 统一操作各种数据库：MySQL、PostgreSQL、SQLite……换数据库时只要改连接字符串。

练功房里用的是 SQLite 内存数据库（sqlite::memory:），不用安装，每次运行都是一个全新的空数据库。连接 MySQL 时只是第一行不同：

<?php
$pdo = new PDO('mysql:host=127.0.0.1;dbname=shop;charset=utf8mb4', '用户名', '密码');

建表、插入、查询

exec($sql)：执行不返回结果的语句（建表等）。
prepare($sql) + execute([...])：预处理语句，SQL 里的值用 ? 占位，数据单独传进去。
fetchAll(PDO::FETCH_ASSOC)：取出所有行，每行是一个关联数组。
lastInsertId()：刚插入那一行的自增 id。

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // 出错就抛异常

$pdo->exec('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, price REAL)');

$stmt = $pdo->prepare('INSERT INTO books (title, price) VALUES (?, ?)');
$stmt->execute(['PHP 入门', 59]);
$stmt->execute(['MySQL 必知必会', 39.5]);
$stmt->execute(['算法图解', 49]);
echo "最后插入的 id：", $pdo->lastInsertId(), "\n\n";

$rows = $pdo->query('SELECT * FROM books ORDER BY price')->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    echo "{$row['id']}. {$row['title']}：{$row['price']} 元\n";
}

带条件的查询、修改和删除

占位符也可以起名字：:max，传参时用关联数组。rowCount() 返回受影响的行数。

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, price REAL)');
$insert = $pdo->prepare('INSERT INTO books (title, price) VALUES (?, ?)');
foreach ([['PHP 入门', 59], ['MySQL 必知必会', 39.5], ['算法图解', 49]] as $b) {
    $insert->execute($b);
}

$stmt = $pdo->prepare('SELECT title FROM books WHERE price < :max');
$stmt->execute(['max' => 50]);
echo "50 元以下：", implode("、", $stmt->fetchAll(PDO::FETCH_COLUMN)), "\n";

$update = $pdo->prepare('UPDATE books SET price = price * 0.8 WHERE price > ?');
$update->execute([50]);
echo "打折了 ", $update->rowCount(), " 本\n";

$delete = $pdo->prepare('DELETE FROM books WHERE id = ?');
$delete->execute([2]);
echo "还剩 ", $pdo->query('SELECT COUNT(*) FROM books')->fetchColumn(), " 本";

SQL 注入：为什么一定要用预处理语句

如果把用户输入直接拼进 SQL 字符串，攻击者就能输入一段精心构造的文字，改变 SQL 语句本身的含义。下面这个登录检查，攻击者不知道密码也能登进去。

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (name TEXT, password TEXT)");
$pdo->exec("INSERT INTO users VALUES ('admin', 'secret')");

// 攻击者在密码框里输入的内容
$input = "' OR '1'='1";

// 危险：直接拼接字符串
$sql = "SELECT * FROM users WHERE name = 'admin' AND password = '$input'";
echo "拼出来的 SQL：\n$sql\n";
echo $pdo->query($sql)->fetch() ? "→ 登录成功（被绕过了！）\n\n" : "→ 登录失败\n\n";

// 安全：预处理语句，输入只会被当作数据
$stmt = $pdo->prepare('SELECT * FROM users WHERE name = ? AND password = ?');
$stmt->execute(['admin', $input]);
echo $stmt->fetch() ? "→ 登录成功\n" : "→ 预处理语句：登录失败，攻击无效\n";

选择题

防止 SQL 注入最可靠的做法是？

练习 1

查询及格的学生

表 students 已经建好了。查询分数大于等于 $pass 的学生，按分数从高到低排序，每行输出一个名字。分数线要通过占位符传进 SQL。期望输出：

小红
小刚
小明

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE students (name TEXT, score INTEGER)');
$insert = $pdo->prepare('INSERT INTO students VALUES (?, ?)');
foreach ([['小明', 61], ['小红', 95], ['小刚', 78], ['小李', 42]] as $row) {
    $insert->execute($row);
}

$pass = 60;

练习 2

添加用户

写函数 addUser(PDO $pdo, string $name, int $age): int：用预处理语句往 users 表插入一行，返回新行的 id。

检查时会插入一个叫 O'Brien 的用户，名字里有单引号。如果你拼接字符串，SQL 就会出错。

<?php
function addUser(PDO $pdo, string $name, int $age): int {

}

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');
echo addUser($pdo, "小明", 20);