PHP 怎么安全删除数据

wen PHP项目 2026-08-11 159

本文目录导读：

PHP 怎么安全删除数据

使用预处理语句（防止 SQL 注入）
使用软删除（逻辑删除）
添加操作验证（防止误删）
CSRF 防护（表单提交删除）
使用事务确保数据一致性
完整的安全删除示例
最佳实践总结

在 PHP 中安全删除数据涉及多个层面，包括防止 SQL 注入、防止误删、权限控制、数据备份等，以下是全面的安全删除指南：

使用预处理语句（防止 SQL 注入）

PDO 方式（推荐）

<?php
// 使用 PDO 预处理语句
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 安全删除单个记录
$id = $_GET['id'];
$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => $id]);
?>

MySQLi 方式

<?php
// 使用 MySQLi 预处理语句
$mysqli = new mysqli('localhost', 'user', 'password', 'test');
// 安全删除
$id = $_GET['id'];
$sql = "DELETE FROM users WHERE id = ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("i", $id); // i 表示整数类型
$stmt->execute();
?>

使用软删除（逻辑删除）

在表中添加 deleted_at 字段

<?php
// 创建表时添加软删除字段
// ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;
class UserRepository {
    private $pdo;
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    // 软删除 - 逻辑删除
    public function softDelete($id) {
        $sql = "UPDATE users SET deleted_at = NOW(), updated_at = NOW() 
                WHERE id = :id AND deleted_at IS NULL";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([':id' => $id]);
    }
    // 查询时排除已删除的数据
    public function findAll() {
        $sql = "SELECT * FROM users WHERE deleted_at IS NULL";
        $stmt = $this->pdo->query($sql);
        return $stmt->fetchAll();
    }
}
?>

添加操作验证（防止误删）

<?php
class SecureDelete {
    private $pdo;
    public function secureDelete($table, $id, $userId = null) {
        // 1. 验证 ID 格式
        if (!filter_var($id, FILTER_VALIDATE_INT)) {
            throw new InvalidArgumentException('无效的 ID');
        }
        // 2. 验证用户权限（示例：只能删除自己的数据）
        if ($userId) {
            $checkSql = "SELECT id FROM $table WHERE id = :id AND user_id = :userId";
            $checkStmt = $this->pdo->prepare($checkSql);
            $checkStmt->execute([':id' => $id, ':userId' => $userId]);
            if (!$checkStmt->fetch()) {
                throw new Exception('无权删除该记录');
            }
        }
        // 3. 执行删除
        $sql = "DELETE FROM $table WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $result = $stmt->execute([':id' => $id]);
        // 4. 记录操作日志
        $this->logOperation($table, $id, $_SESSION['user_id'] ?? 'anonymous');
        return $result;
    }
    private function logOperation($table, $id, $operator) {
        $sql = "INSERT INTO operation_logs (table_name, record_id, operator, action, created_at) 
                VALUES (:table, :id, :operator, 'DELETE', NOW())";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':table' => $table,
            ':id' => $id,
            ':operator' => $operator
        ]);
    }
}
?>

CSRF 防护（表单提交删除）

<?php
session_start();
// 生成 CSRF Token
function generateCsrfToken() {
    if (empty($_SESSION['csrf_token'])) {
        $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
    }
    return $_SESSION['csrf_token'];
}
// 验证 CSRF Token
function verifyCsrfToken($token) {
    return isset($_SESSION['csrf_token']) && 
           hash_equals($_SESSION['csrf_token'], $token);
}
// 删除表单
?>
<form method="POST" action="delete.php">
    <input type="hidden" name="csrf_token" value="<?php echo generateCsrfToken(); ?>">
    <input type="hidden" name="id" value="<?php echo $record['id']; ?>">
    <button type="submit">确认删除</button>
</form>
<?php
// 处理删除请求
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // 验证 CSRF
    if (!verifyCsrfToken($_POST['csrf_token'])) {
        die('CSRF Token 验证失败');
    }
    // 验证数据
    $id = filter_input(INPUT_POST, 'id', FILTER_VALIDATE_INT);
    if (!$id) {
        die('无效的 ID');
    }
    // 执行删除操作...
}
?>

使用事务确保数据一致性

<?php
try {
    $pdo->beginTransaction();
    // 删除用户
    $sql1 = "DELETE FROM users WHERE id = :id";
    $stmt1 = $pdo->prepare($sql1);
    $stmt1->execute([':id' => $id]);
    // 删除相关的用户日志
    $sql2 = "DELETE FROM user_logs WHERE user_id = :id";
    $stmt2 = $pdo->prepare($sql2);
    $stmt2->execute([':id' => $id]);
    // 提交事务
    $pdo->commit();
    echo "删除成功";
} catch (Exception $e) {
    // 回滚事务
    $pdo->rollBack();
    error_log($e->getMessage());
    echo "删除失败: " . $e->getMessage();
}
?>

完整的安全删除示例

<?php
class SafeDeletion {
    private $pdo;
    private $logger;
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    public function safeDeleteRecord($table, $id, $userContext = null) {
        try {
            // 1. 输入验证
            $id = filter_var($id, FILTER_VALIDATE_INT);
            if ($id === false || $id <= 0) {
                throw new InvalidArgumentException("无效的记录 ID");
            }
            // 2. 表名白名单验证
            $allowedTables = ['users', 'posts', 'comments', 'products'];
            if (!in_array($table, $allowedTables)) {
                throw new InvalidArgumentException("不允许的操作表");
            }
            // 3. 开启事务
            $this->pdo->beginTransaction();
            // 4. 首先进行软删除（标记为已删除）
            $sqlUpdate = "UPDATE $table SET 
                         deleted_at = NOW(), 
                         updated_at = NOW() 
                         WHERE id = :id AND deleted_at IS NULL";
            $stmtUpdate = $this->pdo->prepare($sqlUpdate);
            $stmtUpdate->execute([':id' => $id]);
            if ($stmtUpdate->rowCount() > 0) {
                // 5. 如果需要，备份到历史表
                $this->backupToHistory($table, $id);
                // 6. 删除关联数据（可选）
                $this->deleteRelatedData($table, $id);
                // 7. 记录操作日志
                $this->logDeletion($table, $id, $userContext);
                // 8. 提交事务
                $this->pdo->commit();
                return [
                    'success' => true,
                    'message' => '删除成功'
                ];
            } else {
                $this->pdo->rollBack();
                return [
                    'success' => false,
                    'message' => '记录不存在或已被删除'
                ];
            }
        } catch (Exception $e) {
            $this->pdo->rollBack();
            error_log('删除失败: ' . $e->getMessage());
            return [
                'success' => false,
                'message' => '删除失败: ' . $e->getMessage()
            ];
        }
    }
    private function backupToHistory($table, $id) {
        // 备份到历史表
        $targetTable = $table . '_history';
        $sql = "INSERT INTO $targetTable (original_id, data_snapshot, deleted_at) 
                SELECT id, JSON_OBJECT('data', row_to_json($table)), NOW() 
                FROM $table WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
    }
    private function logDeletion($table, $id, $userContext = null) {
        $logData = [
            'table_name' => $table,
            'record_id' => $id,
            'operator' => $userContext['id'] ?? 'system',
            'ip' => $_SERVER['REMOTE_ADDR'] ?? 'unknown',
            'action' => 'DELETE',
            'detail' => json_encode($userContext ?? [])
        ];
        $sql = "INSERT INTO deletion_logs (table_name, record_id, operator, ip, action, detail, created_at) 
                VALUES (:table, :id, :operator, :ip, 'DELETE', :detail, NOW())";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($logData);
    }
    private function deleteRelatedData($table, $id) {
        // 根据业务需求，删除关联数据
        $relations = [
            'users' => ['user_profiles', 'user_settings'],
            'posts' => ['post_comments', 'post_likes']
        ];
        if (isset($relations[$table])) {
            foreach ($relations[$table] as $relatedTable) {
                $sql = "UPDATE $relatedTable SET deleted_at = NOW() 
                        WHERE parent_id = :id";
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute([':id' => $id]);
            }
        }
    }
}
// 使用示例
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$deleter = new SafeDeletion($pdo);
$userAuth = [
    'id' => $_SESSION['user_id'] ?? null,
    'role' => $_SESSION['role'] ?? 'guest'
];
$result = $deleter->safeDeleteRecord('users', $_GET['id'], $userAuth);
if ($result['success']) {
    echo json_encode(['status' => 'success', 'message' => '删除成功']);
} else {
    http_response_code(400);
    echo json_encode(['status' => 'error', 'message' => $result['message']]);
}
?>