SQL Server命令–动态SQL

Warning: This article contains examples of SQL injection. The example queries can be harmful to data and are run on the AdventureWorks2012 database DO NOT run equivalents on your production database. Backup your AdventureWorks2012 database if you don’t want to lose data. Please note that the techniques are purely for education purposes and we do not condone the use of them for any other purpose. 警告：本文包含SQL注入的示例。 示例查询可能对数据有害，并且在AdventureWorks2012数据库上运行。请勿在生产数据库上运行等效查询。 如果您不想丢失数据，请备份AdventureWorks2012数据库。 请注意，这些技术仅用于教育目的，我们不容忍将其用于任何其他目的。

介绍 (Introduction)

Knowing how to write SQL commands can sometimes be too little for the need at hand. Sometimes one needs to make queries dynamic so that they can change them on the fly and make them work the way they need them too. The way to do this is to use the system stored procedure sp_executesql, for which you can glean more information here. This way of running queries can be useful in making an application’s code more malleable in terms of its coupling with the database. However, there are also some negative points to consider and though dynamic SQL is practical it can be dangerous at times.

知道如何编写SQL命令有时对于手头的需求来说太少了。 有时，需要使查询动态化，以便他们可以即时更改它们，并使它们也按需要的方式工作。 这样做的方法是使用系统存储过程sp_executesql，您可以在此处获取更多信息。 这种运行查询的方式对于使应用程序的代码与数据库耦合更具有延展性很有用。 但是，还需要考虑一些负面因素，尽管动态SQL很实用，但有时可能很危险。

sp_executesql Stored Procedure

sp_executesql存储过程

Take the following SELECT query from the AdventureWorks2012 test database:

从AdventureWorks2012测试数据库中进行以下SELECT查询：

 
SELECT TOP 100	LineTotal, SalesOrderDetailID, OrderQty        
FROM		      Sales.SalesOrderDetail
WHERE		     UnitPrice > 500
 
    

This is a simple query and it is also very static and not very interesting to have embedded in one’s application code. Granted, there are other methods of making this more supple, like using an entity framework database provider, but that is outside the scope of this article.

这是一个简单的查询，并且嵌入到自己的应用程序代码中也是非常静态的，并且不是很有趣。 当然，还有其他方法可以使此操作更加灵活，例如使用实体框架数据库提供程序，但这不在本文的讨论范围之内。

Using sp_executesql you can turn this simple query into a dynamically built one. The following example turns the row restriction (TOP), the list of columns and the UnitPrice predicate into dynamically given entities.

使用sp_executesql，可以将此简单查询转换为动态构建的查询。 以下示例将行限制（TOP），列列表和UnitPrice谓词转换为动态给定的实体。

 
CREATE PROCEDURE sp_GetSalesOrderDetails @NUMBER_OF_ROWS INT, @COLUMN_LIST NVARCHAR(1000), @UNIT_PRICE INT        
AS
 
BEGIN
DECLARE	@TSQL NVARCHAR(4000) = N'SELECT TOP ' + CAST(@NUMBER_OF_ROWS AS NVARCHAR) + 
' ' + ISNULL(@COLUMN_LIST, N'*') + N' FROM Sales.SalesOrderDetail WHERE ' + CASE WHEN
@UNIT_PRICE IS NULL THEN N'1 = 1' ELSE N'UnitPrice >= ' + CAST(@UNIT_PRICE AS NVARCHAR)
END
 
EXEC sys.sp_executesql  @TSQL
END
 
GO
    

As you can see this example makes a stored procedure that accepts three variables: one for the TOP clause, one for the list of columns and one for the minimum unit price. The column list and unit price are nullable. If the column list is not given the stored procedure replaces it with a * wildcard. If the unit price is not given the code replaces it with a WHERE clause that is always true (WHERE 1 = 1).

如您所见，该示例使存储过程接受三个变量：一个用于TOP子句，一个用于列列表，一个用于最小单价。 列列表和单价可以为空。 如果未提供列列表，则存储过程将使用*通配符将其替换。 如果未给出单价，则代码将替换为始终为true的WHERE子句（WHERE 1 = 1）。

The query code is broken down into a string of hardcoded words concatenated with the given variables in a specific order. There are ISNULL and CASE WHEN clause to deal with NULL variables. Go ahead and create the stored procedure and play with the variables to see how it acts. You can run the stored procedure by using this code:

查询代码被分解成一串硬编码的单词，这些单词与给定变量按特定顺序连接在一起。 有ISNULL和CASE WHEN子句来处理NULL变量。 继续创建存储过程，并使用变量查看其行为。 您可以使用以下代码运行存储过程：

 
EXEC sp_GetSalesOrderDetails @NUMBER_OF_ROWS = 2000, @COLUMN_LIST =
N'CarrierTrackingNumber, UnitPrice, SalesOrderID', @UNIT_PRICE = 500

Obviously, this type of query can be further perfected by added a check in the sys.columns/sys.tables tables to see whether the given column list exists for example.

显然，可以通过在sys.columns / sys.tables表中添加检查以查看给定的列列表是否存在来进一步完善这种查询。

动态SQL的问题 (Problems with dynamic SQL)

The first major problem that needs to be tackled whenever one uses dynamic SQL commands in one’s application code is SQL injection. The fact that the application accepts NVARCHAR strings and concatenates them with actual SQL queries that are later run opens up the possibility for SQL injection. Without going into the details yet just run this query for yourself (be sure to cancel the query because, as you’ll notice, it loops infinitely):

每当用户在其应用程序代码中使用动态SQL命令时，首先要解决的主要问题是SQL注入 。 应用程序接受NVARCHAR字符串并将其与稍后运行的实际SQL查询连接在一起的事实为SQL注入提供了可能性。 无需赘述，只需自己运行此查询（请确保取消查询，因为您会注意到，它无限循环）：

 
EXEC sp_GetSalesOrderDetails @NUMBER_OF_ROWS = 2000, @COLUMN_LIST = '''hello, your SQL
injection worked'' ; WHILE 1 = 1 SELECT ''You got owned!''; --', @UNIT_PRICE = 500
    

So, if you’re familiar with SQL injections you’ll notice that this dynamic SQL query is cut into two different ones by adding a “;” breaker and then running a TSQL query and cutting the rest of the query off by add comment markers “–“ The query that is actually being run is the following:

因此，如果您熟悉SQL注入，则会注意到通过添加“;”将此动态SQL查询分为两个不同的查询。 破坏程序，然后运行TSQL查询，并通过添加注释标记“ –”来切断其余查询，实际正在运行的查询如下：

 
SELECT TOP 2000 'hello, your SQL injection worked' ; WHILE 1 = 1 SELECT 'You got owned!';
-- FROM Sales.SalesOrderDetail WHERE UnitPrice >= 500
    

Granted, this SQL injection is pretty bad but it could be much worse. Depending on how wise the solution architect was the query may or may not be running with limited privileges. But even if the SQL user the application is using only has read/write privileges the person doing the SQL injection attack could replace WHILE 1 = 1 SELECT‘You got owned!’; with a “DELETE FROM” SQL Command. You can try it by running this query (please backup your database if you are not ready to lose the data):

当然，这种SQL注入是很糟糕的，但可能会更糟。 根据解决方案架构师的明智程度，查询是否可以以有限的特权运行。 但是，即使仅使用该应用程序SQL用户具有读/写权限，进行SQL注入攻击的人员也可以替换WHILE 1 = 1 SELECT'You got own！ ; 使用“ DELETE FROM” SQL命令。 您可以通过运行以下查询来尝试（如果您还没有准备好丢失数据，请备份数据库）：

 
EXEC sp_GetSalesOrderDetails @NUMBER_OF_ROWS = 2000, @COLUMN_LIST = '''hello, your SQL
injection worked'' ; DELETE ', @UNIT_PRICE = NULL

This security nightmare can get a whole lot worse if an unscrupulous architect assigned a user with sysadmin privileges to the application. In that case it is not impossible to drop users, table, or worse yet, entire databases from the SQL Server instance via SQL command injection. So, please take note that one should be certain the application user has minimal security rights and try to use different data types than strings and cast them to nvarchar in the stored procedure later. This has be done in the above stored procedure for the @NUMBER_OF_ROWS and @UNIT_PRICE variables and they are therefore not at risk of SQL injection.

如果不道德的架构师为用户分配了对应用程序具有sysadmin特权的用户，则此安全噩梦会变得更加糟糕。 在这种情况下，并非不可能通过SQL命令注入从SQL Server实例中删除用户，表或更糟的是整个数据库。 因此，请注意，应确保应用程序用户具有最小的安全权限，并尝试使用不同于字符串的数据类型，然后将它们转换为存储过程中的nvarchar。 在上面的存储过程中，已经为@NUMBER_OF_ROWS和@UNIT_PRICE变量完成了此操作，因此它们没有SQL注入风险。

SQL injection is the most drastic negative point of using dynamic SQL commands but there is another weak-spot and that has to do with performance. Due to the fact that SQL Server stores optimized query plans in cache when using stored procedures one may think that using dynamic SQL in a stored procedure as above would be optimizing the system as opposed to building ad-hoc queries in the application and generating a new query plan every time they are run. However, using dynamic-SQL breaks down this capability as the original query plan becomes invalidated so the engine has to create a new one whenever a new version of the dynamic SQL command is run. More information about that here and here. You may want to look into parameterization to safely navigate those waters. Bear in mind, however, that this only applies to extreme highly transactional queries that run thousands of times per day. The caching of a new query plan is a quick process (more info about the intricacies of this process can be found here). This can be tested by used the SET STATISTICS TIME ON; command. If you run a query for the first time there is a small amount of time allocated to “parse and compile time”. This time differs between different servers with varying amounts of power. It also varies with the complexity of the query. However, you can try it yourself by running the following query to create test data:

SQL注入是使用动态SQL命令的最严重的缺点，但是还有另一个弱点，它与性能有关。 由于使用存储过程时SQL Server将优化的查询计划存储在缓存中，因此人们可能会认为，在存储过程中使用上述动态SQL将会优化系统，而不是在应用程序中建立即席查询并生成新的查询。每次运行时查询计划。 但是，使用动态SQL会破坏此功能，因为原始查询计划会失效，因此，只要运行新版本的动态SQL命令，引擎就必须创建一个新的查询。 有关详细信息， 在这里和这里 。 您可能需要研究参数化以安全地导航这些水域。 但是请记住，这仅适用于每天运行数千次的极高事务性查询。 缓存新查询计划是一个快速的过程（有关此过程复杂性的更多信息，请参见此处 ）。 可以通过使用SET STATISTICS TIME ON来测试。 命令。 如果您是第一次运行查询，则会有少量时间分配给“解析和编译时间”。 在具有不同电量的不同服务器之间，此时间有所不同。 它也随查询的复杂性而变化。 但是，您可以自己运行以下查询来创建测试数据：

 
DECLARE @START_DATE DATETIME
DECLARE @ENDDATE DATETIME
SET @START_DATE = '19500101'
SET @ENDDATE = '20991231'
;
WITH CTE_DATES AS
(
SELECT
       @START_DATE DateValue UNION ALL SELECT
       DateValue + 1
FROM CTE_DATES
WHERE DateValue + 1 < @ENDDATE)
 
 
       SELECT
             CAST(DateValue AS date) AS DateTest INTO #tempTestTable
       FROM CTE_DATES
       OPTION (MAXRECURSION 0)
    

Now when you run this simple query you will see that the first time you run it there is some time allocated to compilation and storing the query plan in cache:

现在，当您运行此简单查询时，您将看到第一次运行它时，分配了一些时间来将查询计划编译并存储在缓存中：

 
SET STATISTICS TIME ON
 
SELECT	* 
FROM	dbo.#tempTestTable
WHERE	DateTest BETWEEN  '19940101' AND '20050101' 
 
SET STATISTICS TIME OFF
    

You should see some time allocated to parsing and compilation as follows:

您应该看到分配给解析和编译的时间如下：

 
SQL Server parse and compile time: 
   CPU time = 69 ms, elapsed time = 69 ms.
    

However if you rerun this exact query you will notice that this time is almost non-existent:

但是，如果您重新运行此确切的查询，您会发现这段时间几乎不存在：

 
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
    

If the query test itself was changed then the engine would be forced to recompile and re-cache a new query plan. The times to do this are small but if the query is run extremely often they can add up to lots of wasted resources.

如果查询测试本身已更改，则引擎将被迫重新编译并重新缓存新的查询计划。 这样做的时间很少，但是如果查询运行得非常频繁，它们可能会增加大量浪费的资源。

那么什么时候可以安全地使用动态SQL？ (So when can one safely use dynamic SQL?)

Dynamic SQL can be used by a developer to create an agile query that allows for dynamic results. However, we have seen that this practice can be very dangerous. There are, however, other uses for this type of dynamic SQL. DBAs may use it to help in maintenance/administrative tasks and indeed it works wonders for saving time. SQL injection and query plan optimization is not a problem in this case because these scripts are not run constantly and repetitively as are application/production queries.

开发人员可以使用动态SQL来创建允许动态结果的敏捷查询。 但是，我们已经看到这种做法可能非常危险。 但是，这种类型的动态SQL还有其他用途。 DBA可以使用它来帮助进行维护/管理任务，并且确实可以节省时间。 在这种情况下，SQL注入和查询计划优化不是问题，因为这些脚本不会像应用程序/生产查询那样持续不断地重复运行。

I will leave you with a very handy index maintenance query for any DBA that does the following using a dynamic SQL command:

对于使用动态SQL命令执行以下操作的任何DBA，我将为您提供一个非常方便的索引维护查询：

1. Creates a temporary work table
   创建一个临时工作表
2. Inserts the index names, table names and database names of all table that needs either a REBUILD or REORGANIZE (this info is included too)
   插入需要重新构建或重新组织的所有表的索引名称，表名称和数据库名称（此信息也包括在内）
3. Using cursors, the query builds a dynamic T-SQL command for each index (ALTER INDEX) and runs a rebuild on indexes over 30% fragmented and a reorganize for indexes between 10 and 30% fragmented.
   该查询使用游标为每个索引（ALTER INDEX）构建一个动态的T-SQL命令，并对碎片超过30％的索引运行重建，并为碎片之间10％到30％的索引重新组织。
4. This followed by an error handing CATCH statement so the query can keep running (this generally happens with older data-types that cannot be build ONLINE for security reasons.
   这之后是一个错误处理CATCH语句，以便查询可以继续运行（这通常发生在出于安全原因而无法在线构建的旧数据类型中。
5. Finally the query runs an index statistics update so the engine can use these fresh statistics for future query plans.
   最后，查询运行索引统计信息更新，以便引擎可以将这些新的统计信息用于将来的查询计划。

 
CREATE TABLE dbo.#FragTab 
(DB_Name varchar(100),
[Schema] varchar(50), 
[Table] varchar(200), 
[Index] varchar(200), 
avg_fragmentation_in_percent FLOAT, 
REBUILD_Necessary BIT, 
REORGANISE_Necessary BIT)
 
EXEC sys.sp_MSforeachdb '
USE ?;
INSERT INTO #FragTab
SELECT 
DBs.name as ''DB_Name'',
dbschemas.[name] as ''Schema'', 
dbtables.[name] as ''Table'', 
dbindexes.[name] as ''Index'',
indexstats.avg_fragmentation_in_percent,
CASE WHEN  indexstats.avg_fragmentation_in_percent > 30 THEN 1 ELSE 0 END as REBUILD_Necessary,
CASE WHEN  indexstats.avg_fragmentation_in_percent BETWEEN 10 AND 30 THEN 1 ELSE 0 END as REORGANISE_Necessary
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
LEFT OUTER JOIN sys.databases as DBs ON DBs.database_id = indexstats.database_id
WHERE indexstats.database_id = DB_ID()
AND indexstats.avg_fragmentation_in_percent > 10
AND dbindexes.name IS NOT NULL
AND indexstats.database_id > 4' -- Exclude the system databases (msdb, tempdb, master)
 
DECLARE @table VARCHAR(200)
DECLARE @Index VARCHAR(200)
DECLARE @DB_Name VARCHAR(100)
DECLARE @SQL NVARCHAR(4000)
 
DECLARE CUR_INDEXES_REBUILD CURSOR FOR SELECT
	   [Table],
	   [Index],
	   [DB_Name]
FROM #FragTab
WHERE REBUILD_Necessary = 1
 
OPEN CUR_INDEXES_REBUILD
 
FETCH NEXT FROM
CUR_INDEXES_REBUILD INTO @table, @Index, @DB_name
 
 
WHILE @@FETCH_STATUS = 0
 
BEGIN
BEGIN TRY
 
SET @SQL = 'ALTER INDEX [' + @Index + '] ON [' + @DB_Name + '].[dbo].[' + @table + '] REBUILD WITH (ONLINE = ON) '
 
EXEC sys.sp_executesql @SQL
END TRY
BEGIN CATCH
PRINT 'An error occured on the table : ' + @Table +' in the database :' + @DB_name + ', the cursor will continue treating other indexes. The error message is : ' + ERROR_MESSAGE()
END CATCH
 
FETCH NEXT FROM CUR_INDEXES_REBUILD INTO @table, @Index, @DB_name
 
 
END
 
CLOSE CUR_INDEXES_REBUILD
DEALLOCATE CUR_INDEXES_REBUILD
 
 
DECLARE CUR_INDEXES_REORGANIZE CURSOR FOR SELECT
	   [Table],
	   [Index],
	   [DB_Name]
FROM #FragTab
WHERE REORGANISE_Necessary = 1
 
OPEN CUR_INDEXES_REORGANIZE
 
FETCH NEXT FROM
CUR_INDEXES_REORGANIZE INTO @table, @Index, @DB_name
 
 
WHILE @@FETCH_STATUS = 0
 
BEGIN
 
BEGIN TRY 
 
SET @SQL = 'ALTER INDEX [' + @Index + '] ON [' + @DB_Name + '].[dbo].[' + @table + '] REORGANIZE '
 
EXEC sys.sp_executesql @SQL
END TRY
BEGIN CATCH
PRINT 'An error occured on the table : ' + @Table +' in the database :' + @DB_name + ', the cursor will continue treating other indexes. The error message is : ' + ERROR_MESSAGE()
END CATCH
 
FETCH NEXT FROM CUR_INDEXES_REORGANIZE INTO @table, @Index, @DB_name
 
 
END
 
CLOSE CUR_INDEXES_REORGANIZE
DEALLOCATE CUR_INDEXES_REORGANIZE
 
 
DROP TABLE #FragTab
 
EXEC sys.sp_MSforeachdb '
USE ?; 
EXEC sp_updatestats; '
    

结论 (Conclusion)

Dynamic T-SQL commands are very handy and are fun to use. They can make life easier for developers but lots of thought should go into the subject and this should be discussed with a DBA beforehand. In most cases, it may be better to take a parameterized SQL command approach. However, for maintenance or investigative reasons dynamic SQL commands can be a wonderful tool for DBAs. The article includes a use case for rebuilding and reorganizing indexes but one could also use it for restoring multiple databases.

动态T-SQL命令非常方便，而且使用起来很有趣。 它们可以使开发人员的生活更轻松，但是应该多加考虑，并且应该事先与DBA讨论。 在大多数情况下，采用参数化SQL命令方法可能会更好。 但是，出于维护或调查的原因，动态SQL命令对于DBA可能是一个很好的工具。 本文包括一个用于重建和重新组织索引的用例，但也可以将其用于还原多个数据库。

翻译自: https://www.sqlshack.com/sql-server-commands-dynamic-sql/