本文目录导读：
在 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']]);
}
?>
最佳实践总结
数据库层防护
- 使用外键约束和级联删除
- 定期备份数据库
- 使用数据库审计功能
应用层防护
- 始终使用预处理语句
- 实现软删除机制
- 添加操作验证和权限控制
- 使用 CSRF 防护
- 记录操作日志
- 使用事务保证数据一致性
部署层防护
- 限制数据库用户权限
- 使用 HTTPS 传输
- 设置合理的数据库防火墙规则
- 定期进行安全审计
通过实施这些方法,可以确保在 PHP 应用中的删除操作既安全又可控，同时保护数据完整性和系统安全性。
