楼梯到T-SQL：超越基础水平9：动态T-SQL代码
楼梯到T-SQL：超越基础水平9：动态T-SQL代码
作者：Gregory Larsen，2016/07/29（第一次发布：2014/07/23）
本文是楼梯系列的一部分：T-SQL楼梯：超越基础
接下来从他的楼梯到T-SQL DML，Gregory Larsen涵盖了更高级的T-SQL语言，如子查询。
什么是动态TSQL，为什么要使用它？
动态的TSQL是什么？动态TSQL是一种每次你运行它都可能生成并执行生成不同的TSQL代码，生成的代码是基于某些条件来创建的。在批处理参数中，当“条件或参数”不同时，TSQL代码会生成不同的TSQL来执行。你通常使用动态TSQL,当你希望以编程方式确定TSQL字数据库表中所需要的，或参数和/或数据时，动态TSQL的用途是无穷无尽的。下面是你可能想要使用动态TSQL的两个示例：
1、你想要用管理员身份去从下拉列表中选择一些标准，这可能会使查询的顺序不同于排列顺序。
2、你的应用程序不知道要运行的这个表的名称。
由于TSQL语言不允许你使用变量或参数特定的表或列名，因此可以使用动态TSQL.
为了更好地理解动态tsql，让我们来看几个例子。
创建简单的动态数据TSQL
对于如何创建动态TSQL的第一个示例，让我们考虑下面的情况。
假设你有一个应用程序，用户界面允许用户从下拉列表中选择想要读取的表。因此，每当有人使用这个接口时，他们就可以选择不同的表来返回数据。对于本例，我们假设这个用户界面显示来自 AdventureWorks2012 的表格信息：数据库，用户选择表中列出的清单中的代码使用动态TSQL代码返回前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);
清单1：简单的动态TSQL示例
清单中的代码首先声明一个变量名称@CMD来保存，将要构建的动态选择语句和@Table变量保存表名。然后将表变量设置为
让我来构建我的实际动态
TSQL语句使用SET语句，这个语句设置了变量
对包含选择的连接字符串值的@CMD语句和@Table变量值，然后执行我的动态，使用执行的@CMD变量中包含的TSQL语句声明。
为了进一步测试清单一中的动态TSQL，可以尝试一下方法，利用AdventureWork2012 的不同，通过更改“集合”中的代码表@Table,使用语句的语句。
处理更复杂的动态SQL服务器需求
有时你需要编写一些更复杂的动态TSQL。
在这种情况下。可能需要做的是什么时候想要生成代码来执行某种数据库维护。当需要为数据库维护构建动态的TSQL时，通常会读取system视图，然后生成显示或执行的脚本。假设你是一个数据库管理员，它接管了一个数据库，你想要删除几个测试表在数据库中创建的表中都有以前缀“Test”开头的名称，以演示如何读取系统。表视图和生成适当的DELETE语句，让我们看看清单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;
清单2：删除测试表的动态代码
清单2中的代码包含三个不同的部分。第一部分创建了一个名为DYNA的数据库，然后创建4个不同的表，其中两个表以“Test”开头，这两个以“Test”开头的表，表一想用动态TSQL代码删除这些表，代码的第二部分是我的动态TSQL代码，代码的最后一部分通过删除测试数据库来清理。
如果你查看第二节中的代码，你会发现动态TSQL代码首先打印出它运行的delete语句，然后删除我在第一部分中创建的测试表。我通过处理一个WHILE循环，在寻找不同的表时，从字符串“Test”开始，我构造了一个储存在变量@CMD中的DELETE命令。然后，我使用PRINT语句来显示DELETE语句，然后使用EXECUTE语句执行语句。最后一部分，通过删除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 Injection攻击可以将数据插入到数据库表中，删除表，或更糟糕的是，使用sysadmin权限设置新的登录。
为了演示动态TSQL如果不能正确管理SQL注入攻击，请先用清单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);
清单3：创建数据库和表以演示SQL注入攻击
清单3中的代码创建一个数据库名称DYNA，然后创建并填充具有4行数据的表名称Product。
假设我的应用程序有一个数据选择屏幕，最终用户可以输入一个包含在ProductName中的文本字符串，然后应用程序将返回包含输入的文本字符串的所有Product表格记录。 应用程序通过将用户输入的文本字符串传递到存储过程名称GetProducts，然后从存储过程返回的数据显示给用户。 存储过程GetProducts的编码如清单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);
清单4：存储过程GetUserName的代码
通过查看清单4中的存储过程GetProducts，您可以看到此存储过程接受单个参数@EnteredText此参数用于动态创建存储在变量@CMD中的TSQL语句。 然后执行该变量。 （请注意，这个过程可能是在不使用动态SQL的情况下编写的。我在这里使用动态SQL来说明潜在的问题。）
为了演示如何使用这个存储过程，我可以通过运行清单5中的代码来执行它。
EXEC GetProducts 'Red';
清单5正常执行存储过程GetUserName
清单5中的代码调用GetProducts存储过程，并生成报表1中的结果。
ProductName Price ------------------------------------------------------------------- ------------Red Wagon 12.99 Red Barn
报告1：使用清单5中的代码调用GetUserName的结果
因为我的存储过程GetProducts中的代码使用一个参数并生成varchar变量@CMD，因此存储过程打开以进行SQL注入攻击。 我可以通过使用清单6中的代码执行GetProducts存储过程来演示这一点。
EXEC GetProducts 'Red%'' and ID = 1 --';
清单6：显示GetProducts存储过程如何易受SQL注入的代码
如果您查看清单6中的代码，您可以看到我将一些其他字符附加到字符串“Red”到我的存储过程GetProducts。我传递的这些附加字符允许我限制我的查询，只返回ProductName列中具有“Red”的产品，ID值为1.通过允许我的存储过程在@EnteredText参数中使用未编辑的文本，可以让我在该参数中注入额外的字符，以使代码执行其他最初未在GetProducts存储过程中使用的操作。
在我的最后一个例子中，我使用myGetProducts存储过程中的动态TSQL向您展示了非破坏性SQL注入攻击。大多数SQL注入攻击正在尝试从系统中获取额外的数据，或者只是想破坏您的数据库。我们再来看一下清单7中的代码。
EXEC GetProducts 'Red'' ;SELECT * FROM Product;--';
清单7：SQL注入以返回其他数据
如果我运行清单7中的代码，它会生成两个结果集。 第一个结果集具有零行，第二个集合是报表2中的文本：
ID ProductName
Price
----------- ------------------------------
1 Red Wagon 12.99
2 Red Barn 23.18
2 Farm Animals 7.59
2 Toy Solders 17.76
报告2：运行清单7中的代码时的文本结果
如果比较结果1中找到的GetProduct存储过程的正常执行结果与结果2中找到的结果，您可以看到清单7中的代码生成了一些其他输出列，我的存储过程最初没有设计为显示，但是由于SQL注入攻击而显示。
清单7中的示例仍然不是对SQL Injection的破坏性使用，但它允许我利用GetProduct存储过程的@EnteredText参数来返回Client表的所有列的数据。为了完成这个，我添加了“'; SELECT * FROM Product; - ”字符串到我的参数。请注意，在我的附加字符串末尾添加了两个破折号（“ - ”）。这允许我在参数后面注释掉我的存储过程可能包含的任何字符或代码。
对于我的最后一个例子，让我执行一个破坏性的TSQL注入攻击。查看清单8中的代码以查看我的破坏性TSQL注入命令。
EXEC GetProducts 'Red'' ;DROP TABLE Product;--';
清单8：破坏性TSQL注入EXEC命令
在清单8中，我向@EMAIL参数添加了一个DELETE语句。 在这个例子中，我删除了客户端表。 如果我运行清单8中的代码，它将删除Client表
如何打击SQL注入攻击
没有人想要让他们的代码受到SQL注入攻击的危害。为了防止SQL Injection攻击，您应该在开发TSQL应用程序代码时考虑以下几点：
•避免SQL Injection攻击的最佳方法是不使用动态SQL
•编辑用户输入的特殊字符参数，如分号和注释
•只有在需要支持用户输入的数据时，才能使参数发生
•如果必须使用动态SQL，则使用使用sp_execute sql的参数化TSQL来执行动态TSQL而不是EXEC。
•加强安全性，只允许执行动态TSQL所需的最少权限。
如果您的应用规范要求您需要构建一些包含动态TSQL的代码，那么使用参数化的TSQL是打击SQL注入的好方法。在清单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;
清单9：使用参数化的TSQL
在清单9中，我更改了我的GetProducts存储过程，以使用sp_executesql来执行我的动态TSQL。在这个修改后的存储过程中，我做了以下更改：
•将字符串@CMD更改为不再包含命令字符串中的@EnteredText变量的值。而是将用户输入的文本引入名为@EnteredParm的变量中。
•添加了一个SET语句来设置变量@WildCardParm将通配符（％）放在@EnteredText参数的开头和结尾。
•更改了如何执行字符串@CMD。而不是使用EXEC语句来执行字符串，我使用过程sp_executesql。
通过进行这两个更改，用户输入的文本现在将作为参数驱动查询执行。通过这样做，用户不能再尝试在我的GetProduct存储过程中注入额外的TSQL代码。要验证这一点，请运行清单5,6,7和8所示的四个不同的命令。但是由于我已经删除了我的产品表，所以我首先需要用数据重新创建它。为此，我需要首先运行清单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);
清单9：创建并填充Client表
在运行清单9以重新创建我的产品表之后，我可以运行列表5,6,7和8来证明我解决了我的SQL注入问题。 当您运行这些不同的命令时，您将发现只有清单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注入攻击的风险？
•执行
•sp_executesql
回答：
问题1：
正确的答案是避免SQL注入的最佳方法是不允许您的应用程序中的动态TSQL代码。 。
问题2：
正确的答案是e，以上所有。使用SQL Injection，恶意用户可以执行许多不同的SQL操作。它们可以执行的命令类型取决于用于运行动态TSQL命令的帐户的权限。如果应用程序帐户具有sysadmin权限，则SQL Injection攻击可以执行用户想要的任何操作。
问题3：
正确的答案是b。通过使用sp_executesql，您可以传递用户使用参数输入数据到参数化的TSQL代码。
本文是T-SQL楼梯的一部分：超越基础楼梯
