翻译：通往T-SQL的阶梯:超越基本的9级:动态T-SQL代码。
通往T-SQL的阶梯:超越基本的9级:动态T-SQL代码。
作者Gregory Larsen, 2016/07/29(第一次出版:2014/07/23)
该系列
这篇文章是楼梯系列的一部分:T-SQL的阶梯:超越基础。
从他的楼梯到T-SQL DML，格雷戈里·拉森涵盖了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的前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把动态的SELECT语句,是构建和@ table变量来保存表名。 然后我设置@ table变量AdventureWorks.Sales.SalesOrderDetail。 建立我的实际动态TSQL语句使用一组语句。 这个语句设置变量@CMD连接字符串值包含一个SELECT语句和@ table变量值。 我然后执行动态TSQL语句中包含@CMD变量使用execute语句。
进一步测试动态TSQL在清单1中,您可以尝试使用不同的AdventureWork2012表在代码中通过改变“@ table = "声明中使用AdventureWorks2012.Sales.Sales.OrderHeader表。
处理更复杂的动态SQL服务器的需求
有些时候你需要编写一些更复杂的动态TSQL。 作为一个DBA的情况,我可能需要是当我想生成代码来执行某种数据库维护。 当我需要建立动态TSQL数据库维护的目的我通常读系统视图,然后生成一个脚本,该脚本将显示和/或执行。 假设您是一名DBA,接管维护一个数据库,你想删除几个测试表中创建一个数据库。 开始的表都有名称前缀“测试”。 演示如何读取sys.tables视图和生成适当的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代码。 最后一节的代码清理删除我创建测试数据库。
如果你回顾第二节中的代码首先你会发现动态TSQL代码打印运行delete语句,然后删除第一节中创建的测试表我。 我通过处理一个WHILE循环在寻找不同的表,从字符串“测试”开始。 为每个表我发现从“测试”开始构建一个存储在变量@CMD删除命令。 然后我显示DELETE语句使用PRINT语句,立即紧随其后的是执行语句使用执行语句。 最后一节,第三节清理掉DNYA数据库。
为了测试这个代码我建议你单独运行每个部分从第一节开始。 运行第一节审查后强啡肽数据库,并验证有四个强啡肽数据库中的表。 接下来第二部分运行。 当您运行这个章节中,你将会看到两个消息显示在标签在查询分析器中窗口的消息。 这两个语句所示是两个动态生成和执行DELETE语句。 一旦你完成第二部分中的代码运行,回去复习强啡肽数据库中的表。 如果你使用SQL Server Management Studio中的对象资源管理器,别忘了刷新。 或者,你可以选择从sys.tables视图。 你应该现在发现只有两个表存在,和两个表删除那些与“测试”开始。 一旦你做了什么验证第2部分中的代码执行,我将跑第三节中的代码清理。 这段代码将把强啡肽数据库。
这是一个非常简单的例子,如何检查元数据和生成动态TSQL行。 DBA在许多次,它会派上用场了解如何编写TSQL生成TSQL代码的代码。
避免SQL注入
你可能听说过动态TSQL是恶的。 动态TSQL的邪恶的部分是,它开辟了一个SQL注入攻击的可能性。 SQL注入是一个黑客技术,恶意用户设法利用免费的表单数据输入字段的使用。 这些恶意用户试图将额外的TSQL代码插入一个数据输入字段之外的数据输入字段本来是如何被使用。 通过插入TSQL代码可以愚弄系统返回数据,他们原本不应该得到,或更糟的是,针对SQL Server数据库的额外TSQL命令运行。 取决于您的应用程序的权限下运行,SQL注入攻击可以插入数据到你的数据库表,删除一个表,或者更糟的是设置一个新的登录,系统管理员的权利。
演示如何受到动态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中的代码创建了一个数据库名称强啡肽,然后创建和填充一个表名产品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
通过回顾存储过程GetProducts在清单4中可以看到这个存储过程接受单个参数@EnteredText然后使用这个参数来动态创建一个存储在变量@CMD TSQL声明。 然后执行这个变量。 (注意,这个过程可能是不使用动态SQL编写的。 我在这里使用动态SQL说明潜在的问题。)
为了演示如何使用这个存储过程让我通过运行清单5中的代码执行它。
EXEC GetProducts 'Red';
Listing 5 Executing stored procedure GetUserName normally
The code in Listing 5 calls the GetProducts stored procedure and produces the results found in Report 1.
ProductName Price ------------------------------------------------------------------- ------------- Red Wagon 12.99 Red Barn 23.18
报告1:调用的结果GetUserName在清单5中使用代码
因为我的存储过程的代码GetProducts需要一个参数,生成varchar变量@CMD它打开了存储过程的SQL注入攻击。 我可以通过执行来证明这一点GetProducts存储过程与清单6中的代码。
EXEC GetProducts 'Red%'' and ID = 1 --';
单6:代码公开如何GetProducts存储过程容易受到SQL注入
如果你检查代码在清单6中可以看到我通过其他一些附加到字符串中字符“红色”我的存储过程GetProducts。 我通过了这些额外的字符允许我限制查询只返回产品“红色”ProductName列和有一个ID值为1。 通过允许我存储过程使用未经编辑的文本@EnteredText参数允许我注入额外字符参数引起的代码执行其他操作不是最初打算使用的GetProducts存储过程。
我在最后一个例子向您展示了一个无损使用动态SQL注入攻击TSQL在我GetProducts存储过程。 大多数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中的代码文本的结果
如果你比较的正常执行的结果GetProduct存储过程中发现结果1,结果发现在结果2中,可以看到清单7中的代码生成一些额外的输出列,存储过程并不是最初设计显示,但显示由于SQL注入攻击。
我的例子在清单7中还不是破坏性使用SQL注入,但它确实让我利用@EnteredText参数的GetProduct存储过程返回所有列的数据客户端表。 为此我添加了”' ;SELECT * FROM Product;--“字符串参数。 请注意,我添加了两个破折号(“-”)结束时我的额外的字符串。 这使得我注释掉任何字符或代码存储过程可能包括参数后。
我的最后一个例子让我执行一个破坏性的TSQL注入攻击。 回顾清单8中的代码,看看我的破坏性TSQL注入命令。
EXEC GetProducts“红”,删除表产品;——”;
清单8:破坏性TSQL注入EXEC命令
在清单8中,我添加一个DELETE语句参数。 在这个例子中我删除了客户端表。 如果我运行清单8中的代码将删除客户端表
如何打击SQL注入攻击
没有人想要他们的代码被一个SQL注入攻击。 为了打击SQL注入攻击时应该考虑以下几点发展TSQL应用程序代码:
- 避免SQL注入攻击的最好办法就是不使用动态SQL
- 编辑用户输入参数等特殊字符的分号和评论
- 只要让你的参数需要支持用户输入数据
- 如果你必须使用动态SQL,那么使用参数化TSQL使用sp_execute sql执行动态TSQL,而不是执行。
- 加强安全只允许执行动态TSQL所需最小的权利。
如果您的应用程序的规范要求,您需要构建一些代码,其中包含动态TSQL然后使用参数化TSQL对抗SQL注入是一个很好的方法。 在清单9中,我已经提供了我如何修改的一个例子GetUserName使用参数化TSQL存储过程。
报告2:当运行清单7中的代码文本的结果
如果你比较的正常执行的结果GetProduct存储过程中发现结果1,结果发现在结果2中,可以看到清单7中的代码生成一些额外的输出列,存储过程并不是最初设计显示,但显示由于SQL注入攻击。
我的例子在清单7中还不是破坏性使用SQL注入,但它确实让我利用@EnteredText参数的GetProduct存储过程返回所有列的数据客户端表。 为此我添加了”' ;SELECT * FROM Product;--“字符串参数。 请注意,我添加了两个破折号(“-”)结束时我的额外的字符串。 这使得我注释掉任何字符或代码存储过程可能包括参数后。
我的最后一个例子让我执行一个破坏性的TSQL注入攻击。 回顾清单8中的代码,看看我的破坏性TSQL注入命令。
EXEC GetProducts“红”,删除表产品;——”;
清单8:破坏性TSQL注入EXEC命令
在清单8中,我添加一个DELETE语句参数。 在这个例子中我删除了客户端表。 如果我运行清单8中的代码将删除客户端表
如何打击SQL注入攻击
没有人想要他们的代码被一个SQL注入攻击。 为了打击SQL注入攻击时应该考虑以下几点发展TSQL应用程序代码:
- 避免SQL注入攻击的最好办法就是不使用动态SQL
- 编辑用户输入参数等特殊字符的分号和评论
- 只要让你的参数需要支持用户输入数据
- 如果你必须使用动态SQL,那么使用参数化TSQL使用sp_execute sql执行动态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参数。
- 改变字符串@CMD是如何执行的。 而不是使用EXEC语句执行字符串,我使用了过程sp_executesql。
通过这两个改变了用户输入的文本将会作为一个参数驱动执行查询。 通过这种方法,用户可以不再试图为我注入额外的TSQL代码GetProduct存储过程。 为了验证这一点,运行四个不同的命令,如清单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代码
- 编辑用户输入数据用于动态TSQL允许SQL注入攻击的特殊字符
- 让用户输入参数动态TSQL尽可能短
- 使用参数化TSQL代码
问题2:
什么样的东西可以用SQL注入附加用户完成(选择所有适用)?
- 返回数据,应用程序并没有打算让用户选择
- 将数据插入表应用程序不是有意的
- 删除一个表
- 提供系统管理员权利一个新帐户
- 上面所有的
问题3:
如果你要部署动态TSQL代码包含在一个变量中,这两种执行方法最好使用以减少SQL注入攻击的风险吗?
- 执行
- sp_executesql
答案:
问题1:
正确的答案是a。避免SQL注入的最好方式是不允许动态TSQL代码在您的应用程序。
问题2:
正确的答案是e,上面所有的。 与SQL注入恶意用户可以执行许多不同的SQL操作。 他们可以执行的命令取决于账户的权利用来运行动态TSQL命令。 如果应用程序帐户系统管理员权利,SQL注入攻击可以做任何用户想要的。
问题3:
正确的答案是b。通过使用sp_executesql您可以使用一个参数通过用户输入数据参数化TSQL代码。
