Stairway to T-SQL: Beyond The Basics Level 9: Dynamic T-SQL Code
By Gregory Larsen, 2016/07/29 (first published: 2014/07/23)
原文链接：http://www.sqlservercentral.com/articles/Stairway+Series/113118/
The Series
本文是系列的一部分：通往T-SQL的阶梯：超越基础
下面就从它通往T-SQL DML的阶梯、Gregory Larsen所叙述的覆盖更先进的T-SQL语言方面如子查询。
还有当你需要写代码，创建特定的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表。清单1中的代码显示了一个使用动态TSQL代码从adventureworks.sales.salesorderdetail表返回的前10 条记录的方法。
清单1：简单的动态TSQL实例
清单1中的代码首先声明一个变量名称@CDM来保存要构建的动态SELECT语句和@表变量来保存表名。然后我为表adventureworks.sales.salesorderdetail设置表变量。建立我的实际动态TSQL语句我可以使用SET语句。此语句将变量CMD设置为包含SELECT语句和@表变量值的级联字符串值。然后我执行我的包含在@CDM变量中的动态TSQL语句使用EXECUTE语句执行。
为了进一步检验清单1中的动态TSQL，你可以尝试使用不同的代码通过改变adventurework2012表的“设置”表=“来声明使用这个表。
处理更复杂的动态SQL Server需求
还有当你需要写一些更复杂的动态TSQL倍。作为DBA，我可能需要这样做的一种情况是，当我想生成代码来执行某种数据库维护时。当我需要建立数据库维护的目的，我通常看一个系统视图和生成脚本，显示和/或执行动态TSQL。假设您是一个DBA，接管了维护一个数据库，并希望删除在数据库中创建的几个测试表。表中都有以前缀“test”开头的名称。表明你可能读sys.tables视图并生成相应的delete语句，让我们看清单2中的代码。
清单2：动态代码删除测试表
清单2中的代码包含三个不同的部分。第一部分创建的数据库称为DYNA，然后创建4个不同的表，其中两张表以“TEST”开头从，这两张表以“TEST”开头是我想删除动态TSQL代码。代码的第二部分是我的动态TSQL代码。最后一部分代码通过删除我创建的测试数据库进行清理。
如果你查看代码，在2节中你会发现动态TSQL代码首先打印出DELETE语句，它运行，然后删除我在1节中创建的测试表。我通过while循环处理，同时查找以字符串“test”开头的不同的表。对于每一个表，我发现以“test”开头，我构建了一个删除命令，该命令存储在变量CMD中。然后，通过使用打印语句显示删除语句，然后紧接着通过使用执行语句执行语句。最后一部分，第三部分清理删除DYNA数据库。
为了测试此代码，我建议您从第1节开始独立运行每个部分。当您运行1节回顾动态数据库和验证在动态数据库表四。下一步，第2节,运行此部分时，您将看到两个消息显示在“查询分析器”窗口中的“消息”选项卡中。显示的两个语句是动态生成和执行的两个删除语句。一旦你完成了在2节中运行代码，回去复习你的动态数据库的表。如果您正在使用SQL Server Management Studio中的对象资源管理器，不要忘记刷新。或者，你可以从sys.tables视图选择。您现在应该发现只有两个表存在，两个表是以“test”开始的。一旦您完成了第2节中代码的验证，我将在第3节中运行代码以清除。此代码将删除DYNA数据库。
这是一个很简单的例子关于如何检查一行数据生成动态TSQL。作为一个DBA，很多时候，它会派上用场，了解如何编写TSQL代码生成TSQL代码。
避免SQL注入
你可能已经听说了动态TSQL是邪恶的。动态TSQL邪恶的部分是它开辟了SQL注入式攻击可能性。SQL注入是一种黑客技术，恶意用户试图利用*向数据表输入字段。这些恶意用户尝试插入额外的TSQL代码到数据输入字段的数据输入字段超越最初用意。通过插入TSQL代码就可以骗过系统返回的数据，得到他们本来不应该得到的，或更糟的是运行额外的TSQL命令对SQL Server数据库得到应用程序运行的权限，SQL注入攻击可以将数据插入到数据库表，删除表，或者更糟的是安装一个新的登录获得系统管理员权限。
为了表明动态TSQL如果不妥善管理如何不会受到SQL注入攻击，让我先创建一个数据库和清单3中的代码表。我将使用这个数据库和表来演示动态TSQL如何容易受到SQL注入攻击。
清单3：创建数据库和表来演示SQL注入攻击
清单3中的代码创建一个叫DYNA的数据库，然后创建并填充表的4行数据。
假设我的应用程序数据选择让用户向屏幕输入一个包含在ProductName，然后应用程序将返回所有已有表记录的包含输入的文本字符串的文本字符串。该应用程序通过传递文本字符串，用户进入到一个叫getProducts存储的过程，然后将数据从存储过程返回显示给用户。存储过程getProducts编码，如清单4所示。
清单4：存储过程返回用户名密码
通过对存储过程getProducts在清单4中可以看到该存储过程接受一个参数“enteredtext，这个参数是用来创建一个动态的TSQL语句存储在变量中的命令。然后执行该变量。（注意，这个过程可能是在没有使用动态SQL的情况下编写的，我在这里使用动态SQL来说明潜在的问题。）
为了演示如何使用这个存储过程，让我运行清单5中的代码来执行它。
清单5执行存储过程返回用户名通常
清单5中的代码调用getProducts存储过程产生结果在报告1中显示。
报告1：从使用清单5中的代码调用GetUserName结果
因为在我的存储过程的代码getProducts需要一个参数生成varchar变量命令它离开存储过程并且开始SQL注入攻击。我可以证明这个通过执行getProducts存储过程与清单6中的代码。
清单6：代码揭露getProducts存储过程很容易受到SQL注入
如果你回顾清单6中的代码，你可以看到我通过了附加一些其他字符的字符串“red”到我的存储过程getProducts。这些额外的字符通过我的限制查询只返回的产品有“red”在产品名称列有1的ID值。让我的存储过程enteredtext参数使用未经编辑的文字让我注入额外的字符转换为参数来执行其他原本不打算用于getProducts代码的存储过程的动作。
在我的最后一个例子中我会给你一个无损的SQL注入攻击利用动态的TSQL在我的myGetProducts存储过程。大多数SQL注入攻击都试图从系统中获取额外数据，或者只想破坏数据库。为了更深入地探讨这个问题，让我们看看清单7中的代码。
清单7：SQL注入以返回其他数据
如果我运行清单7中的代码，它将生成两个结果集。第一个结果集具有零行，第二组是报告2中找到的文本：
报表2：运行清单7中的代码时的文本结果
如果你比较正常的getProduct存储过程执行结果的的结果1，结果发现2的结果,你可以看到清单7中的代码生成一些额外的输出列，我的存储过程不是最初设计的显示而是由于SQL注入攻击。
我在清单7中的例子仍然不是一个破坏性使用SQL注入，但它确实让我利用@ enteredtext参数的存储过程的所有列的客户表返回数据。为了实现这一点，我从产品中添加了“*”；select*from product。注意，在附加字符串的结尾添加了两个破折号（“-”）。这允许我注释出存储过程在参数之后可能包含的字符或代码。
我的最后一个例子让我展示一个破坏性的TSQL注入攻击。在清单8中看到我的破坏性的TSQL注射命令。
清单8：破坏TSQL注射执行命令
在清单8中，我添加了一个删除语句到@电子邮件参数。在这个例子中，我删除了客户表。如果我运行清单8中的代码，它将删除客户表。
如何对付SQL注入攻击
没有人想要他们的代码受到SQL注入攻击受损。为了应对SQL注入攻击，你应该考虑以下几点时在开发你的TSQL代码应用：
为了避免SQL注入攻击是不使用动态SQL的最好方式
编辑用户输入参数的特殊字符，如分号和评论
让你的参数，只要需要支持用户输入的数据
如果你必须使用动态SQL，然后使用参数化SQL执行TSQL使用sp_execute执行动态TSQL，而不是执行。
加强安全性只允许最小的权利需要执行动态TSQL。
如果你的应用程序需要建立一些代码，包含动态TSQL然后使用参数化TSQL是反SQL注入的一种好方法。在清单9中，我已经提供了我如何修改我的GetUserName存储过程使用参数化的TSQL实例。
清单9：使用参数化TSQL
在清单9中，我改变了我的getProducts存储过程使用sp_executesql执行我的动态TSQL。在这个修改后的存储过程中，我做了如下更改：
改变字符串@ CMD中不再包括enteredtext变量在命令字符串的值。相反我介绍了用户向文本输入一个名为@ EnteredParm。
增加了一个SET语句来设置变量@ WildCardParm在开始增加通配符（%）和在结束增加enteredtext参数。
更改了如何执行字符串CMD。而不是使用exec语句执行字符串，我使用了程序sp_executesql。
通过进行这两个更改，用户输入的文本现在将作为参数驱动的查询执行。这样，用户不可以再试图进入我的getProduct存储过程注入更多的TSQL代码。为了验证这一点，运行清单5, 6, 7和清单8所示的四个不同的命令。但是因为我已经删除了我的产品表，我首先需要用数据重新创建它。为此我需要清单9首先运行代码。
清单9：创建和填充客户表
在运行清单9重新创建产品表之后，我可以运行清单5, 6, 7和8，以证明我解决了SQL注入攻击的问题。运行这些不同的命令时，您会发现只有清单5返回数据。别人不返回数据的原因是动态TSQL产生现在正在寻找包含额外的用户输入值ProductName值注入，这当然不符合任何产品的产品表中的列值。
总结
没有人希望看到一个SQL注入攻击。当然，确保它不发生的最佳解决方案是在应用程序中不使用动态SQL代码。如果您的应用程序确实需要动态SQL，本文将向您介绍如何尽量减少与SQL注入相关的风险。下一次编写动态SQL时，一定要采取步骤避免SQL注入攻击的可能性。
问题和答案
在本节中，您可以通过回答以下问题来了解您对SQL注入的理解程度。
问题1：
避免SQL注入攻击最好的方法是什么（最好的方法）？
A不部署TSQL代码使用动态TSQL
B编辑用户输入用于特殊字符允许SQL注入攻击的动态TSQL数据
C让用户输入参数的动态TSQL尽可能短
D使用参数化的TSQL代码
问题2：
用户可以通过SQL注入来完成什么样的事情（选择所有的应用程序）？
A返回应用程序不打算让用户选择的数据
B将数据插入到应用程序不打算使用的表中
C删除表
D系统管理员权限来提供新的帐户
E以上所有
问题3：
如果你要部署动态TSQL代码是包含在一个变量，这两种执行方法最好是用以减少您的风险，SQL注入攻击？
exec
sp_executesql
答案:
问题1：
正确答案是A.避免SQL注入的最好方式是不允许动态TSQL代码在您的应用程序。
问题2：
正确答案是E，以上所有。使用SQL注入，恶意用户可以执行许多不同的SQL操作。这样的命令可以执行取决于用于运行动态TSQL命令该帐户的权利。如果应用程序的帐户具有管理员权限，SQL注入攻击可以用户想要做什么。
问题3：
正确答案是B.利用sp_executesql你可以通过你的用户输入数据使用的参数到你的参数化TSQL代码。
本文是部分对通往T-SQL楼梯的说明：超越了基本的知识