Nodejs编写API时执行带UNION的动态MySQL查询出现语法错误

问题根因 #

• 占位符与参数数量不匹配：你的SQL语句里有2处拼接了动态WHERE条件，对应2倍的?占位符，但调用pool.query时仅传入了1次参数数组，导致第二个WHERE后的占位符没有对应值，被SQL引擎解析为非法语法。
• 条件判断逻辑错误：photoAvailable的未定义判断写为photoAvailable == 'undefined'，仅能匹配字符串值"undefined"，无法正确识别变量未定义的场景。
• 非标准逻辑运算符：SQL中直接使用||、&&存在风险，部分SQL模式下||会被识别为字符串拼接符，而非逻辑或。

修复方案 #

1. 传入参数时将参数数组拼接2次，匹配两个子查询的占位符数量
2. 修正photoAvailable的未定义判断逻辑
3. 替换逻辑运算符为SQL标准的OR、AND

修正后代码 #

getReviewsAndPhotoReportBySellerId: (sellerId, reviewRating, title, photoAvailable, dateFrom, dateTo, callback) => {
    // 根据用户输入动态生成查询条件
     function buildConditions() {
       var conditions = [];
       var values = [];

       if (typeof sellerId !== 'undefined') {
        conditions.push("r.SellerId IN (?)");
        values.push(sellerId);
       }
     
       if (typeof reviewRating !== 'undefined') {
         conditions.push("r.ReviewRating IN (?)");
         values.push(reviewRating);
       }
 
       if(title == "has comment"){
        conditions.push("r.Title IS NOT NULL");
       }else if(title == "no comment"){
        conditions.push("r.Title IS NULL");
       }

       if(photoAvailable == 1){
        conditions.push("rp.ReviewImageUrl1 IS NOT NULL OR rp.ReviewImageUrl2 IS NOT NULL OR rp.ReviewImageUrl3 IS NOT NULL");
       }else if(typeof photoAvailable == 'undefined'){
        conditions.push("rp.ReviewImageUrl1 IS NULL AND rp.ReviewImageUrl2 IS NULL AND rp.ReviewImageUrl3 IS NULL");
       }

       if(typeof dateFrom !== 'undefined' && typeof dateTo !== 'undefined' ){
        conditions.push("r.DateCreated BETWEEN (?) AND (?) ");
        values.push(dateFrom);
        values.push(dateTo);
      }

       return {
         where: conditions.length ?
                  conditions.join(' AND ') : '1',
         values: values
       };
     }
     
     var conditions = buildConditions();
     // 拼接参数数组，匹配两个子查询的占位符
     var queryParams = conditions.values.concat(conditions.values);

     var sql = 
     '(select *, r.ReviewId  AS ActualReviewId, r.DateCreated AS ActualDateCreated from review r LEFT join reviewphoto rp ON r.ReviewId = rp.ReviewId WHERE '  
     + conditions.where 
     + ') UNION ' 
     + '(select *, r.ReviewId  AS ActualReviewId, r.DateCreated AS ActualDateCreated from review r RIGHT join reviewphoto rp ON r.ReviewId = rp.ReviewId WHERE ' 
     + conditions.where 
     + ') ORDER BY ActualDateCreated DESC ';     
     console.log(sql);
     pool.query(sql, queryParams,  (error, results, fields) => {
           if (error) {
             callback(error);
           }
           return callback(null, results);
         }
     );
   },

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