一个项目如果写的是“sql测试脚本-未完成”十有八九不是代码写不出来而是写到了一半发现需求比想象中大。我手头就有一份这样的脚本本来只是想着快速校验几个上线前的SQL文件结果越做越往里钻最后变成一个兼顾语法检查、注入风险扫描、回归对比的测试框架。虽然仓库名上还挂着“未完成”但已经跑通的模块在项目里顶了不少事。这篇就把它拆开聊聊做了什么、为什么这么做、哪些地方还没做完以及中途踩进去又能爬出来的坑。这个项目适合谁参考正在给团队做自动化测试尤其是数据库变更脚本、接口联调SQL、批量数据修复SQL的验证场景的人应该能从我这里找到几条能直接抄作业的思路。哪怕你只是一个人维护几个库里面关于如何设计“可重复执行、能报出有效告警”的SQL测试脚本的方法也能帮你省掉几顿加班。1. 项目背景与整体设计思路1.1 这个脚本到底要解决什么问题先说场景。我这边经常要接收开发提交上来的SQL变更文件有的是建表、加索引有的是数据订正还有一部分是复杂报表查询。原来上线前靠人肉过一遍把SQL贴到SSMS里执行一下感觉没问题就发上线单。但实际坑过几次之后就明白了人肉检查有三个靠不住的地方。第一语法正确不代表业务正确。一条SQL能在查询编辑器里跑出结果可不代表它会命中你想操作的数据范围。比如一个UPDATE语句漏了WHERE条件执行前和环境正常一执行就是全表覆盖。第二同一套SQL在不同环境的表现不一样开发库能跑测试库可能因为权限、隔离级别、统计数据差异直接报错或者性能差十几倍。第三注入风险这类问题靠人眼看容易漏。尤其当脚本是动态拼出来的时候肉眼很难追完每一处字符串拼接。所以这个脚本的目标就三个快速对一批SQL文件做静态检查识别高风险写法。能连上目标数据库做动态校验至少保证语法和权限没问题。输出一个统一格式的检查报告能接进发布流程也能丢给开发自己看。这三个目标听起来不大但做下去之后涉及到的模块一个都不少包括文件遍历、SQL解析、数据库连接、执行策略、日志记录和报告渲染。1.2 为什么选脚本化而非纯手工验证很多人觉得就几份SQL文件打开SSMS跑一遍不就行了吗搞什么自动化脚本杀鸡用牛刀。但真到发布窗口那几分钟你就知道手工执行的问题了你今天记得开事务明天可能就忘你把生产库的UPDATE执行完才想起来没看影响行数你为了省事把所有脚本一次性全选执行结果第二个语句挂了前面的事务状态一头雾水。脚本化能解决的是“流程一致性”。同样的检查步骤每次跑出来都是固定结果格式同样的规则对每个文件都生效同样的连接参数不用反复在GUI里配。更关键的是脚本可以留痕跑完留下日志和报告以后审计或复盘的时候能精确知道当时执行了什么、结果如何。从投入产出比看第一次脚本化的成本确实不低但只要SQL提交频率稍微高一点第二周就能回本。而且自动化脚本还能在天没亮的时候自己跑把结果发到群里人只需要处理异常不用守着窗口期。这才是测试脚本最大的价值。1.3 整体架构与模块划分整个项目我最初设计成了五个模块分工很清楚模块职责状态文件采集器扫描指定目录下的SQL文件支持按正则过滤已完成静态分析器不连数据库检查危险关键字和注入特征已完成动态执行器连接目标库按配置执行SQL或获取执行计划已完成报告生成器输出Markdown/HTML格式的检查报告大部分完成定时调度器接入Jenkins/计划任务定时触发未完成这个模块划分最主要的原因是为了隔离风险。静态分析不需要连库普通开发自己就能跑动态执行必须连库所以要有独立的权限配置和执行控制报告生成单独拆开因为同一个检查结果可能需要输出多种格式拆开之后后续扩展方便。文件采集器是最先写的因为不管是静态还是动态检查第一步都是拿到“要测什么”。实际开发中代价最小的模块是报告生成器但价值却很明显。一开始我老盯着分析逻辑后来发现团队里其他人最关心的是报告能不能一眼看出问题。后来我把报告输出排在优先级前面效果立竿见影。2. 核心细节解析与实操要点2.1 静态分析模块危险语句与注入特征扫描静态分析器的目标是不碰数据库先把文件里明显有问题的写法抓出来。这个模块不追求穷尽所有问题因为SQL语义复杂纯静态很难做全面但几个高频风险点一定要能识别。我实现了三类检测逻辑。第一类是危险语句检测。重点抓无WHERE条件的UPDATE/DELETE、TRUNCATE TABLE、DROP TABLE、DBCC命令这类高影响操作。实现思路很简单逐行扫描遇到关键字就记录位置然后看同一语句范围内是否有WHERE子句。判断方法没有用那种重型SQL解析器而是靠分号切分语句再对每段做关键字计数能覆盖绝大多数场景。如果你要更严谨可以考虑引入jsqlparser这个Java库或者sqlparse这个Python库能把AST解析出来再判断准确率更高但成本也更大。第二类是注入特征检测。这里不是要做渗透工具而是帮助测试人员快速发现“可能被外部输入污染”的写法。我维护了一个特征正则库匹配常见的注入模式 OR 11、 OR 11--、拼接双引号的字符串常量、UNION SELECT出现在动态SQL里、EXEC配合字符串变量、sp_executesql的第一个参数不是常量等。每命中一条就会标记风险等级并给出建议。这里要特别说明正则命中不等于一定存在注入漏洞比如业务里确有合法查询包含“OR 11”这种参数所以报告里会区分为“高风险”、“需人工确认”、“提示”三个级别避免误报淹没有效告警。第三类是格式与可维护性检查。这个属于锦上添花但实际使用率很高。检查项包括是否存在SELECT *在正式变更里很多时候是不允许的、是否有超长行、是否混用了Tab和空格、关键SQL是否包含注释。这些都是经验积累因为上线后出问题最多的往往不是语法而是可读性差导致评审没看出问题。动态SQL扫描的伪代码大概长这样import re RISK_PATTERNS [ (re.compile(rEXEC\s*\(.*\, re.I), 动态SQL字符串拼接), (re.compile(rOR\s1\s*\s*1, re.I), 可能的认证绕过注入特征), (re.compile(rUNION\sSELECT, re.I), UNION SELECT出现在非预期位置), (re.compile(r--\s*$, re.M), 行尾注释包裹后续条件), ] def scan_injection(filetext: str): findings [] for lineno, line in enumerate(filetext.splitlines(), 1): for pattern, desc in RISK_PATTERNS: if pattern.search(line): findings.append({line: lineno, detail: desc, level: review}) return findings第一版我塞了好几个正则进去结果把团队逼疯了因为合法SQL也疯狂告警。后来我总结了实操心得注入扫描的正则宁可少而精也不要贪多。真正有效的不是靠正则穷举而是靠“参数化检查”也就是看拼SQL的时候是否强制做了类型转换和参数绑定这个后面动态执行部分会一起做。2.2 文件遍历与执行顺序控制静态扫描文件的时候顺序无所谓一旦进入动态执行阶段文件顺序就变得重要。比如一个目录里有10个SQL文件其中3号文件建了一张临时表5号文件要查询这张表如果按照文件名排序依次执行顺序没问题但如果是通过通配符随机执行或者并发执行大概率会失败。所以文件采集器不只是扫描文件还要支持一个“执行顺序控制”的配置。我定义了一个简单的清单规则[order] 001_create_table.sql 1 002_insert_data.sql 2 003_create_index.sql 3 004_query_report.sql 4未在清单里的文件按文件名升序排列。这样既灵活又能满足大多数场景。另一种可行的方案是读取文件的头部注释约定好-- DEPENDS ON: xxx.sql这样的格式自动构建执行拓扑但投入比较大我目前没有实现只在文档里留了设计。采集器还有一个容易被忽略的点编码识别。Windows环境下来的SQL文件经常是GBK或GB2312编码Python按UTF-8读取直接乱码。我研究了一套很土的方案先用chardet检测编码检测失败就尝试常见编码逐个decode最后兜底用errorsignore。这个细节不写进代码里没人会记得但第一次跑崩之后你就记住了。def read_sql_file(path): raw path.read_bytes() for enc in [utf-8, gbk, gb2312, latin-1]: try: return raw.decode(enc) except UnicodeDecodeError: continue return raw.decode(utf-8, errorsignore)这套读取逻辑单独写成了工具函数因为后续的静态分析、动态执行、报告生成都要读文件统一入口能少很多麻烦。2.3 动态执行器连接数据库与执行策略动态执行器是这个脚本里最容易被低估的部分。很多人以为连上库、然后把SQL字符串丢给数据库执行就行了。实际操作起来要命的问题一堆。连接配置我单独做了一个config.yml里面区分了dev、test、prod三种环境。每种环境包括host、port、database、authentication但权限级别不同测试库可以执行写操作生产库默认只允许只读校验。为了防止误操作我在代码里加了环境确认步骤如果目标环境是prod且包含DROP/TRUNCATE语法脚本会直接拒绝执行并给出警告。执行策略方面我分成了三种模式dry_run模式只做语法解析和执行计划分析不真正执行DML。transaction模式把多个SQL包在事务里出错回滚。batch模式按文件顺序逐条执行每条记录耗时和影响行数遇到错误跳出并保存断点。事务模式是我用得最多的。上线前把整个变更脚本放进一个事务里执行如果中间任何一条出问题直接回滚环境不会被弄脏。批量模式适合不需要回滚的场景比如同步历史数据。这里有一个重要的实现细节如果你用Python的pyodbc连接SQL Server事务控制不能直接使用conn.autocommit False就把所有语句包住。SQL Server的某些DDL语句比如CREATE INDEX可能隐式提交事务。所以最稳妥的方式是把事务模式下的所有语句拼成一个批次利用SQL Server自身的BEGIN TRANSACTION ... COMMIT包裹而不是依赖驱动层事务。伪代码示例import pyodbc def execute_with_transaction(cursor, statements): cursor.execute(SET XACT_ABORT ON) cursor.execute(BEGIN TRANSACTION) try: for stmt in statements: cursor.execute(stmt) cursor.execute(COMMIT) return {status: success} except Exception as e: cursor.execute(ROLLBACK) return {status: failed, error: str(e)}为什么要SET XACT_ABORT ON因为默认情况下某些错误并不算严重错误SQL Server只回滚当前语句事务还会继续执行这会导致部分提交。XACT_ABORT ON能保证任何运行时错误直接回滚整个事务减少脏数据风险。动态执行器里还要考虑SQL超时。默认情况下pyodbc的超时时间可能是0表示永不超时。如果测试环境有个跑不完的查询调度任务会卡死。我把超时参数统一设置成30秒防止单条语句把整个巡检拖死。同时用cursor.description获取结果集结构只记录结果集的行数和前几行数据摘要避免大查询结果把内存打爆。3. 实操过程与核心环节实现3.1 搭建最小可用的执行框架整个脚本我选了Python核心原因有三个正则处理方便、pyodbc连SQL Server成熟、后续接报告模板生态丰富。如果你对别的主语言更熟比如JavaJDBC、Godatabase/sql也完全可以这套设计思想是语言无关的。最小可用框架包括4个文件sql_test_runner/ ├── config.yml ├── runner.py ├── static_scan.py └── report.pyrunner.py是入口负责读取配置、加载SQL文件列表、调用静态分析、进行动态执行、最后生成报告。static_scan.py里放静态分析函数report.py负责生成报告。一个完整的执行调用链路def main(): config load_config(config.yml) files collect_sql_files(config[input_dir]) static_results [] for f in files: static_results.append(scan_file(f)) dynamic_results [] if config[execute][enabled]: conn create_connection(config[target_env]) dynamic_results execute_sql_files(conn, files, modeconfig[execute][mode]) conn.close() report_text render_report(static_results, dynamic_results, config) write_report(report_text, config[output_path])这个框架第一版跑通只需要3个小时后面的复杂逻辑都是在这个骨架上长出来的。我的心得是先把主链路跑通哪怕结果只是打印在控制台也比一开始就追求完美报告要强。3.2 如何把检测规则写进可维护的配置检测规则最容易变成一堆散落的正则和if-else维护起来非常痛苦。后来我把规则抽成JSON配置文件每次加规则不用动代码只动配置。{ sql_injection_patterns: [ { name: auth_bypass_or, regex: \\sOR\\s1\\s*\\s*1, level: high, description: 检测到OR条件恒真疑似绕过登录认证 }, { name: union_select, regex: UNION\\sSELECT, level: high, description: 检测到UNION SELECT可能用于数据越权 }, { name: comment_bypass, regex: --, level: low, description: 行注释可能被注释掉后续安全检查 } ], dangerous_statements: { TRUNCATE TABLE: high, DROP TABLE: high, DROP DATABASE: critical, DBCC SHRINKFILE: medium } }这个做法有多好用团队里后来有安全工程师加入他只需要新增一个JSON片段就能把新的注入模式纳入测试范围。只要保证每次规则变更前跑一遍历史样本看看有没有误报就能稳定维护。规则命中之后报告格式我会写成这样规则名文件行号风险级别说明auth_bypass_orauth_check.sql12high检测到OR条件恒真疑似绕过登录认证union_selectreport.sql45high检测到UNION SELECT可能用于数据越权这个表格在报告里非常醒目开发人员一看到行号和级别马上就能定位。3.3 用执行计划代替真实执行有一类校验是“这条SQL到底能不能走索引”。单纯执行一遍如果数据量小根本看不出问题等到了生产大数据量才爆发。所以我在动态执行器里加了一个模式不真正跑完整个查询而是获取预估执行计划。在SQL Server上获取预估执行计划的命令是SET SHOWPLAN_ALL ON; GO SELECT * FROM your_table WHERE column_a x; GO SET SHOWPLAN_ALL OFF;如果你用pyodbc执行这段返回的第一个结果集就是执行计划明细里面包含StmtText字段。解析StmtText可以判断有没有出现Table Scan、Index Scan、RID Lookup这类低效操作。实操代码def get_query_plan(cursor, sql): cursor.execute(SET SHOWPLAN_ALL ON) rows cursor.execute(sql).fetchall() cursor.execute(SET SHOWPLAN_ALL OFF) for row in rows: text row[0] # StmtText列 if Table Scan in text or RID Lookup in text: return {has_scan: True, plan: text[:500]} return {has_scan: False, plan: }有一说一这个模块判断逻辑比较粗糙它只看有没有“Table Scan”关键字覆盖率有限。更专业的方案是查询DMV比如sys.dm_exec_query_stats里看执行计划或者接入SentinelOne那样做全量采集但这些都属于锦上添花。小团队能在一开始就有这个意识已经很好了。执行计划校验最大的价值在于让开发在提交时就自查而不是等DBA上线前review才返工。我把这个功能做进了CI提交SQL变更时自动触发如果预估计划里有全表扫描CI直接给出警告。这个改动上线之后测试环境慢查询的数量肉眼可见下降。3.4 把报告做成团队看得懂的样子报告输出必须人性化。最开始我输出的是一堆纯文本日志开发反馈“看不懂、不想看”。后来我把报告改成Markdown格式每条问题都有文件、行号、级别、建议。再后来做成HTML因为可以在浏览器里点开而且可以直接发邮件。我的HTML报告结构!DOCTYPE html html headtitleSQL Test Report/title/head body h1SQL Test Report/h1 h2静态扫描结果/h2 table border1 trth规则/thth文件/thth行号/thth级别/thth说明/th/tr !-- 动态生成 -- /table h2动态执行结果/h2 table border1 trth文件/thth状态/thth耗时(ms)/thth影响行数/thth错误信息/th/tr !-- 动态生成 -- /table /body /html看起来技术含量不高但实用。团队里每个人打开邮件都能秒懂绿色是通过红色是失败黄色是告警。相比以前在群里贴截图这个方案专业太多。4. 尚未完成的部分为什么“未完成”4.1 未完成清单与原因分析我的项目名字叫“未完成”不是自谦是真的还有几个模块做得不完整。第一是定时调度器。目前只能手动执行或者通过命令行触发。虽然这也算自动化但没有真正接入Jenkins的构建步骤。按我原来的规划应该做到每次发布时自动拉取变更脚本、自动执行全量检查、自动将报告关联到发布单。这个没做完的主要原因是CI平台的权限和网络策略没有完全打通测试脚本要连的数据库在CI服务器上访问受限。这个问题需要运维侧配合我暂时搁置了。第二是静态分析模块对存储过程、函数这类复杂对象的覆盖。目前文件级的正则扫描能覆盖简单TSQL但对于存储过程内部多层嵌套的动态SQL正则很容易误判。要彻底解决需要引入TSQL解析器比如ANTLR的TSQL语法文件然后做AST层面的分析。这块工程量比较大而且公司内部的存储过程数量有限投入产出比一般所以暂时没做。第三是风险评分体系。现在报告只会列出“有问题/没问题”但决策者不一定清楚“这次变更的风险到底高不高”。我设想过一个综合评分模型根据文件数量、涉及表数量、高风险语句数量、是否包含DDL、执行时长等因子计算一个0-100的风险分。超过80分需要DBA人工复核低于50分可以自动发布。这个模型我已经写了第一版但没有经过大量样本训练还没敢直接用。4.2 剩余模块的技术选型设想调度模块如果继续做我会优先考虑以下方案轻量级用Linux cron或Windows计划任务写一个shell/bat脚本调用runner.py。正规化写一个Jenkins Pipeline步骤将SQL测试作为构建流水线中的一环。Jenkins的Publish HTML Report插件可以直接展示HTML报告。进阶引入Airflow或DolphinScheduler这类数据调度平台适合团队里已经有相关平台的情况。事件触发方式上我倾向于“代码仓库变更触发”也就是开发提交PR时自动触发用Webhook调用runner。这样集成度最高但需要后端支持优先级排在后面。TSQL解析器这块我调研过的路线有两条。第一条是使用Python的sqlparse做基础词法分析虽然它不会解析AST但能对语句进行分割、Token级别识别用在存储过程体扫描场景时比纯正则可控很多。第二条是走Java生态的antlr4 tsql.g4这是一套官方语法文件解析出来的AST非常准确但需要写大量遍历逻辑小脚本场景下性价比不划算。第三是调用SQL Server官方提供的一些系统函数比如sys.dm_exec_describe_first_result_set可以帮你推断查询的返回结构算是一种轻量级的语义校验。风险评分模型我认为后续最有价值。可以先做出规则权重表比如检查项权重说明高风险语句数量30DROP/TRUNCATE/无WHERE的UPDATE涉及核心表数量20根据表名匹配核心表清单是否动态执行20EXEC/sp_executesql预估执行计划扫描20全表扫描/大表索引缺失文件变更数量与体量10单次变更规模过大扣分每个维度打分后按权重求和再加一个“一票否决”清单比如出现DROP DATABASE直接判定100分。评分模型做好了发布流程里就能自动拦截明显有风险的变更。4.3 项目继续推进的优先级建议如果我现在还有时间继续做我的优先级排序是这样的先把调度模块打通。因为这是让脚本真正“自动化”的关键没有调度它仍然是个手动工具。然后做风险评分模型。有了评分才能和发布流程衔接。最后才是存储过程AST解析因为这是最复杂、ROI最低的部分。另外测试脚本本身也需要测试。我在“未完成”期间最大的体会是给SQL测试脚本写单元测试太重要了否则你自己改了一版规则都不知道把之前的什么功能改坏了。现在我的仓库里至少有10个用例是验证静态分析器本身的比如造了几条已知有注入特征的SQL确保它们能被扫出来。5. 踩坑实录SQL测试脚本落地过程中的问题与排查5.1 环境与连接类问题速查表这类问题在自动化脚本中最常见经常让人怀疑是代码写错了其实多半是环境问题。现象可能原因解决办法Cannot open server xxx requested by the login - Client with IP address is not allowedSQL Server未启用远程连接在SQL Server配置管理器中启用TCP/IP并放行防火墙端口Login failed for user sa. Reason: Password did not match连接字符串或账号密码错误核对默认库、用户映射和账号锁定状态The server principal xxx is not able to access the database yyy用户缺少库级别的guest权限执行USE [yyy]; CREATE USER xxx FOR LOGIN xxx; EXEC sp_addrolemember db_datareader, xxx;The OLE DB provider MSDASQL has not been registered32位/64位驱动不匹配确保pyodbc连接串里指定正确的ODBC Driver如DRIVER{ODBC Driver 17 for SQL Server}我在环境问题里最常踩的坑是SQL Server的“ad hoc distributed queries”被阻止。有一次脚本里用到了OPENROWSET去读另一台服务器上的数据本地SSMS能跑但自动化脚本直接报错。后来明白这是SQL Server的Ad Hoc Distributed Queries组件默认关闭了。解决方式是需要打开高级选项EXEC sp_configure show advanced options, 1; RECONFIGURE; EXEC sp_configure ad hoc distributed queries, 1; RECONFIGURE;不过这里要提醒一下开启这个组件有一定安全风险会让服务器更容易被利用来访问外部数据源。如果不是业务硬性要求不建议在自动化脚本中开启。我是因为这个功能被专项审计否过才长记性。5.2 脚本执行本身的诡异坑有些问题是SQL Server特有的“坑”不跑一遍自动化脚本你根本遇不到。第一个坑是sp_executesql与事务的相互作用。我有一段脚本测试事务模式把所有语句拼进一个事务里但其中一个语句是EXEC sp_executesql N...内部又开了隐式事务结果事务提交顺序错乱。后来我明确要求测试脚本里不直接使用事务内的动态SQL或者把动态SQL改成单条独立语句减少不确定性。第二个坑是GETDATE()这类函数的不确定性。回归测试里我要对比两次执行结果是否一致但SQL里如果包含GETDATE()、NEWID()、RAND()这类不确定性函数自然会不一致。后来我在回归对比时会对结果集做一次序列化然后忽略掉这些函数导致的差异只对比业务字段。具体做法是在测试SQL外面包一层SELECT把不确定字段先转换成字符串“忽略此列”再对比。第三是字符集和排序规则的问题。SQL Server默认的排序规则可能是Chinese_PRC_CI_AS对中文大小写不敏感但到测试环境如果排序规则不同某些字符串查询行为会不一样。我第一次跑脚本时开发库查出来10行测试库查出来12行排查了半天最后发现是Collate不同。建议在配置阶段就统一所有环境的排序规则或者在关键SQL里显式加上COLLATE DATABASE_DEFAULT。5.3 自动化与脚本化特有的坑作为测试脚本它本身也容易引入新问题。第一个是死锁。当多个测试脚本同时连接数据库互相等待锁资源。自动化巡检如果在业务高峰期跑特别容易造成阻塞。我的对策是避开业务高峰期并且把巡检会话设置成READ UNCOMMITTED脏读保证不阻塞业务。在SQL Server里可以通过在连接串里加IsolationLevelReadUncommitted或者在脚本开头执行SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED。第二个坑是日志文件膨胀。测试脚本如果频繁插入大量数据即使最终回滚也可能导致日志文件瞬间暴涨。尤其是用事务模式大批量插入时日志会线性增长。这个问题我在压测场景里遇到过那个服务器的日志盘直接写满。后来我给执行器加了一个“最大回滚大小”检查如果预估影响行数超过阈值直接跳过执行。第三个是隐藏的账号权限问题。自动化脚本最好用专门的测试账号不要用开发或DBA的超管账号。这样一旦脚本本身被攻击或者执行了恶意SQL危害范围可控。我在设计连接配置时就强制要求生产环境不允许使用sa或sysadmin权限账号。这一点刚开始团队有意见觉得变麻烦了但等真出过一两次问题之后大家就都理解了。写在最后的实战心得我这段时间最大的感受是测试脚本这东西核心不是写代码而是定流程。代码只是一个载体把“怎么检查SQL”的经验固化下来。写的过程中你会慢慢发现很多以前靠人肉保证的事情其实都能变成规则。规则多了流程就硬了流程硬了发布的时候心就稳了。如果你也想搞一个类似的sql测试脚本我的建议是别从零开始追求完美先把最简单的文件扫描和关键字检测跑通哪怕只检查一个“无WHERE条件的UPDATE”也好。用起来再迭代比憋大招有用得多。项目虽然写着“未完成”但它已经在帮我干活了这比一个“完成但没人用”的系统强一百倍。