PHP中MySQL预处理语句出现伪随机语法错误求助

我在PHP中用预处理语句实现了一个MySQL查询，目的是防止SQL注入。这个查询会扫描文章数据库，搜索$derired_word：词在标题中权重1000，摘要中100，仅在正文中10；通过两个JOIN获取额外信息，已经为MATCH()涉及的所有字段创建了FULLTEXT索引。

预处理语句代码如下：

$stmt = $conn->prepare(
    "SELECT a.articleTitle, a.articleSummary, a.articleText, a.articleText2, a.date, b.tags, b.icon, c.user

    MATCH (a.articleTitle) AGAINST ((?) IN BOOLEAN MODE) * 1000 AS rel1,
    MATCH (a.articleSummary) AGAINST ((?) IN BOOLEAN MODE) * 100 AS rel2,
    MATCH (a.articleText, a.articleText2) AGAINST ((?) IN BOOLEAN MODE) * 10 AS rel3

    FROM table_a a
    INNER JOIN table_b b ON (a.id = b.id)
    INNER JOIN table_c c ON a.id = c.id
    
    WHERE MATCH (articleTitle, articleSummary, articleText, articleText2) 
    
    AGAINST ((?) IN BOOLEAN MODE)
    
    ORDER BY (rel1)+(rel2)+(rel3) DESC
                
    LIMIT 10");

$stmt->bind_param("ssss", $derired_word, $derired_word, $derired_word, $derired_word);

执行时出现如下错误：

PHP Fatal error: Uncaught mysqli_sql_exception: syntax error, unexpected '+' in /var/www/html/a.php:7371

Stack trace:

#0 /var/www/html/a.php(7371): mysqli_stmt->execute()

#1 /var/www/html/a.php(9234): call()

#2 /var/www/html/call2.php(428): call3()

#3 {main}

thrown in /var/www/html/a.php on line 7371, referer: https://xxxxxx

奇怪的是报错里的意外字符每次调用都不一样（比如@、)、-、$end），看起来像是预处理语句没正常工作，输入数据被当作代码解析了，这是什么原因？

解决分析 #

这不是预处理语句的问题，核心是SQL语法错误：在SELECT字段列表中，最后一个常规字段c.user和后面的MATCH(...) AS rel1之间缺少逗号，导致SQL解析器无法正确识别字段边界，后续的内容被错误解析，加上每次$derired_word的内容不同，就会出现随机的“意外字符”报错。

修正后的代码只需在c.user后面添加逗号，另外可以去掉AGAINST里多余的括号（(?)改成?不影响功能，更简洁）：

$stmt = $conn->prepare(
    "SELECT a.articleTitle, a.articleSummary, a.articleText, a.articleText2, a.date, b.tags, b.icon, c.user,

    MATCH (a.articleTitle) AGAINST (? IN BOOLEAN MODE) * 1000 AS rel1,
    MATCH (a.articleSummary) AGAINST (? IN BOOLEAN MODE) * 100 AS rel2,
    MATCH (a.articleText, a.articleText2) AGAINST (? IN BOOLEAN MODE) * 10 AS rel3

    FROM table_a a
    INNER JOIN table_b b ON (a.id = b.id)
    INNER JOIN table_c c ON a.id = c.id
    
    WHERE MATCH (articleTitle, articleSummary, articleText, articleText2) 
    
    AGAINST (? IN BOOLEAN MODE)
    
    ORDER BY (rel1)+(rel2)+(rel3) DESC
                
    LIMIT 10");

$stmt->bind_param("ssss", $derired_word, $derired_word, $derired_word, $derired_word);

另外可以优化查询性能：当前代码重复调用了4次MATCH，可以通过子查询或表达式复用的方式减少重复计算，不过这属于性能优化范畴，优先解决语法错误即可。

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