翻译：超越基本的9级:动态的t - sql代码
通往t - sql的阶梯:超越基本的9级:动态的t - sql代码
By 格雷戈里·拉森(Gregory Larsen)
该系列这篇文章是楼梯系列的一部分:通往t - sql的楼梯:超越基础
从他的阶梯到t - sql DML,Gregory Larsen涵盖了t - sql语言的更高级的方面，如子查询。
有时需要编写TSQL代码来创建特定的TSQL代码并执行它。当您这样做时，您正在创建动态的TSQL代码。您用来创建动态TSQL的代码可能很简单，也可能很复杂。在编写动态TSQL时，您需要了解动态代码如何打开SQL注入攻击的可能性。在本文中，我解释了为什么您可能希望使用动态TSQL，以及如何生成动态TSQL。我还将探索SQL注入，并讨论如何在动态TSQL代码中避免SQL注入攻击。
什么是动态TSQL，为什么要使用它?
什么是动态TSQL ?动态TSQL是一种代码，在每次运行时都可能不同。它是一组动态生成并执行的TSQL代码。生成的动态代码是基于批处理中的某些条件或参数创建的。当“条件或参数”不同时，TSQL代码会产生不同的TSQL来执行。
当您希望以编程方式决定您需要基于数据库表中的参数和/或数据的TSQL时，通常使用动态TSQL。动态TSQL的用途是无穷无尽的。这里有两个例子，您可能想使用动态TSQL:
您希望用户从下拉列表中选择一些标准，这样可能导致查询以不同的方式运行，比如排序顺序
您的应用程序不知道要运行到运行时的表的名称。
由于TSQL语言不允许您将变量或参数用于特定的表或列名，因此可以使用动态TSQL。
为了更好地理解动态TSQL，我们来看看几个例子。
创建简单的动态T SQL
关于如何创建动态TSQL的第一个示例，请考虑以下情况。假设您有一个应用程序，用户界面允许用户从下拉列表中选择要读取的表。因此，每当有人使用该接口时，他们可以选择一个不同的表来返回数据。对于这个示例假设这个用户界面显示表信息从AdventureWorks2012 AdventureWorks2012.Sales数据库和用户选择。SalesOrderDetail表。清单1中的代码显示了一种使用动态TSQL代码来返回adventurework . sales的前10条记录的方法。SalesOrderDetail表。
-- 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示例
清单1中的代码首先声明了一个变量名@ cmd，以保存将要构建的动态选择语句和保存表名的@ table变量。然后我设置@ table变量AdventureWorks.Sales.SalesOrderDetail。要构建实际的动态TSQL语句，我使用SET语句。该语句将变量@ cmd设置为包含SELECT语句和@ table变量值的连接字符串值。然后，使用execute语句执行包含在@ cmd变量中的动态TSQL语句。
进一步测试动态TSQL在清单1中,您可以尝试使用不同的AdventureWork2012表代码中通过改变“@ table = "声明使用AdventureWorks2012.Sales.Sales。OrderHeader表。
处理更复杂的动态SQL Server需求
有时您需要编写一些更复杂的动态TSQL。作为一名DBA，我可能需要这样做的一种情况是，当我想要生成代码来执行某种数据库维护时。当需要为数据库维护目的构建动态TSQL时，通常会读取系统视图，然后生成显示和/或执行的脚本。假设您是一个DBA，已经接管了维护数据库，并且您希望删除在数据库中创建的几个测试表。这些表都有以前缀“Test”开头的名称。来演示如何阅读sys。表视图并生成适当的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代码。最后一部分代码通过删除我创建的测试数据库来清理。
如果您回顾第2部分中的代码，您将发现动态TSQL代码首先打印出它运行的delete语句，然后删除第1节中创建的测试表。我通过处理WHILE循环来实现这一点，同时寻找从字符串“Test”开始的不同的表。对于每一个表，我发现从“Test”开始，我构造一个被存储在变量@ cmd的DELETE命令。然后使用PRINT语句显示DELETE语句，然后使用EXECUTE语句立即执行语句。最后一部分，第3节通过删除DNYA数据库来清理。
为了测试这段代码，我建议您独立运行每个部分，从第1节开始。运行第1节检查DYNA数据库并验证DYNA数据库中有4个表。接下来第二部分运行。当您运行此部分时，您将看到在Query Analyzer窗口中消息选项卡中显示的两个消息。显示的两个语句是动态生成和执行的两个DELETE语句。完成第2部分中的代码后，返回并查看DYNA数据库中的表。如果您在SQL Server Management Studio中使用对象资源管理器，请不要忘记刷新。或者，您可以从sys中选择。表视图。您现在应该发现，只有两个表存在，而删除的两个表是那些以“Test”开头的表。在验证第2节执行的代码之后，我将在第3节中运行代码来清理。这段代码将删除DYNA数据库。
这是一个非常简单的示例，说明如何检查行元数据并生成动态TSQL。作为一名DBA，在很多情况下，了解如何编写生成TSQL代码的TSQL代码非常方便。
避免SQL注入
您可能听说过动态TSQL是邪恶的。动态TSQL的邪恶部分是它打开了SQL注入攻击的可能性。SQL注入是一种黑客技术，恶意用户试图利用自由表单数据输入字段的使用。这些恶意用户试图将额外的TSQL代码插入到数据输入字段之外，超出了数据输入字段最初的用途。通过插入TSQL代码，他们可以将系统欺骗到他们原本不应该得到的数据，或者更糟的是，在SQL Server数据库上运行额外的TSQL命令。根据您的应用程序运行的权限，SQL注入攻击可以将数据插入到数据库表中，删除一个表，或者更糟糕的是，设置一个使用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行数据创建并填充一个表名产品。
假设我的应用程序有一个数据选择屏幕，其中终端用户可以输入一个ProductName中包含的文本字符串，然后应用程序将返回包含输入文本字符串的所有产品表记录。应用程序通过将用户输入的文本字符串传递给存储过程名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 23.18
报告1:使用清单5中的代码调用GetUserName
因为我的存储过程GetProducts中的代码获取一个参数并生成varchar变量@ cmd，因此它将存储过程打开以进行SQL注入攻击。我可以通过使用清单6中的代码执行GetProducts存储过程来演示这一点。
EXEC GetProducts 'Red%'' and ID = 1 --';
清单6:显示GetProducts存储过程如何容易受SQL注入影响的代码
如果您回顾清单6中的代码，您可以看到我将许多其他字符通过“红色”传递给存储过程GetProducts。我通过的这些附加字符允许我限制查询，只返回ProductName列中的“红色”的产品，并具有ID值为1。通过允许我的存储过程在@ enteredtext参数中使用未编辑的文本，我可以在该参数中注入额外的字符，以使代码执行其他的操作，而这些操作原本不是用于GetProducts存储过程的。
在我的最后一个示例中，我向您展示了使用myGetProducts存储过程中的动态TSQL进行的非破坏性SQL注入攻击。大多数SQL注入攻击都试图从您的系统中获取额外的数据，或者只是想要破坏您的数据库。为了进一步研究这个问题，我们来看看清单7中的代码。
EXEC GetProducts 'Red'' ;SELECT * FROM Product;--';
清单7:SQL注入，以返回额外的数据
如果我运行清单7中的代码，它将生成两个结果集。第一个结果集有0行，第二组是报告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中的代码时的文本结果
如果比较结果GetProduct正常执行的存储过程中发现结果1,结果发现在结果2中,可以看到清单7中的代码生成一些额外的输出列,存储过程并不是最初设计显示,但显示由于SQL注入攻击。
清单7中的示例仍然不是SQL注入的破坏性使用，但它确实允许我利用GetProduct存储过程的@ enteredtext参数来返回客户表所有列的数据。为了完成这个任务，我添加了“';从产品中选择*;—”字符串到我的参数。注意，我在附加字符串的末尾添加了两个d灰烬(“—”)。这让我可以对存储过程可能包含在参数之后的任何字符或代码进行注释。
对于我的最后一个示例，让我执行一个破坏性的TSQL注入攻击。查看清单8中的代码，查看破坏性的TSQL注入命令。
EXEC GetProducts 'Red'' ;DROP TABLE Product;--';
清单8:破坏性TSQL注入EXEC命令
在清单8中，我向@ email参数添加了一个DELETE语句。在这个示例中，我删除了客户端表。如果我运行清单8中的代码，它将删除客户端表
如何对抗SQL注入攻击
没有人希望自己的代码受到SQL注入攻击的影响。为了对抗SQL注入攻击，在开发TSQL应用程序代码时，应该考虑以下几点:
避免SQL注入攻击的最好方法是不使用动态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;
添加一个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:创建和填充客户端表
在我运行清单9重新创建我的产品表之后，我可以运行清单5、6、7和8来证明我解决了SQL注入问题。当您运行这些不同的命令时，您将发现只有清单5返回数据。其他人不返回数据的原因是，现在生成的动态TSQL正在寻找包含额外用户输入值的ProductName值，当然这与产品表中的产品列值不匹配。
摘要
没有人希望对他们的手表进行SQL注入攻击。当然，确保它不会发生的最佳解决方案不是在应用程序中使用动态SQL代码。如果您的应用程序确实需要动态SQL，希望本文提供了一些关于如何将与SQL注入关联的风险最小化的建议。下一次编写动态SQL时，请确保采取步骤避免SQL注入攻击的可能性。
问题和答案
在本节中，您可以通过回答下列问题来回顾您对SQL注入的理解程度。
问题1:
避免SQL注入攻击的最佳方法是什么(最好的方法)?
不部署使用动态TSQL的TSQL代码吗
编辑用户输入用于支持SQL注入攻击的特殊字符的动态TSQL的数据
让用户尽可能短地输入动态TSQL的参数
使用参数化TSQL代码
问题2:
使用SQL注入附加(选择所有应用程序)，用户可以完成哪些事情?
返回应用程序不打算让用户选择的数据
将数据插入由应用程序不打算使用的表中
删除一个表
为新帐户提供sysadmin权限
上面所有的
问题3:
如果您打算部署包含在一个变量中的动态TSQL代码，那么这两种执行方法中的哪一种最好使用，以最小化SQL注入攻击的风险?
执行
sp_executesql
答案:
问题1:
正确的答案是a .避免SQL注入的最好方法是不允许应用程序中的动态TSQL代码。
问题2:
正确答案是e，所有这些。有了SQL注入，恶意用户可以执行许多不同的SQL操作。它们可以执行的命令类型取决于用于运行动态TSQL命令的帐户的权限。如果应用程序帐户有sysadmin权限，那么SQL注入攻击可以做任何用户想做的事情。
问题3:
正确的答案是b。通过使用sp_executesql，您可以通过参数化TSQL代码将用户输入数据输入数据。
这篇文章是通往t - sql的楼梯的一部分:除了基本的楼梯
