Stairway to TSQL Beyond The Basics Level 9 Dynamic TSQL Code
通往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时，您需要了解动态代码如何打开SQL注入攻击的可能性。在本文中，我解释了为什么您可能希望使用动态TSQL，以及如何生成动态TSQL。我还将探索SQL注入，并讨论如何在动态TSQL代码中避免SQL注入攻击。
什么是动态TSQL，为什么要使用它?
什么是动态TSQL ?动态TSQL是一种代码，在每次运行时都可能不同。它是一组动态生成并执行的TSQL代码。生成的动态代码是基于批处理中的某些条件或参数创建的。当“条件或参数”不同时，TSQL代码会产生不同的TSQL来执行。
当您希望以编程方式决定您需要基于数据库表中的参数和/或数据的TSQL时，通常使用动态TSQL。动态TSQL的用途是无穷无尽的。这里有两个例子，您可能想使用动态TSQL:
•您希望用户从下拉列表中选择一些标准，这可能导致查询以不同的方式运行，比如排序顺序
•您的应用程序不知道要运行到运行时的表的名称。
由于TSQL语言不允许您将变量或参数用于特定的表或列名，因此可以使用动态TSQL。
为了更好地理解动态TSQL，我们来看看几个例子。
创建简单的动态TSQL
关于如何创建动态TSQL的第一个示例，请考虑以下情况。假设您有一个应用程序，用户界面允许用户从下拉列表中选择要读取的表。因此，每当有人使用该接口时，他们可以选择一个不同的表来返回数据。对于这个示例假设这个用户界面显示表信息从AdventureWorks2012 AdventureWorks2012.Sales数据库和用户选择。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代码来返回adventurework . sales的前10条记录的方法。SalesOrderDetail表。
---何阳美
处理更复杂的动态SQL服务器的要求 [U1]
有些时候你需要编写一些更复杂的动态TSQL。作为一个DBA的情况,我可能需要的是当我想生成代码来执行某种数据库的维护。当我需要建立动态TSQL数据库维护的目的时我通常读系统视图,然后生成一个脚本,该脚本将显示和/或执行。假设您是一名DBA,接管维护一个数据库,你想删除几个测试表中创建一个数据库。开始的表都有名称前缀“测试”。演示如何读取系统。表视图和生成适当的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中的代码包含三个不同的部分。第一部分创建一个数据库称为强啡肽,然后创建4个不同的表,其中两个“测试”开始。这两个表,先从“测试”代码的第二部分是我的动态TSQL代码。最后一节的代码清理删除我创建测试数据库。
如果你回顾第二节中的代码首先你会发现动态TSQL代码打印运行delete语句,然后删除第一节中创建的测试表我。我通过处理一个WHILE循环在寻找不同的表,从字符串“测试”开始。为每个表我发现从“测试”我构造一个删除命令存储在变量@CMD。然后我显示DELETE语句使用PRINT语句,立即紧随其后的是执行语句使用执行语句。最后一节,第三节清理掉DNYA数据库。
为了测试这个代码我建议你单独运行每个部分从第一节开始。运行第一节审查后强啡肽数据库,并验证有四个强啡肽数据库中的表。接下来第二部分运行。当您运行这个章节中,你将会看到两个消息显示在标签在查询分析器中窗口的消息。这两个语句所示是两个动态生成和执行DELETE语句。一旦你完成第二部分中的代码运行,回去复习强啡肽数据库中的表。如果你使用SQL Server Management Studio中的对象资源管理器,别忘了刷新。或者,您可以选择从系统。表视图。你应该现在发现只有两个表存在,和两个表删除那些与“测试”开始。一旦你做了什么验证第2部分中的代码执行,我将跑第三节中的代码清理。这段代码将把强啡肽数据库。
这是一个非常简单的例子,如何检查元数据和生成动态TSQL行。DBA在许多次,它会派上用场了解如何编写TSQL生成TSQL代码的代码。
避免SQL注入
你可能听说过动态TSQL是邪恶的。动态TSQL的邪恶的部分是,它开辟了一个SQL注入攻击的可能性。SQL注入是一个黑客技术,恶意用户设法利用免费的表单数据输入字段的使用。这些恶意用户试图将额外的TSQL代码插入一个数据输入字段之外的数据输入字段本来是如何被使用。通过插入TSQL代码可以愚弄系统返回数据,他们原本不应该得到,或更糟的是,针对SQL Server数据库的额外TSQL命令运行。取决于您的应用程序的权限下运行,SQL注入攻击可以插入数据到你的数据库表,删除一个表,或者更糟的是设置一个新的登录,系统管理员的权利。
演示如何受到动态TSQL SQL注入攻击如果不妥善处理,让我先创建一个数据库和一个表与清单3中的代码。我将使用这个数据库和表来演示如何将动态TSQL容易受到SQL注入攻击。
---蔡锦薇
清单3：创建数据库和表为了演示SQL注入攻击
清单3中的代码创建一个名称为DYNA的数据库，然后创建并填充一个名称为产品的表的4行数据。
假设我的应用程序有一个数据选择屏幕，在这最终用户可以输入一个包含一个产品名称的文本字符串，然后应用程序将返回所有记录包含输入的文本字符串的产品表。该应用程序通过传递文本字符串，使用户进入到一个名称为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的代码需要一个参数，生成可变长字符串变量@CMD，它离开存储过程打开SQL注入攻击。我用清单6中的代码通过执行GetProducts存储过程可以证明这个。
EXEC GetProducts 'Red%'' and ID = 1 --';
清单6：代码揭露GetProducts存储过程很容易受到SQL注入攻击。
如果你回顾清单6中的代码，你可以看到我通过附加一些字符另外还有红色的字符串到我的存储过程GetProducts中。通过我加的这些额外的字符允许我限制我的查询只返回在产品名称列有1的ID值的并且有“红”的产品。让我的存储过程在让我注入额外的字符参数@enteredtext中使用未经编辑的文字，转换为参数来执行原本不打算用于GetProducts代码的存储过程的其他动作。
在我最后一个例子我展示了一个利用动态TSQL在mygetproducts存储过程的无损的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注入有破坏性的使用，但它确实让我利用GetProduct存储过程中的@ enteredtext参数来返回客户表的所有列数据。为了完成这个我加入了“';SELECT * FROM Product;--” 我的字符串参数。请注意，我添加了两个破折号（“--”）在我额外的字符串的末尾。这允许我注释出存储过程在参数之后可能包含的字符或代码。
我的最后一个例子让我演示一个破坏性的TSQL注入攻击。在清单8中看我的破坏性TSQL注入命令检查代码。
EXEC GetProducts 'Red'' ;DROP TABLE Product;--';
---李茜茜
清单8：破坏TSQL注射exec命令
在清单8中，我添加了一个删除语句到@电子邮件参数。在这个例子中，我删除了客户表。如果我运行清单8中的代码，它将删除客户表
如何对付SQL注入攻击
没有人想要有自己的SQL注入攻击的代码由一个妥协。为了对SQL注入攻击的战斗时，你应该考虑以下TSQL代码点的应用开发：
•避免SQL注入攻击是不使用动态SQL的最好方式
•编辑用户输入参数的特殊字符，如分号和评论
让你的参数•只要需要支持用户输入的数据
•如果你必须使用动态SQL，然后使用参数化SQL执行TSQL使用sp_execute动态TSQL，而不是执行。
•收紧安全只允许最小的权利需要执行动态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。
•添加一组语句设置变量@ WildCardParm将通配符（%）在开始和结束的“enteredtext参数。
•如何改变字符串”命令被执行死刑。而不是使用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注入攻击？
•exec
•sp_executesql
答案:
问题1：
正确答案是A.避免SQL注入的最好方式是不允许动态TSQL代码在您的应用程序在所有。。
问题2：
正确答案是E，以上所有。使用SQL注入，恶意用户可以执行许多不同的SQL操作。这样的命令可以执行取决于用于运行动态TSQL命令该帐户的权利。如果应用程序的帐户具有管理员权限，SQL注入攻击可以用户想要做什么。
问题3：
正确答案是B.利用sp_executesql你可以通过你的用户输入数据使用的参数到你的参数化TSQL代码。
本文是对T-SQL楼梯部分：除了基本的楼梯
注册到我们的RSS提要并在我们发布一个新级别的楼梯时得到通知！
---陈婷
[U1]漏译Dealing with More Complex Dynamic SQL Server Requirements
原文链接：http://www.sqlservercentral.com/articles/Stairway+Series/113118/
浙公网安备 33010602011771号