PHP中Cookie值嵌入MySQL查询报错的解决求助

核心问题分析 #

1. 直接拼接的安全与语法风险：直接将$_COOKIE变量拼接进SQL语句，不仅存在SQL注入风险，若Cookie值包含特殊字符或格式异常，会直接破坏SQL语法结构，触发前端解析错误（你看到的Uncaught SyntaxError大概率是PHP输出的SQL错误信息混入前端JS代码导致的）。
2. 代码笔误：原查询中AND i.latitude < ".$maxLng."是错误的，应该是AND i.longitude < ".$maxLng."，这个笔误会导致经纬度范围判断逻辑完全错误。

修复步骤 #

1. 验证并过滤Cookie参数 #

先确保从Cookie获取的经纬度是合法的浮点数，避免无效值进入查询：

// 从Cookie获取经纬度，同时验证格式
$minLat = isset($_COOKIE['minLat']) ? filter_var($_COOKIE['minLat'], FILTER_VALIDATE_FLOAT) : null;
$maxLat = isset($_COOKIE['maxLat']) ? filter_var($_COOKIE['maxLat'], FILTER_VALIDATE_FLOAT) : null;
$minLng = isset($_COOKIE['minLng']) ? filter_var($_COOKIE['minLng'], FILTER_VALIDATE_FLOAT) : null;
$maxLng = isset($_COOKIE['maxLng']) ? filter_var($_COOKIE['maxLng'], FILTER_VALIDATE_FLOAT) : null;

// 校验参数有效性，无效则终止执行
if (!$minLat || !$maxLat || !$minLng || !$maxLng) {
    echo json_encode(['error' => '无效的经纬度参数']);
    exit;
}

2. 使用预处理语句绑定参数（推荐） #

预处理语句能自动处理参数转义，避免语法错误和SQL注入，以下提供两种常见数据库扩展的实现：

方式一：MySQLi预处理

// 假设已建立MySQLi连接$conn
$query = "SELECT i.latitude, i.longitude,
    IFNULL(i.name, '') AS `name`,
    IF(i.representative_ext IS NULL,
        CONCAT(SUBSTRING_INDEX(TRIM(LEADING '.' FROM i.path), '.', 1 ), '-sq.', SUBSTRING_INDEX(TRIM(LEADING '.' FROM i.path), '.', -1 )),
        TRIM(LEADING '.' FROM
            REPLACE(i.path, TRIM(TRAILING '.' FROM SUBSTRING_INDEX(i.path, '/', -1 )),
                CONCAT('pwg_representative/',
                    CONCAT(
                        TRIM(TRAILING '.' FROM SUBSTRING_INDEX( SUBSTRING_INDEX(i.path, '/', -1 ) , '.', 1 )),
                        CONCAT('-sq.', i.representative_ext)
                    )
                )
            )
        )
    ) AS `pathurl`,
    TRIM(TRAILING '/' FROM CONCAT( i.id, '/category/', IFNULL(i.storage_category_id, '') ) ) AS `imgurl`,
    IFNULL(i.comment, '') AS `comment`,
    IFNULL(i.author, '') AS `author`,
    i.width
FROM ".IMAGES_TABLE." AS i
INNER JOIN (".IMAGE_CATEGORY_TABLE." AS ic ".$INNER_JOIN.") ON i.id = ic.image_id
WHERE ".$LIMIT_SEARCH." i.latitude IS NOT NULL AND i.longitude IS NOT NULL
    AND i.latitude > ? 
    AND i.latitude < ?
    AND i.longitude > ? 
    AND i.longitude < ?
".$forbidden." GROUP BY i.id;";

// 初始化预处理语句
$stmt = $conn->prepare($query);
// 绑定4个浮点数参数（'dddd'表示4个double类型）
$stmt->bind_param('dddd', $minLat, $maxLat, $minLng, $maxLng);
// 执行查询
$stmt->execute();
// 获取结果集
$result = $stmt->get_result();

// 后续可通过$result->fetch_assoc()等方式处理数据

方式二：PDO预处理

// 假设已建立PDO连接$pdo
$query = "SELECT i.latitude, i.longitude,
    IFNULL(i.name, '') AS `name`,
    IF(i.representative_ext IS NULL,
        CONCAT(SUBSTRING_INDEX(TRIM(LEADING '.' FROM i.path), '.', 1 ), '-sq.', SUBSTRING_INDEX(TRIM(LEADING '.' FROM i.path), '.', -1 )),
        TRIM(LEADING '.' FROM
            REPLACE(i.path, TRIM(TRAILING '.' FROM SUBSTRING_INDEX(i.path, '/', -1 )),
                CONCAT('pwg_representative/',
                    CONCAT(
                        TRIM(TRAILING '.' FROM SUBSTRING_INDEX( SUBSTRING_INDEX(i.path, '/', -1 ) , '.', 1 )),
                        CONCAT('-sq.', i.representative_ext)
                    )
                )
            )
        )
    ) AS `pathurl`,
    TRIM(TRAILING '/' FROM CONCAT( i.id, '/category/', IFNULL(i.storage_category_id, '') ) ) AS `imgurl`,
    IFNULL(i.comment, '') AS `comment`,
    IFNULL(i.author, '') AS `author`,
    i.width
FROM ".IMAGES_TABLE." AS i
INNER JOIN (".IMAGE_CATEGORY_TABLE." AS ic ".$INNER_JOIN.") ON i.id = ic.image_id
WHERE ".$LIMIT_SEARCH." i.latitude IS NOT NULL AND i.longitude IS NOT NULL
    AND i.latitude > :minLat 
    AND i.latitude < :maxLat
    AND i.longitude > :minLng 
    AND i.longitude < :maxLng
".$forbidden." GROUP BY i.id;";

// 预处理SQL
$stmt = $pdo->prepare($query);
// 绑定参数并执行
$stmt->execute([
    ':minLat' => $minLat,
    ':maxLat' => $maxLat,
    ':minLng' => $minLng,
    ':maxLng' => $maxLng
]);

// 获取结果集
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

3. 临时修复（不推荐，仅作应急） #

如果暂时无法改用预处理，至少要对参数进行转义并修正笔误：

// 假设已建立数据库连接$conn
$minLat = $conn->real_escape_string($_COOKIE['minLat']);
$maxLat = $conn->real_escape_string($_COOKIE['maxLat']);
$minLng = $conn->real_escape_string($_COOKIE['minLng']);
$maxLng = $conn->real_escape_string($_COOKIE['maxLng']);

// 修正笔误后的查询
$query="SELECT i.latitude, i.longitude,
    IFNULL(i.name, '') AS `name`,
    IF(i.representative_ext IS NULL,
        CONCAT(SUBSTRING_INDEX(TRIM(LEADING '.' FROM i.path), '.', 1 ), '-sq.', SUBSTRING_INDEX(TRIM(LEADING '.' FROM i.path), '.', -1 )),
        TRIM(LEADING '.' FROM
            REPLACE(i.path, TRIM(TRAILING '.' FROM SUBSTRING_INDEX(i.path, '/', -1 )),
                CONCAT('pwg_representative/',
                    CONCAT(
                        TRIM(TRAILING '.' FROM SUBSTRING_INDEX( SUBSTRING_INDEX(i.path, '/', -1 ) , '.', 1 )),
                        CONCAT('-sq.', i.representative_ext)
                    )
                )
            )
        )
    ) AS `pathurl`,
    TRIM(TRAILING '/' FROM CONCAT( i.id, '/category/', IFNULL(i.storage_category_id, '') ) ) AS `imgurl`,
    IFNULL(i.comment, '') AS `comment`,
    IFNULL(i.author, '') AS `author`,
    i.width
        FROM ".IMAGES_TABLE." AS i
            INNER JOIN (".IMAGE_CATEGORY_TABLE." AS ic ".$INNER_JOIN.") ON i.id = ic.image_id
            WHERE ".$LIMIT_SEARCH." i.latitude IS NOT NULL AND i.longitude IS NOT NULL
    AND i.latitude > ".$minLat."  AND i.latitude < ".$maxLat."
             AND i.longitude > ".$minLng."  AND i.longitude < ".$maxLng."


           ".$forbidden." GROUP BY i.id;";

注意：这种方式仍存在SQL注入风险，仅作临时过渡使用，建议尽快改用预处理语句。

内容的提问来源于stack exchange，提问作者Vincent Dc