T-SQL进阶：超越基础 Level 9：动态T-SQL代码

By Gregory Larsen, 2016/07/29 (首次发表于: 2014/07/23)

关于系列

本文属于进阶系列：Stairway to T-SQL: Beyond The Basics

跟随Gregory Larsen的T-SQL DML进阶系列，其涵盖了更多的高级方面的T-SQL语言，如子查询。

---

有时您需要编写创建特定TSQL代码的TSQL代码并执行它。 执行此操作时，您将创建动态TSQL代码。 用于创建动态TSQL的代码可能很简单，或者可能很复杂。 编写动态TSQL时，您需要了解动态代码如何打开SQL注入攻击的可能性。 在本文中，我解释了为什么你可能想要使用动态TSQL以及如何生成动态TSQL。 我还将探索SQL注入，并讨论如何避免SQL注入攻击您的动态TSQL代码。

什么是动态TSQL以及为什么你想要使用它？

什么是动态TSQL？动态TSQL是每次运行它时潜在的代码。它是一批在运行中生成和执行的TSQL代码。基于批处理中的某些条件或参数创建的即时生成代码。当“条件或参数”不同时，TSQL代码会产生不同的TSQL来执行。

您希望以编程方式根据数据库表中的参数和/或数据来确定所需的TSQL时，通常使用动态TSQL。动态TSQL的用途是无止境的。以下是您可能希望使用动态TSQL的两个示例：

• 您希望用户从下拉列表中选择一些可能导致查询运行不同的条件，例如排序
• 您的应用程序不知道在运行之前要运行的表的名称

因为TSQL语言不允许您使用变量或参数到特定的表或列名称，因此可以使用动态TSQL。

为了更好地了解动态TSQL，我们来看几个例子。

创建简单的T SQL

对于如何创建动态TSQL的第一个例子，我们来考虑以下情况。 假设您有一个应用程序，用户界面允许用户从下拉列表中选择要读取的表。 因此，每次有人使用界面时，他们都可以选择一个不同的表，从中返回数据。 对于这个例子，我们假设这个用户界面显示了DataBase AdventureWorks2012中的Table information，用户选择了Table AdventureWorks2012.Sales.SalesOrderDetail。 Listing 1中的代码显示了一种使用动态TSQL代码从AdventureWorks.Sales.SalesOrderDetail表中返回TOP 10记录的方法。

-- Declare variable to hold dynamic TSQL code
DECLARE @CMD nvarchar(1000);
-- Declare name of table to read
DECLARE @Table nvarchar(125);
SET @Table = 'AdventureWorks2012.Sales.SalesOrderDetail';
-- Build dynamic TSQL Statement
SET @CMD = 'SELECT TOP 10 * FROM ' + @Table;
--Execute dynamic TSQL Statement
EXECUTE (@CMD);

Listing 1：简单动态TSQL示例

Listing 1中的代码首先声明一个变量名称@CMD来保存要构建的动态SELECT语句，并使用@Table变量来保存表名。 然后我将@Table变量设置为AdventureWorks.Sales.SalesOrderDetail。 要构建我实际的动态TSQL语句，我使用一个SET语句。 此语句将变量@CMD设置为包含SELECT语句和@TABLE变量值的级联字符串值。 然后我使用EXECUTE语句执行@CMD变量中包含的动态TSQL语句。

为了进一步测试Listing 1中的动态TSQL，您可以尝试通过修改“SET @ Table =”语句来在代码中使用AdventureWork2012中不同的表，以使用AdventureWorks2012.Sales.Sales.OrderHeader表。

处理更复杂的动态SQL Server服务要求

有时你需要编写一些更复杂的动态TSQL。 作为DBA，我可能需要这样做的情况之一是当我想生成代码来执行某种数据库维护。 当我需要构建动态TSQL以进行数据库维护时，通常会读取系统视图，然后生成显示和/或执行的脚本。 假设您是已经接管了数据库的DBA，并且您要删除在数据库中创建的多个测试表。 这些表都有以“Test”开头的名称。 为了演示如何读取sys.tables视图并生成相应的DELETE语句，我们来看看Listing 2中的代码。

-- Section 1: Create database and Sample Tables
USE master;
go
CREATE DATABASE DYNA;
GO
USE DYNA; 
GO
CREATE TABLE MyData1 (Id int, DataDesc varchar(100));
CREATE TABLE MyData2 (Id int, DataDesc varchar(100));
CREATE TABLE TestData1 (Id int, DataDesc varchar(100));
CREATE TABLE TestData2 (Id int, DataDesc varchar(100));
GO
-- Section 2: Dynamic TSQL code to generate script to delete Test tables
USE DYNA;
GO
DECLARE @TableName varchar(100);
DECLARE @CMD varchar(1000);
SELECT TOP 1 @TableName = name FROM sys.tables
WHERE name like 'Test%'
ORDER BY name;
WHILE @@ROWCOUNT > 0
BEGIN
    SELECT @CMD = 'DROP TABLE ' + @TableName + ';';
    PRINT @CMD
    EXECUTE(@CMD);
    SELECT TOP 1 @TableName = name FROM sys.tables
    WHERE name like 'Test%' and name > @TableName
    ORDER BY name;
END
-- Section 3: Cleanup 
USE master;
GO
DROP DATABASE DYNA;

Listing 2：删除测试表的动态代码

Listing 2中的代码包含三个不同的部分。第一部分创建一个名为DYNA的数据库，然后创建4个不同的表，其中两个表以“Test”开头。以“Test”开头的这两个表是要用动态TSQL代码删除的表。代码的第二部分是我的动态TSQL代码。最后一部分代码通过删除我创建的测试数据库进行清理。

如果您查看第2节中的代码，您将发现动态TSQL代码首先打印出运行的delete语句，然后删除我在第1节中创建的测试表。我通过处理一个WHILE循环，同时寻找不同的表从字符串“Test”开头。对于每个表，我发现以“Test”开头，我构造了存储在变量@CMD中的DELETE命令。然后通过使用PRINT语句显示DELETE语句，然后立即使用EXECUTE语句执行语句。最后一节，第3节通过删除DNYA数据库进行清理。

为了测试这个代码，我建议您从第1节开始，按照顺序独立运行每个部分。运行第1节后，查看DYNA数据库并验证DYNA数据库中有4个表。接下来运行第2节。运行此部分时，将在“查询分析器”窗口的“消息”选项卡中看到两条消息。显示的两个语句是动态生成和执行的两个DELETE语句。一旦完成了第2节中的代码，请返回并查看DYNA数据库中的表。如果您在SQL Server Management Studio中使用对象资源管理器，请不要忘记刷新。或者，您可以从sys.tables视图中进行选择。现在你应该会发现只有两个表存在，而删除的两个表是那些以“Test”开头的表。一旦完成验证第2部分中的代码执行后，我将运行第3节中的代码进行清理。该代码将删除DYNA数据库。

这个非常简单的例子说明了如何检查元数据行并生成动态TSQL。作为DBA，了解如何编写生成TSQL代码的TSQL代码将会多次派上用场。

避免SQL注入式攻击

你可能听说动态TSQL是邪恶的。动态TSQL之所以邪恶是因为提供了SQL注入式攻击的可能性。 SQL注入式攻击是一种黑客技术，恶意用户尝试利用自由格式数据输入字段。这些恶意用户尝试将额外的TSQL代码插入数据输入字段，使其超出了原始打算使用数据输入字段的方式。通过插入TSQL代码，他们可以愚弄系统返回原本不应该获得的数据，或者更糟的是，对SQL Server数据库运行附加的TSQL命令。根据您的应用程序运行的权限，SQL注入式攻击可以将数据插入到数据库表中，删除表，或更糟糕的是，使用sysadmin权限设置新的登录。

为了演示动态TSQL如果不能正确管理SQL注入攻击，请先用Lsting 3中的代码创建一个数据库和一个表。我将使用该数据库和表来演示动态TSQL是如何易受到攻击SQL注入攻击的。

USE master;
go 
CREATE DATABASE DYNA;
GO
USE DYNA;
GO
CREATE TABLE Product(ID int, 
                     ProductName varchar(100),
                     Price money);
INSERT INTO Product VALUES (1, 'Red Wagon', 12.99),
                           (2, 'Red Barn', 23.18),
                    (2, 'Farm Animals', 7.59),
                    (2, 'Toy Solders', 17.76);
                    

Listing 3: 创建数据库和表来演示SQL注入式攻击

Listing 3中的代码将创建一个名为DYNA的数据库，然后创建并填充具有4行数据名为Product的表。

假设我的应用程序有一个数据选择屏幕，最终用户可以输入一个包含在ProductName中的文本字符串，然后应用程序将返回包含输入的文本字符串的所有Product表格记录。 应用程序通过将用户输入的文本字符串传递到名为GetProducts的存储过程，然后将存储过程返回的数据显示给用户。 存储过程GetProducts的编码如Listing 4所示。

CREATE PROC GetProducts 
    (@EnteredText varchar (100))
AS  
DECLARE @CMD varchar(1000);
SET @CMD = 'SELECT ProductName, Price ' + 
           'FROM Product ' +
           'WHERE ProductName LIKE ''%' + 
           @EnteredText + '%''';
           PRINT @CMD
EXEC (@CMD);

通过查看Listing 4中的存储过程GetProducts，您可以看到此存储过程接受单个参数@EnteredText，此参数用于动态创建存储在变量@CMD中的TSQL语句。 然后执行该变量。 （请注意，这个过程可能是在不使用动态SQL的情况下编写的。我在这里使用动态SQL来说明潜在的问题。）

为了演示如何使用这个存储过程，我可以通过运行清单5中的代码来执行它。

EXEC GetProducts 'Red';

Listing 5：正常执行存储在Procedure中的GetUserName

Listing 5中的代码调用存储在Procedure的GetUserName，并返回Report 1中的结果。

ProductName                                                         Price
------------------------------------------------------------------- -------------
Red Wagon                                                           12.99
Red Barn                                                            23.18

Report 1：使用Listing 5中的代码调用GetUserName后的结果

因为我的存储过程GetProducts中的代码使用一个参数并生成varchar变量@CMD，因此存储过程打开以进行SQL注入攻击。 我可以通过使用Listing 6中的代码执行GetProducts存储过程来演示这一点。

EXEC GetProducts 'Red%'' and ID = 1 --';

Listing 6：用于暴露GetProducts存储过程是如何易受SQL注入的代码

如果您查看Listing 6中的代码，您可以看到我将一些其他字符附加到字符串“Red”后面到我的存储过程GetProducts。 我传递的这些附加字符允许我限制我的查询，只返回ProductName列中具有“Red”的产品，ID值为1.通过允许我的存储过程在@EnteredText参数中使用未编辑的文本，可以让我 在该参数中注入额外的字符，使代码执行其他最初未在GetProducts存储过程中使用的操作。

在我的最后一个例子中，我使用myGetProducts存储过程中的动态TSQL向您展示了非破坏性SQL注入攻击。 大多数SQL注入攻击正在尝试从系统中获取额外的数据，或者只是想破坏您的数据库。 我们再来看一下Listing 7中的代码。

EXEC GetProducts 'Red'' ;SELECT * FROM Product;--';

Listing 7：SQL注入式攻击返回额外的数据

如果我运行Listing 7中的代码，它会生成两个结果集。 第一个结果集具有零行，第二个集合是Report 2中的结果：

 ID          ProductName                                                 Price
----------- ------------------------------------------------------------ ---------------------
1           Red Wagon                                                    12.99
2           Red Barn                                                     23.18
2           Farm Animals                                                 7.59
2           Toy Solders                                                  17.76

Report 2：执行Listing 7后的结果

如果比较Report 1中找到的GetProduct存储过程的正常执行结果与Report 2中找到的结果，您可以看到Listing 7中的代码生成了一些其他的输出列，我的存储过程最初并没有设计为显示 ，但却由于SQL注入攻击而显示。

Listing 7中的示例仍然不是对SQL Injection的破坏性使用，但它允许我利用GetProduct存储过程的@EnteredText参数来返回Client表的所有列的数据。 为了完成这个，我添加了“'; SELECT * FROM Product; - ”字符串到我的参数。 请注意，在我的附加字符串末尾添加了两个破折号（“ - ”）。 这允许我在参数后面注释掉我的存储过程可能包含的任何字符或代码。

对于我的最后一个例子，我将执行一个破坏性的TSQL注入攻击。 查看Listing 8中的代码以查看我的破坏性TSQL注入命令。

EXEC GetProducts 'Red'' ;DROP TABLE Product;--';

Listing 8：破坏性的TSQL注入式攻击EXEC命令

在Listing 8中，我向@EMAIL参数添加了一个DELETE语句。 在这个例子中，我删除了客户端表。 如果我运行Listing 8中的代码，它将删除Client表。

如何防止SQL注入式攻击

没有人想要让他们的代码受到SQL注入攻击的危害。 为了防止SQL 注入式攻击，您应该在开发TSQL应用程序代码时考虑以下几点：

• 避免SQL注入式攻击的最佳方法是不使用动态SQL
• 编辑用户输入的特殊字符参数，如分号和注释
• 仅在需要支持用户输入的数据时才能使参数发生
• 如果必须使用动态SQL，则使用参数化的TSQL，使用sp_execute sql来执行动态TSQL而不是EXEC。
• 加强安全性，只允许执行动态TSQL所需的最少权限。

如果您的应用规范要求您需要构建一些包含动态TSQL的代码，那么使用参数化的TSQL是防止SQL注入的好方法。 在Listing 9中，我提供了一个如何修改我的GetUserName存储过程以使用参数化的TSQL的例子。

ALTER PROC GetProducts 
    (@EnteredText varchar (100))
AS  
DECLARE @CMD nvarchar(1000);
DECLARE @WildCardParm varchar(102);
SET @CMD = 'SELECT ProductName, Price ' + 
           'FROM Product ' +
           'WHERE ProductName LIKE @EnteredParm';
SET @WildCardParm = '%' + @EnteredText + '%';
EXEC sp_executesql @CMD,N'@EnteredParm varchar(100)',@EnteredParm=@WildCardParm;

Listing 9：使用参数化的TSQL

在Listing 9中，我更改了我的GetProducts存储过程，以使用sp_executesql来执行我的动态TSQL。在这个修改后的存储过程中，我做了以下更改：

将字符串@CMD更改为不再包含命令字符串中的@EnteredText变量的值。而是将用户输入的文本引入名为@EnteredParm的变量中。
添加了一个SET语句，设置变量@WildCardParm将通配符（％）放在@EnteredText参数的开头和结尾。
更改了字符串@CMD的执行方式。而不是使用EXEC语句来执行字符串，我使用过程sp_executesql。
通过进行这两个更改，用户输入的文本现在将作为参数驱动查询执行。通过这样做，用户不能再尝试在我的GetProduct存储过程中注入额外的TSQL代码。要验证这一点，请运行Listing 5,6,7和8所示的四个不同的命令。但是由于我已经删除了我的产品表，所以我首先需要用数据重新创建它。为此，首先我需要运行Listing 9中的代码。

CREATE TABLE Product(ID int, 
                     ProductName varchar(100),
                     Price money);
INSERT INTO Product VALUES (1, 'Red Wagon', 12.99),
                           (2, 'Red Barn', 23.18),
                    (2, 'Farm Animals', 7.59),
                    (2, 'Toy Solders', 17.76);
                    

Listing 9：创建并填充Client表

在运行Listing 9重新创建我的产品表之后，我可以运行Listing 5,6,7和8来证明我解决了我的SQL注入问题。 当您运行这些不同的命令时，您将发现只有Listing 5返回数据。 其他人不返回数据的原因是现在生成的动态TSQL正在寻找包含其他用户输入注释值的ProductName值，当然这与“Product”表中的任何Product列值不匹配。

总结

没有人想要别人在他们眼皮底下进行SQL注入式攻击。 当然，确保不会发生的最佳解决方案是使您的应用程序中没有动态SQL代码。 如果您的应用程序确实需要动态SQL，那么本文将为您提供一些有关如何最小化相关SQL注入式攻击风险的建议。 下次写动态SQL时，请确保采取措施避免SQL注入式攻击的可能性。

问题和答案

在本节中，您可以通过回答下列问题来回顾您对SQL注入的了解程度。

问题1：

避免SQL注入攻击的最佳方法是什么（最好的方法）？

• 不要部署使用动态TSQL的TSQL代码
• 编辑用户输入的动态TSQL中用于允许SQL注入攻击的特殊字符的数据
• 使用户输入的动态TSQL参数尽可能短
• 使用参数化的TSQL代码

问题2：

用户可以使用SQL注入附件来完成哪些事情（选择所有适用的内容）？

• 返回应用程序不希望用户选择的数据
• 将数据插入到应用程序不想要的表中
• 撤销一张表
• 为新帐户提供系统管理员权限
• 以上所有

问题3：

如果要部署变量中包含的动态TSQL代码，最好使用这两种执行方法中的哪一种来最大程度降低SQL注入攻击的风险？

• EXEC
• sp_executesql

答案：

问题1：

正确的答案是a。避免SQL注入式攻击的最佳方法是不允许您的应用程序中的动态TSQL代码。

问题2：

正确的答案是e，以上所有。使用SQL 注入式攻击，恶意用户可以执行许多不同的SQL操作。它们可以执行的命令类型取决于用于运行动态TSQL命令的帐户的权限。如果应用程序帐户具有sysadmin权限，则SQL注入式攻击可以执行用户想要的任何操作。

问题3：

正确的答案是b。通过使用sp_executesql，您可以传递用户使用参数输入数据到参数化的TSQL代码中。

---