MySQL UNION联表查询部分表无数据触发不完整响应报错排查

问题场景 #

开发Node.js接口时，通过UNION语句关联多张MySQL表查询安保人员档案数据。考虑到部分关联表可能不存在对应人员的相关数据，代码中预设了空值处理逻辑：查询到对应行数据则赋值，无数据则将对应数据对象置为null，接口实现代码如下：

const guardProfile = (req, res) => {
  const guardProfile = {};
  const { id } = req.params; // guard ID
  try {
    let isAdmin = 0;
    if (isAdmin === 0) {
      connection.query(
        `SELECT 'guard' AS tablename, guard.* FROM guard WHERE guardID=${id}
        UNION
        SELECT 'guardaddress' AS tablename, guardaddress.*, Null as col6, Null as col7, Null as col8, Null as col9, Null as col10, Null as col11, Null as col12, Null as col13, Null as col14, Null as col15, Null as col16 FROM guardaddress WHERE fk_guard=${id}
        UNION
        SELECT 'document' as tablename, document.* FROM document WHERE fk_guard=${id}
        UNION
        SELECT 'otherdocs' AS tablename, otherdocs.*, Null as col5, Null as col6, Null as col7, Null as col8, Null as col9, Null as col10, Null as col11, Null as col12, Null as col13, Null as col14, Null as col15, Null as col16 FROM otherdocs WHERE fk_guard=${id}
        UNION
        SELECT 'bank' AS tablename, bank.*, Null as col8, Null as col9, Null as col10, Null as col11, Null as col12, Null as col13, Null as col14, Null as col15, Null as col16 FROM bank WHERE guard_id=${id}`,
        function (err, rows) {
          if (!err) {
              const guardToken = jwt.sign(
              {
                id: rows[0].guardID,
                isAdmin: rows[0].isAdmin,
                isGuard: rows[0].isGuard,
                isCompany: rows[0].isCompany,
              },
              SECRET,
              {
                expiresIn: "3d",
              }
            );
            
            let guard = null, address = null, documents = null, otherdocs = null, bank = null;
            rows.forEach(row => {
              switch (row.tablename) {
                case 'guard':
                  guard = {
                    guardID: row.guardID,
                    firstName: row.firstName,
                    middleName: row.middleName,
                    lastName: row.lastName,
                    email: row.email,
                    password: row.password,
                    phone: row.phone,
                    dob: row.dob,
                    gender: row.gender,
                    emergencyContact: row.emergencyContact,
                  };
                  break;
                case 'guardaddress':
                    address = {
                      state: rows[1].firstName,
                      city: rows[1].middleName,
                      postalCode: rows[1].lastName,
                    };
                    break;
                case 'document':
                    documents = {
                      four82: rows[2].firstName,
                      PCR: rows[2].middleName,
                      CPR: rows[2].lastName,
                      CrowdControl: rows[2].email,
                      License: rows[2].password,
                      Firearms: rows[2].phone,
                      FirstAid: rows[2].dob,
                      MediCare: rows[2].gender,
                      Passport: rows[2].status,
                      ResponsibleAlcohol: rows[2].isAdmin,
                      Visa: rows[2].admin_id,
                      WhiteCard: rows[2].address_id,
                      YellowCard: rows[2].isGuard,
                      WorkingWithChildren: rows[2].isCompany,
                    };
                    break;
                case 'otherdocs':
                    otherdocs = {
                      name: rows[3].firstName,
                      document: rows[3].middleName,
                    };
                    break;
                case 'bank':
                    bank = {
                      bankName: rows[4].firstName,
                      accountTitle: rows[4].middleName,
                      accountNo: rows[4].lastName,
                      bsb: rows[4].email,
                      abn: rows[4].password,
                    };
                    break;
                default:
                    guard = {};
                    address = {};
                    documents = {};
                    otherdocs = {};
                    bank = {};
                    break;
              }
            });
            
            res.status(201).json({
              status: true,
              message: "Got Guard Profile!",
              guardProfile: {
                  guard,
                  address,
                  documents,
                  otherDocs,
                  bank,
              },
              guardToken,
            });
          } else {
            res
              .status(404)
              .json({ success: false, message: "Guard Not Found!" });
          }
        }
      );
    }
  } catch (error) {
    res.status(500);
    throw new Error(error);
  }
};

故障现象：即使添加了上述空值判断逻辑，接口仍会返回<h1>Incomplete response received from application</h1>错误。当查询的安保人员在所有关联表中都存在对应数据时，接口可以正常返回结果；若存在部分表无对应数据的情况，就会触发上述报错。

错误原因 #

代码存在5个直接或间接导致报错的问题：

• 结果取值逻辑错误：forEach循环遍历每一行数据时，除了guard表的取值用了当前遍历项row，其余表的取值全部硬编码写死了数组下标rows[1]/rows[2]/rows[3]/rows[4]。只要某张关联表没有对应数据，UNION返回的结果总行数就会少于5行，硬编码下标对应的元素是undefined，读取undefined上的属性会直接抛出类型错误，中断响应流程。
• 前置校验缺失：生成JWT时直接读取rows[0]的属性，如果主表guard都没有对应人员数据，rows是空数组，rows[0]为undefined，读取属性直接抛错。
• 变量名拼写不匹配：返回体里写的键是otherDocs（大写D），但前面声明赋值的变量是otherdocs（小写d），JS会将未声明的otherDocs识别为undefined，严格模式下直接抛出引用错误。
• 异常捕获失效：connection.query是异步回调函数，回调内部抛出的错误不会被外层的try/catch捕获，错误发生时进程直接异常退出，无法返回正常响应。
• 额外安全隐患：SQL语句直接拼接用户传入的id参数，存在SQL注入风险。

修复要点 #

1. 所有表的字段取值统一使用当前遍历的row对象，删除硬编码的固定数组下标
2. 处理结果前先做非空校验：如果rows为空、或者不存在guard主表记录，直接返回404
3. 统一变量命名，声明和返回时的otherdocs/otherDocs拼写保持一致
4. 异步回调内部补充错误处理逻辑，不要依赖外层try/catch捕获异步错误
5. SQL查询改用参数化传参，替换直接拼接字符串的写法，避免SQL注入

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