动态T-SQL与应用并发设计

# 动态 T-SQL 与应用并发设计 ## 1. 动态 T-SQL 安全考量 动态 SQL 是处理需要灵活性的存储过程的强大工具，但在使用时必须考虑安全问题，主要涉及引用对象的权限和接口规则两方面。 ### 1.1 引用对象的权限 动态 SQL 的调用范围与静态 SQL 不同，执行时会检查其中引用的所有对象的权限。为避免授权异常，执行动态 SQL 的用户要么直接有权限访问引用对象，要么模拟具有访问权限的用户。 由于调用动态 SQL 时上下文的改变会打破已建立的所有权链，这与静态 SQL 存储过程面临的挑战有所不同。若要管理权限层次，使用户能访问使用动态 SQL 的存储过程，但不能访问其引用的基表，可深入了解证书签名和 `EXECUTE AS` 子句。 ### 1.2 接口规则 开发人员常尝试在动态 SQL 中使用传递表名、列列表、`ORDER BY` 列表等可选参数，但从软件开发角度看，这些做法很危险，会导致数据库与应用程序紧密耦合，还可能扭曲存储过程的隐含输出契约，增加测试和维护难度。 一般规则是，不应从应用程序向存储过程传递任何数据库对象名称。若必须在存储过程中修改表名或其他对象名，可通过一组参数封装名称，而非让应用程序指定。 例如，有如下存储过程： ```sql CREATE PROC SelectDataFromTable @TableName nvarchar(200) AS BEGIN SET NOCOUNT ON; DECLARE @sql nvarchar(max); SET @sql = '' + 'SELECT ' + 'ColumnA, ' + 'ColumnB, ' + 'ColumnC ' + 'FROM ' + @TableName; EXEC(@sql); END; GO ``` 表名不能参数化，使用 `sp_executesql` 也无济于事。但在多数情况下，可传入存储过程的表名是有限的。若已知该存储过程仅使用 `TableA`、`TableB` 和 `TableC` 表，可重写存储过程： ```sql ALTER PROC SelectDataFromTable @UseTableA bit = 0, @UseTableB bit = 0, @UseTableC bit = 0 AS BEGIN SET NOCOUNT ON; IF ( CONVERT(tinyint, COALESCE(@UseTableA, 0)) + CONVERT(tinyint, COALESCE(@UseTableB, 0)) + CONVERT(tinyint, COALESCE(@UseTableC, 0)) ) <> 1 BEGIN RAISERROR('Must specify exactly one table', 16, 1); RETURN; END DECLARE @sql nvarchar(max); SET @sql = '' + 'SELECT ' + 'ColumnA, ' + 'ColumnB, ' + 'ColumnC ' + 'FROM ' + CASE WHEN @UseTableA = 1 THEN 'TableA' WHEN @UseTableB = 1 THEN 'TableB' WHEN @UseTableC = 1 THEN 'TableC' END EXEC(@sql); END; GO ``` 此版本的存储过程虽更复杂，但易理解。`IF` 块验证是否仅选择了一个表，`CASE` 表达式处理表名的动态选择。 若无法采用上述技术，且必须支持应用程序动态传递对象名，可使用 `QUOTENAME` 函数防止 SQL 注入。例如： ```sql ALTER PROC SelectDataFromTable @TableName nvarchar(200); AS BEGIN SET NOCOUNT ON; DECLARE @sql nvarchar(max); SET @sql = '' + 'SELECT ' + 'ColumnA, ' + 'ColumnB, ' + 'ColumnC ' + 'FROM ' + QUOTENAME(@TableName); EXEC(@sql); END; GO ``` 不过，这无法解决接口问题，修改数据库架构仍可能需修改应用程序代码。 ### 1.3 总结 使用动态 SQL 时，要确保正确使用，以平衡性能、可维护性和安全性。始终对查询进行参数化，切勿信任调用者的输入。 ## 2. 应用并发系统设计 单用户应用程序的表现和扩展性通常较好，但当多个实际终端用户同时访问系统时，可能会出现各种问题。并发是应用开发中较难处理的领域，问题常依赖于特定的时间安排，难以完全消除，但合理的前期设计可减少问题发生。 ### 2.1 并发问题分类 在数据库应用中，并发进程引发的问题通常分为以下三类： | 问题类型 | 描述 | 示例 | | ---- | ---- | ---- | | 数据覆盖 | 两个或多个用户同时编辑同一数据，一个用户的更改被另一个用户覆盖，可能导致数据丢失和数据库层面的数据损坏 | 销售点应用中，两个销售终端同时处理同一产品的销售，一个终端可能覆盖另一个终端的更改 | | 不可重复读 | 应用程序读取一组数据并进行计算，之后再次读取同一组数据时，数据已发生变化 | 分析系统的钻取报告中，用户点击汇总数据项后读取的详细数据与初始读取的数据不一致 | | 阻塞 | 一个进程写入数据时，另一个进