如何将含switch case的动态SQL查询转换为参数化查询

问题背景 #

已掌握普通SQL转参数化查询的方法，但不清楚如何改造通过switch case动态构建WHERE条件的SQL，实现安全的参数化查询。

原代码 #

let query = `select * from (select acf.ID "call_flow_ID", acf.Name "CALL_FLOW_NAME", acf.SLO "SERVICE_OBJECTIVE", acf.LOG_FILTER "LOG_FILTER", acf.Max_jump,
(SELECT LISTAGG(NAME, ',') WITHIN GROUP (ORDER BY name) FROM ACR_SUBROUTINE acs RIGHT JOIN ACR_CALL_FLOW_SUB ON SUB_ID=acs.ID WHERE FLOW_ID=acf.ID AND TYPE IN ('ANC') GROUP BY TYPE) "ANNOUNCEMENT_TREATMENT",
(SELECT NAME FROM ACR_USER_DOMAIN aud WHERE aud.DOMAIN_ID=acf.DOMAIN_ID) "DOMAIN_NAME",
(SELECT DOMAIN_ID FROM ACR_USER_DOMAIN aud WHERE aud.DOMAIN_ID=acf.DOMAIN_ID) "DOMAIN_ID",
(
    SELECT LISTAGG(name,',') WITHIN GROUP (ORDER BY name)
    FROM ACR_VIRTUAL_QUEUE
    WHERE FLOW_ID=acf.id
    GROUP BY FLOW_ID
) "VIRTUAL_QUEUE",
            (select ohs.name OFFICE_HOUR_SET
                from 
                acr_call_flow f,
                acr_cf_hs hs,
                acr_holiday_set s,
                acr_office_day od,
                acr_office_day_set os, 
                acr_office_hour_set ohs
        WHERE
        od.flow_id = f.id and 
        hs.flow_id(+) =f.id and 
        hs.hs_id= s.id(+) and
        ohs.id=os.ohs_id and 
        os.od_id=od.id and 
        f.id=acf.id        
                ) "OFFICE_HOUR_SET"
                from ACR_CALL_FLOW acf)`;

let whereConstant = "";
let whereString = "";
let hasMoreThanOneCondition = false;
let parameterizedQueryObjects = [];
req.body.searchFields.forEach((field) => {
  whereConstant = " WHERE ";
  if (hasMoreThanOneCondition) {
    whereString += req.body.checkAll ? "AND" : "OR";
  }
  let checkfield = field.value.toUpperCase();
  if (checkfield == "DEBUG") {
    field.VALUE = "3";
  }
  if (checkfield == "ERROR") {
    field.VALUE = "2";
  }
  if (checkfield == "INFO") {
    field.VALUE = "1";
  }
  if (checkfield == "WARN") {
    field.VALUE = "0";
  }

  whereString += `UPPER(${field.field})`;
  switch (field.operator) {
    case "EQUALS":
    case "CONTAINS":
      whereString += `LIKE '%' || UPPER(:${field.field}) || '%'`;
      break;
    case "DOESNT_EQUALS":
    case "DOESNT_CONTAIN":
      whereString += `NOT LIKE  UPPER(:${field.field}) || '%'`;
      break;
    case "STARTS_WITH":
      whereString += ` LIKE  UPPER(:${field.field}) || '%'`;
      break;
  }
  parameterizedQueryObjects.push(field.value.replace(/\*/g, "%"));
  hasMoreThanOneCondition = true;
});

let results = await Connection.execute(
  query + (whereString !== "" ? whereConstant + whereString : ""),
  parameterizedQueryObjects,
  { outFormat: oracledb.OUT_FORMAT_OBJECT }
).catch((err) => {
  throw err;
});

核心问题分析 #

原代码使用数组传递参数，依赖参数顺序绑定，一旦存在重复字段查询（如多次查询CALL_FLOW_NAME）或条件顺序变更，会导致参数绑定错误；同时日志级别映射逻辑直接修改field.VALUE（疑似笔误，应为field.value），易引发变量混乱。

修改后的参数化实现 #

let query = `select * from (select acf.ID "call_flow_ID", acf.Name "CALL_FLOW_NAME", acf.SLO "SERVICE_OBJECTIVE", acf.LOG_FILTER "LOG_FILTER", acf.Max_jump,
(SELECT LISTAGG(NAME, ',') WITHIN GROUP (ORDER BY name) FROM ACR_SUBROUTINE acs RIGHT JOIN ACR_CALL_FLOW_SUB ON SUB_ID=acs.ID WHERE FLOW_ID=acf.ID AND TYPE IN ('ANC') GROUP BY TYPE) "ANNOUNCEMENT_TREATMENT",
(SELECT NAME FROM ACR_USER_DOMAIN aud WHERE aud.DOMAIN_ID=acf.DOMAIN_ID) "DOMAIN_NAME",
(SELECT DOMAIN_ID FROM ACR_USER_DOMAIN aud WHERE aud.DOMAIN_ID=acf.DOMAIN_ID) "DOMAIN_ID",
(
    SELECT LISTAGG(name,',') WITHIN GROUP (ORDER BY name)
    FROM ACR_VIRTUAL_QUEUE
    WHERE FLOW_ID=acf.id
    GROUP BY FLOW_ID
) "VIRTUAL_QUEUE",
            (select ohs.name OFFICE_HOUR_SET
                from 
                acr_call_flow f,
                acr_cf_hs hs,
                acr_holiday_set s,
                acr_office_day od,
                acr_office_day_set os, 
                acr_office_hour_set ohs
        WHERE
        od.flow_id = f.id and 
        hs.flow_id(+) =f.id and 
        hs.hs_id= s.id(+) and
        ohs.id=os.ohs_id and 
        os.od_id=od.id and 
        f.id=acf.id        
                ) "OFFICE_HOUR_SET"
                from ACR_CALL_FLOW acf)`;

let whereString = "";
let hasMoreThanOneCondition = false;
// 使用对象存储命名参数，键名与SQL占位符一一对应
const params = {};
// 用于处理重复字段的后缀计数
const fieldCounter = {};

req.body.searchFields.forEach((field) => {
  if (hasMoreThanOneCondition) {
    whereString += req.body.checkAll ? " AND " : " OR ";
  }

  // 处理日志级别映射
  let fieldValue = field.value;
  const upperValue = field.value.toUpperCase();
  if (upperValue === "DEBUG") {
    fieldValue = "3";
  } else if (upperValue === "ERROR") {
    fieldValue = "2";
  } else if (upperValue === "INFO") {
    fieldValue = "1";
  } else if (upperValue === "WARN") {
    fieldValue = "0";
  }

  // 处理重复字段，生成唯一占位符
  const fieldName = field.field.toLowerCase();
  fieldCounter[fieldName] = (fieldCounter[fieldName] || 0) + 1;
  const paramKey = `${fieldName}_${fieldCounter[fieldName]}`;
  const placeholder = `:${paramKey}`;

  // 构建WHERE条件片段
  whereString += `UPPER(${field.field})`;
  switch (field.operator) {
    case "EQUALS":
    case "CONTAINS":
      whereString += ` LIKE '%' || UPPER(${placeholder}) || '%'`;
      params[paramKey] = fieldValue.replace(/\*/g, "%");
      break;
    case "DOESNT_EQUALS":
    case "DOESNT_CONTAIN":
      whereString += ` NOT LIKE '%' || UPPER(${placeholder}) || '%'`;
      params[paramKey] = fieldValue.replace(/\*/g, "%");
      break;
    case "STARTS_WITH":
      whereString += ` LIKE UPPER(${placeholder}) || '%'`;
      params[paramKey] = fieldValue.replace(/\*/g, "%");
      break;
  }

  hasMoreThanOneCondition = true;
});

// 拼接完整SQL
const finalQuery = whereString ? `${query} WHERE ${whereString}` : query;

// 执行参数化查询
const results = await Connection.execute(
  finalQuery,
  params,
  { outFormat: oracledb.OUT_FORMAT_OBJECT }
).catch((err) => {
  throw err;
});

关键改动说明 #

1. 改用命名参数对象：使用params对象存储参数，键名与SQL中的占位符一一对应，避免数组顺序绑定的风险。
2. 处理重复字段：通过fieldCounter为重复字段生成唯一占位符（如call_flow_name_1、call_flow_name_2），确保参数绑定准确。
3. 优化日志级别映射：直接赋值给临时变量fieldValue，不修改原field对象，避免变量污染。
4. 规范WHERE条件拼接：添加空格避免SQL语法错误，简化whereConstant逻辑。

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