Oracle中能否结合用户输入使用ORDER BY？附失效代码求助

问题描述 #

编写了支持动态指定排序字段的存储过程，但调用后排序未生效。原代码尝试通过参数传递排序字段，执行后结果未按预期列排序。

原存储过程及函数代码：

PROCEDURE GET_DEPARTMENT_LIST(ORDER_BY_PARAM IN VARCHAR2, DEPT_DATA OUT  T_CURSOR) IS
  V_CURSOR T_CURSOR;
  BEGIN
  OPEN V_CURSOR FOR
  SELECT GET_LIST(ORDER_BY_PARAM) FROM DUAL;
  DEPT_DATA  := V_CURSOR;
  END GET_DEPARTMENT_LIST;
  
  FUNCTION GET_LIST (PAR_ORDER_BY IN VARCHAR2)
     RETURN SYS_REFCURSOR
    IS
      L_CR SYS_REFCURSOR;
    BEGIN
      OPEN L_CR FOR
          SELECT DEPARTMENT_ID, DEPARTMENT_CODE, DEPARTMENT_NAME 
          FROM DEPARTMENT ORDER BY PAR_ORDER_BY ASC;
      RETURN L_CR;
  END;

执行语句：

VARIABLE RC REFCURSOR;
EXECUTE DEPARTMENT_PKG.GET_DEPARTMENT_LIST('DEPARTMENT_NAME', :RC);
PRINT RC;

问题原因 #

静态SQL中ORDER BY PAR_ORDER_BY会将PAR_ORDER_BY当作字符串字面量而非列名处理，实际是按固定字符串排序，因此无法实现动态列排序的效果。

解决方案 #

1. 使用动态SQL拼接排序字段 #

修改函数，通过动态SQL将排序字段作为列名拼接进查询语句，同时加入输入验证避免SQL注入。

2. 简化游标赋值逻辑 #

原存储过程中通过SELECT GET_LIST(...) FROM DUAL嵌套游标的写法冗余，直接将函数返回的游标赋值给输出参数即可。

修正后的完整代码：

PROCEDURE GET_DEPARTMENT_LIST(ORDER_BY_PARAM IN VARCHAR2, DEPT_DATA OUT  T_CURSOR) IS
BEGIN
  -- 直接将函数返回的游标赋值给输出参数
  DEPT_DATA := GET_LIST(ORDER_BY_PARAM);
END GET_DEPARTMENT_LIST;

FUNCTION GET_LIST (PAR_ORDER_BY IN VARCHAR2)
   RETURN SYS_REFCURSOR
IS
  L_CR SYS_REFCURSOR;
  L_VALID_ORDER_COL VARCHAR2(100);
BEGIN
  -- 验证输入的排序字段，仅允许指定列，防止SQL注入
  L_VALID_ORDER_COL := CASE PAR_ORDER_BY
                          WHEN 'DEPARTMENT_ID' THEN 'DEPARTMENT_ID'
                          WHEN 'DEPARTMENT_CODE' THEN 'DEPARTMENT_CODE'
                          WHEN 'DEPARTMENT_NAME' THEN 'DEPARTMENT_NAME'
                          ELSE 'DEPARTMENT_ID' -- 默认排序字段
                        END;

  -- 动态拼接SQL语句，将验证后的列名作为排序字段
  OPEN L_CR FOR
      'SELECT DEPARTMENT_ID, DEPARTMENT_CODE, DEPARTMENT_NAME 
       FROM DEPARTMENT ORDER BY ' || L_VALID_ORDER_COL || ' ASC';
  RETURN L_CR;
END;

补充说明 #

• 输入验证步骤不可省略：通过CASE语句限定允许的排序列，避免恶意输入导致SQL注入风险。
• 也可通过查询USER_TAB_COLUMNS表动态验证列名是否存在，进一步提升安全性。

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