MySQL哈希标签搜索的精确控制与安全实践

<?php
$_GET["tag"] = '#nba'; // 假设用户输入
$fulltag = preg_replace("/[^a-zA-Z0-9_]/", '', $_GET["tag"]); // 清理标签，只保留字母数字下划线

// 构造SQL查询，存在SQL注入风险
$sql = 'SELECT * FROM status WHERE data LIKE "%#'.$fulltag.'%" LIMIT 12';

// 执行查询并输出结果
// ...
?>

SELECT *
FROM (
    SELECT
        data,
        CASE
            WHEN data = "#NBA" THEN 1 -- 如果是精确匹配，则标记为1
            ELSE 0                   -- 否则标记为0
        END AS is_exact             -- 定义一个别名is_exact
    FROM status
    WHERE data LIKE "%#NBA%"        -- 先进行模糊匹配，获取所有相关结果
    LIMIT 12
) AS matches
ORDER BY is_exact DESC, data ASC; -- 优先显示精确匹配（is_exact=1），然后按数据排序

<?php
$_GET["tag"] = '#nba'; // 假设用户输入
$fulltag = preg_replace("/[^a-zA-Z0-9_]/", '', $_GET["tag"]); // 清理标签

// 数据库连接信息，请替换为您的实际连接参数
$dsn = 'mysql:host=localhost;dbname=your_database_name;charset=utf8mb4';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // 错误模式：抛出异常
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // 默认获取关联数组
    ]);
} catch (PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}

$sql = '
    SELECT *
    FROM (
        SELECT
            data,
            CASE
                WHEN data = CONCAT("#", :tag1) -- 使用CONCAT和参数化处理精确匹配
                THEN 1
                ELSE 0
            END AS is_exact
        FROM status
        WHERE data LIKE CONCAT("%#", :tag2, "%") -- 使用CONCAT和参数化处理模糊匹配
        LIMIT 12
    ) AS matches
    ORDER BY is_exact DESC, data ASC
';

$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':tag1' => $fulltag, // 绑定精确匹配的参数
    ':tag2' => $fulltag, // 绑定模糊匹配的参数
]);

$results = $stmt->fetchAll();

foreach ($results as $row) {
    echo $row['data'] . " (精确度: " . $row['is_exact'] . ")\n";
}
?>

<?php
$_GET["tag"] = '#nba'; // 假设用户输入
$fulltag = preg_replace("/[^a-zA-Z0-9_]/", '', $_GET["tag"]); // 清理标签

// 数据库连接信息（同上）
$dsn = 'mysql:host=localhost;dbname=your_database_name;charset=utf8mb4';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
} catch (PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}

$sql = 'SELECT * FROM status WHERE data LIKE CONCAT("%#", :fullTag, "%") LIMIT 12';

$stmt = $pdo->prepare($sql);
$stmt->execute([':fullTag' => $fulltag]);

$results = $stmt->fetchAll();

foreach ($results as $row) {
    echo $row['data'] . "\n";
}
?>