动态 SQL：技术、安全与优化

在本指南中，我将解释动态 SQL 的关键要点，包括构建与执行动态查询的基本技巧、应用场景、安全注意事项、性能优化策略以及常见用例。

动态 SQL 之所以令人兴奋，是因为它是一种强大的技术，可在运行时构建并执行 SQL 语句。动态 SQL 使开发人员能够编写更灵活、更具适应性的查询，在执行过程中根据不同的输入、条件与逻辑作出响应。

如果您是 SQL 新手，建议先学习我们的 Introduction to SQL 课程或 Intermediate SQL 课程打好基础。另外，我认为可下载的 SQL Basics Cheat Sheet 十分实用，因为其中汇总了最常见的 SQL 函数，便于随时查阅。

什么是动态 SQL？

动态 SQL 指的是在运行时构建并执行的 SQL 语句，而不是预先硬编码。此方法可以根据程序执行时定义的可变输入或逻辑来构造并运行 SQL 查询。

使用动态 SQL，您将 SQL 语句作为字符串创建，并通过特定的执行函数或结构来运行它们。与在代码中预定义并直接嵌入的静态 SQL 不同，动态 SQL 通过允许在运行时确定查询结构（如表名、筛选条件或排序逻辑）而提供更大的灵活性。这使其在应用运行前无法确定查询需求的场景中成为一种多用途工具。

为何使用动态 SQL？

现代应用程序往往需要能够适应不断变化的业务需求与用户驱动逻辑的数据库查询。动态 SQL 在实现更具响应性与可定制的数据库交互方面至关重要。

动态 SQL 的常见用例包括：生成带可选筛选条件的报表、构建带可自定义参数的搜索界面、自动化重复的数据库任务，以及以编程方式管理诸如表和索引等对象。它让开发人员能够编写强大且可适应的应用程序，减少硬编码逻辑的需求。通过适应不断变化的需求并支持用户驱动的行为，动态 SQL 在以灵活性、自动化与数据驱动决策为优先的环境中尤为重要。

动态 SQL 与静态 SQL

了解静态 SQL 与动态 SQL 的差异，有助于您选择合适的方法。下面是执行查询时在两者之间做出选择前应了解的关键区别。

关键差异

静态 SQL 语句是硬编码的，运行时不会发生变化。对数据分析师而言，这意味着语句具有可预测的性能且更易维护。另一方面，动态 SQL 在运行时构建并执行，提供更高的灵活性，但代价是增加了复杂性与潜在的安全风险。我已在下表中总结了这些差异。

何时选择动态 SQL

当查询结构需在运行时才能确定，或对灵活性要求较高时，动态 SQL 更为合适。这些情形包括：

• 动态报表： 需要在运行时选择列、筛选条件或排序方式。

• 可变数据库对象： 如多租户应用或使用时间分区表的数据仓库，表或列名需动态确定。

• 复杂搜索与筛选： 当高级搜索需基于多项用户自定义条件组装查询。

• 执行 DDL 语句： 对于许多平台上无法用静态 SQL 完成的操作，如 CREATE、DROP、GRANT 或 ALTER。

然而，在动态场景下使用静态 SQL 的局限包括：

• 无法处理运行时的查询结构或数据库对象变化。
• 当需要多条相似查询时，会增加代码重复。
• 缺乏对用户驱动逻辑的适应性，降低应用灵活性。

SQL Server、PostgreSQL 与 Oracle 中的动态 SQL

执行动态 SQL 涉及创建查询字符串，并使用合适的数据库函数安全高效地运行它们。本节将概述动态执行的主要方法与最佳实践。

使用 EXEC 命令

大多数数据库平台提供 EXEC 或 EXECUTE 命令来运行动态 SQL。

例如，以下查询在 SQL Server 中运行动态 SQL，但它不支持参数化，因而增加了 SQL 注入的风险。

-- Declare a variable to hold the SQL statement
DECLARE @sql NVARCHAR(MAX)

-- Build dynamic SQL with a WHERE condition for Department = 'Sales'
SET @sql = 'SELECT * FROM Employees WHERE Department = ''Sales'''

-- Execute the dynamic SQL
EXEC(@sql)技术支持

建议学习我们的 Introduction to SQL Server 和 Intermediate SQL Server 课程，进一步了解在 SQL Server 中处理数据的方法。

对于 PostgreSQL，EXECUTE 命令应在 PL/pgSQL 代码块中使用，例如在函数或 DO 块内。例如：

-- Executes a static SQL string using dynamic SQL (PL/pgSQL context)
DO $
BEGIN
  EXECUTE 'SELECT * FROM employees';
END;
$;技术支持

在 Oracle 中，除非配合 INTO 或游标使用，否则 EXECUTE IMMEDIATE 不能直接从 SELECT 语句返回结果集。

-- Executes a dynamic SQL statement (usually DML or DDL) at runtime
DECLARE
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM employees' INTO v_count;
  DBMS_OUTPUT.PUT_LINE(v_count);
END;技术支持

使用 sp_executesql 及同类机制

SQL Server 提供 sp_executesql 存储过程，可通过参数执行动态 SQL，提升安全性与性能：

-- Declare variables to hold SQL command and parameter value
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @city NVARCHAR(75)

-- Set parameter value
SET @city = 'London'

-- Build parameterized dynamic SQL query
SET @sqlCommand = 'SELECT * FROM Person.Address WHERE City = @city'

-- Execute the query safely using sp_executesql with parameter definition and binding
EXECUTE sp_executesql @sqlCommand, N'@city NVARCHAR(75)', @city = @city技术支持

其他数据库也使用类似结构来降低 SQL 注入风险，并允许重用查询计划。例如：

• Oracle：EXECUTE IMMEDIATE

• PostgreSQL： 在 PL/pgSQL 中使用 EXECUTE

安全地使用参数化查询

参数化执行通过将查询逻辑与用户输入分离来防止 SQL 注入。例如，下面的查询使用 sp_executesql 来实现参数化。

-- Declare a variable to hold the dynamic SQL query and initialize Employee ID
DECLARE @sql NVARCHAR(MAX), @empId INT = 1001;

-- Build parameterized SQL query using a named parameter (@ID)
SET @sql = 'SELECT * FROM Employees WHERE EmployeeID = @ID';

-- Execute the SQL query securely using sp_executesql with parameter declaration and binding
EXEC sp_executesql @sql, N'@ID INT', @ID = @empId;技术支持

如何构建动态 SQL 语句

既然您已经了解了执行动态 SQL 查询的不同方法，下面我将通过一个示例演示如何构建并执行动态 SQL 命令。以下查询在 SQL Server 中执行。

步骤 1：定义变量

设置变量，以根据 Department 列的值 'Sales' 动态筛选 Employees 表中的行。

-- Prepare dynamic SQL variables to filter the Employees table
DECLARE @tableName NVARCHAR(100) = 'Employees';
DECLARE @filterColumn NVARCHAR(100) = 'Department';
DECLARE @filterValue NVARCHAR(100) = 'Sales';
DECLARE @sql NVARCHAR(MAX);技术支持

步骤 2：构建 SQL 语句

构建一条参数化的动态 SQL 查询，从指定表中选出给定列等于指定值的所有记录。

-- Construct a safe dynamic SQL query with table and column names 
-- Use QUOTENAME to prevent injection.
SET @sql = 
  'SELECT * FROM ' + QUOTENAME(@tableName) + 
  ' WHERE ' + QUOTENAME(@filterColumn) + ' = @val';技术支持

步骤 3：使用参数执行

现在使用参数 @val 安全地绑定筛选值 'Sales' 来执行该动态 SQL 查询。

-- Execute the dynamic query with @val parameter securely passed as 'Sales'.
EXEC sp_executesql @sql, N'@val NVARCHAR(100)', @val = @filterValue;技术支持

我建议您学习我们的课程：Writing Functions and Stored Procedures in SQL Server，以理解 SQL 函数的结构与顺序，从而实现优化查询。

安全注意事项与缓解策略

虽然动态 SQL 提供了灵活性，但也带来更高的风险，尤其与 SQL 注入 相关。下面我们讨论常见漏洞与保护动态 SQL 的最佳实践。

SQL 注入漏洞

基于字符串的动态 SQL 尤其易受 SQL 注入攻击，恶意输入可能改变查询的预期逻辑。攻击者可利用构造不当的 SQL 操纵数据、提取敏感信息或破坏数据库完整性。

例如，下面的查询就容易受到 SQL 注入攻击，因为它在未参数化的情况下直接将用户输入拼接进 SQL 字符串。

DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM Users WHERE Username = ''' + @input + '''';
EXEC(@sql);
-- If @input = 'admin' OR '1'='1', this returns all users.技术支持

纵深防御方法

以下方法可确保您的动态 SQL 查询具备必要的安全措施：

• 参数化： 使用参数化查询是最有效的防御措施。它确保用户输入被严格视为数据，而非可执行代码。

• 对象清理： 当表或列名必须动态化时，将其与允许列表进行校验，并使用 QUOTENAME() 等函数防止注入。

• 最小权限原则： 限制数据库用户权限，使用于动态 SQL 的账户仅拥有最低必要的访问权。即使出现漏洞，也能限制可能造成的损害，降低未授权数据暴露或修改的风险。

• 输入校验： 始终在将用户输入用于 SQL 语句之前，对其进行格式与取值范围的校验与约束。对不符合要求的输入予以拒绝或清理，进一步缩小攻击面。

性能优化技巧

动态 SQL 可能因运行时编译与计划生成带来性能开销。要提升动态 SQL 查询的性能，我建议采用以下查询优化技巧。

计划缓存与重用

当执行动态 SQL 时，数据库引擎可能会缓存执行计划以便重用，从而提升重复查询的性能。然而，计划重用在查询参数化时最为有效，而不是每次都构造唯一的 SQL 字符串。

使用带参数的 sp_executesql 等结构，可使数据库识别相似查询并重用执行计划，降低编译开销、提升效率。SQL Server 允许将计划指南附加到动态 SQL，上下文中可影响优化与执行计划，在复杂场景下获得更好性能。

批处理优化

将多个操作合并为单个执行批次可减少应用与数据库之间的往返次数，降低事务开销并提高吞吐量。

维护与调试策略

由于其运行时特性，动态 SQL 的维护与调试可能比静态 SQL 更复杂。以下是我建议用于日志与错误处理的实用策略，以提升可见性、可追溯性与可靠性。

执行日志

记录动态 SQL 的执行对于审计与故障排查都很重要。捕获实际运行的 SQL 语句（尤其在运行时生成的情形），有助于定位性能瓶颈、意外逻辑与安全问题。请遵循以下做法以确保合适的日志策略：

• 审计追踪： 通过审计表或触发器等日志机制记录动态 SQL 执行与数据变更。
• 故障排查： 存储已执行的 SQL 语句、输入参数、执行时间与用户信息，有助于更快定位错误源头、性能瓶颈或未授权访问。
• 性能监控： 通过分析执行日志，识别慢查询、频繁执行模式或资源密集型操作，以便主动进行性能调优。

错误处理

动态 SQL 执行应置于健壮的错误处理逻辑中，以捕获并优雅地应对失败。例如，下面的查询会将有用的诊断信息记录到表中。

BEGIN TRY
    -- Attempt to execute the parameterized dynamic SQL
    EXEC sp_executesql @sql, @paramDef, @paramVal;
END TRY
BEGIN CATCH
    -- On error, log the message, line, and procedure name with timestamp into ErrorLog table
    INSERT INTO ErrorLog (ErrorMessage, ErrorLine, ErrorProcedure, LoggedAt)
    VALUES (ERROR_MESSAGE(), ERROR_LINE(), ERROR_PROCEDURE(), GETDATE());
END CATCH;技术支持

此外，错误处理还应考虑以下做法：

• 始终捕获完整的错误上下文：消息、行号、过程名称与时间戳。
• 使用日志将错误与导致它们的确切 SQL 关联起来。
• 在生产系统中，基于记录的错误设置告警机制。

结语

要充分发挥动态 SQL 的作用，开发人员必须采用稳健的安全实践。同时，应通过计划重用、批处理与执行日志来谨慎管理性能。此外，建议采取平衡的方法，将静态 SQL 的安全与速度与动态 SQL 的适应性相结合。

展望未来，数据库引擎的改进（如更智能的执行计划与更先进的 SQL 运行时功能）将使动态 SQL 更高效、更安全、并且更易于大规模管理。接下来，欢迎学习我们的 Associate Data Analyst in SQL 职业路径课程，并订阅 DataFramed 播客，其中有许多精彩节目，例如这期由 SQL 的共同发明者参与的专访：50 Years of SQL with Don Chamberlin。