引言：API 的“隐形杀手”
在前后端分离的开发模式中，CRUD 接口是最基础的交付物。然而，往往是这些看似简单的列表查询接口，在生产环境中引发了最严重的事故：
- 数据泄露： 一个简单的搜索框，因为未做参数化处理，导致全库数据被拖走（SQL 注入）。
- 服务雪崩： 随着数据量增长，一个“第 10000 页”的请求导致数据库 CPU 飙升，阻塞所有业务（深分页性能问题）。
- 报错频发： 前端传了一个数据库不存在的排序字段，直接抛出 500 异常。
本文将从 SQL 层面出发，探讨如何设计一个既安全又高效的列表查询 API。
一、 安全基石：防御 SQL 注入的唯一正解
场景复现：
前端有一个搜索用户名的接口：GET /users?name=admin。
后端代码直接拼接 SQL：
-- 危险的写法
String sql = "SELECT * FROM t_users WHERE name = '" + userName + "'";
攻击方式：
攻击者传入参数：admin' OR '1'='1。
最终执行的 SQL 变为：
SELECT * FROM t_users WHERE name = 'admin' OR '1'='1';
由于 '1'='1' 永远为真，OR 导致 WHERE 条件失效，攻击者直接获取了全表数据。
技术解法：参数化查询 (Parameterized Query)
防御 SQL 注入不需要复杂的正则过滤，只需要遵循一个原则：数据是数据，代码是代码，二者永不混淆。
所有主流数据库驱动（JDBC, PyMySQL, Go sql）都支持 预编译 (Prepared Statement)。
-- 正确的写法 (JDBC 示例)
String sql = "SELECT * FROM t_users WHERE name = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userName); // 驱动会自动处理转义
QuickAPI 的设计理念：
在 QuickAPI 等现代 API 构建工具中，通常强制使用 {{param}} 语法。
例如：SELECT * FROM users WHERE name = {{name}}。
底层引擎会自动将其转换为预编译的 ? 或 :name 占位符，从架构层面彻底杜绝拼接 SQL 的可能性。
二、 性能瓶颈：千万级数据的“深分页”优化
场景复现：
后台管理系统需要展示日志列表，采用传统的 OFFSET 分页。
API 接收参数：page=10000, size=10。
生成的 SQL：
SELECT * FROM t_logs ORDER BY id DESC LIMIT 100000, 10;
性能陷阱：
在 MySQL 中，LIMIT 100000, 10 的执行逻辑是：
- 先读取并排序前 100,010 条记录。
- 抛弃前 100,000 条。
- 只保留最后 10 条。
- 随着页码越往后，扫描的数据量越大，响应时间呈线性增长（）。当数据量达到千万级，查询第 100 页可能就需要几秒钟。
技术解法：游标分页 (Keyset Pagination / Seek Method)
如果业务允许（如移动端无限滚动 feed 流），应抛弃 OFFSET，改用**“游标”**机制。即：利用上一页最后一条记录的 ID 作为下一页的查询条件。
API 设计：
请求参数由 page 变为 last_id。
SQL 优化：
-- 即使查第 1000 万条数据，利用主键索引，速度依然是 O(1) 或 O(logN)
SELECT * FROM t_logs
WHERE id < {{last_id}} -- 游标条件
ORDER BY id DESC
LIMIT 10;
这种方式利用了 B+ 树索引的特性，直接定位到目标位置开始扫描，无论数据量多大，性能都稳定如初。
三、 交互设计：灵活且安全的“动态排序”
场景复现：
前端表格支持点击表头排序。API 接收参数：sort_by=price，order=desc。
危险写法：
直接将参数拼接到 SQL 中：
-- 极度危险：攻击者可以传入 "price; DROP TABLE orders"
"ORDER BY " + sortBy + " " + sortOrder
即使使用了预编译，标准的 SQL 语法通常不支持在 ORDER BY 后使用占位符（即 ORDER BY ? 是无效的，因为数据库无法确定按哪一列排序）。
技术解法：白名单映射 (Whitelisting) 或 CASE WHEN
方案 A：应用层白名单（推荐）
在后端代码中维护一个 Map<String, String>，只允许特定的字段名通过。
Map<String, String> allowedSorts = Map.of("price", "price", "date", "create_time");
String safeColumn = allowedSorts.getOrDefault(inputSort, "id"); // 默认为 id
方案 B：SQL 层映射（QuickAPI 常用）
如果你在编写纯 SQL 的 API 服务，可以使用 CASE WHEN 这里的技巧来实现动态排序，同时避免注入风险。
SELECT * FROM products
ORDER BY
CASE WHEN {{sort_by}} = 'price' THEN price END DESC,
CASE WHEN {{sort_by}} = 'stock' THEN stock END DESC,
id DESC; -- 默认兜底排序
注：上述写法视具体数据库方言可能略有差异，核心思路是利用逻辑判断替代直接拼接。
总结
设计一个健壮的列表查询 API，不仅是实现功能，更要通过架构设计规避潜在风险：
- 安全： 永远不要相信前端传入的字符串，**预编译（参数化）**是防御注入的唯一防线。
- 速度： 面对海量数据，游标分页 (Where id > x) 远优于传统的 Offset 分页。
- 稳定： 动态排序必须经过白名单过滤或逻辑映射，严禁直接拼接字段名。
