PHP怎么过滤SQL注释_PHPSQL注释符号处理技巧

--

#

/* ... */

preg_replace

' OR 1=1 /*

/*

preg_replace

<?php

function remove_sql_comments($sql_string) {
    // 匹配多行注释 /* ... */
    // [\s\S]*? 匹配任何字符（包括换行符）非贪婪模式
    $multi_line_comment_pattern = '/\/\*[\s\S]*?\*\//';

    // 匹配单行注释 -- 和 #
    // --.* 匹配以 -- 开头到行尾的所有字符
    // #.* 匹配以 # 开头到行尾的所有字符
    // $ 确保匹配到行尾，m 修正符让 ^ 和 $ 匹配行首行尾而不是字符串首尾
    $single_line_comment_pattern = '/(--.*)|(#.*)/m';

    // 组合正则表达式，先移除多行注释，再移除单行注释，这样更稳妥
    // 注意顺序，多行注释可能包含单行注释的字符，先处理多行
    $sql_string = preg_replace($multi_line_comment_pattern, '', $sql_string);
    $sql_string = preg_replace($single_line_comment_pattern, '', $sql_string);

    // 移除多余的空格和换行符，让SQL更整洁
    $sql_string = trim(preg_replace('/\s\s+/', ' ', $sql_string));

    return $sql_string;
}

// 示例
$dirty_sql = "SELECT id, name FROM users # 获取用户数据
WHERE status = 1 -- 活跃用户
AND created_at < '2023-01-01' /* 这是一个日期过滤 */
ORDER BY id DESC;";

$clean_sql = remove_sql_comments($dirty_sql);
echo "原始SQL:\n" . $dirty_sql . "\n\n";
echo "过滤后SQL:\n" . $clean_sql . "\n";

// 另一个例子，注释在中间
$dirty_sql_2 = "INSERT INTO products (name, price /* product price */) VALUES ('Test', 100);";
$clean_sql_2 = remove_sql_comments($dirty_sql_2);
echo "\n原始SQL 2:\n" . $dirty_sql_2 . "\n\n";
echo "过滤后SQL 2:\n" . $clean_sql_2 . "\n";

?>

--

#

[\s\S]*?

?

*/

'这是一个包含 -- 的字符串'

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=testdb;charset=utf8mb4", "username", "password");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $user_id = 1;
    $user_name = "John Doe' OR 1=1 --"; // 恶意输入，但会被当作数据

    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ? AND name = ?");
    $stmt->execute([$user_id, $user_name]); // 数据通过数组绑定

    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($user) {
        echo "Found user: " . $user['name'] . "\n";
    } else {
        echo "User not found.\n";
    }

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

<?php
$mysqli = new mysqli("localhost", "username", "password", "testdb");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$user_id = 1;
$user_name = "John Doe' OR 1=1 --"; // 恶意输入

$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ? AND name = ?");
if ($stmt) {
    $stmt->bind_param("is", $user_id, $user_name); // "is" 表示第一个参数是整数，第二个是字符串
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result->num_rows > 0) {
        $user = $result->fetch_assoc();
        echo "Found user: " . $user['name'] . "\n";
    } else {
        echo "User not found.\n";
    }

    $stmt->close();
} else {
    echo "Error preparing statement: " . $mysqli->error . "\n";
}

$mysqli->close();
?>

$user_name