PHP mysqli查询中WHERE子句应使用哪种引号？

问题现象 #

在phpMyAdmin中，以下带双引号WHERE子句的动态透视表SQL可正常执行：

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('max(case when category = ''', category, ''' then status end) ',category)) INTO @sql FROM Meeting;
SET @sql = CONCAT('SELECT username,department ', @sql, ' FROM Meeting WHERE department ="SOCIAL WORK" GROUP BY username');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

但移植到PHP mysqli中使用相同引号写法时，查询无数据返回：

$sql = 'SET @sql = NULL;';
$sql.= 'SELECT GROUP_CONCAT(DISTINCT CONCAT("max(case when category = """, category, """ then status end) ",category)) INTO @sql FROM Meeting;';
$sql.= 'SET @sql = CONCAT("SELECT username,department, ", @sql, " FROM Meeting WHERE department ="SOCIAL WORK" GROUP BY username");';
$sql.= 'PREPARE stmt FROM @sql;';
$sql.= 'EXECUTE stmt;';   
$sql.= 'DEALLOCATE PREPARE stmt;'; 

• 移除主表的WHERE department ="SOCIAL WORK"子句后，可正常返回全部数据
• 仅在生成透视列的查询中添加WHERE category="Internal"子句时，也能正常工作

问题原因 #

核心是PHP字符串拼接时的引号转义错误，导致最终生成的SQL语句中，WHERE department ="SOCIAL WORK"部分语法不符合MySQL要求：
嵌套的引号未正确转义，使得MySQL接收到的SQL里，SOCIAL WORK没有被引号正确包裹，甚至出现语法错误，导致查询匹配不到数据。

解决方案 #

在PHP中拼接动态SQL时，推荐用单引号包裹SQL中的字符串值，同时根据PHP字符串类型（单/双引号）正确转义内部引号：

方案1：PHP单引号字符串拼接 #

在PHP单引号字符串中，用两个连续单引号（''）转义内部的单引号：

$sql = 'SET @sql = NULL;';
$sql .= 'SELECT GROUP_CONCAT(DISTINCT CONCAT(\'max(case when category = \'\'', category, '\'\' then status end) \', category)) INTO @sql FROM Meeting;';
$sql .= 'SET @sql = CONCAT(\'SELECT username,department, \', @sql, \' FROM Meeting WHERE department = \'\'SOCIAL WORK\'\' GROUP BY username\');';
$sql .= 'PREPARE stmt FROM @sql;';
$sql .= 'EXECUTE stmt;';
$sql .= 'DEALLOCATE PREPARE stmt;';

方案2：PHP双引号字符串拼接 #

在PHP双引号字符串中，用反斜杠（\）转义内部的双引号，SQL值用单引号包裹：

$sql = "SET @sql = NULL;";
$sql .= "SELECT GROUP_CONCAT(DISTINCT CONCAT(\"max(case when category = '\", category, \"' then status end) \", category)) INTO @sql FROM Meeting;";
$sql .= "SET @sql = CONCAT(\"SELECT username,department, \", @sql, \" FROM Meeting WHERE department = 'SOCIAL WORK' GROUP BY username\");";
$sql .= "PREPARE stmt FROM @sql;";
$sql .= "EXECUTE stmt;";
$sql .= "DEALLOCATE PREPARE stmt;";

额外建议 #

如果动态SQL中的值来自用户输入，强烈建议使用MySQL预处理语句传递参数以避免SQL注入；对于动态生成列的场景，可拆分步骤执行：先获取动态列的SQL片段，再拼接主查询并绑定参数。

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