网硕互联帮助中心/* ================================================================================
SQL Server 2025 — 动态 SQL 构建：技术、安全与最佳实践
Dynamic Query Building in SQL: Techniques, Security, and Best Practices
——————————————————————————–
主题：用参数化查询、存储过程、运行时过滤器与注入防护，安全地构建动态 SQL
行业：国际化珠宝行业 ERP / CRM / HR（七国：CN HK US JP SG FR AE）
目标：SQL Server 2025 (17.0.1135.8) Enterprise Edition
场景：万亿行数据量级 + 高并发 OLTP/OLAP 混合
══════════════════════════════════════════════════════════════════════════════
【本脚本的九条核心结论】（先读这里，再读代码）
══════════════════════════════════════════════════════════════════════════════
1. 动态 SQL 本身不危险，「拼接」才危险。危险的不是 EXEC()，是把用户输入
当成 SQL 代码拼进去。参数化后，动态 SQL 与静态 SQL 的安全性等价。
2. EXEC(@sql) 不接受参数 → 只能拼接 → 这是注入的第一入口。
sp_executesql 接受参数 → 值走参数通道，永远不进解析器。
★ 结论：能用 sp_executesql 就不要用 EXEC。
3. 但 sp_executesql 只能参数化【值】，参数化不了【标识符】。
表名/列名/ORDER BY/方向 必须走【白名单映射】，绝不能直接拼。
4. QUOTENAME 是标识符转义的正确工具，且必须指定第二个参数（引号类型）。
只写 QUOTENAME(@x) 默认用 [] 而不管内容是 ' 还是 "，容易漏。
5. 运行时过滤器（"任意组合的查询条件"）有两条路：
静态 SQL + (col = @p OR @p IS NULL) —— 计划稳定，索引可能用不满
动态 SQL + 只拼出现过的条件 —— 计划最优，但要参数化 + 白名单
★ 万亿级高并发下推荐后者，配合 sp_executesql 复用计划。
6. 计划缓存的粒度是【完整语句字符串】。动态 SQL 拼接时只要有一个字面量
不同，就是一条全新的语句 → 缓存爆炸（Adhoc 计划泛滥）。
★ 对策：参数化 + 规范化 + 必要时 optimize for ad hoc workloads。
7. 注入防御是纵深体系，不是单点：参数化（第一层）+ 白名单（第二层）+
最小权限（第三层）+ 模块签名/EXECUTE AS（第四层）+ 监控审计（第五层）。
8. 权限上，动态 SQL 在【调用者】上下文执行。EXECUTE AS OWNER + 模块签名
可以做到"只给 EXECUTE 权限，不给基表权限"——这是权限最小化的关键。
9. 万亿级场景的动态 SQL 还得解决：分区裁剪（拼分区键）、键集分页（避免
OFFSET 深翻页）、以及"动态 SQL 永远不会被自动参数化"带来的缓存压力。
══════════════════════════════════════════════════════════════════════════════
【最容易踩的 11 个坑】（本机 SQL Server 2025 实测复现）
══════════════════════════════════════════════════════════════════════════════
坑 1 EXEC 拼接字符串时，日期/字符串必须自带引号：EXEC('…=''' + @d + '''')
漏了引号 → 语法错误 156 或更糟：静默执行了错误的语句。
坑 2 sp_executesql 的参数名必须带 @，且顺序/类型要与 SQL 文本一致。
常见错误：把 N'@c' 写成 N'c' → 报 214（过程需要参数）。
坑 3 sp_executesql 的 SQL 文本必须是 nvarchar（N'…'）。写成 varchar
会让中文字符串变成 '?'，且可能报 214 类型不匹配。
坑 4 QUOTENAME 只防标识符，不防值。QUOTENAME('a'';DROP–') 返回的仍是
带引号的原串，若拿它去拼【值】位置毫无意义，还会造成错误的安全感。
坑 5 ORDER BY 里拼列名，即使 QUOTENAME 了，也能通过注入已有的合法列名
做"盲注排序"（例如探测某列是否存在）。必须白名单。
坑 6 SELECT … FROM ' + QUOTENAME(@tbl) 看起来安全，但如果 @tbl 来自
用户且未做白名单，SQL Server 的【延迟名称解析】会让你在运行期才
发现表不存在（甚至触发权限探测）。必须白名单。
坑 7 动态 SQL 默认在【调用者】权限下运行，权限不继承存储过程的。
想让普通用户查基表，需要 EXECUTE AS OWNER 或模块签名，否则报 229。
坑 8 参数化后【参数嗅探】依然存在：动态 SQL 用 sp_executesql 传参，
同样会按首次执行的值生成计划。大数据量倾斜列上要 OPTION(RECOMPILE)
或 OPTIMIZE FOR。
坑 9 ★ 本机实测：REGEXP_LIKE 是【谓词】不是一个标量函数。
它只能在 WHERE / CASE WHEN / IF / EXISTS 等布尔上下文里用；
放进 SELECT 列表、SET @v =、或派生表投影里会报
「消息 156 关键字 'REGEXP_LIKE' 附近有语法错误」。
而 REGEXP_COUNT / REGEXP_REPLACE / REGEXP_SUBSTR 返回标量，
在任何位置都能用。用它做输入合法性校验时务必注意位置。
坑 10 ★ 本机实测：RowCount 在 SQL Server 2025 里是【保留关键字】。
作为列名/别名必须写成 [RowCount]，否则报
「消息 156 关键字 'RowCount' 附近有语法错误」。同类还有 LineNo、
Rule（CASCADE/RULE 相关）等新增保留字，命名前先查一遍。
坑 11 ★ 本机实测的三个 sp_executesql 参数错误码，务必分清：
· 声明串漏写 @（如 'cc char(2)'）→ 报 102（语法错误），不是 214
· 实参多传一个不在声明串里的命名参数 → 报 8144（指定了过多的参数）
· 把外层变量名写进"实参字符串"里 → 报 134（变量名必须唯一）
· 同一批次重复 DECLARE 同一变量名 → 也报 134（变量名必须唯一）
· SQL 文本里用了未声明的 @param → 报 137（必须声明标量变量）
══════════════════════════════════════════════════════════════════════════════
【执行方式】
sqlcmd -S "localhost\\SQL2025" -E -d JewelryAnalytics -l 60 -t 900 ^
-f "i:65001,o:65001" -y 0 -i SQLServer2025_DynamicSQL_JewelryDemo.sql ^
-o SQLServer2025_DynamicSQL_验证输出.txt
══════════════════════════════════════════════════════════════════════════════
本脚本可重复执行（幂等）：00-B 节负责清理，末尾不遗留脏对象。
================================================================================ */
/* —————————————————————————
【统一 SET 选项】
– QUOTED_IDENTIFIER ON：使用 "" 作为标识符引号，XML 方法与部分语法要求 ON
– ANSI_NULLS ON：与大多数客户端驱动一致，避免 IS NULL 语义歧义
– XACT_ABORT ON：运行时错误回滚整个事务，避免半成品状态
————————————————————————— */
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
SET XACT_ABORT ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
GO
PRINT N'';
PRINT N'###################################################################';
PRINT N'## SQL Server 2025 — 动态 SQL 构建：技术、安全与最佳实践 ##';
PRINT N'## 国际化珠宝行业 ERP / CRM / HR 示例 · 万亿级高并发场景 ##';
PRINT N'###################################################################';
GO
/* ===========================================================================
【00】环境确认 — 先看清脚下的地
===========================================================================
动态 SQL 的行为高度依赖实例配置与数据库选项：
· 兼容级别 → 决定 REGEXP_* 等新函数是否可用
· CTFP / MAXDOP → 决定动态 SQL 并行与否
· Query Store → 动态 SQL 的计划能否被捕获与强制
· 内存 → 决定能否承受计划缓存爆炸
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【00】环境确认';
PRINT N'═══════════════════════════════════════════════════════════════════';
SELECT
项目 = N'实例版本',
值 = CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(40)) + N' / '
+ CAST(SERVERPROPERTY('Edition') AS nvarchar(60));
SELECT
项目 = N'当前数据库 / 兼容级别',
值 = DB_NAME() + N' / ' + CAST(
(SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME())
AS nvarchar(10));
SELECT
项目 = N'物理内存 MB / 可用 MB / SQL 进程 MB',
值 = CAST((SELECT total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS nvarchar(20))
+ N' / ' + CAST((SELECT available_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS nvarchar(20))
+ N' / ' + CAST((SELECT physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) AS nvarchar(20));
SELECT
项目 = N'CTFP / MAXDOP / 最大服务器内存 MB',
值 = CAST((SELECT value_in_use FROM sys.configurations WHERE name='cost threshold for parallelism') AS nvarchar(10))
+ N' / ' + CAST((SELECT value_in_use FROM sys.configurations WHERE name='max degree of parallelism') AS nvarchar(10))
+ N' / ' + CAST((SELECT value_in_use FROM sys.configurations WHERE name='max server memory (MB)') AS nvarchar(20));
SELECT
项目 = N'RCSI / 快照隔离',
值 = CASE WHEN is_read_committed_snapshot_on = 1 THEN N'ON' ELSE N'OFF' END
+ N' / ' + CASE WHEN snapshot_isolation_state_desc = N'ON' THEN N'ON' ELSE N'OFF' END
FROM sys.databases WHERE name = DB_NAME();
SELECT
项目 = N'Query Store 状态 / 只读原因 / 占用 MB',
值 = actual_state_desc
+ N' / ' + CAST(readonly_reason AS nvarchar(20))
+ N' / ' + CAST(current_storage_size_mb AS nvarchar(20))
FROM sys.database_query_store_options;
PRINT N'';
PRINT N' ★ 环境提示：动态 SQL 的计划缓存会占用内存。本机为 32 GB 物理内存';
PRINT N' 共享环境，故本脚本所有演示均使用【小结果集 + 单月/单店范围】，';
PRINT N' 避免触发错误 701（资源池 internal 内存不足）。';
GO
/* ===========================================================================
【00-B】幂等清理 — 让脚本可以反复执行
===========================================================================
清理顺序：先删"被引用者"再删"引用者"，先删索引再删表。
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【00-B】幂等清理（可反复执行）';
PRINT N'═══════════════════════════════════════════════════════════════════';
/* — 存储过程 ————————————————————- */
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_SearchOrders_Static;
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_SearchOrders_Dynamic;
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_SearchOrders_Unsafe;
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_SearchByRuntimeFilters;
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_ReportSafe;
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_SignedRead;
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_PagingKeyset;
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_Demo_Injection;
PRINT N' · 存储过程清理完成';
/* — 函数 —————————————————————– */
DROP FUNCTION IF EXISTS dbo.fn_Dyn_IsSafeIdentifier;
DROP FUNCTION IF EXISTS dbo.fn_Dyn_QuoteValue;
PRINT N' · 函数清理完成';
/* — 视图 —————————————————————– */
DROP VIEW IF EXISTS dbo.vw_Dyn_OrderSummary;
PRINT N' · 视图清理完成';
/* — 普通表（按依赖倒序）————————————————– */
DROP TABLE IF EXISTS dbo.Dyn_AuditLog;
DROP TABLE IF EXISTS dbo.Dyn_SearchLog;
DROP TABLE IF EXISTS dbo.Dyn_PlanSnapshot;
DROP TABLE IF EXISTS dbo.Dyn_ResultCheck;
DROP TABLE IF EXISTS dbo.Dyn_AllowedColumn;
DROP TABLE IF EXISTS dbo.Dyn_AllowedTable;
DROP TABLE IF EXISTS dbo.Dyn_InjectionDemo;
PRINT N' · 数据表清理完成';
/* — 证书 / 对称密钥（模块签名用）—————————————– */
IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = N'Cert_Dyn_SignedReader')
DROP CERTIFICATE Cert_Dyn_SignedReader;
PRINT N' · 证书清理完成';
/* — 测试身份（权限演示用）———————————————– */
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'u_Dyn_TestUser')
DROP USER u_Dyn_TestUser;
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'u_Dyn_CertReader')
DROP USER u_Dyn_CertReader;
PRINT N' · 测试身份清理完成';
GO
/* — 数据库主密钥（第 06 节的模块签名需要）——————————– */
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Dyn@Demo#2025!Key';
PRINT N' · 数据库主密钥已创建（模块签名所需）';
END
ELSE
PRINT N' · 数据库主密钥已存在（跳过）';
GO
/* ===========================================================================
【00-C】建立本节的演示辅助表与索引
===========================================================================
· Dyn_AllowedTable / Dyn_AllowedColumn —— 白名单元数据表（核心安全机制）
· Dyn_SearchLog —— 记录每次动态查询的构造过程
· Dyn_InjectionDemo —— 注入演示靶场（隔离的假表）
=========================================================================== */
PRINT N'';
PRINT N'───────────────────────────────────────────────────────────────────';
PRINT N'【00-C】建立演示辅助对象';
PRINT N'───────────────────────────────────────────────────────────────────';
/* — 白名单：允许被动态拼接的表名 — */
CREATE TABLE dbo.Dyn_AllowedTable
(
TableId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
SchemaName sysname NOT NULL,
TableName sysname NOT NULL,
DisplayName nvarchar(80) NOT NULL,
IsEnabled bit NOT NULL CONSTRAINT DF_Dyn_AT_Enabled DEFAULT (1),
CONSTRAINT UQ_Dyn_AT UNIQUE (SchemaName, TableName)
);
INSERT INTO dbo.Dyn_AllowedTable (SchemaName, TableName, DisplayName) VALUES
(N'erp', N'SalesOrder', N'销售订单'),
(N'erp', N'SalesOrderLine', N'销售订单明细'),
(N'erp', N'Product', N'珠宝商品'),
(N'crm', N'Customer', N'客户'),
(N'crm', N'Interaction', N'客户互动'),
(N'hr', N'Employee', N'员工');
/* — 白名单：允许被动态拼接的列名，按"业务视角"映射 — */
CREATE TABLE dbo.Dyn_AllowedColumn
(
ColumnId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
SchemaName sysname NOT NULL,
TableName sysname NOT NULL,
ColumnName sysname NOT NULL,
DisplayName nvarchar(80) NOT NULL,
DataType sysname NOT NULL,
Sortable bit NOT NULL CONSTRAINT DF_Dyn_AC_Sortable DEFAULT (0),
IsEnabled bit NOT NULL CONSTRAINT DF_Dyn_AC_Enabled DEFAULT (1),
CONSTRAINT UQ_Dyn_AC UNIQUE (SchemaName, TableName, ColumnName)
);
INSERT INTO dbo.Dyn_AllowedColumn (SchemaName, TableName, ColumnName, DisplayName, DataType, Sortable) VALUES
(N'erp', N'SalesOrder', N'OrderNo', N'订单号', N'varchar', 1),
(N'erp', N'SalesOrder', N'OrderDate', N'订单日期', N'datetime2',1),
(N'erp', N'SalesOrder', N'CountryCode', N'国家/地区', N'char', 1),
(N'erp', N'SalesOrder', N'StoreCode', N'门店编码', N'varchar', 1),
(N'erp', N'SalesOrder', N'NetAmountUsd', N'净额(USD)', N'decimal', 1),
(N'erp', N'SalesOrder', N'Status', N'订单状态', N'varchar', 1),
(N'erp', N'SalesOrderLine', N'Quantity', N'数量', N'int', 1),
(N'erp', N'SalesOrderLine', N'LineAmount', N'行金额', N'decimal', 1),
(N'erp', N'Product', N'Sku', N'商品编码', N'varchar', 1),
(N'erp', N'Product', N'ProductName', N'商品名称', N'nvarchar', 1),
(N'erp', N'Product', N'Category', N'品类', N'nvarchar', 1),
(N'erp', N'Product', N'UnitPrice', N'单价', N'decimal', 1),
(N'crm', N'Customer', N'CustomerNo', N'客户编号', N'varchar', 1),
(N'crm', N'Customer', N'CustomerName', N'客户名称', N'nvarchar', 1),
(N'crm', N'Customer', N'Tier', N'客户等级', N'varchar', 1),
(N'crm', N'Customer', N'RegisterDate', N'注册日期', N'date', 1),
(N'hr', N'Employee', N'EmployeeNo', N'工号', N'varchar', 1),
(N'hr', N'Employee', N'FullName', N'姓名', N'nvarchar', 1),
(N'hr', N'Employee', N'Department', N'部门', N'nvarchar', 1),
(N'hr', N'Employee', N'HireDate', N'入职日期', N'date', 1);
/* — 动态查询审计表 — */
CREATE TABLE dbo.Dyn_SearchLog
(
LogId bigint NOT NULL IDENTITY(1,1) PRIMARY KEY,
LoggedAt datetime2(3) NOT NULL CONSTRAINT DF_Dyn_SL_At DEFAULT (SYSDATETIME()),
CallerSid varbinary(85) NOT NULL CONSTRAINT DF_Dyn_SL_Sid DEFAULT (SUSER_SID()),
ProcName sysname NULL,
FilterJson nvarchar(1000) NULL,
SqlPreview nvarchar(1000) NULL,
ParamCount int NOT NULL CONSTRAINT DF_Dyn_SL_PC DEFAULT (0),
ElapsedUs bigint NULL,
/* ★ 注意：RowCount 在 SQL Server 2025 里是【保留关键字】（156），
作为裸列名会报「关键字 'RowCount' 附近有语法错误」，必须加 []。 */
[RowCount] bigint NULL,
Strategy varchar(20) NOT NULL CONSTRAINT DF_Dyn_SL_St DEFAULT ('dynamic')
);
CREATE INDEX IX_Dyn_SearchLog_At ON dbo.Dyn_SearchLog (LoggedAt DESC);
/* — 注入演示靶场（刻意隔离的假表，绝不可指向真实业务表）— */
CREATE TABLE dbo.Dyn_InjectionDemo
(
RowId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
Sku varchar(20) NOT NULL,
ItemName nvarchar(80) NOT NULL,
Price decimal(18,2) NOT NULL,
IsSecret bit NOT NULL CONSTRAINT DF_Dyn_ID_Secret DEFAULT (0)
);
INSERT INTO dbo.Dyn_InjectionDemo (Sku, ItemName, Price, IsSecret) VALUES
(N'SKU-0001', N'足金项链 18K', 12800.00, 0),
(N'SKU-0002', N'钻石戒指 0.5ct', 45600.00, 0),
(N'SKU-0003', N'翡翠手镯 A货', 98000.00, 0),
(N'SKU-0004', N'内部成本核价单', 3200.00, 1);
/* — 结果校验表（供自验证节写入/校验）— */
CREATE TABLE dbo.Dyn_ResultCheck
(
CheckKey varchar(40) NOT NULL PRIMARY KEY,
CheckValue nvarchar(200) NOT NULL,
CheckedAt datetime2(3) NOT NULL CONSTRAINT DF_Dyn_RC_At DEFAULT (SYSDATETIME())
);
SELECT 对象 = N'dbo.Dyn_AllowedTable', 行数 = COUNT(*) FROM dbo.Dyn_AllowedTable
UNION ALL SELECT N'dbo.Dyn_AllowedColumn', COUNT(*) FROM dbo.Dyn_AllowedColumn
UNION ALL SELECT N'dbo.Dyn_InjectionDemo', COUNT(*) FROM dbo.Dyn_InjectionDemo
UNION ALL SELECT N'dbo.Dyn_SearchLog', COUNT(*) FROM dbo.Dyn_SearchLog
UNION ALL SELECT N'dbo.Dyn_ResultCheck', COUNT(*) FROM dbo.Dyn_ResultCheck;
PRINT N' · 辅助对象建立完成（白名单 6 表 / 20 列）';
GO
/* ===========================================================================
【01】动态 SQL 的两种形态 & SQL 注入的解剖
===========================================================================
1.1 EXEC() 与 sp_executesql 的本质区别
这是理解一切安全问题的基础。
EXEC(@sql) sp_executesql N'…', N'@p', @v
───────────────────────── ─────────────────────────────────
参数只能靠字符串拼接 参数走独立的参数通道
值进入解析器 = 可被当代码执行 值永远是值，不进入解析器
每次拼接都是新语句 → 缓存爆炸 同结构语句复用同一计划
不做类型检查 参数有明确类型，可做类型检查
★ 唯一优势：SQL 文本长度无限制 ★ 支持任意 SQL 片段（含 DDL）
1.2 为什么参数化能挡住注入？
因为 SQL 的生命周期分【解析/编译】和【执行】两个阶段。
参数化把"值"推迟到执行阶段才绑定，编译阶段根本看不到它，
所以注入的内容永远只是数据，不可能变成语法。
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【01】动态 SQL 的两种形态';
PRINT N'═══════════════════════════════════════════════════════════════════';
/* —————————————————————————
1.1 最基础的动态查询：EXEC 拼接（仅作对比，不推荐）
————————————————————————— */
PRINT N'';
PRINT N'── 1.1 EXEC 拼接式（可读，但不安全）───────────────────────────';
DECLARE @country char(2) = N'HK';
DECLARE @minAmt decimal(18,2) = 5000.00;
DECLARE @sqlEx nvarchar(4000) =
N'SELECT TOP 5 OrderNo, OrderDate, CountryCode, NetAmountUsd '
+ N'FROM erp.SalesOrder '
+ N'WHERE CountryCode = ''' + @country + N''' '
+ N' AND NetAmountUsd >= ' + CAST(@minAmt AS nvarchar(20)) + N' '
+ N'ORDER BY NetAmountUsd DESC;';
PRINT N' 拼接出来的 SQL 文本：';
PRINT N' ' + @sqlEx;
EXEC (@sqlEx);
PRINT N' ★ 注意两个坑：字符串要自带引号（''''），数字要 CAST 成字符串。';
PRINT N' ★ 更致命的是：@country 的内容【直接进入了 SQL 语法结构】。';
/* —————————————————————————
1.2 同样的查询，参数化写法
————————————————————————— */
PRINT N'';
PRINT N'── 1.2 参数化写法（推荐，且更简洁）───────────────────────────';
DECLARE @sqlPs nvarchar(4000) =
N'SELECT TOP 5 OrderNo, OrderDate, CountryCode, NetAmountUsd '
+ N'FROM erp.SalesOrder '
+ N'WHERE CountryCode = @pCountry '
+ N' AND NetAmountUsd >= @pMinAmt '
+ N'ORDER BY NetAmountUsd DESC;';
PRINT N' 参数化 SQL 文本（无任何拼接痕迹）：';
PRINT N' ' + @sqlPs;
EXEC sys.sp_executesql
@sqlPs,
N'@pCountry char(2), @pMinAmt decimal(18,2)',
@pCountry = @country,
@pMinAmt = @minAmt;
PRINT N' ★ SQL 文本是【编译期常量】，值在运行期才绑定 —— 注入无门。';
PRINT N' ★ 且语句文本固定 → 计划可复用（详见第 09 节）。';
/* —————————————————————————
1.3 ★ 注入解剖（隔离靶场）：让攻击真实发生一次，才懂怎么防
—————————————————————————
靶场表 dbo.Dyn_InjectionDemo 只有 4 行，是【刻意隔离】的假数据。
所有演示均在靶场内进行，绝不触及真实业务表。
————————————————————————— */
PRINT N'';
PRINT N'── 1.3 SQL 注入解剖（隔离靶场 dbo.Dyn_InjectionDemo）────────';
PRINT N' 靶场数据：';
SELECT RowId, Sku, ItemName, Price, IsSecret FROM dbo.Dyn_InjectionDemo ORDER BY RowId;
/* — 攻击 1：经典 OR 1=1 绕过条件 — */
PRINT N'';
PRINT N' ▸ 攻击 1：OR 1=1 —— 绕过 WHERE 条件，拖走全表';
DECLARE @userInput1 nvarchar(100) = N'SKU-0001'' OR ''1''=''1';
DECLARE @badSql1 nvarchar(1000) =
N'SELECT RowId, Sku, ItemName, Price FROM dbo.Dyn_InjectionDemo WHERE Sku = '''
+ @userInput1 + N''';';
PRINT N' 恶意输入 = ' + @userInput1;
PRINT N' 生成 SQL = ' + @badSql1;
EXEC (@badSql1);
PRINT N' ★ 攻击者没猜密码、没改权限，只是让 WHERE 恒真 —— 条件被"逻辑短路"了。';
/* — 攻击 2：UNION 注入，窃取本不该看的列 — */
PRINT N'';
PRINT N' ▸ 攻击 2：UNION SELECT —— 把隐藏列 IsSecret 拼进结果集';
/* 恶意输入以 SKU 收尾，并用 — 吃掉拼接进来的那个收尾单引号 */
DECLARE @userInput2 nvarchar(200) =
N'x'' UNION ALL SELECT RowId, Sku, ItemName, CAST(IsSecret AS decimal(18,2)) '
+ N'FROM dbo.Dyn_InjectionDemo WHERE IsSecret = 1 –';
DECLARE @badSql2 nvarchar(2000) =
N'SELECT RowId, Sku, ItemName, Price FROM dbo.Dyn_InjectionDemo WHERE Sku = '''
+ @userInput2 + N''';';
PRINT N' 生成 SQL = ' + @badSql2;
EXEC (@badSql2);
PRINT N' ★ 注意结尾的 — ，它注释掉原 SQL 剩下的引号，让拼接"合拢"。';
PRINT N' ★ UNION 要求列数与类型匹配，攻击者用 CAST 把 bit 转成 decimal 对齐。';
/* — 攻击 3：堆叠注入（多语句）— */
PRINT N'';
PRINT N' ▸ 攻击 3：堆叠注入 —— 用分号追加第二条语句';
DECLARE @userInput3 nvarchar(100) = N'SKU-0001''; SELECT ''已被堆叠注入'' AS 警告; –';
DECLARE @badSql3 nvarchar(2000) =
N'SELECT RowId, Sku FROM dbo.Dyn_InjectionDemo WHERE Sku = '''
+ @userInput3 + N''';';
PRINT N' 生成 SQL = ' + @badSql3;
EXEC (@badSql3);
PRINT N' ★ EXEC / sp_executesql 都允许批内多语句 —— 但参数化后这招彻底失效。';
/* — 1.4 同一批恶意输入，参数化后是什么结果？— */
PRINT N'';
PRINT N'── 1.4 同样的恶意输入，参数化后 → 变成无害的"字符串字面量"───';
DECLARE @sameInput nvarchar(100) = N'SKU-0001'' OR ''1''=''1';
PRINT N' 输入仍是：' + @sameInput;
DECLARE @result nvarchar(200);
SET @result =
(SELECT TOP 1 Sku FROM dbo.Dyn_InjectionDemo WHERE Sku = @sameInput);
PRINT N' 参数化查询结果：' + ISNULL(@result, N'（0 行 —— 没有 SKU 叫这个名字）');
IF @result IS NULL
PRINT N' ★ 成功挡住！因为 @sameInput 被当成一整个【值】去比较，';
ELSE
PRINT N' ★ 意外匹配，请检查数据。';
PRINT N' 而不是被拆成 "SKU-0001 OR 1=1" 两个语义片段。';
/* — 1.5 攻击面小结 — */
PRINT N'';
PRINT N'── 1.5 结论：拼接 vs 参数化 的对照 ───────────────────────────';
SELECT
环节 = 步骤,
EXEC拼接 = 拼接方式,
sp_executesql参数化 = 参数化方式,
注入能否成功 = 结果
FROM (VALUES
(N'1. 值进入解析器', N'是（当代码解析）', N'否（当数据处理）', N'拼接=必中 / 参数化=免疫'),
(N'2. OR 1=1 绕过', N'绕过成功', N'视为普通字符串', N'拼接=危险 / 参数化=安全'),
(N'3. UNION 窃密', N'可窃取任意列', N'视为普通字符串', N'拼接=危险 / 参数化=安全'),
(N'4. 堆叠多语句', N'可执行任意语句', N'视为普通字符串', N'拼接=危险 / 参数化=安全'),
(N'5. 计划复用', N'每条新串=新计划', N'同结构复用计划', N'参数化=更省内存')
) v(步骤, 拼接方式, 参数化方式, 结果);
GO
/* ===========================================================================
【02】四种执行方式对照 —— EXEC / sp_executesql / 静态SQL / 存储过程
===========================================================================
本节用同一份业务需求（"按国家/地区 + 金额区间查珠宝订单"）写出四种实现，
然后从【安全性 / 计划复用 / 缓存开销 / 权限 / 可维护性】五个维度打分。
同时揭示两个关键事实：
A. SELECT * FROM ' + @table → 延迟名称解析，运行期才报错（坑 6）
B. 动态 SQL 永远不会被"自动参数化"，即使 SQL Server 2025 的开销阈值
自动参数化也管不到 EXEC 里的字符串
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【02】四种执行方式对照';
PRINT N'═══════════════════════════════════════════════════════════════════';
/* —————————————————————————
2.1 方式一：EXEC 拼接（最差实践，仅作反面教材）
————————————————————————— */
PRINT N'';
PRINT N'── 2.1 方式一：EXEC 拼接 ────────────────────────────────────';
DECLARE @c1 char(2) = N'US';
DECLARE @m1 decimal(18,2) = 10000;
DECLARE @s1 nvarchar(4000) =
N'SELECT COUNT(*) AS 订单数, CAST(SUM(NetAmountUsd) AS decimal(18,2)) AS 合计USD '
+ N'FROM erp.SalesOrder WHERE CountryCode = ''' + @c1 + N''' AND NetAmountUsd >= '
+ CAST(@m1 AS nvarchar(20)) + N';';
EXEC (@s1);
PRINT N' ✗ 值直接进解析器：@c1 若含引号即可注入';
PRINT N' ✗ ''US'' 与 10000 都成了字面量 → 换个数就是一条全新的缓存语句';
PRINT N' ✗ 无类型检查：客户端传个乱串也在运行期才炸';
/* —————————————————————————
2.2 方式二：sp_executesql 参数化（动态 SQL 的最佳实践）
————————————————————————— */
PRINT N'';
PRINT N'── 2.2 方式二：sp_executesql 参数化 ──────────────────────────';
DECLARE @s2 nvarchar(4000) =
N'SELECT COUNT(*) AS 订单数, CAST(SUM(NetAmountUsd) AS decimal(18,2)) AS 合计USD '
+ N'FROM erp.SalesOrder WHERE CountryCode = @c AND NetAmountUsd >= @m;';
EXEC sys.sp_executesql @s2, N'@c char(2), @m decimal(18,2)', @c = @c1, @m = @m1;
PRINT N' ✓ 值走参数通道，永不进解析器';
PRINT N' ✓ SQL 文本固定 → 不同参数复用同一计划';
PRINT N' ✓ 参数有明确类型，编译期即可报类型错误';
/* — 证明计划复用：连续三次不同参数，观察缓存中的语句数 — */
PRINT N'';
PRINT N' ▸ 验证计划复用：同一段 SQL 文本用 3 组不同参数执行';
EXEC sys.sp_executesql @s2, N'@c char(2), @m decimal(18,2)', @c = N'CN', @m = 20000;
EXEC sys.sp_executesql @s2, N'@c char(2), @m decimal(18,2)', @c = N'JP', @m = 30000;
EXEC sys.sp_executesql @s2, N'@c char(2), @m decimal(18,2)', @c = N'SG', @m = 15000;
SELECT
指标 = N'缓存中匹配该 SQL 文本的条目数',
值 = CAST(COUNT(*) AS nvarchar(10)),
解读 = CASE WHEN COUNT(*) <= 2 THEN N'✓ 多组参数共用少量计划（复用成功）'
ELSE N'⚠ 条目偏多，检查是否有字面量拼接' END
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE t.text LIKE N'%FROM erp.SalesOrder WHERE CountryCode = @c%';
/* — 对比：EXEC 拼接的三种金额 → 三条独立语句 — */
PRINT N'';
PRINT N' ▸ 反证：EXEC 用 3 组不同金额拼接 → 3 条独立语句，3 份计划';
DECLARE @vals TABLE (i int IDENTITY(1,1), v decimal(18,2));
INSERT INTO @vals (v) VALUES (11000), (12000), (13000);
DECLARE @i int = 1, @n int = (SELECT COUNT(*) FROM @vals);
WHILE @i <= @n
BEGIN
DECLARE @vv decimal(18,2) = (SELECT v FROM @vals WHERE i = @i);
DECLARE @sx nvarchar(2000) =
N'SELECT COUNT(*) AS 订单数 FROM erp.SalesOrder WHERE CountryCode = ''US'' AND NetAmountUsd >= '
+ CAST(@vv AS nvarchar(20)) + N';';
EXEC (@sx);
SET @i += 1;
END
SELECT
指标 = N'EXEC 拼接产生的缓存条目数（同一模式）',
值 = CAST(COUNT(*) AS nvarchar(10)),
解读 = CASE WHEN COUNT(*) >= 3 THEN N'✗ 每个不同字面量 = 一条新语句（缓存膨胀）'
ELSE N'（样本不足，可能已被内存压力淘汰）' END
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE t.text LIKE N'%FROM erp.SalesOrder WHERE CountryCode = ''US'' AND NetAmountUsd >=%';
/* —————————————————————————
2.3 方式三：静态 SQL（无条件时的最优解）
————————————————————————— */
PRINT N'';
PRINT N'── 2.3 方式三：静态 SQL（能用就用）──────────────────────────';
SELECT COUNT(*) AS 订单数, CAST(SUM(NetAmountUsd) AS decimal(18,2)) AS 合计USD
FROM erp.SalesOrder WHERE CountryCode = 'US' AND NetAmountUsd >= 10000;
PRINT N' ✓ 编译期完全可见 → 优化器可做最好估计';
PRINT N' ✓ 自动参数化（simple/forced）有机会命中';
PRINT N' ✓ 无注入面，零维护成本';
PRINT N' ✗ 缺点：条件组合是有限的，无法应对"任意组合动态筛选"';
/* —————————————————————————
2.4 方式四：存储过程（安全与计划的最终答案）
————————————————————————— */
PRINT N'';
PRINT N'── 2.4 方式四：存储过程 ─────────────────────────────────────';
PRINT N' 创建一个参数化的存储过程，内部仍用 sp_executesql；';
PRINT N' 但 SQL 文本完全由【代码内常量】拼成，不接受任何外部片段。';
EXEC(N'
CREATE PROCEDURE dbo.usp_Dyn_SearchOrders_Static
@CountryCode char(2) = NULL,
@MinAmount decimal(18,2) = NULL,
@TopN int = 10
AS
BEGIN
SET NOCOUNT ON;
/* 静态 SQL + 可选参数模式：计划稳定，但索引可能用不满 */
SELECT TOP (@TopN)
OrderNo, OrderDate, CountryCode, StoreCode,
NetAmountUsd, Status
FROM erp.SalesOrder
WHERE (@CountryCode IS NULL OR CountryCode = @CountryCode)
AND (@MinAmount IS NULL OR NetAmountUsd >= @MinAmount)
ORDER BY NetAmountUsd DESC;
END
');
PRINT N' · dbo.usp_Dyn_SearchOrders_Static 创建完成';
GO
PRINT N' ▸ 调用（只传国家/地区）：';
EXEC dbo.usp_Dyn_SearchOrders_Static @CountryCode = N'HK', @TopN = 5;
PRINT N' ▸ 调用（两个条件都传）：';
EXEC dbo.usp_Dyn_SearchOrders_Static @CountryCode = N'JP', @MinAmount = 20000, @TopN = 5;
PRINT N' ▸ 调用（都不传 → 取全量 Top 5）：';
EXEC dbo.usp_Dyn_SearchOrders_Static @TopN = 5;
GO
/* —————————————————————————
2.5 五维打分总表
————————————————————————— */
PRINT N'';
PRINT N'── 2.5 五种方式对照总表 ─────────────────────────────────────';
SELECT
方式 = 方式,
安全性 = 安全,
计划复用 = 复用,
注入面 = 注入面,
适用场景 = 场景
FROM (VALUES
(N'EXEC 拼接', N'✗ 低（值进解析器）', N'✗ 差（每条新串）', N'大', N'仅限内部常量，绝不接外部输入'),
(N'sp_executesql', N'✓ 高（参数化）', N'✓ 好（同文本复用）', N'小', N'动态 SQL 首选：拼结构、传参数'),
(N'静态 SQL', N'✓ 最高', N'✓ 好（自动参数化）', N'无', N'条件固定时最优'),
(N'存储过程', N'✓ 高（可签名）', N'✓ 好 + 可强制计划', N'小', N'生产核心：权限收敛 + 计划稳定'),
(N'视图/函数', N'✓ 高', N'✓ 好', N'无', N'固定口径复用，注意参数化陷阱')
) v(方式, 安全, 复用, 注入面, 场景);
GO
/* ===========================================================================
【02-B】动态 SQL 与"自动参数化"的边界（一个常被误解的点）
===========================================================================
SQL Server 有【简单参数化】和【强制参数化】两种自动参数化机制，
但它们只作用于【直接提交的静态 SQL 批】。
包在 EXEC() / sp_executesql 字符串里的 SQL，
如果自己没写参数，就不会被自动参数化 —— 字面量就是字面量。
实测：同一句静态 SQL 连执行两次（仅字面量不同），观察是否被参数化。
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【02-B】自动参数化的边界';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N' 说明：本库当前是否开启强制参数化：';
SELECT
项目 = N'数据库参数化选项',
值 = N'is_parameterization_forced = '
+ CAST(is_parameterization_forced AS nvarchar(5))
FROM sys.databases WHERE name = DB_NAME();
PRINT N' ★ 即使开启强制参数化，EXEC(''…'') 内的字面量也不受其管辖 ——';
PRINT N' 这就是为什么动态 SQL 必须【自己】参数化。';
GO
/* ===========================================================================
【03】sp_executesql 深入 —— 参数化的正确姿势与全部陷阱
===========================================================================
3.1 参数声明串的写法与常见错误（坑 2 / 坑 3）
3.2 输出参数：把结果带回调用者
3.3 NULL 参数与"可选条件"的三种写法
3.4 类型不匹配的静默行为（比报错更可怕）
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【03】sp_executesql 深入';
PRINT N'═══════════════════════════════════════════════════════════════════';
/* —————————————————————————
3.1 参数声明串：写法与三个必踩的坑
————————————————————————— */
PRINT N'';
PRINT N'── 3.1 参数声明串的三个坑 ────────────────────────────────────';
DECLARE @sql nvarchar(500);
DECLARE @cc char(2) = N'CN';
DECLARE @n int = 3;
/* ✓ 正确写法 */
SET @sql = N'SELECT TOP (@topn) OrderNo, CountryCode, NetAmountUsd '
+ N'FROM erp.SalesOrder WHERE CountryCode = @cc ORDER BY NetAmountUsd DESC;';
PRINT N' ✓ 正确：参数名带 @，类型与顺序一致';
EXEC sys.sp_executesql @sql, N'@cc char(2), @topn int', @cc = @cc, @topn = @n;
/* ✗ 坑 2 复现：参数声明漏掉 @ —— 报 102（解析期就知道错了）*/
PRINT N'';
PRINT N' ✗ 坑 2：声明串写成 ''cc char(2)''（漏 @）会怎样？';
BEGIN TRY
EXEC sys.sp_executesql @sql, N'cc char(2), topn int', @cc = @cc, @topn = @n;
END TRY
BEGIN CATCH
PRINT N' 捕获到错误 ' + CAST(ERROR_NUMBER() AS nvarchar(10))
+ N'：' + ERROR_MESSAGE();
PRINT N' ★ 教训：声明串里的变量名必须带 @。注意这里报的是 102（语法），';
PRINT N' 而不是 214 —— 因为声明串本身要先被解析成变量列表。';
END CATCH
/* ✗ 坑 2 变体：SQL 里用了未声明的参数 */
PRINT N'';
PRINT N' ✗ 坑 2 变体：SQL 文本里用了 @undeclared，但声明串没给';
BEGIN TRY
EXEC sys.sp_executesql
N'SELECT TOP 1 OrderNo FROM erp.SalesOrder WHERE CountryCode = @undeclared;',
N'@cc char(2)', @cc = N'CN';
END TRY
BEGIN CATCH
PRINT N' 捕获到错误 ' + CAST(ERROR_NUMBER() AS nvarchar(10))
+ N'：' + ERROR_MESSAGE();
PRINT N' ★ 教训：SQL 文本里出现的每个 @param，声明串里都必须有。';
END CATCH
/* ✗ 坑 3 复现：SQL 文本用 varchar 承载 */
PRINT N'';
PRINT N' ✗ 坑 3：把 SQL 文本装进 varchar 变量 —— 报 214';
DECLARE @noN varchar(300) = 'SELECT 1 AS x;';
BEGIN TRY
EXEC sys.sp_executesql @noN;
END TRY
BEGIN CATCH
PRINT N' 捕获到错误 ' + CAST(ERROR_NUMBER() AS nvarchar(10))
+ N'：' + ERROR_MESSAGE();
PRINT N' ★ 教训：sp_executesql 的 @statement 参数类型必须是';
PRINT N' nvarchar / nchar / ntext，传 varchar 会直接报 214。';
END CATCH
PRINT N'';
PRINT N' ✓ 正确：用 nvarchar 承载，中文才能安全通过';
DECLARE @withN nvarchar(300) = N'SELECT N''中文测试'' AS 结果_nvarchar;';
EXEC sys.sp_executesql @withN;
PRINT N' ★ 更进一步：SQL 文本内部的中文/Unicode 字面量也要加 N 前缀。';
PRINT N' N''中文'' vs ''中文'' —— 后者在有 Unicode 列比较时会隐式转换，';
PRINT N' 既可能乱码，也会让索引失效（详见 Query Optimization 篇的隐式转换）。';
/* —————————————————————————
3.2 输出参数：把动态 SQL 的结果带回调用者
————————————————————————— */
PRINT N'';
PRINT N'── 3.2 输出参数（OUTPUT）─────────────────────────────────────';
DECLARE @cnt int, @sumAmt decimal(18,2), @avgAmt decimal(18,2);
DECLARE @sqlAgg nvarchar(600) =
N'SELECT @oCnt = COUNT(*), @oSum = SUM(NetAmountUsd), @oAvg = AVG(NetAmountUsd) '
+ N'FROM erp.SalesOrder WHERE CountryCode = @pcc;';
EXEC sys.sp_executesql
@sqlAgg,
N'@pcc char(2), @oCnt int OUTPUT, @oSum decimal(18,2) OUTPUT, @oAvg decimal(18,2) OUTPUT',
@pcc = N'FR',
@oCnt = @cnt OUTPUT,
@oSum = @sumAmt OUTPUT,
@oAvg = @avgAmt OUTPUT;
SELECT 国家 = 'FR', 订单数 = @cnt,
合计USD = @sumAmt, 平均USD = @avgAmt;
PRINT N' ★ 输出参数让动态 SQL 能"返回值"，这是 EXEC 拼接做不到的。';
PRINT N' ★ 注意 OUTPUT 关键字在声明串和调用处【都要写】。';
/* —————————————————————————
3.3 "可选条件"的三种写法对比
————————————————————————— */
PRINT N'';
PRINT N'── 3.3 可选条件三写法 ────────────────────────────────────────';
PRINT N' 需求：国家可选、金额可选、状态可选 —— 任意组合。';
/* 写法 A：OR IS NULL 模式（静态，计划稳定但索引利用率差）*/
PRINT N'';
PRINT N' 写法 A：静态 SQL + (col = @p OR @p IS NULL)';
DECLARE @fC char(2) = N'HK', @fM decimal(18,2) = NULL, @fS varchar(10) = NULL;
DECLARE @sqlA nvarchar(800) =
N'SELECT COUNT(*) AS 命中数 FROM erp.SalesOrder '
+ N'WHERE (@c IS NULL OR CountryCode = @c) '
+ N' AND (@m IS NULL OR NetAmountUsd >= @m) '
+ N' AND (@s IS NULL OR Status = @s);';
EXEC sys.sp_executesql @sqlA, N'@c char(2), @m decimal(18,2), @s varchar(10)',
@c = @fC, @m = @fM, @s = @fS;
PRINT N' ✓ 一条 SQL 应对所有组合 → 只用 1 份计划';
PRINT N' ✗ 优化器无法为"只按国家"最优地选索引（因为 OR 让它无法裁剪谓词）';
/* 写法 B：动态拼接 + 参数化（计划最优，本系列推荐）*/
PRINT N'';
PRINT N' 写法 B：动态拼接【只拼出现过的条件】+ sp_executesql 传参';
/* （复用上面 §3.3 写法 A 已声明的 @fC / @fM / @fS，同一批次不可重复 DECLARE）*/
DECLARE @dyn nvarchar(1000) = N'SELECT COUNT(*) AS 命中数 FROM erp.SalesOrder WHERE 1=1';
DECLARE @decl nvarchar(400) = N'';
IF @fC IS NOT NULL
BEGIN
SET @dyn += N' AND CountryCode = @c';
SET @decl += N'@c char(2),';
END
IF @fM IS NOT NULL
BEGIN
SET @dyn += N' AND NetAmountUsd >= @m';
SET @decl += N'@m decimal(18,2),';
END
IF @fS IS NOT NULL
BEGIN
SET @dyn += N' AND Status = @s';
SET @decl += N'@s varchar(10),';
END
SET @decl = LEFT(@decl, NULLIF(LEN(@decl) – 1, 0)); /* 去尾逗号 */
SET @dyn += N';';
PRINT N' 构造出的 SQL = ' + @dyn;
PRINT N' 参数声明串 = ' + ISNULL(@decl, N'(无)');
/* ★ 关键点：sp_executesql 的实参必须与声明串严格一一对应：
· 多传一个不在声明串里的命名参数 → 8144「指定了过多的参数」
· 在"实参串"里写 @fC 这种外层变量名 → 134（内层作用域看不到）
所以条件数量可变时，有两种正确做法：
做法① 全部用固定参数 + 用 NULL 表示"不启用"（简单，推荐）
做法② 动态拼【位置实参】的 EXEC，让每个值都由本层的变量传入
这里演示做法②：把实参写成 (@a, @b, @c) 形式，用变量承载。 */
DECLARE @a1 nvarchar(30) = CAST(@fC AS nvarchar(30));
DECLARE @a2 nvarchar(30) = CAST(@fM AS nvarchar(30));
DECLARE @a3 nvarchar(30) = CAST(@fS AS nvarchar(30));
/* 本例只有 @c 生效，所以只传一个实参 */
IF @fM IS NULL AND @fS IS NULL
BEGIN
EXEC sp_executesql @dyn, @decl, @c = @a1;
END
ELSE
BEGIN
/* 演示"全部生效"的分支（本例不走，但完整起见保留）*/
EXEC sp_executesql @dyn, @decl, @c = @a1, @m = @a2, @s = @a3;
END
PRINT N' ✓ 只出现过的条件才进 SQL → 优化器看到最干净的谓词';
PRINT N' ✓ 值仍走参数通道 → 零注入面';
PRINT N' ★ 注意 1：动态拼 SQL 文本时，【实参列表】也要跟着条件数变化，';
PRINT N' 否则报 8144；而把外层变量名写进字符串会报 134。';
PRINT N' → 实践中更推荐【固定参数 + NULL 语义】的做法（见下文做法①）。';
PRINT N' ★ 注意 2：不同条件组合 = 不同 SQL 文本 = 不同计划，这是特性不是缺陷。';
/* 做法①：固定参数 + NULL 语义（生产中最常用、最不易出错）*/
PRINT N'';
PRINT N' 做法①（推荐）：SQL 结构固定，用"条件开关+参数"组合'
PRINT N' —— 兼得"计划复用"与"谓词干净"，且实参列表永远固定。';
DECLARE @useC bit = 1, @useM bit = 0, @useS bit = 0;
DECLARE @pC char(2) = N'HK', @pM decimal(18,2) = 0, @pS varchar(10) = N'';
DECLARE @sqlFixed nvarchar(1000) =
N'SELECT COUNT(*) AS 命中数 FROM erp.SalesOrder WHERE 1=1 '
+ N' AND (@useC = 0 OR CountryCode = @c) '
+ N' AND (@useM = 0 OR NetAmountUsd >= @m) '
+ N' AND (@useS = 0 OR Status = @s);';
PRINT N' SQL（结构固定，永不变化）= ';
PRINT N' ' + @sqlFixed;
EXEC sp_executesql @sqlFixed,
N'@useC bit, @useM bit, @useS bit, @c char(2), @m decimal(18,2), @s varchar(10)',
@useC = @useC, @useM = @useM, @useS = @useS,
@c = @pC, @m = @pM, @s = @pS;
PRINT N' ✓ 一条 SQL 文本应对所有组合 → 计划只有 1 份（复用最佳）';
PRINT N' ✓ 实参列表固定 → 不会踩 8144 / 134';
PRINT N' ✓ 用 bit 开关而非 ISNULL(列) → 不破坏 SARGability（可走索引 Seek）';
PRINT N' ★ 这是"静态结构 + 动态开关"的折中，生产中 90% 的"运行时过滤器"';
PRINT N' 需求都可以用它解决。只有当条件组合极多、且计划差异巨大时，';
PRINT N' 才值得上做法②（动态拼接）。';
GO
/* 写法 C：全部传值 + ISNULL 兜底（不推荐，会破坏 SARGability）*/
PRINT N'';
PRINT N' 写法 C：把条件值先兜底再比较（✗ 破坏 SARGability，仅作反例）';
PRINT N' WHERE CountryCode = ISNULL(@c, CountryCode)';
PRINT N' ✗ 列被函数包裹 → 索引失效（详见 Query Optimization 篇）';
PRINT N' ✗ 语义也不对：@c 为 NULL 时退化为"自比较"，永远为真，但少了 NULL 行';
/* —————————————————————————
3.4 类型不匹配的静默行为
————————————————————————— */
PRINT N'';
PRINT N'── 3.4 类型不匹配：静默截断比报错更危险 ──────────────────────';
DECLARE @tooLong nvarchar(10) = N'THIS_IS_A_VERY_LONG_NAME';
DECLARE @sqlT nvarchar(400) = N'SELECT @out AS 传入值;';
DECLARE @echo nvarchar(60);
EXEC sys.sp_executesql @sqlT, N'@out nvarchar(10)', @out = @tooLong;
PRINT N' ★ 参数声明为 nvarchar(10)，传入 22 字符 → 静默截断为前 10 字符，';
PRINT N' 不报错、不警告。校验长度必须在【入参处】自己做，不能靠类型兜底。';
SELECT
检查项 = N'参数声明长度 vs 实际值长度',
声明长度 = 10,
实际长度 = LEN(@tooLong),
结论 = CASE WHEN LEN(@tooLong) > 10
THEN N'✗ 已被静默截断，必须入参校验' ELSE N'✓ 安全' END;
GO
/* ===========================================================================
【04】运行时过滤器 —— 生产级动态查询构建器
===========================================================================
需求（珠宝行业常见）：一个"高级搜索"页面，用户可以任意组合：
· 国家/地区（多选） · 门店（多选）
· 订单日期区间 · 金额区间
· 订单状态（多选） · 客户等级
· 排序字段 + 方向 · 分页
这是动态 SQL 最典型、也最容易写错的场景。
本节实现的 usp_Dyn_SearchOrders_Dynamic 演示全部正确姿势：
✓ 值 → 参数化（sp_executesql）
✓ 多选列表 → STRING_SPLIT + 表变量，不拼进 SQL
✓ 标识符（排序列/方向）→ 白名单映射
✓ 分页 → OFFSET/FETCH 参数化
✓ 全过程写入审计日志
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【04】运行时过滤器：生产级动态查询构建器';
PRINT N'═══════════════════════════════════════════════════════════════════';
/* —————————————————————————
4.1 ★ 错误示范：把"动态"理解成"什么都拼"（仅讲解，不执行）
—————————————————————————
以下是无数生产事故的根源。请仔细看它错在哪：
CREATE PROCEDURE usp_BAD
@SortColumn nvarchar(50), @SortDir nvarchar(4),
@Countries nvarchar(4000), @Keyword nvarchar(100)
AS
DECLARE @sql nvarchar(4000) =
N'SELECT * FROM erp.SalesOrder WHERE 1=1 ';
— ✗ 错 1：把用户输入直接当 SQL 片段拼进去
IF @Keyword <> N''
SET @sql += N' AND OrderNo LIKE ''%' + @Keyword + N'%'' ';
— ✗ 错 2：国家列表直接拼，'CN'',''HK 这种就构造出多值
IF @Countries <> N''
SET @sql += N' AND CountryCode IN (' + @Countries + N') ';
— ✗ 错 3：排序字段和方向直接拼，这里是注入重灾区
SET @sql += N' ORDER BY ' + @SortColumn + N' ' + @SortDir;
EXEC (@sql);
三个致命缺陷：
错 1 → @Keyword 传 ''x'' OR 1=1 –' 即全表泄露
错 2 → @Countries 传 'CN'') OR 1=1 –' 同上
错 3 → @SortColumn 传 'OrderNo; DROP TABLE … –' 后果自负
并且 EXEC 拼接 → 每组参数都是新语句 → 计划缓存爆炸。
下面我们用【正确实现】逐一解决这三个问题。
————————————————————————— */
PRINT N'';
PRINT N'── 4.1 错误示范的三个致命缺陷（文字讲解，不执行）───────';
SELECT
缺陷 = 缺陷,
攻击输入 = 示例输入,
后果 = 后果
FROM (VALUES
(N'错 1：关键字直接拼', N'''x'' OR 1=1 –', N'全表泄露，WHERE 被逻辑短路'),
(N'错 2：IN 列表直接拼', N'''CN'') OR 1=1 –', N'同上，且可 UNION 窃取其它列'),
(N'错 3：排序列直接拼', N'OrderNo; DROP TABLE …', N'可执行任意 SQL，最严重'),
(N'附加：EXEC 而非参数化', N'（任意参数变化）', N'计划缓存爆炸，内存被吃光')
) v(缺陷, 示例输入, 后果);
/* —————————————————————————
4.2 ★ 正确实现：生产级动态查询构建器
————————————————————————— */
PRINT N'';
PRINT N'── 4.2 正确实现：usp_Dyn_SearchOrders_Dynamic ──────────────';
GO
/* ★ CREATE OR ALTER PROCEDURE 必须是批次的第一条语句，前面必须 GO 断开 */
CREATE OR ALTER PROCEDURE dbo.usp_Dyn_SearchOrders_Dynamic
@Countries nvarchar(200) = NULL, — 多选，逗号分隔：'CN,HK,US'
@StoreCodes nvarchar(400) = NULL, — 多选，逗号分隔
@DateFrom date = NULL,
@DateTo date = NULL,
@MinAmount decimal(18,2) = NULL,
@MaxAmount decimal(18,2) = NULL,
@Statuses nvarchar(100) = NULL, — 多选：'Paid,Shipped'
@SortKey nvarchar(40) = N'OrderDate', — ★ 逻辑键，非物理列名
@SortDir nvarchar(4) = N'DESC', — ★ ASC / DESC
@PageNo int = 1,
@PageSize int = 20
AS
BEGIN
SET NOCOUNT ON;
/* —- 入参防御：分页边界 —- */
SET @PageNo = ISNULL(NULLIF(@PageNo, 0), 1);
SET @PageSize = ISNULL(NULLIF(@PageSize, 0), 20);
IF @PageNo < 1 SET @PageNo = 1;
IF @PageSize < 1 SET @PageSize = 20;
IF @PageSize > 200 SET @PageSize = 200; — 硬上限，防止拖库
DECLARE @Offset int = (@PageNo – 1) * @PageSize;
/* —- ★ 核心：排序列走白名单映射，绝不拼用户输入 —- */
DECLARE @OrderCol sysname;
SELECT @OrderCol = ColumnName
FROM dbo.Dyn_AllowedColumn
WHERE SchemaName = N'erp' AND TableName = N'SalesOrder'
AND IsEnabled = 1
AND (
(@SortKey = N'OrderNo' AND ColumnName = N'OrderNo')
OR (@SortKey = N'OrderDate' AND ColumnName = N'OrderDate')
OR (@SortKey = N'CountryCode' AND ColumnName = N'CountryCode')
OR (@SortKey = N'StoreCode' AND ColumnName = N'StoreCode')
OR (@SortKey = N'NetAmountUsd'AND ColumnName = N'NetAmountUsd')
OR (@SortKey = N'Status' AND ColumnName = N'Status')
);
/* 白名单没命中 → 回落到安全的默认值，而不是报错或放行 */
IF @OrderCol IS NULL SET @OrderCol = N'OrderDate';
/* ★ 方向：只允许两个字面量之一 */
DECLARE @Dir varchar(4) = CASE WHEN UPPER(@SortDir) = N'ASC' THEN 'ASC' ELSE 'DESC' END;
/* —- 多选列表：拆成【临时表】，作为"关系"参与 EXISTS，不进 SQL 文本 —-
★ 关键知识点（本机实测）：
表变量 @tv 【不可见】于 sp_executesql 内的动态 SQL → 报 1087
本地临时表 #tmp 【可见】于同一会话的嵌套动态 SQL → 正常
所以凡是"要把列表传进动态 SQL"，必须用 #temp 而不是 @table。 */
CREATE TABLE #CountryList (Code nvarchar(20) NOT NULL PRIMARY KEY);
CREATE TABLE #StoreList (Code nvarchar(20) NOT NULL PRIMARY KEY);
CREATE TABLE #StatusList (Code nvarchar(20) NOT NULL PRIMARY KEY);
IF @Countries IS NOT NULL
INSERT INTO #CountryList (Code)
SELECT DISTINCT UPPER(LTRIM(RTRIM(value)))
FROM STRING_SPLIT(@Countries, N',')
WHERE LTRIM(RTRIM(value)) <> N'';
IF @StoreCodes IS NOT NULL
INSERT INTO #StoreList (Code)
SELECT DISTINCT UPPER(LTRIM(RTRIM(value)))
FROM STRING_SPLIT(@StoreCodes, N',')
WHERE LTRIM(RTRIM(value)) <> N'';
IF @Statuses IS NOT NULL
INSERT INTO #StatusList (Code)
SELECT DISTINCT value
FROM STRING_SPLIT(@Statuses, N',')
WHERE LTRIM(RTRIM(value)) <> N'';
/* —- 构造动态 SQL：只拼"结构"，值全部走参数 —- */
DECLARE @sql nvarchar(4000) =
N'SELECT o.OrderNo, o.OrderDate, o.CountryCode, o.StoreCode,
o.NetAmountUsd, o.Status
FROM erp.SalesOrder o
WHERE 1 = 1 ';
/* 临时表存在时才 JOIN（这是"结构"变化，允许拼）*/
IF EXISTS (SELECT 1 FROM #CountryList)
SET @sql += N' AND EXISTS (SELECT 1 FROM #CountryList cl WHERE cl.Code = o.CountryCode) ';
IF EXISTS (SELECT 1 FROM #StoreList)
SET @sql += N' AND EXISTS (SELECT 1 FROM #StoreList sl WHERE sl.Code = o.StoreCode) ';
IF EXISTS (SELECT 1 FROM #StatusList)
SET @sql += N' AND EXISTS (SELECT 1 FROM #StatusList stl WHERE stl.Code = o.Status) ';
/* 范围条件：用参数占位符，不拼值 */
IF @DateFrom IS NOT NULL SET @sql += N' AND o.OrderDate >= @pDateFrom ';
IF @DateTo IS NOT NULL SET @sql += N' AND o.OrderDate < @pDateToEnd ';
IF @MinAmount IS NOT NULL SET @sql += N' AND o.NetAmountUsd >= @pMinAmt ';
IF @MaxAmount IS NOT NULL SET @sql += N' AND o.NetAmountUsd <= @pMaxAmt ';
/* ★ 排序列已经在白名单里验证过，这里拼的是【白名单里的真实列名】，
而不是用户输入 —— 这是安全的。仍用 QUOTENAME 兜底转义。 */
SET @sql += N' ORDER BY ' + QUOTENAME(@OrderCol) + N' ' + @Dir
+ N' OFFSET @pOffset ROWS FETCH NEXT @pPageSize ROWS ONLY;';
/* —- 声明串：与 SQL 文本严格对应 —- */
DECLARE @decl nvarchar(500) = N'@pDateFrom date, @pDateToEnd date,
@pMinAmt decimal(18,2), @pMaxAmt decimal(18,2),
@pOffset int, @pPageSize int';
/* —- 写审计日志（成功与否都记）—- */
INSERT INTO dbo.Dyn_SearchLog
(ProcName, FilterJson, SqlPreview, ParamCount, Strategy)
VALUES
(N'usp_Dyn_SearchOrders_Dynamic',
N'{Countries:' + ISNULL(@Countries, N'null') + N', Sort:' + @SortKey + N' ' + @Dir + N'}',
LEFT(@sql, 1000), 6, 'dynamic');
/* —- 执行 —- */
DECLARE @t0 datetime2(7) = SYSDATETIME();
EXEC sys.sp_executesql @sql, @decl,
@pDateFrom = @DateFrom,
@pDateToEnd = @DateTo,
@pMinAmt = @MinAmount,
@pMaxAmt = @MaxAmount,
@pOffset = @Offset,
@pPageSize = @PageSize;
/* —- 回写耗时 —- */
UPDATE TOP (1) dbo.Dyn_SearchLog
SET ElapsedUs = DATEDIFF(MICROSECOND, @t0, SYSDATETIME())
WHERE ProcName = N'usp_Dyn_SearchOrders_Dynamic'
AND LogId = (SELECT MAX(LogId) FROM dbo.Dyn_SearchLog);
END
PRINT N' · dbo.usp_Dyn_SearchOrders_Dynamic 创建完成';
GO
/* —————————————————————————
4.3 测试各种条件组合
————————————————————————— */
PRINT N'';
PRINT N'── 4.3 测试（注意：下面的输入里【故意带了注入载荷】）───────';
PRINT N' ▸ 用例 1：单条件（只看中国香港）';
EXEC dbo.usp_Dyn_SearchOrders_Dynamic
@Countries = N'HK', @PageSize = 3;
PRINT N' ▸ 用例 2：多选国家 + 多选状态 + 金额区间';
EXEC dbo.usp_Dyn_SearchOrders_Dynamic
@Countries = N'CN,US,JP', @Statuses = N'Paid,Shipped',
@MinAmount = 30000, @MaxAmount = 50000, @PageSize = 4;
PRINT N' ▸ 用例 3：日期区间 + 排序';
EXEC dbo.usp_Dyn_SearchOrders_Dynamic
@DateFrom = '2025-01-01', @DateTo = '2025-07-01',
@SortKey = N'NetAmountUsd', @SortDir = N'ASC', @PageSize = 4;
PRINT N' ▸ 用例 4：★ 注入载荷攻击排序列（应被白名单挡下，回落到默认）';
EXEC dbo.usp_Dyn_SearchOrders_Dynamic
@Countries = N'HK',
@SortKey = N'OrderNo; DROP TABLE dbo.Dyn_InjectionDemo –',
@PageSize = 3;
PRINT N' ★ 观察：查询正常返回（按 OrderDate DESC 排序），没有执行 DROP。';
PRINT N' 因为 @SortKey 不在白名单映射里 → 回落到默认列。';
PRINT N' ▸ 用例 5：★ 注入载荷攻击国家列表（应被当作普通字符串）';
EXEC dbo.usp_Dyn_SearchOrders_Dynamic
@Countries = N'HK'') OR 1=1 –',
@PageSize = 3;
PRINT N' ★ 观察：返回 0 行。因为载荷被 STRING_SPLIT 拆成【一个字符串】，';
PRINT N' 去和 CountryCode 列比较时永远匹配不上 —— 它只是数据，不是语法。';
PRINT N' 注意：这里临时表列用 nvarchar(20)，载荷能完整存入；';
PRINT N' 若列宽只有 char(2)，会先报 2628「字符串将被截断」。';
PRINT N' 两种结果都【不会】让注入成功，但报错会影响可用性，';
PRINT N' 所以列宽要留足，并在入参处做长度校验（见 4.5）。';
/* —————————————————————————
4.5 入参防御：长度校验与合法性校验的正确位置
————————————————————————— */
PRINT N'';
PRINT N'── 4.5 入参防御清单 ─────────────────────────────────────────';
SELECT
防御点 = 防御点,
做法 = 做法
FROM (VALUES
(N'列表长度上限', N'拼接前检查 LEN(@Countries) <= 200，超长直接 RAISERROR 拒绝'),
(N'元素个数上限', N'STRING_SPLIT 后 COUNT(*) 检查，防止 IN 列表爆炸'),
(N'元素取值校验', N'用 NOT EXISTS(SELECT … WHERE PATINDEX(''%[^A-Z]%'', Code)>0) 拒绝非字母'),
(N'日期区间方向', N'检查 @DateFrom IS NULL OR @DateTo IS NULL OR @DateFrom <= @DateTo'),
(N'金额区间方向', N'检查 @MinAmount IS NULL OR @MaxAmount IS NULL OR @MinAmount <= @MaxAmount'),
(N'分页上限', N'@PageSize 硬编码上限（本例 200），防止深度分页拖库'),
(N'排序列', N'白名单映射（本例用 Dyn_AllowedColumn 表），未命中则回落默认'),
(N'排序方向', N'CASE WHEN UPPER(@Dir)=''ASC'' THEN ''ASC'' ELSE ''DESC'' END'),
(N'标识符转义', N'QUOTENAME(@col, '']'') —— 拼标识符时永远加这一层'),
(N'值一律参数化', N'任何来自外部的值，都不以任何形式进入 SQL 文本')
) v(防御点, 做法);
PRINT N' ★ 记住顺序：先【入参规范化与校验】，再【白名单映射】，最后【参数化】。';
PRINT N' 三层缺一不可，但优先级依次递减 —— 参数化是底线，白名单是标识符的底线。';
/* —————————————————————————
4.4 验证：靶场表还在（证明 DROP 注入确实失败了）
————————————————————————— */
PRINT N'';
PRINT N'── 4.4 验证注入攻击失败 ──────────────────────────────────────';
IF OBJECT_ID(N'dbo.Dyn_InjectionDemo', N'U') IS NOT NULL
PRINT N' ✓ 靶场表 dbo.Dyn_InjectionDemo 依然存在 —— 注入攻击被成功阻止。';
ELSE
PRINT N' ✗ 靶场表已被删除 —— 注入成功，这是严重问题！';
SELECT TOP 5
日志ID = LogId, 记录时间 = CONVERT(char(19), LoggedAt, 120),
过程 = ProcName, 参数摘要 = LEFT(FilterJson, 50),
耗时微秒 = ElapsedUs
FROM dbo.Dyn_SearchLog ORDER BY LogId DESC;
GO
/* ===========================================================================
【05】标识符安全 —— QUOTENAME、白名单、与"延迟名称解析"陷阱
===========================================================================
参数化解决"值"，标识符（表名/列名/排序）必须另想办法。
本节把 QUOTENAME 的边界讲透，并实测一个极隐蔽的坑：
"延迟名称解析"（Deferred Name Resolution）——对象名不存在也要到运行期才报错。
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【05】标识符安全：QUOTENAME 与白名单';
PRINT N'═══════════════════════════════════════════════════════════════════';
/* —————————————————————————
5.1 QUOTENAME 到底防什么、不防什么
————————————————————————— */
PRINT N'';
PRINT N'── 5.1 QUOTENAME 的边界 ──────────────────────────────────────';
SELECT
输入 = 输入,
QUOTENAME结果 = QUOTENAME(输入),
带单引号参数 = QUOTENAME(输入, ''''),
带双引号参数 = QUOTENAME(输入, '"'),
说明 = 说明
FROM (VALUES
(N'SalesOrder', N'普通标识符，默认加 []'),
(N'Order Date', N'含空格，[] 让它成为合法标识符'),
(N'weird]; DROP–', N'★ 含 ] 和分号 —— QUOTENAME 会把 ] 转义成 ]]'),
(N'a''b', N'★ 含单引号 —— 用 [] 时单引号【不会被处理】'),
(N'[already]', N'已带方括号，会被再包一层'),
(NULL, N'NULL 输入返回 NULL')
) v(输入, 说明);
PRINT N' ★ 关键认知：';
PRINT N' 1) QUOTENAME(@x) 默认用 []，它【只保证结果是一个合法的定界标识符】，';
PRINT N' 不保证这个标识符【存在】、也不保证它【在允许范围内】。';
PRINT N' 2) ''weird]; DROP–'' 经 QUOTENAME 后是 [weird]]; DROP–]，';
PRINT N' 作为标识符是"安全"的（整体当一个名字），但这个名字显然不是表名。';
PRINT N' → 所以 QUOTENAME 必须【配合白名单】才有意义。';
PRINT N' 3) 拼【值】时用 QUOTENAME 是错的（应参数化）；';
PRINT N' 拼【标识符】时用 QUOTENAME 是对的（但还要白名单）。';
/* — 演示：QUOTENAME 后仍不存在的表会怎样 — */
PRINT N'';
PRINT N' ▸ 演示：QUOTENAME 一个恶意表名，看它变成什么';
DECLARE @evilTable nvarchar(100) = N'erp.SalesOrder; DROP TABLE dbo.Dyn_InjectionDemo –';
DECLARE @quoted nvarchar(200) = QUOTENAME(@evilTable);
PRINT N' 原始 = ' + @evilTable;
PRINT N' QUOTENAME 后 = ' + @quoted;
PRINT N' ★ 它整体成了一个"标识符"，语法上安全 —— 但该对象不存在，';
PRINT N' 会在【运行期】报 208「对象名无效」。见 5.3 的延迟名称解析。';
/* —————————————————————————
5.2 白名单校验函数：把"合法"定义清楚
————————————————————————— */
PRINT N'';
PRINT N'── 5.2 白名单校验函数 ────────────────────────────────────────';
PRINT N' 思路：不判断"什么非法"，只判断"是否在白名单中"—— 默认拒绝。';
GO
/* ★ 注意：CREATE FUNCTION / CREATE PROCEDURE 必须是批次的第一个语句，
所以前面必须用 GO 断开（否则报 111「必须是查询批次中的第一个语句」）。*/
CREATE OR ALTER FUNCTION dbo.fn_Dyn_IsSafeIdentifier
(
@SchemaName sysname,
@TableName sysname,
@ColumnName sysname = NULL
)
RETURNS bit
AS
BEGIN
IF @ColumnName IS NULL
RETURN CASE WHEN EXISTS
(SELECT 1 FROM dbo.Dyn_AllowedTable
WHERE SchemaName = @SchemaName AND TableName = @TableName AND IsEnabled = 1)
THEN 1 ELSE 0 END;
RETURN CASE WHEN EXISTS
(SELECT 1 FROM dbo.Dyn_AllowedColumn
WHERE SchemaName = @SchemaName AND TableName = @TableName
AND ColumnName = @ColumnName AND IsEnabled = 1)
THEN 1 ELSE 0 END;
END
GO
PRINT N' · dbo.fn_Dyn_IsSafeIdentifier 创建完成';
PRINT N'';
PRINT N' ▸ 校验结果演示：';
SELECT
被测对象 = 架构 + N'.' + 表名 + N'.' + 列名,
是否放行 = CASE WHEN dbo.fn_Dyn_IsSafeIdentifier(架构, 表名, 列名) = 1
THEN N'✓ 放行' ELSE N'✗ 拒绝' END,
场景 = 场景
FROM (VALUES
(N'erp', N'SalesOrder', N'OrderNo', N'白名单内：正常业务列'),
(N'erp', N'SalesOrder', N'NetAmountUsd', N'白名单内：正常业务列'),
(N'erp', N'SalesOrder', N'Password', N'白名单外：不存在的列，拒绝'),
(N'erp', N'SalesOrder', N'UnitCost', N'白名单外：敏感成本列，拒绝'),
(N'dbo', N'sysusers', N'name', N'白名单外：系统表，拒绝'),
(N'erp', N'SalesOrder; DROP TABLE x –', N'OrderNo', N'白名单外：恶意表名，拒绝')
) v(架构, 表名, 列名, 场景);
/* —————————————————————————
5.3 ★ 延迟名称解析（Deferred Name Resolution）—— 最隐蔽的坑之一
—————————————————————————
创建存储过程时，SQL Server 不会检查【引用的对象是否存在】。
这意味着：一个指向不存在表的存储过程可以【创建成功】，
直到第一次调用才报错。
在动态 SQL 里这更危险：拼接的表名在运行期才被解析，
期间可能已经产生副作用（比如你已经在同一批次里删了别的东西）。
————————————————————————— */
PRINT N'';
PRINT N'── 5.3 延迟名称解析实测 ──────────────────────────────────────';
/* 创建一个引用了不存在表的存储过程 —— 注意它能创建成功 */
GO
CREATE OR ALTER PROCEDURE dbo.usp_Dyn_Demo_Injection
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM dbo.ThisTableDoesNotExist_12345; /* 不存在 */
END
GO
PRINT N' ✓ 存储过程创建【成功】了 —— 尽管它引用了不存在的表。';
PRINT N' 这就是"延迟名称解析"：编译期不校验对象存在性。';
PRINT N'';
PRINT N' ▸ 现在调用它，才会报错：';
BEGIN TRY
EXEC dbo.usp_Dyn_Demo_Injection;
END TRY
BEGIN CATCH
PRINT N' 运行期错误 ' + CAST(ERROR_NUMBER() AS nvarchar(10))
+ N'：' + ERROR_MESSAGE();
PRINT N' ★ 教训：动态 SQL 拼表名时，错误发生在【运行期】。';
PRINT N' 必须在拼接【之前】用白名单确认表名合法且存在，';
PRINT N' 而不是指望 SQL Server 帮你拦住。';
END CATCH
/* —————————————————————————
5.4 ★ 正确姿势：白名单 + QUOTENAME 双层
————————————————————————— */
PRINT N'';
PRINT N'── 5.4 正确姿势：白名单 + QUOTENAME 双层 ───────────────────';
DECLARE @reqSchema sysname = N'erp';
DECLARE @reqTable sysname = N'Product';
DECLARE @reqCol sysname = N'Category';
/* 第一层：白名单（默认拒绝） */
IF dbo.fn_Dyn_IsSafeIdentifier(@reqSchema, @reqTable, @reqCol) = 0
BEGIN
RAISERROR(N'标识符不在白名单内：%s.%s.%s', 16, 1, @reqSchema, @reqTable, @reqCol);
END
ELSE
BEGIN
/* 第二层：QUOTENAME（兜底转义） */
DECLARE @safeObj nvarchar(200) =
QUOTENAME(@reqSchema) + N'.' + QUOTENAME(@reqTable);
DECLARE @safeCol nvarchar(200) = QUOTENAME(@reqCol);
DECLARE @sql nvarchar(1000) =
N'SELECT TOP 5 ' + @safeCol + N' AS 品类, COUNT(*) AS 商品数 '
+ N'FROM ' + @safeObj + N' '
+ N'GROUP BY ' + @safeCol + N' ORDER BY 商品数 DESC;';
PRINT N' 生成 SQL = ' + @sql;
EXEC sys.sp_executesql @sql;
PRINT N' ✓ 双层防护：白名单决定"能不能用"，QUOTENAME 保证"语法正确"。';
END
/* — 反例：恶意表名被白名单拦下 — */
PRINT N'';
PRINT N' ▸ 反例：同一个方法，传入恶意表名';
DECLARE @badTable sysname = N'Product; DROP TABLE dbo.Dyn_InjectionDemo –';
IF dbo.fn_Dyn_IsSafeIdentifier(N'erp', @badTable, N'Sku') = 0
PRINT N' ✓ 已在白名单层被拒绝：' + @badTable;
ELSE
PRINT N' ✗ 放行了（不应发生）';
/* —- 清理演示用的过程 —- */
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_Demo_Injection;
GO
/* —————————————————————————
5.5 动态 SQL 里使用标识符的四条铁律
————————————————————————— */
PRINT N'';
PRINT N'── 5.5 铁律 ─────────────────────────────────────────────────';
SELECT
铁律 = 铁律, 说明 = 说明
FROM (VALUES
(N'铁律 1', N'能不用动态标识符就不用：表名/列名固定时一律写死'),
(N'铁律 2', N'必须动态时走白名单表（或代码内 CASE 映射），默认拒绝'),
(N'铁律 3', N'白名单通过后再套 QUOTENAME(名称, ''<'') 做语法兜底'),
(N'铁律 4', N'绝不把用户输入"部分匹配"进标识符（如 ''t_'' + @user）')
) v(铁律, 说明);
GO
/* ===========================================================================
【06】权限与执行上下文 —— 动态 SQL 的安全纵深
===========================================================================
6.1 动态 SQL 在【调用者】权限下运行（最常见的权限困惑）
6.2 EXECUTE AS：切换执行上下文
6.3 所有权链（Ownership Chain）为什么能"免权限"访问基表
6.4 模块签名：权限最小化的终极方案
6.5 注入防御的纵深体系总结
★ 本节所有权限演示都在【当前数据库内】用 CREATE USER … WITHOUT LOGIN
创建隔离的测试身份，不触碰任何服务器级安全主体，运行后立即清理。
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【06】权限与执行上下文';
PRINT N'═══════════════════════════════════════════════════════════════════';
/* —————————————————————————
6.1 核心认知：动态 SQL 的权限来自【调用者】，不是存储过程
—————————————————————————
静态 SQL 在存储过程里执行时受"所有权链"保护：
若过程与基表同属 dbo，则只给 EXECUTE 权限即可访问基表。
但动态 SQL（EXEC / sp_executesql）会【打断所有权链】，
因为它是一条独立编译的语句，权限按调用者计算。
=========================================================================== */
PRINT N'';
PRINT N'── 6.1 所有权链 vs 动态 SQL 的权限断裂 ──────────────────────';
PRINT N' 先用一个简化的对照说明（文字）：';
SELECT
场景 = 场景,
需要的权限 = 需要的权限,
原因 = 原因
FROM (VALUES
(N'静态 SQL 过程（与基表同属 dbo）',
N'只需 GRANT EXECUTE ON 过程',
N'所有权链连贯，SQL Server 自动"跳过"基表权限检查'),
(N'动态 SQL 过程（EXEC / sp_executesql）',
N'需要 GRANT SELECT ON 基表',
N'所有权链被打断，动态语句按【调用者】权限检查'),
(N'动态 SQL + EXECUTE AS OWNER',
N'只需 GRANT EXECUTE ON 过程',
N'在过程【所有者】上下文中执行，链重新连贯'),
(N'动态 SQL + 模块签名',
N'只需 GRANT EXECUTE ON 过程',
N'用证书签名过程，把证书权限"传染"给动态 SQL')
) v(场景, 需要的权限, 原因);
/* —————————————————————————
6.2 ★ 实测：动态 SQL 打断所有权链
—————————————————————————
建立隔离测试身份 u_Dyn_TestUser（无登录名），
给它 EXECUTE 权限但【不给】SELECT 权限，然后分别调用
静态过程与动态过程，观察结果。
————————————————————————— */
PRINT N'';
PRINT N'── 6.2 ★ 实测：动态 SQL 打断所有权链 ────────────────────────';
GO
/* 建立隔离测试身份 */
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'u_Dyn_TestUser')
DROP USER u_Dyn_TestUser;
CREATE USER u_Dyn_TestUser WITHOUT LOGIN;
PRINT N' · 测试身份 u_Dyn_TestUser 建立完成（无登录名，仅在库内）';
GO
/* 静态过程：所有权链完整 */
CREATE OR ALTER PROCEDURE dbo.usp_Dyn_SignedRead
AS
BEGIN
SET NOCOUNT ON;
SELECT COUNT(*) AS 静态读取订单数 FROM erp.SalesOrder;
END
GO
/* 动态过程：用 EXEC 拼接，所有权链被打断 */
CREATE OR ALTER PROCEDURE dbo.usp_Dyn_SignedRead_Dyn
AS
BEGIN
SET NOCOUNT ON;
EXEC (N'SELECT COUNT(*) AS 动态读取订单数 FROM erp.SalesOrder;');
END
GO
/* 只授予 EXECUTE，不授予任何表权限 */
GRANT EXECUTE ON dbo.usp_Dyn_SignedRead TO u_Dyn_TestUser;
GRANT EXECUTE ON dbo.usp_Dyn_SignedRead_Dyn TO u_Dyn_TestUser;
PRINT N' · 已只授予 EXECUTE 权限（未授予任何基表 SELECT 权限）';
GO
/* — 测试 1：静态过程（应成功，所有权链生效）— */
PRINT N'';
PRINT N' ▸ 测试 1：以 u_Dyn_TestUser 身份调用【静态】过程';
EXECUTE AS USER = N'u_Dyn_TestUser';
BEGIN TRY
EXEC dbo.usp_Dyn_SignedRead;
PRINT N' ✓ 成功 —— 所有权链让过程可以访问同属 dbo 的基表。';
END TRY
BEGIN CATCH
PRINT N' ✗ 失败 ' + CAST(ERROR_NUMBER() AS nvarchar(10))
+ N'：' + ERROR_MESSAGE();
END CATCH
REVERT;
/* — 测试 2：动态过程（应失败 229，权限不足）— */
PRINT N'';
PRINT N' ▸ 测试 2：以 u_Dyn_TestUser 身份调用【动态】过程';
EXECUTE AS USER = N'u_Dyn_TestUser';
BEGIN TRY
EXEC dbo.usp_Dyn_SignedRead_Dyn;
PRINT N' ✓ 成功（若出现此行，说明所有权链未被打断）';
END TRY
BEGIN CATCH
PRINT N' ✗ 失败 ' + CAST(ERROR_NUMBER() AS nvarchar(10))
+ N'：' + ERROR_MESSAGE();
PRINT N' ★ 这就是重点：动态 SQL 的权限检查按【调用者】做，';
PRINT N' 所有权链被打断 → 即使过程本身可执行，基表也读不到。';
PRINT N' 这是很多"本地能跑、上线 229"问题的根因。';
END CATCH
REVERT;
GO
/* —————————————————————————
6.3 ★ 解法一：EXECUTE AS OWNER
—————————————————————————
让过程以【所有者】(dbo) 的身份执行动态 SQL，链重新连贯。
优点：一行搞定。缺点：权限"过宽"——过程中所有代码都获得 dbo 权限。
=========================================================================== */
PRINT N'';
PRINT N'── 6.3 解法一：EXECUTE AS OWNER ──────────────────────────────';
GO
CREATE OR ALTER PROCEDURE dbo.usp_Dyn_SignedRead_Dyn
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
EXEC (N'SELECT COUNT(*) AS 动态读取订单数 FROM erp.SalesOrder;');
END
GO
PRINT N' · 已把 usp_Dyn_SignedRead_Dyn 改为 WITH EXECUTE AS OWNER';
PRINT N' ▸ 再次以 u_Dyn_TestUser 身份调用：';
EXECUTE AS USER = N'u_Dyn_TestUser';
BEGIN TRY
EXEC dbo.usp_Dyn_SignedRead_Dyn;
PRINT N' ✓ 成功 —— EXECUTE AS OWNER 让动态 SQL 以 dbo 身份运行。';
END TRY
BEGIN CATCH
PRINT N' ✗ 失败 ' + CAST(ERROR_NUMBER() AS nvarchar(10))
+ N'：' + ERROR_MESSAGE();
END CATCH
REVERT;
PRINT N'';
PRINT N' ★ 对比测试：以 u_Dyn_TestUser 身份直接查基表（应失败）';
EXECUTE AS USER = N'u_Dyn_TestUser';
BEGIN TRY
SELECT TOP 1 OrderNo FROM erp.SalesOrder;
PRINT N' ✗ 意外成功（不应发生）';
END TRY
BEGIN CATCH
PRINT N' ✓ 失败 ' + CAST(ERROR_NUMBER() AS nvarchar(10))
+ N'：' + ERROR_MESSAGE();
PRINT N' ★ 关键：用户【仍然无法】直接访问基表，';
PRINT N' 只能通过我们控制的过程访问 → 权限最小化达成。';
END CATCH
REVERT;
GO
/* —————————————————————————
6.4 ★ 解法二：模块签名（更精确的权限授予）
—————————————————————————
EXECUTE AS OWNER 的问题是"给了全部权限"。
模块签名可以只授予动态 SQL 真正需要的权限（例如只读一张表）。
步骤：
1) CREATE CERTIFICATE（含私钥）
2) ADD SIGNATURE TO 过程 BY CERTIFICATE
3) CREATE USER FROM CERTIFICATE
4) GRANT 精确权限 TO 该证书用户
5) 删除私钥（可选，签名后不再需要）
=========================================================================== */
PRINT N'';
PRINT N'── 6.4 解法二：模块签名（精细权限）──────────────────────────';
GO
/* 0) 建证书需要数据库主密钥（用 EXPIRY_DATE / 私钥时会强制要求）
★ 坑：没有主密钥时报 15581
「请在执行此操作之前，在数据库中创建主密钥，或在会话中打开该主密钥」
★ 生产上主密钥密码要妥善保管，且应做 BACKUP MASTER KEY */
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Dyn@Demo#2025!Key';
PRINT N' · 步骤 0：数据库主密钥已创建';
END
ELSE
PRINT N' · 步骤 0：数据库主密钥已存在（跳过）';
GO
/* 1) 建证书（仅库内，不导出） */
IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = N'Cert_Dyn_SignedReader')
CREATE CERTIFICATE Cert_Dyn_SignedReader
WITH SUBJECT = N'动态 SQL 只读签名证书', EXPIRY_DATE = N'20351231';
PRINT N' · 步骤 1：证书 Cert_Dyn_SignedReader 就绪';
GO
/* 把动态过程改回不带 EXECUTE AS，改由签名授权 */
CREATE OR ALTER PROCEDURE dbo.usp_Dyn_SignedRead_Dyn2
AS
BEGIN
SET NOCOUNT ON;
EXEC (N'SELECT COUNT(*) AS 签名读取订单数 FROM erp.SalesOrder;');
END
GO
/* 2) 给过程加签名 */
ADD SIGNATURE TO dbo.usp_Dyn_SignedRead_Dyn2 BY CERTIFICATE Cert_Dyn_SignedReader;
PRINT N' · 步骤 2：已用证书给过程签名';
GO
/* 3) 从证书创建数据库用户 */
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'u_Dyn_CertReader')
CREATE USER u_Dyn_CertReader FROM CERTIFICATE Cert_Dyn_SignedReader;
PRINT N' · 步骤 3：已从证书创建用户 u_Dyn_CertReader';
GO
/* 4) 只授予【精确的】SELECT 权限（这一张表） */
GRANT SELECT ON OBJECT::erp.SalesOrder TO u_Dyn_CertReader;
GRANT EXECUTE ON dbo.usp_Dyn_SignedRead_Dyn2 TO u_Dyn_TestUser;
PRINT N' · 步骤 4：已授予证书用户 erp.SalesOrder 的 SELECT';
PRINT N' 已授予测试用户该过程的 EXECUTE';
GO
/* 5) 测试：签名过程应可用，其它表仍不可访问 */
PRINT N'';
PRINT N' ▸ 测试：以 u_Dyn_TestUser 身份调用签名过程';
EXECUTE AS USER = N'u_Dyn_TestUser';
BEGIN TRY
EXEC dbo.usp_Dyn_SignedRead_Dyn2;
PRINT N' ✓ 成功 —— 签名把证书用户的权限"传染"给了动态 SQL。';
END TRY
BEGIN CATCH
PRINT N' ✗ 失败 ' + CAST(ERROR_NUMBER() AS nvarchar(10))
+ N'：' + ERROR_MESSAGE();
END CATCH
REVERT;
PRINT N'';
PRINT N' ▸ 对照：同名用户访问【未授权】的表 crm.Customer（应失败）';
EXECUTE AS USER = N'u_Dyn_TestUser';
BEGIN TRY
SELECT TOP 1 CustomerNo FROM crm.Customer;
PRINT N' ✗ 意外成功（不应发生）';
END TRY
BEGIN CATCH
PRINT N' ✓ 失败 ' + CAST(ERROR_NUMBER() AS nvarchar(10))
+ N'：' + ERROR_MESSAGE();
PRINT N' ★ 这就是模块签名的价值：权限【精确到表】，';
PRINT N' 而不是像 EXECUTE AS OWNER 那样拿到全部权限。';
END CATCH
REVERT;
GO
/* —————————————————————————
6.5 两种方案对比
————————————————————————— */
PRINT N'';
PRINT N'── 6.5 EXECUTE AS OWNER vs 模块签名 ─────────────────────────';
SELECT
方案 = 方案, 授予粒度 = 授予粒度, 典型场景 = 典型场景, 注意事项 = 注意事项
FROM (VALUES
(N'EXECUTE AS OWNER', N'粗（过程内所有代码获 owner 权限）',
N'内部工具过程、快速修复', N'注意 owner 是谁；避免过程被改后权限失控'),
(N'EXECUTE AS ''user''', N'中（切换为指定用户）',
N'跨模块调用、测试', N'需要有 IMPERSONATE 权限'),
(N'模块签名', N'细（可精确到单表单操作）',
N'生产核心、权限最小化要求高', N'证书/私钥要妥善保管；改过程后签名失效'),
(N'直接授权调用者', N'最粗（用户可绕过过程直接查表）',
N'仅限内部可信用户', N'不推荐用于外部输入场景')
) v(方案, 授予粒度, 典型场景, 注意事项);
/* —————————————————————————
6.6 注入防御纵深体系（五层）
————————————————————————— */
PRINT N'';
PRINT N'── 6.6 注入防御的五层纵深 ───────────────────────────────────';
SELECT
层级 = 层级, 措施 = 措施, 拦住什么 = 拦住什么
FROM (VALUES
(N'第 1 层', N'参数化（sp_executesql / 强类型参数）', N'所有"值"类注入（OR 1=1、UNION、堆叠）'),
(N'第 2 层', N'标识符白名单 + QUOTENAME', N'所有"结构"类注入（表名/列名/排序）'),
(N'第 3 层', N'入参校验（长度/范围/格式/枚举）', N'超长、越界、非法格式的畸形输入'),
(N'第 4 层', N'最小权限（EXECUTE AS / 模块签名）', N'即使注入成功，也读不到/改不了敏感数据'),
(N'第 5 层', N'审计与监控（Dyn_SearchLog + 扩展事件）', N'及时发现异常模式，事后追溯')
) v(层级, 措施, 拦住什么);
PRINT N' ★ 单靠任何一层都不够。参数化是底线（第 1 层），';
PRINT N' 最小权限是最后的兜底（第 4 层）—— 假设前面都被绕过了。';
/* —- 清理测试身份与过程 —- */
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_SignedRead;
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_SignedRead_Dyn;
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_SignedRead_Dyn2;
DROP USER IF EXISTS u_Dyn_TestUser;
DROP USER IF EXISTS u_Dyn_CertReader;
IF EXISTS (SELECT 1 FROM sys.certificates WHERE name = N'Cert_Dyn_SignedReader')
DROP CERTIFICATE Cert_Dyn_SignedReader;
PRINT N'';
PRINT N' · 权限演示对象与测试身份已全部清理';
PRINT N' ★ 注意：数据库主密钥（##MS_DatabaseMasterKey##）【故意保留】——';
PRINT N' 它是库级共享对象，可能被其它功能依赖，不应由演示脚本随意删除。';
PRINT N' 生产上应 BACKUP MASTER KEY TO FILE = … ENCRYPTION BY PASSWORD = …;';
GO
/* ===========================================================================
【07】计划缓存 —— 动态 SQL 的内存代价与参数嗅探
===========================================================================
7.1 动态 SQL 的计划缓存机制（为什么"缓存爆炸"是动态 SQL 的专利）
7.2 ★ 实测：字面量拼接 vs 参数化的缓存条目数
7.3 参数嗅探在动态 SQL 里的表现与三种缓解
7.4 optimize for ad hoc workloads（专门治缓存爆炸）
7.5 缓存统计与诊断查询
★ 本机为 32 GB 共享内存环境，故本节只做【小规模】缓存实验，
不执行任何 DBCC FREEPROCCACHE（那会清空全实例计划缓存，影响他人）。
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【07】计划缓存与参数嗅探';
PRINT N'═══════════════════════════════════════════════════════════════════';
/* —————————————————————————
7.1 机制说明
—————————————————————————
计划缓存的 Key 是【语句文本的哈希】（外加 SET 选项、数据库上下文等）。
动态 SQL 的文本是运行期拼出来的字符串，所以：
· 只要拼接结果有一字节不同 → 新 Key → 新计划 → 新缓存条目
· 用 sp_executesql 把变化的部分都参数化 → 文本恒定 → 计划复用
一条经验公式（源自微软顾问实践）：
未参数化的 ad-hoc 计划约占缓存内存的 30%~50%，
单纯靠 optimize for ad hoc workloads 就能省下其中大部分。
————————————————————————— */
PRINT N'';
PRINT N'── 7.1 当前实例的缓存概况 ────────────────────────────────────';
SELECT
缓存类型 = CASE cp.objtype
WHEN 'Adhoc' THEN N'Adhoc（动态/内联 SQL）'
WHEN 'Proc' THEN N'Proc（存储过程）'
WHEN 'Prepared' THEN N'Prepared（参数化/预编译）'
ELSE cp.objtype END,
条目数 = COUNT(*),
占用MB = CAST(SUM(cp.size_in_bytes)/1048576.0 AS decimal(10,2)),
usecount1次 = SUM(CASE WHEN cp.usecounts = 1 THEN 1 ELSE 0 END)
FROM sys.dm_exec_cached_plans cp
GROUP BY cp.objtype
ORDER BY COUNT(*) DESC;
PRINT N' ★ 重点关注 Adhoc 行：条目多、usecounts=1 的多 → 典型的缓存爆炸迹象。';
/* —————————————————————————
7.2 ★ 实测：拼接 vs 参数化 的缓存条目数差异
—————————————————————————
构造 20 组不同的筛选值，分别用两种方式执行，比较缓存条目。
★ 注意：计划缓存是实例级共享的，重复运行本脚本时条目可能已存在。
所以这里【直接统计探针标记对应的条目数】，而不是做前后差值相减 ——
差值法在重复运行时会得到 0 或负数，是常见的测量陷阱。
为了得到干净的对比，每次运行先用【唯一标记】区分两种方式。
————————————————————————— */
PRINT N'';
PRINT N'── 7.2 ★ 实测：20 组参数，两种方式的缓存条目 ───────────────';
/* — 方式 A：EXEC 拼接（20 条独立语句）— */
DECLARE @i int = 1;
WHILE @i <= 20
BEGIN
DECLARE @amt decimal(18,2) = 1000.00 + @i * 137.5; /* 每组都不同 */
DECLARE @sq nvarchar(500) =
N'SELECT /* dyn_cache_probe_A */ COUNT(*) AS c FROM erp.SalesOrder '
+ N'WHERE CountryCode = ''HK'' AND NetAmountUsd >= ' + CAST(@amt AS nvarchar(30)) + N';';
EXEC (@sq);
SET @i += 1;
END
/* — 方式 B：sp_executesql 参数化（同一条语句）— */
DECLARE @sqlB nvarchar(500) =
N'SELECT /* dyn_cache_probe_B */ COUNT(*) AS c FROM erp.SalesOrder '
+ N'WHERE CountryCode = @cc AND NetAmountUsd >= @amt;';
SET @i = 1;
WHILE @i <= 20
BEGIN
DECLARE @amtB decimal(18,2) = 1000.00 + @i * 137.5;
EXEC sys.sp_executesql @sqlB, N'@cc char(2), @amt decimal(18,2)',
@cc = N'HK', @amt = @amtB;
SET @i += 1;
END
/* 注意：A 的 20 条语句共享同一段前缀文本，但其【完整文本】各不相同，
所以 LIKE 匹配到的是 20 条不同的缓存条目。
B 的完整文本恒定，所以只匹配到 1 条。 */
DECLARE @cntA int =
(SELECT COUNT(*) FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE t.text LIKE N'%dyn_cache_probe_A%');
DECLARE @cntB int =
(SELECT COUNT(*) FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE t.text LIKE N'%dyn_cache_probe_B%');
SELECT
方式 = 方式,
执行次数 = 20,
缓存条目数 = 条目数,
平均复用次数 = CAST(20.0 / NULLIF(条目数, 0) AS decimal(10,2)),
结论 = 结论
FROM (VALUES
(N'EXEC 拼接（字面量）', @cntA, N'✗ 每条都是新语句，20 组参数 = 20 份计划'),
(N'sp_executesql 参数化', @cntB, N'✓ 同一条文本，20 次共用 1 份计划')
) v(方式, 条目数, 结论);
PRINT N' ★ 对比要点：同样的业务逻辑、同样的 20 次执行，';
PRINT N' 动态 SQL 的计划条目数可能相差 20 倍。';
PRINT N' 万亿级高并发下，这就是计划缓存内存雪崩的起点。';
PRINT N' ★ 测量方法提醒：不要用"执行前后条目数相减"来度量缓存增长 ——';
PRINT N' 在重复运行时那些条目已存在，差值会是 0 甚至负数。';
PRINT N' 正确做法是给探针打【唯一标记】并直接统计匹配条目数。';
/* —————————————————————————
7.3 ★ 参数嗅探（Parameter Sniffing）在动态 SQL 里的表现
—————————————————————————
参数化后，SQL Server 会用【首次执行的参数值】来估计行数、生成计划。
如果数据分布极不均匀，首次参数是"非典型值"，后续执行就会跑差计划。
动态 SQL 因为同样走 sp_executesql，这个问题【完全一样存在】。
————————————————————————— */
PRINT N'';
PRINT N'── 7.3 ★ 参数嗅探：列值分布极不均匀时的计划差异 ────────────';
PRINT N' 本库 erp.SalesOrder 的国家分布：';
SELECT 国家 = CountryCode, 订单数 = COUNT(*), 占比 =
CAST(100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS decimal(5,2))
FROM erp.SalesOrder GROUP BY CountryCode ORDER BY COUNT(*) DESC;
PRINT N' ★ CN 与 HK 各 1200 行，其余各国 720 行 —— 分布相对均衡，';
PRINT N' 但真实场景里"大客户 vs 长尾客户"往往差 1000 倍以上。';
PRINT N' 这里用一个【人为构造】的倾斜列来演示嗅探效应。';
/* 构造倾斜数据：VIP 客户订单极少，Standard 客户订单极多 */
PRINT N'';
PRINT N' ▸ 构造倾斜表 dbo.Dyn_SkewDemo 并观察两套计划：';
GO
CREATE OR ALTER PROCEDURE dbo.usp_Dyn_SniffDemo
@Tier varchar(10)
AS
BEGIN
SET NOCOUNT ON;
/* 用客户等级做过滤，分布极不均匀 */
SELECT COUNT(*) AS 订单数
FROM erp.SalesOrder so
JOIN crm.Customer c ON c.CustomerId = so.CustomerId
WHERE c.Tier = @Tier;
END
GO
PRINT N' 首次执行（传入高频值 Standard）：';
EXEC dbo.usp_Dyn_SniffDemo @Tier = N'Standard';
PRINT N' 再次执行（传入低频值 VIP）：';
EXEC dbo.usp_Dyn_SniffDemo @Tier = N'VIP';
PRINT N' ★ 第二次用的仍是第一次生成的计划（按 Standard 估计行数），';
PRINT N' 若 VIP 需要完全不同的访问路径（如索引 Seek vs 扫描），就会变慢。';
PRINT N' ★ 可调优手段（按推荐顺序）：';
SELECT
手段 = 手段, 做法 = 做法, 代价 = 代价
FROM (VALUES
(N'1. 让计划与参数无关', N'改写 SQL 让优化器不那么依赖首次值（如加 OPTION(RECOMPILE) 到关键语句）', N'每次编译，CPU 略增'),
(N'2. OPTION(RECOMPILE)', N'每次执行都重新编译 → 每次都按当前参数最优', N'编译开销，高并发下慎用'),
(N'3. OPTIMIZE FOR UNKNOWN', N'按平均分布估计，不偏向任何参数值', N'可能对典型值反而不优'),
(N'4. OPTIMIZE FOR (@p = ''x'')', N'按指定"典型值"估计，适合已知的高频值', N'需人工判断典型值'),
(N'5. 拆分为多个过程', N'按参数范围分派到不同过程（IF @p IN (高频) EXEC proc1 ELSE EXEC proc2）', N'代码重复，但计划最稳')
) v(手段, 做法, 代价);
/* —————————————————————————
7.4 optimize for ad hoc workloads —— 治缓存爆炸的开关
————————————————————————— */
PRINT N'';
PRINT N'── 7.4 optimize for ad hoc workloads ─────────────────────────';
SELECT
配置项 = N'optimize for ad hoc workloads',
当前值 = CAST(value_in_use AS nvarchar(5)),
说明 = CASE WHEN value_in_use = 1
THEN N'已开启：首次执行的 ad-hoc 计划只存"存根"，第二次才存完整计划'
ELSE N'未开启：所有 ad-hoc 计划都完整入缓存（本机现状）' END
FROM sys.configurations WHERE name = 'optimize for ad hoc workloads';
PRINT N' ★ 开启方式（需 sysadmin / server 级权限）：';
PRINT N' EXEC sys.sp_configure N''optimize for ad hoc workloads'', 1;';
PRINT N' RECONFIGURE;';
PRINT N' ★ 效果：只执行一次的 ad-hoc 语句不再占用完整计划内存，';
PRINT N' 通常可释放 20%~40% 的计划缓存内存。对动态 SQL 密集的系统，';
PRINT N' 这是【性价比最高的一个开关】。本脚本不修改服务器配置。';
/* —————————————————————————
7.5 缓存诊断查询（可直接用于生产巡检）
————————————————————————— */
PRINT N'';
PRINT N'── 7.5 缓存诊断：占内存最多的前 10 类动态语句 ────────────────';
SELECT TOP 10
语句摘要 = LEFT(REPLACE(REPLACE(t.text, CHAR(13), N' '), CHAR(10), N' '), 60),
条目数 = COUNT(*),
总占用KB = SUM(cp.size_in_bytes) / 1024,
总执行次数 = SUM(cp.usecounts),
平均成本 = CAST(SUM(qs.total_worker_time) / NULLIF(SUM(qs.execution_count), 0) / 1000.0
AS decimal(18,2))
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
LEFT JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
WHERE t.text LIKE N'%erp.%' OR t.text LIKE N'%crm.%'
GROUP BY LEFT(REPLACE(REPLACE(t.text, CHAR(13), N' '), CHAR(10), N' '), 60)
ORDER BY SUM(cp.size_in_bytes) DESC;
PRINT N' ★ 巡检要点：条目数很多但"总执行次数/条目数"接近 1 → 参数化不到位。';
/* —————————————————————————
7.6 ★ 重要提醒：本脚本不执行 DBCC FREEPROCCACHE
—————————————————————————
很多示例脚本会用 DBCC FREEPROCCACHE 来"重置"缓存以便测量。
但在共享实例上，这会：
· 清空【所有数据库】的计划缓存，导致全实例计划重新编译，CPU 飙升
· 与本机"可用内存仅数 GB"的环境叠加，可能触发 701 内存不足
所以本脚本一律用【LIKE 限定文本】的方式来统计特定语句的条目，
而不是清空整个缓存。
————————————————————————— */
PRINT N'';
PRINT N' ★ 本脚本刻意【未执行】DBCC FREEPROCCACHE —— 共享实例上它会拖垮全体。';
PRINT N' 如需清理单个计划，正确做法：';
PRINT N' · 清空全部： DBCC FREEPROCCACHE; （危险，全实例重编译）';
PRINT N' · 按句柄： DBCC FREEPROCCACHE (@plan_handle); （精确，推荐）';
PRINT N' · 按资源池： DBCC FREEPROCCACHE (''default''); （较温和）';
PRINT N' · 清库级： ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;';
/* —- 清理演示过程 —- */
DROP PROCEDURE IF EXISTS dbo.usp_Dyn_SniffDemo;
GO
/* ===========================================================================
【08】万亿级数据 + 高并发下的动态 SQL 策略
===========================================================================
数据量到万亿行、并发到数千时，动态 SQL 面临四个新问题：
8.1 深分页（OFFSET 越大越慢）→ 键集分页（Keyset Pagination）
8.2 分区裁剪 → 动态 SQL 必须把分区键拼进谓词（值仍参数化）
8.3 计划强制（Query Store Plan Forcing）与动态 SQL 的配合
8.4 高并发下的参数嗅探放大效应
8.5 并发与内存：动态 SQL 的缓存预算估算
★ 本机 rpt.OrgSalesFact 是 500 万行的聚集列存表（CCI），
为 32 GB 共享内存环境计，本节所有查询都限定在【单月/单店】范围。
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【08】万亿级数据 + 高并发下的动态 SQL 策略';
PRINT N'═══════════════════════════════════════════════════════════════════';
/* —————————————————————————
8.1 深分页：OFFSET/FETCH 的线性代价
—————————————————————————
OFFSET 1000000 ROWS FETCH NEXT 20 的含义是：
先取出 1000020 行、丢掉前 1000000 行、只返回 20 行。
所以页码越深越慢，是 O(offset) 而不是 O(1)。
对比两种分页：
① OFFSET/FETCH —— 实现简单，深翻页劣化严重，不适合万亿级
② 键集分页 —— 用"上一页最后一行的键"做 Seek，恒定成本
————————————————————————— */
PRINT N'';
PRINT N'── 8.1 深分页 vs 键集分页 ────────────────────────────────────';
/* — 方式①：OFFSET/FETCH（浅页与深页对比）— */
PRINT N' ▸ 方式① OFFSET/FETCH：浅页（第 1 页）';
SET STATISTICS IO OFF;
DECLARE @t1 datetime2(7) = SYSDATETIME();
DECLARE @pg int = 1;
SELECT OrderNo, OrderDate, NetAmountUsd
FROM erp.SalesOrder
ORDER BY OrderId
OFFSET (@pg – 1) * 20 ROWS FETCH NEXT 20 ROWS ONLY;
SELECT 页 = N'第 1 页（OFFSET 0）',
耗时微秒 = DATEDIFF(MICROSECOND, @t1, SYSDATETIME());
PRINT N' ▸ 方式① OFFSET/FETCH：深页（第 100 页，OFFSET 1980）';
DECLARE @t2 datetime2(7) = SYSDATETIME();
SET @pg = 100;
SELECT OrderNo, OrderDate, NetAmountUsd
FROM erp.SalesOrder
ORDER BY OrderId
OFFSET (@pg – 1) * 20 ROWS FETCH NEXT 20 ROWS ONLY;
SELECT 页 = N'第 100 页（OFFSET 1980）',
耗时微秒 = DATEDIFF(MICROSECOND, @t2, SYSDATETIME());
PRINT N' ★ 本表只有 6000 行，差异不明显；但换成万亿行，';
PRINT N' OFFSET 越大代价线性增长 —— 第 100 万页会读到 2000 万行再丢掉。';
/* — 方式②：键集分页（恒定成本）— */
PRINT N'';
PRINT N' ▸ 方式② 键集分页（Keyset / Seek 分页）—— 恒定成本';
DECLARE @lastOrderId bigint = 0; /* 上一页最后一行的键 */
DECLARE @pageSize int = 20;
DECLARE @t3 datetime2(7) = SYSDATETIME();
SELECT TOP (@pageSize) OrderNo, OrderDate, NetAmountUsd, OrderId
FROM erp.SalesOrder
WHERE OrderId > @lastOrderId /* ★ 关键：Seek，不是 Skip */
ORDER BY OrderId;
SELECT 页 = N'第 1 页（键集，OrderId > 0）',
耗时微秒 = DATEDIFF(MICROSECOND, @t3, SYSDATETIME());
SET @lastOrderId = 2000;
DECLARE @t4 datetime2(7) = SYSDATETIME();
SELECT TOP (@pageSize) OrderNo, OrderDate, NetAmountUsd, OrderId
FROM erp.SalesOrder
WHERE OrderId > @lastOrderId
ORDER BY OrderId;
SELECT 页 = N'第 100 页等效（键集，OrderId > 2000）',
耗时微秒 = DATEDIFF(MICROSECOND, @t4, SYSDATETIME());
PRINT N' ★ 关键差异：键集分页的代价【与页码无关】—— 永远是一次索引 Seek';
PRINT N' + 取 20 行。这就是万亿级分页的唯一正确姿势。';
/* — 键集分页的注意事项 — */
PRINT N'';
PRINT N' ★ 键集分页的四个前提 / 限制：';
SELECT 要点 = 要点, 说明 = 说明
FROM (VALUES
(N'排序键必须唯一且有序', N'用主键或唯一键；若按非唯一列排序，需加 PK 作为 tie-breaker，如 (SortCol, OrderId)'),
(N'不能直接跳页', N'只能"下一页/上一页"，无法跳到第 1000 页（这是可接受的代价）'),
(N'排序键不能变', N'分页过程中数据被改会导致跳行或重复，需业务上可接受'),
(N'WHERE 要能 Seek', N'排序列上有索引才有效；本例 OrderId 是聚集主键，天然有序')
) v(要点, 说明);
/* —————————————————————————
8.2 分区裁剪：动态 SQL 必须把分区键拼进谓词
—————————————————————————
万亿行表通常按月/年分区（或按日期做列存 row group 消除）。
分区裁剪（Partition Elimination）要求【编译期】就能看到分区键的常量范围。
如果把日期参数化成一个变量，优化器无法裁剪 → 全表扫描。
★ 这是一个"参数化"与"分区裁剪"的经典冲突：
值参数化 → 计划复用，但失去分区裁剪
值拼字面量 → 可以裁剪，但计划无法复用（且要防注入）
解法：对分区键用【受控的字面量拼接】（值经过严格校验 + 白名单），
对其它谓词继续参数化。
————————————————————————— */
PRINT N'';
PRINT N'── 8.2 分区裁剪与参数化的冲突 ────────────────────────────────';
PRINT N' 本表 rpt.OrgSalesFact 的规模与索引情况：';
SELECT
项目 = N'真实行数（row group 元数据）',
值 = CAST((SELECT SUM(total_rows)
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('rpt.OrgSalesFact')) AS nvarchar(30));
SELECT
项目 = N'索引类型',
/* ★ 坑：sys 目录视图的列用【目录排序规则】，与库排序规则拼接会报 451。
凡是要把系统列与自己的中文字面量拼接，都要加 COLLATE DATABASE_DEFAULT。*/
值 = i.type_desc COLLATE DATABASE_DEFAULT + N' / '
+ ISNULL(i.name COLLATE DATABASE_DEFAULT, N'(无)')
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('rpt.OrgSalesFact');
PRINT N'';
PRINT N' ▸ 对照实验：日期【参数化】 vs 日期【受控拼接】';
PRINT N' （两者逻辑等价，比较执行计划中的分区/row group 消除情况）';
/* — 参数化版本（优化器看不到常量范围）— */
DECLARE @dtFrom date = '2025-03-01', @dtTo date = '2025-04-01';
DECLARE @sqlP nvarchar(1000) =
N'SELECT /* probe_param */ COUNT(*) AS 行数, CAST(SUM(AmountUsd) AS decimal(18,2)) AS 合计
FROM rpt.OrgSalesFact
WHERE OrderDate >= @f AND OrderDate < @t;';
DECLARE @t5 datetime2(7) = SYSDATETIME();
EXEC sys.sp_executesql @sqlP, N'@f date, @t date', @f = @dtFrom, @t = @dtTo;
SELECT 方式 = N'日期参数化', 耗时微秒 = DATEDIFF(MICROSECOND, @t5, SYSDATETIME());
/* — 受控字面量版本（校验后拼接）— */
/* 校验：必须是合法日期，且在允许范围内 */
DECLARE @safeFrom date, @safeTo date;
IF @dtFrom >= '2020-01-01' AND @dtTo <= '2030-12-31' AND @dtFrom < @dtTo
BEGIN
SET @safeFrom = @dtFrom;
SET @safeTo = @dtTo;
END
ELSE
RAISERROR(N'日期范围非法', 16, 1);
DECLARE @sqlL nvarchar(1000) =
N'SELECT /* probe_literal */ COUNT(*) AS 行数, CAST(SUM(AmountUsd) AS decimal(18,2)) AS 合计
FROM rpt.OrgSalesFact
WHERE OrderDate >= ''' + CONVERT(char(8), @safeFrom, 112) + N''' '
+ N' AND OrderDate < ''' + CONVERT(char(8), @safeTo, 112) + N''';';
DECLARE @t6 datetime2(7) = SYSDATETIME();
EXEC (@sqlL);
SELECT 方式 = N'日期受控拼接（字面量）', 耗时微秒 = DATEDIFF(MICROSECOND, @t6, SYSDATETIME());
PRINT N' ★ 说明：本表未做物理分区，所以两者差异主要来自 row group 消除 ——';
PRINT N' 在【已分区】的万亿级表上，差异会放大到"扫 1 个月 vs 扫 10 年"。';
PRINT N' ★ 拼接字面量的安全性前提（缺一不可）：';
PRINT N' 1) 值来自【强类型变量】（此处 @dtFrom date），不是用户原始字符串';
PRINT N' 2) 用 CONVERT(char(8), @d, 112) 输出 ISO 格式（yyyyMMdd），无歧义';
PRINT N' 3) 拼接前做【范围校验】，非法直接拒绝';
PRINT N' 4) 绝不对字符串类型的分区键这样拼 —— 只对 date/datetime 这类'
PRINT N' 能安全格式化且无引号风险的强类型值使用。';
/* —————————————————————————
8.3 Query Store 计划强制与动态 SQL
————————————————————————— */
PRINT N'';
PRINT N'── 8.3 Query Store 计划强制（Plan Forcing）───────────────────';
SELECT
项目 = N'Query Store 状态',
值 = actual_state_desc + N' / 只读原因=' + CAST(readonly_reason AS nvarchar(10))
FROM sys.database_query_store_options;
PRINT N' ★ 动态 SQL 的计划【也会被 Query Store 捕获】（只要文本可规范化）。';
PRINT N' 这带来两个能力：';
PRINT N' · 计划强制：选中一个已知的好计划，强制后续执行都用它';
PRINT N' · 计划回归检测：发现执行计划在某个时间点性能突然变差';
PRINT N'';
PRINT N' ▸ 当前 Query Store 中开销最高的 5 条查询：';
SELECT TOP 5
查询文本 = LEFT(REPLACE(REPLACE(qt.query_sql_text, CHAR(13), N' '), CHAR(10), N' '), 60),
执行次数 = SUM(rs.count_executions),
平均耗时ms = CAST(AVG(rs.avg_duration)/1000.0 AS decimal(18,3)),
是否可强制 = CASE WHEN MAX(p.plan_id) IS NOT NULL THEN N'是' ELSE N'否' END
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
GROUP BY LEFT(REPLACE(REPLACE(qt.query_sql_text, CHAR(13), N' '), CHAR(10), N' '), 60)
ORDER BY AVG(rs.avg_duration) DESC;
PRINT N' ★ 强制计划语法（需 Query Store 已启用）：';
PRINT N' EXEC sp_query_store_force_plan @query_id = …, @plan_id = …;';
PRINT N' ★ 注意：Query Store 有容量上限，动态 SQL 过多时会自动转入只读；';
PRINT N' 应定期检查 sys.database_query_store_options.readonly_reason。';
/* —————————————————————————
8.4 高并发下的参数嗅探放大效应
————————————————————————— */
PRINT N'';
PRINT N'── 8.4 高并发下的参数嗅探放大 ────────────────────────────────';
PRINT N' 单用户时，一个坏计划只是"慢一次"（几百毫秒）；';
PRINT N' 高并发时，同一个坏计划会被成百上千个会话【同时】复用，';
PRINT N' 每个会话都在做同样低效的全扫 → 内存、CPU、IO 三重放大。';
PRINT N'';
PRINT N' 放大公式（近似）：';
PRINT N' 实际资源消耗 ≈ 单次坏计划成本 × 并发会话数 × 平均驻留时间';
PRINT N' 例：一个坏计划单次多耗 200 ms CPU，200 并发 → 每秒多耗 40 秒 CPU，';
PRINT N' 在 6 核机器上直接饱和。';
PRINT N'';
PRINT N' ★ 高并发场景的参数嗅探对策优先级：';
SELECT 优先级 = 优先级, 手段 = 手段, 理由 = 理由
FROM (VALUES
(N'P0', N'先解决 SQL 本身（加索引 / 改写），让计划差异变小',
N'"坏计划"如果也只是一次 Seek，就无所谓嗅探'),
(N'P1', N'拆分过程 + 按频率分派（IF 高频 EXEC proc1 ELSE EXEC proc2）',
N'计划最稳定，且不需要每次重编译'),
(N'P2', N'OPTIMIZE FOR (@p = 典型值)', N'零运行时开销，适合已知高频值'),
(N'P3', N'OPTIMIZE FOR UNKNOWN', N'不偏袒任何值，但对典型值可能略差'),
(N'P4', N'OPTION (RECOMPILE)', N'每次都最优，但高并发下编译争用严重，最后才用')
) v(优先级, 手段, 理由);
/* —————————————————————————
8.5 动态 SQL 的缓存预算估算
————————————————————————— */
PRINT N'';
PRINT N'── 8.5 动态 SQL 缓存预算估算 ─────────────────────────────────';
SELECT
指标 = 指标, 值 = 值, 说明 = 说明
FROM (VALUES
(N'Adhoc 缓存条目数',
CAST((SELECT COUNT(*) FROM sys.dm_exec_cached_plans WHERE objtype = 'Adhoc') AS nvarchar(20)),
N'未参数化的动态/内联 SQL 条目'),
(N'Adhoc 缓存占用 MB',
CAST((SELECT ISNULL(SUM(size_in_bytes),0)/1048576.0 FROM sys.dm_exec_cached_plans WHERE objtype = 'Adhoc') AS decimal(10,2)),
N'这部分内存本可以通过参数化省下'),
(N'仅执行 1 次的 Adhoc 数',
CAST((SELECT COUNT(*) FROM sys.dm_exec_cached_plans WHERE objtype = 'Adhoc' AND usecounts = 1) AS nvarchar(20)),
N'占 Adhoc 的大多数 → 典型缓存浪费'),
(N'全部缓存条目数',
CAST((SELECT COUNT(*) FROM sys.dm_exec_cached_plans) AS nvarchar(20)),
N'含 Proc / Prepared / View 等')
) v(指标, 值, 说明);
PRINT N' ★ 估算方法：假设平均每个 Adhoc 计划 50 KB，';
PRINT N' 若系统有 10 万条不同的动态 SQL 文本（拼接产生的），';
PRINT N' 就是 5 GB 计划缓存 —— 在 32 GB 机器上足以引发内存压力。';
PRINT N' 参数化后同样的业务量可能只需 5 MB。';
PRINT N' ★ 治理优先级：① 开启 optimize for ad hoc workloads';
PRINT N' ② 消除 EXEC 字面量拼接（改 sp_executesql）';
PRINT N' ③ 对高频动态查询建存储过程（走 Proc 缓存）';
GO
/* ===========================================================================
【09】运行时诊断 —— 可直接用于生产的问题排查查询
===========================================================================
动态 SQL 出问题时，排查工具与静态 SQL 略有不同（因为语句文本是拼出来的）。
本节给出 6 个实用诊断查询。
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【09】运行时诊断';
PRINT N'═══════════════════════════════════════════════════════════════════';
/* —————————————————————————
9.1 查找"缓存中条目最多"的动态 SQL 模式（缓存爆炸定位）
————————————————————————— */
PRINT N'';
PRINT N'── 9.1 缓存爆炸定位：同一模式产生最多条目的语句 ──────────────';
SELECT TOP 8
/* 把数字字面量归一化成 #，从而把"同一模式的不同实例"聚到一起 */
模式摘要 = LEFT(
REPLACE(REPLACE(REPLACE(REPLACE(
t.text, CHAR(13), N' '), CHAR(10), N' '), N' ', N' '), N' ', N' '), 70),
条目数 = COUNT(*),
占用KB = SUM(cp.size_in_bytes) / 1024
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE cp.objtype IN ('Adhoc', 'Prepared')
GROUP BY LEFT(
REPLACE(REPLACE(REPLACE(REPLACE(
t.text, CHAR(13), N' '), CHAR(10), N' '), N' ', N' '), N' ', N' '), 70)
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;
PRINT N' ★ 若某模式条目数很多 → 该处是"字面量拼接"而非参数化，应重点改造。';
/* —————————————————————————
9.2 当前正在执行的动态 SQL（实时排查）
————————————————————————— */
PRINT N'';
PRINT N'── 9.2 当前活动会话 ──────────────────────────────────────────';
SELECT
会话ID = r.session_id,
状态 = r.status,
命令 = r.command,
等待类型 = ISNULL(r.wait_type, N'(运行中)'),
已运行秒 = DATEDIFF(SECOND, r.start_time, GETDATE()),
CPU毫秒 = r.cpu_time,
逻辑读 = r.logical_reads,
语句 = LEFT(REPLACE(REPLACE(ISNULL(t.text, N'(无)'), CHAR(13), N' '), CHAR(10), N' '), 55)
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;
PRINT N' ★ 注意：sp_executesql 内执行的语句，sql_handle 指向【外层过程的文本】，';
PRINT N' 要看到真正的动态语句，需配合 sys.dm_exec_input_buffer(session_id, NULL)。';
/* —————————————————————————
9.3 动态 SQL 的错误捕获（TRY/CATCH + 关键上下文）
————————————————————————— */
PRINT N'';
PRINT N'── 9.3 动态 SQL 错误捕获的完整模板 ──────────────────────────';
DECLARE @badSql nvarchar(500) = N'SELECT * FROM erp.NoSuchTable_Demo;';
BEGIN TRY
EXEC sys.sp_executesql @badSql;
END TRY
BEGIN CATCH
PRINT N' 错误号 = ' + CAST(ERROR_NUMBER() AS nvarchar(10));
PRINT N' 严重级别 = ' + CAST(ERROR_SEVERITY() AS nvarchar(10));
PRINT N' 状态 = ' + CAST(ERROR_STATE() AS nvarchar(10));
PRINT N' 行号 = ' + CAST(ERROR_LINE() AS nvarchar(10));
PRINT N' 消息 = ' + ERROR_MESSAGE();
PRINT N' ';
PRINT N' ★ 动态 SQL 排错必记的四个要点：';
PRINT N' 1) ERROR_LINE() 报的是【动态语句内部】的行号，不是外层过程的行号';
PRINT N' 2) 把 @badSql 的值一起记进日志，否则事后无法复现';
PRINT N' 3) 用 ERROR_PROCEDURE() 区分是过程本身还是动态语句出错';
PRINT N' 4) XACT_ABORT ON 时，某些错误会直接终止事务，慎用嵌套 TRY';
END CATCH
/* —————————————————————————
9.4 本次会话已构造的动态 SQL 审计（来自本节的日志表）
————————————————————————— */
PRINT N'';
PRINT N'── 9.4 动态 SQL 审计日志（聚合）──────────────────────────────';
SELECT
过程 = ISNULL(ProcName, N'(直接调用)'),
调用次数 = COUNT(*),
平均耗时微秒 = CAST(AVG(CAST(ElapsedUs AS float)) AS decimal(18,1)),
最长耗时微秒 = MAX(ElapsedUs),
最近调用 = CONVERT(char(19), MAX(LoggedAt), 120)
FROM dbo.Dyn_SearchLog
GROUP BY ISNULL(ProcName, N'(直接调用)');
PRINT N' ★ 生产建议：把 Dyn_SearchLog 之类的审计表接入监控，';
PRINT N' 对"耗时突增"或"含可疑字符的过滤器"告警。';
/* —————————————————————————
9.5 可疑输入模式检测（注入尝试的迹象）
————————————————————————— */
PRINT N'';
PRINT N'── 9.5 注入尝试检测 ──────────────────────────────────────────';
SELECT
日志ID = LogId,
时间 = CONVERT(char(19), LoggedAt, 120),
过滤器 = LEFT(FilterJson, 60),
判定 = CASE
WHEN FilterJson LIKE N'%–%' THEN N'⚠ 含 SQL 注释符 –'
WHEN FilterJson LIKE N'%''%' THEN N'⚠ 含单引号'
WHEN FilterJson LIKE N'%OR 1=1%' THEN N'⚠ 经典注入特征'
WHEN FilterJson LIKE N'%UNION%' THEN N'⚠ 含 UNION'
WHEN FilterJson LIKE N'%DROP%' THEN N'⚠ 含 DROP'
WHEN FilterJson LIKE N'%;%' THEN N'⚠ 含分号'
ELSE N'✓ 正常'
END
FROM dbo.Dyn_SearchLog
WHERE FilterJson LIKE N'%–%' OR FilterJson LIKE N'%''%'
OR FilterJson LIKE N'%OR 1=1%' OR FilterJson LIKE N'%UNION%'
OR FilterJson LIKE N'%DROP%' OR FilterJson LIKE N'%;%'
ORDER BY LogId;
PRINT N' ★ 上表应能看到第 04 节故意注入的两个载荷 —— 这就是"看见攻击"的能力。';
/* —————————————————————————
9.6 权限检查：当前身份能做什么（部署前自检）
————————————————————————— */
PRINT N'';
PRINT N'── 9.6 当前身份与权限自检 ────────────────────────────────────';
SELECT
当前用户 = USER_NAME(),
当前登录 = SUSER_NAME(),
[是否sysadmin] = CASE WHEN IS_SRVROLEMEMBER('sysadmin') = 1 THEN N'是' ELSE N'否' END,
/* ★ 用 STRING_AGG 替代老式的 FOR XML PATH('') 拼串写法（更简洁）*/
数据库角色 = ISNULL((
SELECT STRING_AGG(r.name, N',')
FROM sys.database_role_members rm
JOIN sys.database_principals r ON r.principal_id = rm.role_principal_id
JOIN sys.database_principals u ON u.principal_id = rm.member_principal_id
WHERE u.name = USER_NAME()
), N'(无)');
PRINT N' ★ 部署动态 SQL 存储过程前，务必确认：';
PRINT N' · 是否需要 EXECUTE AS / 模块签名（否则普通用户会撞 229）';
PRINT N' · 调用者是否被授予了 EXECUTE 权限';
PRINT N' · 命令是否被授予了不必要的基表权限（最小权限原则）';
GO
/* ===========================================================================
【10】决策清单 —— 什么时候用哪种方案
===========================================================================
这是全篇的"速查表"。遇到需求时，从上往下对照。
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【10】决策清单';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'';
PRINT N'── 10.1 按需求选方案 ─────────────────────────────────────────';
SELECT
需求 = 需求,
推荐方案 = 推荐方案,
理由 = 理由
FROM (VALUES
(N'条件完全固定', N'静态 SQL', N'零注入面、自动参数化、可读性最好'),
(N'条件可选（少量组合）', N'静态 SQL + bit 开关参数', N'一条语句应对所有组合，计划稳定（见 03.3 做法①）'),
(N'条件可选（大量组合）', N'动态拼接结构 + sp_executesql 传参', N'计划最贴合谓词（见 04.2）'),
(N'IN 列表来自用户多选', N'STRING_SPLIT → #temp 表 + EXISTS', N'列表作为"关系"而非"文本"（见 04.2，注意不能用表变量）'),
(N'排序列/方向来自用户', N'白名单映射 + QUOTENAME', N'标识符绝不能直接用用户输入（见 05.4）'),
(N'表名/列名需要动态', N'白名单表 + QUOTENAME 双层', N'默认拒绝（见 05.2 / 05.4）'),
(N'需要跨权限访问基表', N'EXECUTE AS OWNER 或模块签名', N'后者权限更精确（见 06.3 / 06.4）'),
(N'深分页（万亿级）', N'键集分页（WHERE key > @last）', N'OFFSET 代价线性增长（见 08.1）'),
(N'分区表 + 日期过滤', N'分区键用受控字面量，其它参数化', N'参数化会失去分区裁剪（见 08.2）'),
(N'计划不稳定（嗅探）', N'拆过程分派 > OPTIMIZE FOR > RECOMPILE', N'按稳定性与开销权衡（见 08.4）'),
(N'计划缓存膨胀', N'开 optimize for ad hoc workloads', N'性价比最高（见 07.4）')
) v(需求, 推荐方案, 理由);
PRINT N'';
PRINT N'── 10.2 动态 SQL 上生产前的检查清单 ─────────────────────────';
SELECT
检查项 = 检查项, 通过标准 = 通过标准
FROM (VALUES
(N'1. 值是否全部参数化', N'SQL 文本里没有来自外部的字符串/数字字面量'),
(N'2. 标识符是否走白名单', N'表名/列名/排序方向都能在代码里找到映射表或 CASE'),
(N'3. 是否有 QUOTENAME 兜底', N'拼接标识符处都有 QUOTENAME(…,''['') 或 ''"' + ''''),
(N'4. 入参是否校验', N'长度/范围/枚举/方向都有检查，非法输入直接拒绝'),
(N'5. 权限是否最小化', N'普通用户只能 EXECUTE 过程，不能直接读写基表'),
(N'6. 执行上下文是否正确', N'动态 SQL 需要的权限已通过 EXECUTE AS / 签名解决'),
(N'7. 是否有审计', N'记录过滤器摘要、耗时、调用者，异常可追溯'),
(N'8. 分页是否防深翻页', N'有 PageSize 上限；大表用键集分页'),
(N'9. 缓存是否会膨胀', N'已评估；建议开启 optimize for ad hoc workloads'),
(N'10. 错误处理是否完整', N'TRY/CATCH，记录 @sql 原文与 ERROR_LINE'),
(N'11. SQL 文本类型是否 nvarchar', N'所有承载 SQL 的变量声明为 nvarchar，字面量加 N 前缀'),
(N'12. 是否有性能基线', N'上线前后有实际执行计划与耗时对比')
) v(检查项, 通过标准);
PRINT N'';
PRINT N'── 10.3 常见反模式速查 ───────────────────────────────────────';
SELECT 反模式 = 反模式, 后果 = 后果, 正确做法 = 正确做法
FROM (VALUES
(N'EXEC(''… '' + @userInput + '' …'')', N'SQL 注入', N'sp_executesql + 参数'),
(N''' AND col IN ('' + @list + '')''', N'SQL 注入 + 类型问题', N'STRING_SPLIT + #temp + EXISTS'),
(N''' ORDER BY '' + @userCol', N'注入 / 盲注', N'白名单映射 + QUOTENAME'),
(N''' FROM '' + @userTable', N'注入 + 越权', N'白名单表 + QUOTENAME'),
(N'@sql varchar(max)', N'中文乱码 / 214', N'@sql nvarchar(max)'),
(N'动态 SQL 里引用外层表变量', N'1087 变量未声明', N'改用 #temp 表'),
(N'动态 SQL 里引用外层 #temp', N'（可行，但要注意作用域）', N'确保 #temp 在外层创建'),
(N'只在 EXEC 里拼日期字面量、不做校验', N'格式注入 / 语义错误', N'用强类型变量 + CONVERT(…,112) + 校验'),
(N'用 DBCC FREEPROCCACHE 做性能对比', N'拖垮全实例', N'按句柄清理或只统计 LIKE 匹配')
) v(反模式, 后果, 正确做法);
GO
/* ===========================================================================
【11】自验证 V1 ~ V16
===========================================================================
★ 本节必须【整体是一个批次】—— 中间不能有 GO，
否则 @pass / @fail 变量会被切断，报 137「必须声明标量变量 @pass」。
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【11】自验证 V1 ~ V16';
PRINT N'═══════════════════════════════════════════════════════════════════';
DECLARE @pass int = 0, @fail int = 0;
DECLARE @msg nvarchar(400);
/* — V1：演示对象全部存在 — */
IF OBJECT_ID(N'dbo.Dyn_AllowedTable', N'U') IS NOT NULL
AND OBJECT_ID(N'dbo.Dyn_AllowedColumn', N'U') IS NOT NULL
AND OBJECT_ID(N'dbo.Dyn_SearchLog', N'U') IS NOT NULL
AND OBJECT_ID(N'dbo.Dyn_InjectionDemo', N'U') IS NOT NULL
AND OBJECT_ID(N'dbo.Dyn_ResultCheck', N'U') IS NOT NULL
BEGIN SET @pass += 1; PRINT N' V1 ✓ 五个演示表全部存在'; END
ELSE BEGIN SET @fail += 1; PRINT N' V1 ✗ 有演示表缺失'; END
/* — V2：白名单内容完整（6 表 / 20 列）— */
DECLARE @tblCnt int = (SELECT COUNT(*) FROM dbo.Dyn_AllowedTable WHERE IsEnabled = 1);
DECLARE @colCnt int = (SELECT COUNT(*) FROM dbo.Dyn_AllowedColumn WHERE IsEnabled = 1);
IF @tblCnt = 6 AND @colCnt = 20
BEGIN SET @pass += 1; PRINT N' V2 ✓ 白名单完整：' + CAST(@tblCnt AS nvarchar(5)) + N' 表 / ' + CAST(@colCnt AS nvarchar(5)) + N' 列'; END
ELSE BEGIN SET @fail += 1; PRINT N' V2 ✗ 白名单数量不符：' + CAST(@tblCnt AS nvarchar(5)) + N' / ' + CAST(@colCnt AS nvarchar(5)); END
/* — V3：注入靶场表未被删除（证明 DROP 注入失败）— */
IF OBJECT_ID(N'dbo.Dyn_InjectionDemo', N'U') IS NOT NULL
BEGIN SET @pass += 1; PRINT N' V3 ✓ 注入靶场表仍在 —— DROP 注入被阻止'; END
ELSE BEGIN SET @fail += 1; PRINT N' V3 ✗ 靶场表被删除，注入成功！'; END
/* — V4：靶场秘密行仍可被合法读出（说明表未被破坏）— */
DECLARE @secretCnt int = (SELECT COUNT(*) FROM dbo.Dyn_InjectionDemo WHERE IsSecret = 1);
IF @secretCnt = 1
BEGIN SET @pass += 1; PRINT N' V4 ✓ 靶场数据完整（含 1 条秘密行）'; END
ELSE BEGIN SET @fail += 1; PRINT N' V4 ✗ 靶场数据异常，秘密行数 = ' + CAST(@secretCnt AS nvarchar(5)); END
/* — V5：参数化确实挡住了注入 — */
DECLARE @inj nvarchar(100) = N'SKU-0001'' OR ''1''=''1';
DECLARE @hit int = (SELECT COUNT(*) FROM dbo.Dyn_InjectionDemo WHERE Sku = @inj);
IF @hit = 0
BEGIN SET @pass += 1; PRINT N' V5 ✓ 参数化挡住注入（命中 0 行，未短路）'; END
ELSE BEGIN SET @fail += 1; PRINT N' V5 ✗ 参数化失效，命中 ' + CAST(@hit AS nvarchar(5)) + N' 行'; END
/* — V6：白名单函数正确放行/拒绝 — */
IF dbo.fn_Dyn_IsSafeIdentifier(N'erp', N'SalesOrder', N'OrderNo') = 1
AND dbo.fn_Dyn_IsSafeIdentifier(N'erp', N'SalesOrder', N'Password') = 0
AND dbo.fn_Dyn_IsSafeIdentifier(N'dbo', N'sysusers', N'name') = 0
BEGIN SET @pass += 1; PRINT N' V6 ✓ 白名单函数：放行合法列、拒绝非法列'; END
ELSE BEGIN SET @fail += 1; PRINT N' V6 ✗ 白名单函数判定错误'; END
/* — V7：动态查询过程存在 — */
IF OBJECT_ID(N'dbo.usp_Dyn_SearchOrders_Dynamic', N'P') IS NOT NULL
BEGIN SET @pass += 1; PRINT N' V7 ✓ 动态查询构建器过程存在'; END
ELSE BEGIN SET @fail += 1; PRINT N' V7 ✗ 动态查询构建器过程缺失'; END
/* — V8：动态过程能正确返回数据 — */
DECLARE @cntHK int;
DECLARE @sqlV8 nvarchar(500) =
N'SELECT @c = COUNT(*) FROM erp.SalesOrder WHERE CountryCode = @cc;';
EXEC sys.sp_executesql @sqlV8, N'@c int OUTPUT, @cc char(2)', @c = @cntHK OUTPUT, @cc = N'HK';
IF @cntHK = 1200
BEGIN SET @pass += 1; PRINT N' V8 ✓ 动态 SQL 返回值正确（HK = 1200）'; END
ELSE BEGIN SET @fail += 1; PRINT N' V8 ✗ 动态 SQL 返回值错误：' + CAST(@cntHK AS nvarchar(10)); END
/* — V9：计划复用被验证（参数化条目数远少于执行次数）—
★ 注意：计划缓存是【实例共享】的，其它会话也可能留下同名语句，
所以这里不做绝对值断言，而是验证"复用确实发生"这一事实：
参数化的探针语句执行了 20 次，缓存条目应【远少于】20。
（同时用 LIKE 限定到本脚本的探针标记，避免统计到无关语句）*/
DECLARE @reuseCnt int =
(SELECT COUNT(*) FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) t
WHERE t.text LIKE N'%dyn_cache_probe_B%');
IF @reuseCnt BETWEEN 1 AND 5
BEGIN SET @pass += 1;
PRINT N' V9 ✓ 参数化计划复用（20 次执行 → ' + CAST(@reuseCnt AS nvarchar(5)) + N' 份计划）'; END
ELSE BEGIN SET @fail += 1;
PRINT N' V9 ✗ 参数化复用异常：条目数 = ' + CAST(@reuseCnt AS nvarchar(5)); END
/* — V10：审计日志已记录 — */
DECLARE @logCnt int = (SELECT COUNT(*) FROM dbo.Dyn_SearchLog);
IF @logCnt > 0
BEGIN SET @pass += 1; PRINT N' V10 ✓ 审计日志已记录 ' + CAST(@logCnt AS nvarchar(5)) + N' 条'; END
ELSE BEGIN SET @fail += 1; PRINT N' V10 ✗ 审计日志为空'; END
/* — V11：审计日志中捕获到了注入载荷 — */
DECLARE @evilCnt int =
(SELECT COUNT(*) FROM dbo.Dyn_SearchLog
WHERE FilterJson LIKE N'%–%' OR FilterJson LIKE N'%DROP%' OR FilterJson LIKE N'%OR 1=1%');
IF @evilCnt > 0
BEGIN SET @pass += 1; PRINT N' V11 ✓ 审计捕获到 ' + CAST(@evilCnt AS nvarchar(5)) + N' 条可疑输入'; END
ELSE BEGIN SET @fail += 1; PRINT N' V11 ✗ 未捕获到可疑输入（第 04 节的注入载荷应被记录）'; END
/* — V12：权限演示对象已清理干净 — */
DECLARE @leftover int = (
(SELECT COUNT(*) FROM sys.database_principals WHERE name IN (N'u_Dyn_TestUser', N'u_Dyn_CertReader'))
+ (SELECT COUNT(*) FROM sys.certificates WHERE name = N'Cert_Dyn_SignedReader')
+ (SELECT COUNT(*) FROM sys.objects WHERE name IN
(N'usp_Dyn_SignedRead', N'usp_Dyn_SignedRead_Dyn', N'usp_Dyn_SignedRead_Dyn2', N'usp_Dyn_SniffDemo', N'usp_Dyn_Demo_Injection')));
IF @leftover = 0
BEGIN SET @pass += 1; PRINT N' V12 ✓ 权限演示对象已全部清理'; END
ELSE BEGIN SET @fail += 1; PRINT N' V12 ✗ 仍有 ' + CAST(@leftover AS nvarchar(5)) + N' 个残留对象'; END
/* — V13：数据库主密钥存在（模块签名所需）— */
IF EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
BEGIN SET @pass += 1; PRINT N' V13 ✓ 数据库主密钥存在'; END
ELSE BEGIN SET @fail += 1; PRINT N' V13 ✗ 数据库主密钥缺失'; END
/* — V14：erp.SalesOrder 基础数据规模符合预期 — */
DECLARE @soCnt int = (SELECT COUNT(*) FROM erp.SalesOrder);
IF @soCnt = 6000
BEGIN SET @pass += 1; PRINT N' V14 ✓ erp.SalesOrder 行数正确（6000）'; END
ELSE BEGIN SET @fail += 1; PRINT N' V14 ✗ erp.SalesOrder 行数异常：' + CAST(@soCnt AS nvarchar(10)); END
/* — V15：sp_executesql 输出参数可用 — */
DECLARE @sumV15 decimal(18,2);
DECLARE @sqlV15 nvarchar(500) =
N'SELECT @s = SUM(NetAmountUsd) FROM erp.SalesOrder WHERE CountryCode = @cc;';
EXEC sys.sp_executesql @sqlV15, N'@s decimal(18,2) OUTPUT, @cc char(2)',
@s = @sumV15 OUTPUT, @cc = N'FR';
IF @sumV15 > 0
BEGIN SET @pass += 1; PRINT N' V15 ✓ 输出参数正常（FR 合计 = ' + CAST(@sumV15 AS nvarchar(30)) + N'）'; END
ELSE BEGIN SET @fail += 1; PRINT N' V15 ✗ 输出参数异常'; END
/* — V16：动态 SQL 无法访问外层表变量（复现 1087，确认规则成立）— */
DECLARE @tblVar table (x int);
INSERT INTO @tblVar (x) VALUES (1);
DECLARE @v16Ok bit = 0;
BEGIN TRY
EXEC sys.sp_executesql N'SELECT * FROM @tblVar;';
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1087 SET @v16Ok = 1;
END CATCH
IF @v16Ok = 1
BEGIN SET @pass += 1; PRINT N' V16 ✓ 确认规则：动态 SQL 看不到外层表变量（1087）'; END
ELSE BEGIN SET @fail += 1; PRINT N' V16 ✗ 预期 1087 未出现，规则可能已变化'; END
/* — 汇总 — */
PRINT N'';
PRINT N'───────────────────────────────────────────────────────────────';
PRINT N' 自验证结果：' + CAST(@pass AS nvarchar(5)) + N' 通过 / '
+ CAST(@fail AS nvarchar(5)) + N' 失败（共 16 项）';
PRINT N'───────────────────────────────────────────────────────────────';
/* — 写入校验表 — */
DELETE FROM dbo.Dyn_ResultCheck WHERE CheckKey = N'V_SUMMARY';
INSERT INTO dbo.Dyn_ResultCheck (CheckKey, CheckValue)
VALUES (N'V_SUMMARY', CAST(@pass AS nvarchar(5)) + N' pass / ' + CAST(@fail AS nvarchar(5)) + N' fail');
IF @fail = 0
PRINT N' ★ 全部通过 —— 本机环境与脚本文档一致，所有结论均已实测确认。';
ELSE
PRINT N' ⚠ 存在失败项，请检查上方 V1~V16 明细。';
GO
/* ===========================================================================
【12】收尾
=========================================================================== */
PRINT N'';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N'【12】一句话总结';
PRINT N'═══════════════════════════════════════════════════════════════════';
PRINT N' 动态 SQL 的安全 = 让"值"走参数 + 让"结构"走白名单。';
PRINT N' · 值 → sp_executesql 强类型参数（第 01 / 03 节）';
PRINT N' · 结构 → 白名单映射 + QUOTENAME（第 05 节）';
PRINT N' · 兜底 → 最小权限 / 模块签名（第 06 节）';
PRINT N' · 规模 → 键集分页 + 分区裁剪 + 计划复用（第 07 / 08 节）';
PRINT N'';
PRINT N' 记住一句话：';
PRINT N' 静态 SQL 能做到的，绝不用动态 SQL；';
PRINT N' 必须动态时，凡是"值"一律参数化，凡是"结构"一律白名单。';
PRINT N'';
PRINT N' ★ 本脚本全部结论均在 SQL Server 2025 (17.0.1135.8) Enterprise 上实测确认。';
PRINT N'###################################################################';
PRINT N'## 脚本执行完毕';
PRINT N'###################################################################';
PRINT N' ? 全过程 12 节执行完成。若第 11 节显示"16 通过 / 0 失败"，则本机环境与文档一致。';
GO
评论前必须登录！
注册