动态排序必须用白名单+类型对齐的CASE表达式(SQL Server/PostgreSQL)或PREPARE拼接(MySQL),禁止直接ORDER BY @col_name;否则将导致报错、错排或SQL注入。

动态排序不能靠拼接 ORDER BY @col_name 实现,所有主流数据库都不支持运行时解析变量为列名;必须用白名单 + 类型对齐的 CASE 表达式(SQL Server/PostgreSQL)或预编译动态 SQL(MySQL),否则轻则排序错乱、重则报错或注入。
SQL Server:用 CASE + 类型强制转换 + 白名单
直接写 ORDER BY CASE WHEN @sort = 'id' THEN id WHEN @sort = 'name' THEN name END 会报 Conversion failed when converting the varchar value ——因为 id 是 INT、name 是 VARCHAR,SQL Server 尝试隐式转成同一类型失败。
- 每个
THEN分支必须显式转成同类型,推荐统一用CONVERT(VARCHAR(100), xxx),日期字段用CONVERT(VARCHAR(23), col, 126)保证字典序等价时间序 -
ELSE必须存在且类型匹配,例如ELSE ''或ELSE GETDATE(),不能留空让系统补NULL - 升降序不能传字符串
@sort_dir = 'DESC'再拼进 SQL,而应拆成@sort_dir BIT(0=ASC,1=DESC),用两层CASE控制正负号:ORDER BY CASE WHEN @sort_dir = 1 THEN -1 ELSE 1 END * CAST(... AS BIGINT) - 字段名必须来自白名单,比如只允许
'id'、'created_at'、'status',参数@sort类型设为VARCHAR(32),避免非法值触发逻辑分支外溢
MySQL:必须用 PREPARE + EXECUTE + 反引号包裹
写 ORDER BY @col_name 直接报 ERROR 1054: Unknown column '@col_name',因为 MySQL 解析器在编译阶段就要求列名是字面量。
- 唯一可行路径是拼接完整 SQL 字符串:
SET @sql = CONCAT('SELECT * FROM t ORDER BY `', @safe_col, '` ', @safe_order) -
@safe_col必须经白名单校验(如CASE @input WHEN 'price' THEN 'price' ELSE 'id' END),不能直接拼用户输入 - 列名必须用反引号包裹(
`price`),否则含空格、连字符或关键字(如order)时语法崩溃 -
@safe_order同样需白名单限制为'ASC'或'DESC',禁止拼接任意字符串防注入
PostgreSQL:多 CASE 并列 + NULLS LAST + $1 占位符
虽然 PostgreSQL 允许不同类型的 CASE 分支隐式转换,但单个 CASE 包所有字段会导致优化器无法走索引,且默认 NULLS FIRST 常把空状态排最前,违反业务预期。
- 别写一个大
CASE,改用多个并列:ORDER BY CASE WHEN $1 = 'updated_at' THEN updated_at END DESC NULLS LAST, CASE WHEN $1 = 'name' THEN name END NULLS LAST - 每个
CASE后紧跟NULLS LAST,确保空值不干扰主排序逻辑 - 参数用
$1占位符(不是@var),存储过程声明为IN sort_col TEXT,避免 SQL 注入 - 如果排序字段是 JSONB 路径(如
data->>'price'),记得建函数索引:CREATE INDEX idx_price ON t ((data->>'price')::numeric),否则必走 Seq Scan
最容易被忽略的是:动态排序字段若无对应索引,即使语法全对,执行计划也几乎必然退化为排序(Sort)操作,尤其当数据量超过几千行后性能断崖下跌;别只盯着语法是否通过,得看 EXPLAIN 里有没有 Index Scan 或 Index Only Scan。











