SQL注入防护

什么是SQL注入

SQL注入（SQL Injection）是一种代码注入技术，攻击者通过在应用程序的输入字段中插入恶意的SQL代码，来操纵或破坏数据库。这是最常见和最危险的Web应用安全漏洞之一。

SQL注入的危害

1. 数据泄露：攻击者可以窃取敏感数据
2. 数据篡改：修改或删除数据库中的数据
3. 权限提升：获得管理员权限
4. 系统控制：在某些情况下可以控制整个服务器

SQL注入的基本原理

1. 不安全的查询构建

<?php
// 危险的代码示例 - 容易受到SQL注入攻击
$userId = $_GET['id'];

// 直接将用户输入拼接到SQL语句中
$sql = "SELECT * FROM users WHERE id = $userId";

$result = mysqli_query($conn, $sql);
?>

2. 攻击示例

<?php
// 正常请求
// URL: user.php?id=1
// 生成的SQL: SELECT * FROM users WHERE id = 1

// 恶意请求
// URL: user.php?id=1 OR 1=1
// 生成的SQL: SELECT * FROM users WHERE id = 1 OR 1=1
// 结果：返回所有用户数据

// 更危险的攻击
// URL: user.php?id=1; DELETE FROM users; --
// 生成的SQL: SELECT * FROM users WHERE id = 1; DELETE FROM users; --
// 结果：删除整个users表
?>

SQL注入的常见类型

1. 基于UNION的注入

<?php
// 攻击者利用UNION查询获取其他表的数据
// 输入: 1 UNION SELECT username, password FROM admin
$userId = $_GET['id'];
$sql = "SELECT name, email FROM users WHERE id = $userId";

// 结果：同时查询admin表的用户名和密码
?>

2. 基于布尔的盲注

<?php
// 攻击者通过真假条件逐个猜测数据
// 输入: 1 AND (SELECT SUBSTRING(password,1,1) FROM admin WHERE id=1)='a'
$userId = $_GET['id'];
$sql = "SELECT * FROM users WHERE id = $userId";

// 通过返回结果判断条件真假
?>

3. 基于时间的盲注

<?php
// 利用数据库函数延迟响应来判断条件
// 输入: 1 AND IF((SELECT password FROM admin WHERE id=1) LIKE 'a%', SLEEP(5), 0)
$userId = $_GET['id'];
$sql = "SELECT * FROM users WHERE id = $userId";
?>

SQL注入防护方法

1. 使用预处理语句（Prepared Statements）

预处理语句是防止SQL注入最有效的方法。

使用PDO预处理语句

<?php
class Database {
    private $pdo;

    public function __construct($host, $dbname, $username, $password) {
        try {
            $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";
            $this->pdo = new PDO($dsn, $username, $password, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false
            ]);
        } catch (PDOException $e) {
            die("数据库连接失败: " . $e->getMessage());
        }
    }

    // 安全的用户查询
    public function getUserById($id) {
        $sql = "SELECT * FROM users WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$id]);
        return $stmt->fetch();
    }

    // 安全的用户登录
    public function loginUser($username, $password) {
        $sql = "SELECT * FROM users WHERE username = ? AND password = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$username, $password]);
        return $stmt->fetch();
    }

    // 安全的插入操作
    public function insertUser($username, $email, $password) {
        $sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([$username, $email, $password]);
    }

    // 安全的更新操作
    public function updateUser($id, $username, $email) {
        $sql = "UPDATE users SET username = ?, email = ? WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([$username, $email, $id]);
    }

    // 安全的删除操作
    public function deleteUser($id) {
        $sql = "DELETE FROM users WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([$id]);
    }
}

// 使用示例
$db = new Database('localhost', 'myapp', 'user', 'pass');

// 安全的查询
$userId = $_GET['id'];
$user = $db->getUserById($userId);

if ($user) {
    echo "用户名: " . htmlspecialchars($user['username']);
} else {
    echo "用户不存在";
}
?>

使用mysqli预处理语句

<?php
class MySQLiDB {
    private $conn;

    public function __construct($host, $username, $password, $dbname) {
        $this->conn = new mysqli($host, $username, $password, $dbname);

        if ($this->conn->connect_error) {
            die("连接失败: " . $this->conn->connect_error);
        }

        // 设置字符集
        $this->conn->set_charset("utf8mb4");
    }

    // 安全的查询方法
    public function query($sql, $params = []) {
        $stmt = $this->conn->prepare($sql);

        if ($stmt === false) {
            die("预处理失败: " . $this->conn->error);
        }

        if (!empty($params)) {
            // 动态绑定参数
            $types = str_repeat('s', count($params));
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        return $stmt;
    }

    // 获取单行数据
    public function fetchOne($sql, $params = []) {
        $result = $this->query($sql, $params);
        return $result->get_result()->fetch_assoc();
    }

    // 获取多行数据
    public function fetchAll($sql, $params = []) {
        $result = $this->query($sql, $params);
        return $result->get_result()->fetch_all(MYSQLI_ASSOC);
    }

    // 插入数据
    public function insert($table, $data) {
        $columns = array_keys($data);
        $placeholders = array_fill(0, count($columns), '?');
        $values = array_values($data);

        $sql = "INSERT INTO $table (" . implode(', ', $columns) . ")
                VALUES (" . implode(', ', $placeholders) . ")";

        $stmt = $this->query($sql, $values);
        return $stmt->insert_id;
    }
}

// 使用示例
$db = new MySQLiDB('localhost', 'user', 'pass', 'myapp');

// 安全的登录验证
$username = $_POST['username'];
$password = $_POST['password'];

$sql = "SELECT id, username, role FROM users WHERE username = ? AND password = ?";
$user = $db->fetchOne($sql, [$username, $password]);

if ($user) {
    session_start();
    $_SESSION['user_id'] = $user['id'];
    $_SESSION['username'] = $user['username'];
    $_SESSION['role'] = $user['role'];
    header('Location: dashboard.php');
} else {
    echo "用户名或密码错误";
}
?>

2. 输入验证和过滤

<?php
class InputValidator {
    // 验证ID（必须是正整数）
    public static function validateId($input) {
        if (filter_var($input, FILTER_VALIDATE_INT) && $input > 0) {
            return (int)$input;
        }
        throw new InvalidArgumentException("无效的ID");
    }

    // 验证用户名（字母数字下划线，3-20字符）
    public static function validateUsername($input) {
        if (preg_match('/^[a-zA-Z0-9_]{3,20}$/', $input)) {
            return $input;
        }
        throw new InvalidArgumentException("用户名只能包含字母、数字和下划线，长度3-20字符");
    }

    // 验证邮箱
    public static function validateEmail($input) {
        if (filter_var($input, FILTER_VALIDATE_EMAIL)) {
            return $input;
        }
        throw new InvalidArgumentException("无效的邮箱地址");
    }

    // 验证字符串长度
    public static function validateLength($input, $min, $max) {
        $length = mb_strlen($input, 'UTF-8');
        if ($length >= $min && $length <= $max) {
            return $input;
        }
        throw new InvalidArgumentException("长度必须在{$min}-{$max}字符之间");
    }

    // 清理输入
    public static function sanitize($input, $type = 'string') {
        switch ($type) {
            case 'int':
                return filter_var($input, FILTER_SANITIZE_NUMBER_INT);
            case 'float':
                return filter_var($input, FILTER_SANITIZE_NUMBER_FLOAT);
            case 'email':
                return filter_var($input, FILTER_SANITIZE_EMAIL);
            case 'url':
                return filter_var($input, FILTER_SANITIZE_URL);
            default:
                return htmlspecialchars(trim($input), ENT_QUOTES, 'UTF-8');
        }
    }
}

// 使用示例
try {
    $userId = InputValidator::validateId($_GET['id']);
    $username = InputValidator::validateUsername($_POST['username']);
    $email = InputValidator::validateEmail($_POST['email']);

    // 验证通过后，使用预处理语句查询
    $user = $db->fetchOne(
        "SELECT * FROM users WHERE id = ? AND username = ? AND email = ?",
        [$userId, $username, $email]
    );

} catch (InvalidArgumentException $e) {
    die("输入验证失败: " . $e->getMessage());
}
?>

3. 使用ORM（对象关系映射）

<?php
// 简单的ORM示例
class User {
    private $db;
    private $table = 'users';

    public function __construct($database) {
        $this->db = $database;
    }

    // 查找单个用户
    public function find($id) {
        $sql = "SELECT * FROM {$this->table} WHERE id = ?";
        return $this->db->fetchOne($sql, [$id]);
    }

    // 根据条件查找
    public function findBy($field, $value) {
        $sql = "SELECT * FROM {$this->table} WHERE {$field} = ?";
        return $this->db->fetchOne($sql, [$value]);
    }

    // 创建用户
    public function create($data) {
        // 验证必填字段
        $required = ['username', 'email', 'password'];
        foreach ($required as $field) {
            if (empty($data[$field])) {
                throw new InvalidArgumentException("字段 {$field} 是必填的");
            }
        }

        return $this->db->insert($this->table, $data);
    }

    // 更新用户
    public function update($id, $data) {
        $fields = [];
        $values = [];

        foreach ($data as $field => $value) {
            $fields[] = "{$field} = ?";
            $values[] = $value;
        }

        $values[] = $id;
        $sql = "UPDATE {$this->table} SET " . implode(', ', $fields) . " WHERE id = ?";

        $stmt = $this->db->query($sql, $values);
        return $stmt->affected_rows > 0;
    }

    // 删除用户
    public function delete($id) {
        $sql = "DELETE FROM {$this->table} WHERE id = ?";
        $stmt = $this->db->query($sql, [$id]);
        return $stmt->affected_rows > 0;
    }

    // 安全的搜索功能
    public function search($keyword, $limit = 10, $offset = 0) {
        // 使用LIKE进行安全搜索
        $sql = "SELECT * FROM {$this->table}
                WHERE username LIKE ? OR email LIKE ?
                LIMIT ? OFFSET ?";

        $pattern = "%{$keyword}%";
        return $this->db->fetchAll($sql, [$pattern, $pattern, $limit, $offset]);
    }
}

// 使用示例
$user = new User($db);

try {
    // 安全的搜索
    $keyword = InputValidator::sanitize($_GET['keyword'], 'string');
    $users = $user->search($keyword);

    // 安全的创建
    $userData = [
        'username' => InputValidator::sanitize($_POST['username']),
        'email' => InputValidator::validateEmail($_POST['email']),
        'password' => password_hash($_POST['password'], PASSWORD_DEFAULT)
    ];

    $userId = $user->create($userData);
    echo "用户创建成功，ID: {$userId}";

} catch (Exception $e) {
    error_log("用户操作失败: " . $e->getMessage());
    echo "操作失败，请稍后重试";
}
?>

检测SQL注入漏洞

1. 自动化检测工具

<?php
class SQLInjectionScanner {
    private $url;
    private $parameters;
    private $errorPatterns;

    public function __construct($url, $parameters = []) {
        $this->url = $url;
        $this->parameters = $parameters;

        // 常见的SQL错误模式
        $this->errorPatterns = [
            "/SQL syntax.*MySQL/i",
            "/Warning.*mysql_.*()/i",
            "/valid MySQL result/i",
            "/MySQLClient.*failed/i",
            "/mysql_fetch_array\(\)/i",
            "/mysql_num_rows\(\)/i",
            "/ORA-[0-9]{5}/i",
            "/Oracle error/i",
            "/Microsoft OLE DB Provider for ODBC Drivers/i",
            "/ODBC Microsoft Access Driver/i",
            "/Microsoft JET Database/i",
            "/SQLServer JDBC Driver/i",
            "/PostgreSQL query failed/i",
            /Warning.*pg_.*()/i",
            "/Npgsql\./i"
        ];
    }

    // 检测SQL注入漏洞
    public function scan() {
        $results = [];

        // 测试用例
        $testPayloads = [
            "'",
            "\"",
            "' OR '1'='1",
            "\" OR \"1\"=\"1",
            "' OR 1=1--",
            "' UNION SELECT NULL--",
            "'; DROP TABLE users; --",
            "' AND (SELECT COUNT(*) FROM information_schema.tables)>0--"
        ];

        foreach ($testPayloads as $payload) {
            $result = $this->testPayload($payload);
            if ($result['vulnerable']) {
                $results[] = $result;
            }
        }

        return $results;
    }

    private function testPayload($payload) {
        // 构建测试URL
        $testParams = [];
        foreach ($this->parameters as $param => $value) {
            $testParams[$param] = $payload;
        }

        $testUrl = $this->url . '?' . http_build_query($testParams);

        // 发送请求
        $response = @file_get_contents($testUrl);

        if ($response === false) {
            return ['payload' => $payload, 'vulnerable' => false, 'reason' => 'Request failed'];
        }

        // 检查响应中的SQL错误
        foreach ($this->errorPatterns as $pattern) {
            if (preg_match($pattern, $response)) {
                return [
                    'payload' => $payload,
                    'vulnerable' => true,
                    'reason' => 'SQL error detected in response',
                    'url' => $testUrl
                ];
            }
        }

        // 检查响应时间（时间盲注）
        $start = microtime(true);
        $slowPayload = "' AND (SELECT SLEEP(5))--";
        $slowUrl = $this->url . '?' . http_build_query(array_fill_keys(array_keys($this->parameters), $slowPayload));
        @file_get_contents($slowUrl);
        $duration = microtime(true) - $start;

        if ($duration > 4) {
            return [
                'payload' => $payload,
                'vulnerable' => true,
                'reason' => 'Possible time-based blind SQL injection',
                'response_time' => $duration
            ];
        }

        return ['payload' => $payload, 'vulnerable' => false];
    }
}

// 使用示例
$scanner = new SQLInjectionScanner(
    'http://example.com/search.php',
    ['id' => '1', 'category' => 'books']
);

$vulnerabilities = $scanner->scan();

if (!empty($vulnerabilities)) {
    echo "发现SQL注入漏洞:\n";
    foreach ($vulnerabilities as $vuln) {
        echo "- 载荷: {$vuln['payload']}\n";
        echo "  原因: {$vuln['reason']}\n";
        if (isset($vuln['url'])) {
            echo "  URL: {$vuln['url']}\n";
        }
        echo "\n";
    }
} else {
    echo "未发现明显的SQL注入漏洞";
}
?>

2. 日志监控

<?php
class SQLInjectionMonitor {
    private $logFile;
    private $suspiciousPatterns;

    public function __construct($logFile = 'sql_injection.log') {
        $this->logFile = $logFile;

        // 可疑的SQL注入模式
        $this->suspiciousPatterns = [
            '/union\s+select/i',
            '/select\s+.*\s+from\s+information_schema/i',
            '/drop\s+table/i',
            '/delete\s+from/i',
            '/insert\s+into/i',
            '/update\s+.*\s+set/i',
            '/exec\s*\(/i',
            '/script\s*>/i',
            '/or\s+1\s*=\s*1/i',
            '/and\s+1\s*=\s*1/i',
            '/\'\s*or\s*\'.*\'.*\'/i',
            '/\"\s*or\s*\".*\".*\"/i'
        ];
    }

    // 监控请求参数
    public function monitorRequest() {
        $allInputs = array_merge($_GET, $_POST, $_COOKIE);

        foreach ($allInputs as $key => $value) {
            if ($this->isSuspicious($value)) {
                $this->logSuspiciousActivity($key, $value);
            }
        }
    }

    // 检查是否可疑
    private function isSuspicious($input) {
        if (is_array($input)) {
            foreach ($input as $value) {
                if ($this->isSuspicious($value)) {
                    return true;
                }
            }
            return false;
        }

        foreach ($this->suspiciousPatterns as $pattern) {
            if (preg_match($pattern, $input)) {
                return true;
            }
        }

        return false;
    }

    // 记录可疑活动
    private function logSuspiciousActivity($parameter, $value) {
        $logEntry = [
            'timestamp' => date('Y-m-d H:i:s'),
            'ip' => $_SERVER['REMOTE_ADDR'] ?? 'unknown',
            'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? 'unknown',
            'request_uri' => $_SERVER['REQUEST_URI'] ?? 'unknown',
            'parameter' => $parameter,
            'value' => $value
        ];

        $logMessage = json_encode($logEntry, JSON_UNESCAPED_UNICODE) . "\n";
        file_put_contents($this->logFile, $logMessage, FILE_APPEND | LOCK_EX);

        // 可以添加实时告警逻辑
        $this->sendAlert($logEntry);
    }

    // 发送告警
    private function sendAlert($activity) {
        // 邮件告警
        $to = 'admin@example.com';
        $subject = 'SQL注入攻击告警';
        $message = "检测到可疑的SQL注入尝试:\n\n";
        $message .= "时间: {$activity['timestamp']}\n";
        $message .= "IP: {$activity['ip']}\n";
        $message .= "参数: {$activity['parameter']}\n";
        $message .= "值: {$activity['value']}\n";
        $message .= "URL: {$activity['request_uri']}\n";

        // 在生产环境中应该使用更安全的邮件发送方式
        // mail($to, $subject, $message);

        // 记录到系统日志
        error_log("SQL注入攻击检测: " . json_encode($activity));
    }
}

// 在每个页面请求开始时进行监控
$monitor = new SQLInjectionMonitor();
$monitor->monitorRequest();
?>

最佳实践总结

1. 永远不要相信用户输入

<?php
// 错误的做法
$sql = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "'";

// 正确的做法
$sql = "SELECT * FROM users WHERE username = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$_POST['username']]);
?>

2. 使用最小权限原则

<?php
// 为应用创建专用数据库用户，只授予必要权限

// 应用用户（只能读写特定表）
// GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.users TO 'app_user'@'localhost';

// 避免使用root权限连接数据库
?>

3. 错误处理

<?php
// 不要向用户显示详细的数据库错误
try {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $result = $stmt->fetchAll();
} catch (PDOException $e) {
    // 记录到日志
    error_log("Database error: " . $e->getMessage());

    // 向用户显示友好的错误信息
    die("系统暂时繁忙，请稍后重试");
}
?>

4. 使用框架的安全功能

<?php
// 如果使用框架（如Laravel），充分利用其ORM和查询构建器

// Laravel示例 - 安全的查询
$users = DB::table('users')
    ->where('active', 1)
    ->where('created_at', '>', $date)
    ->get();

// Eloquent示例 - 更安全的模型操作
$user = User::where('email', $request->email)->first();
?>

通过学习这些SQL注入防护技术，你可以构建更加安全的PHP应用程序，保护用户数据免受恶意攻击。记住，安全是一个持续的过程，需要时刻保持警惕。