前阵子接手一个老系统的运维打开存储过程目录看到一串以sp_开头的函数里面几乎全是EXECUTE SELECT ... || 变量这种写法。我就知道这系统的数据库层迟早要出点事。后来确实出了——不是漏洞被外部打穿而是某个物料编码里带了个单引号直接把整条报表查询打断导致线上功能异常了半个多小时。这事让我重新把目光放在了一个平时不太起眼的主题上用PL/pgSQL写净化SQL的函数。很多人一听净化就想到了过滤关键字、拦截恶意字符串其实这是最大的误解。真正靠谱的净化是借助PostgreSQL原生机制把动态SQL里的结构和数据彻底分开让用户输入永远无法成为SQL语法的一部分。这篇文章我就从实际踩坑出发讲清楚PL/pgSQL里做SQL净化到底在做什么、怎么做、以及上线之后还会遇到哪些很隐蔽的坑。1. 为什么我把SQL净化放进了数据库层1.1 应用层拼SQL的老毛病比你想的更普遍大多数团队的第一道防线在应用层比如用参数化查询、用ORM框架。但现实是总有一些场景绕不开动态拼接多维度的报表查询、自定义排序、批量导入导出、后台管理系统的筛选面板。尤其当业务方说我们条件组合不固定没法参数化的时候开发顺手就会写出一大串where 11加循环拼接的逻辑。应用层代码的问题在于过滤逻辑藏在几十个服务文件里很难统一审查外包项目、历史遗留系统更是重灾区。我见过一个内部数据分析平台前端传过来的排序字段被直接拼进了ORDER BY压根没人管。这种代码如果只是内部用还好一旦暴露到公网等于把数据库裸奔给攻击者看。1.2 数据库层兜底的职责边界把SQL净化下沉到数据库层不是说应用层的参数化就可以不做了而是在应用层拼SQL已成事实的情况下给数据库加一道最终防线。PostgreSQL的PL/pgSQL函数天然适合干这个它支持动态SQL、支持参数绑定、支持对标识符做安全引用还能靠函数自身的权限体系限制调用范围。我个人的经验是净化函数要放在一个独立模式下并且只向真正需要的业务方开放。它不替代应用层校验但可以在应用层漏掉的时候拦住最危险的那部分——也就是让用户输入永远进不了SQL语法结构。1.3 哪些SQL值得净化不是所有SQL都需要净化需要重点关注的是这三类动态生成查询条件、排序字段的报表/搜索接口。这类SQL因为组合不固定最容易走拼接路线。接收批量导入数据的存储过程。文件内容、外部接口字段都可能携带恶意字符而且因为数据量大问题往往更难定位。任何把外部输入直接传给EXECUTE的公共函数。这种函数往往被多个模块复用一处危险处处受牵连。判断标准很简单如果某条SQL的执行计划里出现了变量代表的表名、列名、运算符那这条SQL就必须过净化这关。2. 净化的正确姿势先弄懂净化到底在防什么2.1 黑名单过滤为什么总被绕很多团队一听到防SQL注入第一反应就是列黑名单过滤掉单引号、过滤掉select、过滤掉union、过滤掉注释符。这套路看起来简单实际漏洞百出。举个例子一段过滤了select的代码攻击者只需要写sElEcT就能绕过大小写如果再过滤了大小写变体还有/*注释*/分割法、十六进制编码、Unicode全角字符等绕过方式。更麻烦的是过滤本身会误伤正常业务数据比如一个人的姓名叫OBrien你过滤掉单引号他名字直接被改了某产品型号叫S1-/UNI/X被注释符规则匹配直接拦掉。业务数据千奇百怪黑名单永远追不上攻击者的脑洞。所以我在做净化时的第一原则就是绝不靠关键字黑名单。净化不等于消毒而等于结构化——把变量放回它该在的位置。2.2 PostgreSQL自带的三个防弹衣函数PL/pgSQL里有两个函数是转义标识符和字符串的利器quote_ident和quote_literal还有一个format函数可以组合使用。它们的作用不是过滤恶意字符而是把输入当作纯粹的标识符或字符串值用PostgreSQL自己的规则重新包裹从语法层面消除注入可能。函数作用适用场景quote_ident(text)将输入转为带引号的合法标识符如orders转为orders表名、列名、索引名等结构对象quote_literal(text)将输入转为带引号的字符串字面量如ab转为ab字符串类型的值format(%L,值是%s)按格式安全包裹%L等于字符串字面量%I等于标识符%s为原样输出组合SQL时最常用拿quote_literal来说它会把单引号翻倍成SQL标准形式OBrien会被转成OBrien加在外层的引号保证了这段文本无论里面装了什么都只会被解析成一个字符串。quote_ident则会把标识符用双引号包裹并把内部双引号转义避免恶意文本里塞个; DROP TABLE users; --这样的内容。2.3 真正的净化把数据和结构分开这是PL/pgSQL净化SQL的核心逻辑也是我在团队里反复强调的一点。一切用户输入可以分成两类数据部分where条件里的值、insert/update的值、like的模式串。这部分应该走EXECUTE ... USING参数绑定由数据库驱动负责转义根本不需要手动拼接。结构部分表名、列名、排序方向、聚合方式。这部分没法参数绑定必须单独做校验。举个例子动态查询写成EXECUTE format(SELECT * FROM %I WHERE id $1, v_table) USING v_id这句话的意思是v_table只会被当作表名解析v_id通过USING绑定传参永远不会被当作SQL语法执行。就算攻击者在v_id里塞了1 OR 11它也只是一个绑定变量翻不起浪。所以净化SQL的本质是让结构走白名单或安全引用、数据走参数绑定。这两个手段一组合绝大多数动态SQL的注入风险就没了。3. 一套可复用的PL/pgSQL净化函数实现3.1 标识符白名单校验函数对于表名、列名这种结构对象我最推荐的办法不是quote_ident直接转义而是先过白名单。因为白名单的好处是错就错到底表名字典是有限的业务系统不可能需要运行任何任意表名。下面这个函数是我在多个项目里反复用到的模板-- 校验某个标识符是否在允许列表中 CREATE OR REPLACE FUNCTION tools.is_allowed_ident( p_ident text, p_allow_list text[] ) RETURNS boolean LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN RETURN p_ident ANY (p_allow_list); END; $$;调用的时候传数组SELECT tools.is_allowed_ident( orders, ARRAY[orders, order_items, users, products] ); -- 返回 true SELECT tools.is_allowed_ident( users; DROP TABLE users, ARRAY[orders, order_items] ); -- 返回 false用的时候动态SQL会先做一次判断不在白名单里直接抛异常。这个函数本身没有副作用返回布尔值也可以被其他函数随意调用。3.2 安全执行动态SQL的封装函数光有白名单还不够动态SQL的执行入口也要统一封装。我习惯写一个安全查询执行器把所有动态查询收口到一个函数里CREATE OR REPLACE FUNCTION tools.exec_safe_query( p_table text, -- 表名走白名单 p_columns text[], -- 列名数组全走白名单 p_where_col text, -- where条件列名 p_where_val text -- where条件值走参数绑定 ) RETURNS SETOF record LANGUAGE plpgsql AS $$ DECLARE v_sql text; v_cols text; BEGIN -- 1. 表名白名单校验 IF NOT tools.is_allowed_ident( p_table, ARRAY[orders, order_items, users, products] ) THEN RAISE EXCEPTION 非法表名: %, p_table; END IF; -- 2. 列名逐个校验并安全拼接 SELECT string_agg(quote_ident(c), , ) INTO v_cols FROM unnest(p_columns) AS c WHERE tools.is_allowed_ident(c, ARRAY[ id, created_at, status, customer_name, amount ]); IF v_cols IS NULL OR v_cols THEN RAISE EXCEPTION 非法列名; END IF; -- 3. 组装SQLwhere值使用 $1 绑定 v_sql : format( SELECT %s FROM %I WHERE %I $1, v_cols, p_table, p_where_col ); RETURN QUERY EXECUTE v_sql USING p_where_val; END; $$;这里最关键的是第3步p_where_val根本没有拼进SQL字符串而是通过USING p_where_val绑定。就算用户在p_where_val传了 OR 11它到了执行器里也只是个字符串值。3.3 输入值清洗与类型强转有时候我们不能直接绑定参数比如老的业务系统要求把查询条件拼进SQL字符串这时候就需要对数据部分做清洗。我的习惯是分两步先做类型强转再做长度和正则白名单限制。-- 清洗字符串类型的查询值防止极端输入 CREATE OR REPLACE FUNCTION tools.clean_text_value( p_input text, p_max_len integer DEFAULT 200 ) RETURNS text LANGUAGE plpgsql IMMUTABLE AS $$ DECLARE v_clean text; BEGIN -- 1. 空值直接返回NULL IF p_input IS NULL OR p_input THEN RETURN NULL; END IF; -- 2. 超长截断或直接拒绝按业务选择 IF length(p_input) p_max_len THEN RAISE EXCEPTION 输入超长: 最大允许 % 字符, p_max_len; END IF; -- 3. 去首尾空白 v_clean : trim(p_input); -- 4. 禁止控制字符但保留正常单引号因为后面会quote_literal IF v_clean ~ [\x00-\x08\x0B\x0C\x0E-\x1F] THEN RAISE EXCEPTION 输入包含非法控制字符; END IF; RETURN v_clean; END; $$;注意这里我没有过滤单引号。因为单引号在SQL字符串里属于合法字符真正安全的做法是后续用quote_literal或参数绑定来转义而不是直接删掉。这是很多初写净化函数的人最容易犯的错把业务数据给误杀了。3.4 完整示例一个报表查询入口存储过程把上面这些函数串起来就是一个比较完整的报表查询入口。假设业务场景是多维度的订单筛选支持按时间区间、客户姓名、状态、金额排序。CREATE OR REPLACE FUNCTION reports.generate_order_report( p_status text, p_customer text, p_min_amount numeric, p_sort_col text DEFAULT created_at, p_sort_dir text DEFAULT desc ) RETURNS TABLE(order_id bigint, customer text, status text, amount numeric, created_at timestamptz) LANGUAGE plpgsql AS $$ DECLARE v_sql text; v_where text : ; v_sort_col text; v_order text; BEGIN -- 排序字段白名单映射 v_sort_col : CASE p_sort_col WHEN created_at THEN created_at WHEN amount THEN amount WHEN status THEN status ELSE created_at END; -- 排序方向只允许两个固定值 v_order : CASE WHEN lower(p_sort_dir) asc THEN ASC WHEN lower(p_sort_dir) desc THEN DESC ELSE DESC END; -- 拼接where条件全部用参数绑定 IF tools.clean_text_value(p_status) IS NOT NULL THEN v_where : v_where || format( AND status $1, ); -- 实际需要在EXECUTE时动态带参数 END IF; v_sql : format( SELECT order_id, customer_name, status, amount, created_at FROM orders WHERE 11 %s ORDER BY %I %s LIMIT 100, v_where, v_sort_col, v_order ); RETURN QUERY EXECUTE v_sql USING tools.clean_text_value(p_status); END; $$;这个过程中排序字段没有走quote_ident硬拼而是走CASE映射把用户可输入和实际可执行彻底隔开。排序方向也限制成ASC/DESC两个固定值。这就是净化的另一个思路能不用用户输入就不用能映射就映射少一个拼接点就少一分风险。4. 上线后踩过的几个坑4.1 SECURITY DEFINER 与 search_path 的坑净化函数如果涉及权限提升通常要设置成SECURITY DEFINER意思是以函数创建者的权限执行而不是以调用者的权限执行。这个机制有个配套陷阱如果函数内部大量使用动态SQL并且没有固定search_path就可能被利用做权限劫持。举个例子函数内部写SELECT * FROM orders但当前连接的search_path被改成了public, pg_catalog。攻击者如果在public模式下建一个同名orders表函数执行时就会命中攻击者的表而不是业务表。更危险的是如果函数是SECURITY DEFINER且创建者是超级用户那就等于给了攻击者一条提权路径。我的对策很固定在函数开头固定search_path并且只授予最小权限。具体写法是CREATE OR REPLACE FUNCTION tools.exec_safe_query(...) RETURNS ... LANGUAGE plpgsql SECURITY DEFINER SET search_path tools, pg_catalog AS $$ ... $$;SET search_path tools, pg_catalog是必须的它告诉PostgreSQL只搜索tools模式和系统目录。这一行字能挡住绝大多数基于search_path的攻击。4.2 别把业务数据也净化坏了有一段时间我在清洗函数里加了禁止分号、禁止双减号、禁止--的规则结果被业务方投诉了客户名称里真有XYZ--ABC这种带连字符的分号也出现在产品描述里。后来我才想明白一个道理数据部分只要走参数绑定根本不需要过滤任何字符单引号、分号、双减号都只是字符串内容。只有当你决定用拼接的方式处理字符串时才需要quote_literal兜底。而正确的编码习惯是默认参数绑定只有特殊情况才拼接。如果一上来就写一堆过滤规则你本质上又回到了黑名单的老路。正确的做法是给清洗函数定一个原则只做必要约束长度、类型、控制字符、格式不做关键字拦截。人的姓名、产品描述、地址信息里出现什么字符都是合法的数据库不应该替业务做价值判断。4.3 性能开销regexp大写的误会PL/pgSQL里做正则匹配代价不低如果每个SQL执行都要跑一遍复杂的正则清洗并发稍高就会出现性能下降。我见过一个项目净化函数里写了一个超长的正则用来匹配所有可能危险的关键字组合每个请求动辄多出几毫秒线上报表接口直接慢成狗。净化不是堆正则性能优化可以从三个方向入手能走白名单的走白名单白名单是一个 ANY判断比正则快好几个数量级。数据部分用EXECUTE ... USING绑定不去做字符串级清洗省掉正则开销。如果确实要清洗限制在函数入参阶段处理一次不要在SQL执行循环里反复调用字符串函数。我还习惯把常用的校验函数标记为IMMUTABLE这样PostgreSQL可以在表达式索引和查询规划时直接复用结果减少重复计算。5. 多层防线数据库净化只是兜底不是全部做完上面这些项目的SQL注入面已经收得很窄了但要清楚数据库层的净化函数从来不是第一道防线而是最后一道。我见过有些团队把全部希望寄托在数据库函数上应用层直接不设防这等于把家门的锁都拆了只留保险柜——这不是聪明的做法。真正稳定的体系是分层设防应用层强制参数化查询、ORM约束、输入校验框架。这是成本最低、最有效的入口防线。中间层如果必须拼接SQL只能拼接白名单允许的部分用户输入一律走绑定变量。数据库层PL/pgSQL净化函数负责兜底拦截那些漏过前面防线的危险调用。权限层用角色和REVOKE/GRANT做最小权限控制。业务账号只给需要访问的表和函数权限绝不给public浪投。审计层在净化函数入口记日志记录异常输入。这样就算攻击者尝试了也能在日志里留下痕迹。权限上有个细节容易被忽略函数授权要精确到函数的EXECUTE权限。比如REVOKE ALL ON FUNCTION tools.exec_safe_query(text, text[], text, text) FROM PUBLIC; GRANT EXECUTE ON FUNCTION tools.exec_safe_query(text, text[], text, text) TO app_readonly_role;另外建议开启log_min_duration_statement和错误日志采集配合监控告警。净化函数里RAISE EXCEPTION抛出的异常信息最好统一前缀这样日志查询时可以一条grep抓出所有被拦下的可疑行为。比如RAISE EXCEPTION SECURITY: illegal identifier: %, p_ident方便异常告警系统直接捕获。我个人在多个项目里实践下来的体会是写净化函数这件事难度不在函数本身而在克制二字。少写黑名单、少做自以为聪明的过滤、让PostgreSQL自己的参数绑定机制干活反而是最稳妥的路。数据库层放上一套小而精的净化工具配合完整的权限和审计动态SQL就真的不再是夜里悬着的那把刀了。