PHP小码农

Appearance

数据库安全 ​

数据库安全是 PHP Web 应用安全的重中之重。SQL 注入是最危险的攻击之一，可以直接导致数据泄露、篡改甚至服务器被完全控制。本节深入讲解 SQL 注入的原理与防御、预处理语句的最佳实践、数据库权限管理和安全查询构建。

前置知识

阅读本节前，建议先了解：安全总则、PDO 预处理语句

基础概念 ​

SQL 注入原理 ​

正常查询：SELECT * FROM users WHERE id = 1
注入攻击：SELECT * FROM users WHERE id = 1 OR 1=1
注入删除：SELECT * FROM users WHERE id = 1; DROP TABLE users;

SQL 注入的危害

  • 绕过身份验证
  • 窃取全部数据库数据
  • 修改或删除数据
  • 获取服务器操作系统权限
  • SQL 盲注、时间盲注等高级攻击

预处理语句（核心防御） ​

PDO 预处理语句 ​

php
<?php

declare(strict_types=1);

$pdo = new PDO('mysql:host=localhost;dbname=app', 'app_user', 'password', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false, // 禁用模拟预处理（重要！）
]);

// === 命名占位符（推荐）===
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id AND status = :status');
$stmt->execute(['id' => 1, 'status' => 'active']);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// === 问号占位符 ===
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ? AND status = ?');
$stmt->execute([1, 'active']);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

// === INSERT ===
$stmt = $pdo->prepare('INSERT INTO users (username, email, password_hash) VALUES (:name, :email, :hash)');
$stmt->execute([
    'name' => 'Alice',
    'email' => 'alice@example.com',
    'hash' => password_hash('secret', PASSWORD_DEFAULT),
]);

// === UPDATE ===
$stmt = $pdo->prepare('UPDATE users SET email = :email WHERE id = :id');
$stmt->execute(['email' => 'new@example.com', 'id' => 1]);

// === DELETE ===
$stmt = $pdo->prepare('DELETE FROM users WHERE id = :id');
$stmt->execute(['id' => 1]);

// === LIKE 查询 ===
$stmt = $pdo->prepare('SELECT * FROM users WHERE username LIKE :search');
$stmt->execute(['search' => '%' . $searchTerm . '%']);

// === IN 查询 ===
$ids = [1, 2, 3, 5, 8];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT * FROM users WHERE id IN ({$placeholders})");
$stmt->execute($ids);

// === 动态 WHERE 条件 ===
$conditions = [];
$params = [];

if (!empty($filters['username'])) {
    $conditions[] = 'username LIKE :username';
    $params['username'] = '%' . $filters['username'] . '%';
}
if (!empty($filters['status'])) {
    $conditions[] = 'status = :status';
    $params['status'] = $filters['status'];
}
if (!empty($filters['min_age'])) {
    $conditions[] = 'age >= :min_age';
    $params['min_age'] = (int) $filters['min_age'];
}

$where = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
$sql = "SELECT * FROM users {$where} ORDER BY id DESC LIMIT 20";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

mysqli 预处理语句 ​

php
<?php

declare(strict_types=1);

$mysqli = new mysqli('localhost', 'app_user', 'password', 'app');

// 绑定参数
$stmt = $mysqli->prepare('SELECT id, username FROM users WHERE id = ? AND status = ?');
$stmt->bind_param('is', $id, $status); // i=integer, s=string
$id = 1;
$status = 'active';
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['username'];
}

$stmt->close();

// bind_param 类型说明
// i - integer
// d - double/float
// s - string
// b - blob/binary

重要：禁用模拟预处理 ​

php
<?php

// PDO::ATTR_EMULATE_PREPARES 必须设为 false
// 确保 SQL 和参数分开发送到数据库服务器

$pdo = new PDO('mysql:host=localhost;dbname=app', 'user', 'pass', [
    PDO::ATTR_EMULATE_PREPARES => false, // 关键安全设置！
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

// MySQL 驱动默认 emulating = true
// PostgreSQL 驱动默认 emulating = false

注意

预处理语句只能用于值（values），不能用于表名、列名或 SQL 关键字。这些需要白名单验证。

动态表名和列名 ​

php
<?php

declare(strict_types=1);

// === 动态表名（白名单验证）===
$allowedTables = ['users', 'products', 'orders', 'categories'];
$tableName = $_GET['table'] ?? 'users';

if (!in_array($tableName, $allowedTables, true)) {
    die('非法的表名');
}

$stmt = $pdo->prepare("SELECT * FROM {$tableName} LIMIT 10");
$stmt->execute();

// === 动态排序字段 ===
$allowedSortFields = ['id', 'username', 'created_at', 'updated_at'];
$sortField = $_GET['sort'] ?? 'id';
$sortOrder = strtoupper($_GET['order'] ?? 'ASC');

if (!in_array($sortField, $allowedSortFields, true)) {
    $sortField = 'id';
}
if (!in_array($sortOrder, ['ASC', 'DESC'], true)) {
    $sortOrder = 'ASC';
}

$stmt = $pdo->prepare("SELECT * FROM users ORDER BY {$sortField} {$sortOrder} LIMIT 20");
$stmt->execute();

// === 动态 WHERE 字段 ===
$allowedFields = ['username', 'email', 'status'];
$field = $_GET['field'] ?? '';

if (!in_array($field, $allowedFields, true)) {
    die('非法字段');
}

$stmt = $pdo->prepare("SELECT * FROM users WHERE {$field} = :value");
$stmt->execute(['value' => $_GET['value']]);

数据库权限管理 ​

最小权限原则 ​

sql
-- 创建专用应用用户（不使用 root）
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

-- 仅授予必要的权限
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app_user'@'localhost';

-- 如果应用不需要 DDL，不要授予
-- 不要授予 DROP, ALTER, CREATE, GRANT 等权限

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看当前用户权限
SHOW GRANTS FOR 'app_user'@'localhost';

读写分离权限 ​

sql
-- 读用户
CREATE USER 'app_read'@'%' IDENTIFIED BY 'ReadPassword';
GRANT SELECT ON app.* TO 'app_read'@'%';

-- 写用户
CREATE USER 'app_write'@'localhost' IDENTIFIED BY 'WritePassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app_write'@'localhost';

-- 管理用户（仅用于迁移）
CREATE USER 'app_admin'@'localhost' IDENTIFIED BY 'AdminPassword';
GRANT ALL PRIVILEGES ON app.* TO 'app_admin'@'localhost';

错误处理安全 ​

不要暴露数据库错误 ​

php
<?php

declare(strict_types=1);

// 错误：向用户显示 SQL 错误
try {
    $stmt = $pdo->query($sql);
} catch (PDOException $e) {
    echo "SQL 错误: " . $e->getMessage(); // 泄露表结构和查询
}

// 正确：记录日志，显示通用错误
try {
    $stmt = $pdo->query($sql);
} catch (PDOException $e) {
    error_log("SQL Error: {$e->getMessage()} at {$e->getFile()}:{$e->getLine()}");
    http_response_code(500);
    echo '服务器内部错误，请稍后重试';
}

PDO 错误模式配置 ​

php
<?php

declare(strict_types=1);

// 推荐：ERRMODE_EXCEPTION
$pdo = new PDO('mysql:host=localhost;dbname=app', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // 推荐
    // PDO::ERRMODE_SILENT - 静默（需手动检查错误码）
    // PDO::ERRMODE_WARNING - 警告
]);

// 错误模式对比
// ERRMODE_SILENT:  不自动处理，需要手动 $stmt->errorCode()
// ERRMODE_WARNING: 触发 E_WARNING
// ERRMODE_EXCEPTION: 抛出 PDOException（推荐）

实战示例：安全的查询构建器 ​

php
<?php

declare(strict_types=1);

class SecureQueryBuilder
{
    private array $where = [];
    private array $params = [];
    private array $orderBy = [];
    private int $limit = 20;
    private int $offset = 0;
    private readonly PDO $pdo;
    private readonly string $table;
    private readonly array $allowedFields;

    public function __construct(PDO $pdo, string $table, array $allowedFields)
    {
        $this->pdo = $pdo;
        $this->table = $table;
        $this->allowedFields = $allowedFields;
    }

    public function where(string $field, string $operator, mixed $value): self
    {
        if (!in_array($field, $this->allowedFields, true)) {
            throw new InvalidArgumentException("非法字段: {$field}");
        }

        $allowedOperators = ['=', '!=', '<>', '>', '<', '>=', '<=', 'LIKE', 'NOT LIKE', 'IN', 'NOT IN'];
        $operator = strtoupper($operator);
        if (!in_array($operator, $allowedOperators, true)) {
            throw new InvalidArgumentException("非法操作符: {$operator}");
        }

        $paramName = ':' . str_replace('.', '_', $field) . '_' . count($this->where);

        if ($operator === 'IN' || $operator === 'NOT IN') {
            if (!is_array($value)) {
                throw new InvalidArgumentException("IN 操作符需要数组值");
            }
            $placeholders = implode(',', array_fill(0, count($value), $paramName . '_'));
            $this->where[] = "{$field} {$operator} ({$placeholders})";
            $i = 0;
            foreach ($value as $v) {
                $this->params[$paramName . '_' . $i++] = $v;
            }
        } else {
            $this->where[] = "{$field} {$operator} {$paramName}";
            $this->params[$paramName] = $value;
        }

        return $this;
    }

    public function orderBy(string $field, string $direction = 'ASC'): self
    {
        if (!in_array($field, $this->allowedFields, true)) {
            throw new InvalidArgumentException("非法排序字段: {$field}");
        }
        $direction = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC';
        $this->orderBy[] = "{$field} {$direction}";
        return $this;
    }

    public function limit(int $limit): self
    {
        $this->limit = min(max(1, $limit), 1000);
        return $this;
    }

    public function offset(int $offset): self
    {
        $this->offset = max(0, $offset);
        return $this;
    }

    public function execute(): array
    {
        $sql = "SELECT * FROM {$this->table}";

        if (!empty($this->where)) {
            $sql .= ' WHERE ' . implode(' AND ', $this->where);
        }

        if (!empty($this->orderBy)) {
            $sql .= ' ORDER BY ' . implode(', ', $this->orderBy);
        }

        $sql .= ' LIMIT ? OFFSET ?';

        $stmt = $this->pdo->prepare($sql);
        $params = array_values($this->params);
        $params[] = $this->limit;
        $params[] = $this->offset;
        $stmt->execute($params);

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function count(): int
    {
        $sql = "SELECT COUNT(*) FROM {$this->table}";

        if (!empty($this->where)) {
            $sql .= ' WHERE ' . implode(' AND ', $this->where);
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array_values($this->params));
        return (int) $stmt->fetchColumn();
    }
}

// 使用
$builder = new SecureQueryBuilder($pdo, 'users', [
    'id', 'username', 'email', 'status', 'created_at',
]);

$users = $builder
    ->where('status', '=', 'active')
    ->where('username', 'LIKE', '%admin%')
    ->orderBy('created_at', 'DESC')
    ->limit(20)
    ->offset(0)
    ->execute();

注意事项 ​

1. COUNT(*) 和 LIMIT 使用预处理 ​

php
<?php

// LIMIT 和 OFFSET 不能使用命名占位符
// 必须使用问号占位符或直接拼接（需验证为整数）
$limit = (int) ($_GET['limit'] ?? 20);
$offset = (int) ($_GET['offset'] ?? 0);
$limit = min(max(1, $limit), 1000);
$offset = max(0, $offset);

$stmt = $pdo->prepare('SELECT * FROM users LIMIT ? OFFSET ?');
$stmt->execute([$limit, $offset]);

// 或使用字符串拼接（已验证为整数）
$stmt = $pdo->prepare("SELECT * FROM users LIMIT {$limit} OFFSET {$offset}");
$stmt->execute();

最佳实践 ​

1. 数据库安全清单 ​

php
<?php

// [x] 所有查询使用预处理语句
// [x] PDO 设置 EMULATE_PREPARES = false
// [x] 表名/列名使用白名单验证
// [x] 数据库用户使用最小权限
// [x] 不向用户暴露 SQL 错误
// [x] 设置 ERRMODE_EXCEPTION
// [x] 定期更新数据库驱动

下一节

继续学习：隐藏 PHP

参考链接 ​