t - sql的阶梯:超越基础水平9:动态t - sql代码
作者：格雷戈里·拉森,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：
1：你想要一个用户从下拉列表中选择某些标准可能会导致查询运行不同,比如排序顺序；2：您的应用程序不知道要运行的表的名称，直到运行时。由于TSQL语言不允许您将变量或参数用于特定的表或列名，因此可以使用动态TSQL。为了更好地理解动态TSQL让我们看看几个例子。
创建简单的动态SQL
第一个例子如何创建动态TSQL让我们考虑以下情况： 假设您有一个应用程序的用户界面允许用户选择他们想要的表读从下拉列表中。 所以每次有人使用接口可以选择一个不同的表,存储他们想返回数据。 对于这个示例假设这个用户界面显示表的信息AdventureWorks2012数据库和用户选择AdventureWorks2012.Sales.SalesOrderDetail表。 清单1中的代码显示了一个使用动态方法TSQL代码返回前10位的记录AdventureWorks.Sales.SalesOrderDetail表。
——声明变量来保存动态TSQL代码
声明@CMD nvarchar(1000);
——声明的名字表来阅读
声明@ table nvarchar(125);
设置@ table =“AdventureWorks2012.Sales.SalesOrderDetail”;
——构建动态TSQL语句
设置@CMD = '从“选择十大* + @ table;
——执行动态TSQL语句
执行(@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中的代码。
——第1部分:创建数据库和示例表
使用主;
去
创建数据库强啡肽;
去
使用强啡肽;
去
创建表MyData1(int Id,DataDesc varchar(100));
创建表MyData2(int Id,DataDesc varchar(100));
创建表TestData1(int Id,DataDesc varchar(100));
创建表TestData2(int Id,DataDesc varchar(100));
去
——第2部分:动态TSQL代码生成脚本删除测试表
使用强啡肽;
去
声明@TableName varchar(100);
声明@CMD varchar(1000);
从sys.tables选择上面1 @TableName =姓名
名字像“测试%”
命令名称;
虽然@@ROWCOUNT > 0
开始
选择@CMD =“删除表”+ @TableName +‘;’;
打印@CMD
执行(@CMD);
从sys.tables选择上面1 @TableName =姓名
其中名称“测试%”和名称> @TableName吗
命令名称;
结束
——第三节:清理
使用主;
去
减少数据库强啡肽;
清单2:动态代码删除测试表
清单2中的代码包含三个不同的部分。第一部分创建一个名为DYNA的数据库，然后创建4个不同的表，其中两个表从测试开始。从测试开始的这两个表是我想用动态TSQL代码删除的表。代码的第二部分是我的动态TSQL代码。最后一段代码通过删除我创建的测试数据库来进行清理。
如果您查看第2节中的代码，您会发现动态TSQL代码首先打印出它运行的delete语句，然后删除我在第1部分中创建的测试表。我通过处理WHILE循环来处理这一过程，同时寻找从字符串测试开始的不同的表。对于从测试开始的每个表，我都构建了一个存储在变量@cmd中的DELETE命令。然后，我使用PRINT语句来显示DELETE语句，然后使用EXECUTE语句执行语句。最后一节，第3节将由dr进行清理
为了测试这段代码，我建议您独立运行每个部分，以便从第1部分开始。运行第1部分之后，检查DYNA数据库，并验证在DYNA数据库中有4个表。接下来第二部分运行。当您运行这个部分时，您将看到在Query Analyzer窗口中的MESSAGE选项卡中显示的两个消息。所显示的两个语句是动态生成和执行的两个DELETE语句。在第2部分中运行代码之后，返回并查看您的DYNA数据库中的表。如果在SQL Server管理中使用对象浏览器，别忘了刷新。 或者,你可以选择从sys.tables视图。 你应该现在发现只有两个表存在,和两个表删除那些与“测试”开始。 一旦你做了什么验证第2部分中的代码执行,我将跑第三节中的代码清理。
这是一个非常简单的例子,如何检查元数据和生成动态TSQL行。 DBA在许多次,它会派上用场了解如何编写TSQL生成TSQL代码的代码。
避免SQL注入
您可能听说过动态TSQL是邪恶的。动态TSQL的邪恶部分在于它为SQL注入攻击提供了可能。SQL注入是一种黑客技术，恶意用户试图利用免费表单数据输入字段的使用。这些恶意用户试图在数据输入字段中插入额外的TSQL代码，超出了数据输入字段最初打算使用的范围。通过插入TSQL代码，他们可以欺骗系统返回本来不应该得到的数据，或者更糟的是，在SQL Server数据库上运行额外的TSQL命令。根据应用程序运行的权限，SQL注入攻击可以将数据插入到数据库表中，删除表，或者更糟的是，设置一个带有sysadmin权限的新登录。
演示如何受到动态TSQL SQL注入攻击如果不妥善处理,让我先创建一个数据库和一个表与清单3中的代码。 我将使用这个数据库和表来演示如何将动态TSQL容易受到SQL注入攻击。
使用主;
去
创建数据库强啡肽;
去
使用强啡肽;
去
创建表的产品(int ID,
ProductName varchar(100),
价格钱);
插入产品价值(12.99 1“红色车”),
(23.18 2“红色谷仓”),
(7.59 2“家畜”),
(2,'玩具士兵,17.76);
清单3:创建数据库和表来演示一个SQL注入攻击
清单3中的代码创建了一个数据库名称DYNA，然后创建并填充一个带有4行数据的表名产品。假设我的应用程序有一个数据选择屏幕，其中一个终端用户可以输入一个包含在ProductName中的文本字符串，然后应用程序将返回包含输入的文本字符串的所有产品表记录。应用程序通过传递用户输入到存储过程名称get乘积的文本字符串来完成这一操作，然后，从存储过程返回的数据显示给用户。存储过程GetProducts被编码，如清单4所示。
创建PROC GetProducts
(@EnteredText varchar(100))
作为
声明@CMD varchar(1000);
设置@CMD = +选择ProductName,价格
从产品+
“哪里像“% ProductName”+
@EnteredText +“%””;
打印@CMD
EXEC(@CMD);
清单4:为存储过程的代码GetUserName
通过回顾存储过程GetProducts在清单4中可以看到这个存储过程接受单个参数@EnteredText然后使用这个参数来动态创建一个存储在变量@CMD TSQL声明。 然后执行这个变量。 (注意,这个过程可能是不使用动态SQL编写的。 我在这里使用动态SQL说明潜在的问题。)
为了演示如何使用这个存储过程让我通过运行清单5中的代码执行它。
EXEC GetProducts“红色”;
清单5 GetUserName通常执行存储过程
清单5中的代码调用GetProducts存储过程并产生报告1中的结果。
ProductName价格
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
红色的车12.99
红色谷仓23.18
报告1:调用的结果GetUserName在清单5中使用代码
因为我的存储过程的代码GetProducts需要一个参数,生成varchar变量@CMD它打开了存储过程的SQL注入攻击。 我可以通过执行来证明这一点GetProducts存储过程与清单6中的代码。
EXEC GetProducts“红色%”和ID = 1——”;
清单6:代码公开如何GetProducts存储过程容易受到SQL注入
如果你检查代码在清单6中可以看到我通过其他一些附加到字符串中字符“红色”我的存储过程GetProducts。 我通过了这些额外的字符允许我限制查询只返回产品“红色”ProductName列和有一个ID值为1。 通过允许我存储过程使用未经编辑的文本@EnteredText参数允许我注入额外字符参数引起的代码执行其他操作不是最初打算使用的GetProducts存储过程。
我在最后一个例子向您展示了一个无损使用动态SQL注入攻击TSQL在我GetProducts存储过程。 大多数SQL注入攻击试图获得更多数据从你的系统,或者只是想腐败的数据库。 探索这一点让我们看看清单7中的代码。
EXEC GetProducts“红色”;从产品;选择*——”;
清单7:SQL注入返回额外的数据
如果我运行清单7中的代码生成这两个结果集。 第一个结果集行和第二组为零是文本中发现报告2:
ID ProductName价格
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1红车12.99
2红谷仓23.18
2农场动物7.59
2玩具士兵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存储过程。
改变PROC GetProducts
(@EnteredText varchar(100))
作为
声明@CMD nvarchar(1000);
声明@WildCardParm varchar(102);
设置@CMD = +选择ProductName,价格
从产品+
“ProductName @EnteredParm”;
设置@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中的代码。
创建表的产品(int ID,
ProductName varchar(100),
价格钱);
插入产品价值(12.99 1“红色车”),
(23.18 2“红色谷仓”),
(7.59 2“家畜”),
(2,'玩具士兵,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代码。
原文链接：