MySQL查询在phpMyAdmin正常运行但Express接口调用失效

问题现象 #

对接React前端筛选功能开发Express控制器时出现MySQL查询异常：通过路由传参实现动态查询的接口，用Postman测试时无法正常执行查询，但逻辑完全一致的SQL语句在phpMyAdmin中可正常运行。

相关代码与请求信息 #

控制器代码 #

let { tabla, nameUno, fechaIni, fechaFin, nameDos, nameTres, nameCuatro } =
      req.params;
    conn.query(
      // 预期可正常执行的SQL参考：SELECT * FROM tyt_finan WHERE tf_fecha_r BETWEEN '2022-05-31' AND '2022-06-01' AND tf_city IN ('Bogota') AND tf_estado = 'Pendiente' AND tf_campana = 'IN'
      "SELECT * FROM " +
        tabla +
        " WHERE " +
        nameUno +
        " BETWEEN " +
        fechaIni +
        " AND " +
        fechaFin +
        " AND " +
        nameDos +
        " IN ('') AND " +
        nameTres +
        " = ? AND " +
        nameCuatro +
        " = ?",
      [req.params.value1, req.params.value2, req.params.value3],
)

路由配置 #

routes.get(
  "/searchAll/:tabla/:nameUno/:fechaIni/:fechaFin/:nameDos/:value1/:nameTres/:value2/:nameCuatro/:value3",
  defaultController.searchAll
);

Postman测试请求地址 #

http://localhost:9000/searchAll/tyt_finan/tf_fecha_r/'2022-05-31'/'2022-06-01'/tf_city/'Bogota','Barranquilla'/tf_estado/'Pendiente'/tf_campana/'IN'

错误原因 #

• IN子句逻辑失效：代码中硬编码IN ('')，仅会匹配对应字段为空字符串的记录，路由传入的多值筛选参数value1完全没有被拼入查询逻辑，和phpMyAdmin中执行的SQL逻辑不一致
• 占位符与参数数量不匹配：SQL语句中仅定义了2个?参数占位符，但传入的参数数组长度为3，MySQL驱动会直接抛出参数匹配错误，查询根本不会发送到数据库执行
• 传参不规范：手动在URL参数中为日期、字符串值包裹单引号属于冗余操作，极易出现引号不配对、特殊字符转义失败的问题；直接拼接前端传入的表名、字段名到SQL语句中，存在严重的SQL注入风险。

修复方案 #

1. 新增表名、字段名白名单校验，杜绝SQL注入风险
2. 调整SQL语句结构，为IN子句动态生成对应数量的参数占位符，接入传入的多值筛选参数
3. 统一使用参数化查询处理所有值类型参数，自动处理引号、转义逻辑，不需要手动在URL里给参数加单引号
4. 保证占位符数量和传入参数数量完全匹配

修复后的控制器参考代码：

const { tabla, nameUno, fechaIni, fechaFin, nameDos, value1, nameTres, value2, nameCuatro, value3 } = req.params;

// 配置允许查询的表、字段白名单，根据实际业务调整
const ALLOWED_TABLES = ['tyt_finan'];
const ALLOWED_FIELDS = ['tf_fecha_r', 'tf_city', 'tf_estado', 'tf_campana'];
// 校验传入的表名、字段名是否合法
if (!ALLOWED_TABLES.includes(tabla) || 
    ![nameUno, nameDos, nameTres, nameCuatro].every(field => ALLOWED_FIELDS.includes(field))) {
  return res.status(400).json({ code: 400, msg: '非法查询参数' });
}

// 处理IN子句多值：拆分逗号分隔的传入值，动态生成占位符
const inValueList = value1.split(',');
const inPlaceholders = inValueList.map(() => '?').join(',');

// 拼接SQL，推荐使用mysql2驱动，支持??作为表名、字段名的转义占位符
const sql = `
  SELECT * FROM ??
  WHERE ?? BETWEEN ? AND ?
    AND ?? IN (${inPlaceholders})
    AND ?? = ?
    AND ?? = ?
`;
const queryParams = [
  tabla,
  nameUno, fechaIni, fechaFin,
  nameDos, ...inValueList,
  nameTres, value2,
  nameCuatro, value3
];

conn.query(sql, queryParams, (err, result) => {
  if (err) {
    console.error('SQL执行错误：', err);
    return res.status(500).json({ code: 500, msg: '查询失败', error: err.message });
  }
  res.json({ code: 200, data: result });
})

修复后Postman请求不需要给参数手动加单引号，正确格式如下：

http://localhost:9000/searchAll/tyt_finan/tf_fecha_r/2022-05-31/2022-06-01/tf_city/Bogota,Barranquilla/tf_estado/Pendiente/tf_campana/IN

内容的提问来源于stack exchange，提问作者Juan Sebastian Arias Robayo