9.7 SQL Server动态SQL

 

SQL Server动态SQL

 

目录

• SQL Server动态SQL
  • 简介
  • 使用动态SQL查询任何表
  • SQL Server动态SQL与存储过程
  • SQL Server动态SQL与SQL注入
  • 有关sp_executesql存储过程的更多信息

 

简介

动态SQL是一种可以在运行时动态构造SQL语句的编程技术。因此可以创建更通用、更灵活的SQL语句，因为SQL语句的全文可能在编译时未知。例如，您可以使用动态SQL创建一个存储过程，该存储过程查询一个直到运行时才知道其名称的表的数据。或者是分页存储过程等。

创建动态SQL很简单，只需将其设置字符串，比如：

'SELECT * FROM production.products';

要执行动态SQL语句，请调用存储过程`sp_executesql`，如以下语句所示：

EXEC sp_executesql N'SELECT * FROM production.products';

因为sp_executesql接受Unicode字符串的动态SQL，所以需要加上前缀N。

虽然这个动态SQL不是很有用，但它很好地说明了动态SQL。

使用动态SQL查询任何表

首先，声明两个变量，@table用于保存要查询的表的名称，@sql用于保存动态sql。

	DECLARE
	@table NVARCHAR(128),
	@sql NVARCHAR(MAX);

其次，将@table变量的值设置为production.products。

SET @table = N'production.products';

第三，通过将SELECT语句与表名参数连接起来，构造动态SQL：

SET @sql = N'SELECT * FROM ' + @table;

第四，通过传递@sql参数来调用sp_executesql存储过程。

EXEC sp_executesql @sql;

完整代码：

	DECLARE
	@table NVARCHAR(128),
	@sql NVARCHAR(MAX);
	SET @table = N'production.products';
	SET @sql = N'SELECT * FROM ' + @table;
	EXEC sp_executesql @sql;

上面的代码块与如下SQL语句返回同样的结果集：

SELECT * FROM production.products;

此时如果要从另一个表中查询数据，只需更改@table变量的值。
然而，如果我们将上述T-SQL块包装在存储过程中，则更实用。

SQL Server动态SQL与存储过程

此存储过程接受任何表，并使用动态SQL从指定表返回结果集：

	CREATE PROC usp_query (
	@table NVARCHAR(128)
	)
	AS
	BEGIN
	DECLARE @sql NVARCHAR(MAX);
	-- 构造SQL
	SET @sql = N'SELECT * FROM ' + @table;
	-- 执行SQL
	EXEC sp_executesql @sql;
	END;

调用这个存储过程返回表production.brands表的所有数据

EXEC usp_query 'production.brands';

若要此存储过程按指定列排序并从表中返回前N行：
稍加修改：

	CREATE OR ALTER PROC usp_query_topn(
	@table NVARCHAR(128),
	@topN INT,
	@byColumn NVARCHAR(128)
	)
	AS
	BEGIN
	DECLARE
	@sql NVARCHAR(MAX),
	@topNStr NVARCHAR(MAX);
	SET @topNStr = CAST(@topN as nvarchar(max));
	-- construct SQL
	SET @sql = N'SELECT TOP ' + @topNStr +
	' * FROM ' + @table +
	' ORDER BY ' + @byColumn + ' DESC';
	-- execute the SQL
	EXEC sp_executesql @sql;
	END;

例如，从production.products中获得前10种最贵的产品：

	EXEC usp_query_topn
	'production.products',
	10,
	'list_price';

返回库存量最高的前10种产品：

	EXEC usp_query_topn
	'production.tocks',
	10,
	'quantity';

SQL Server动态SQL与SQL注入

创建一张测试表：

CREATE TABLE tests(id INT);

如下语句返回 production.brands 表中的所有行:

EXEC usp_query 'production.brands';

但是如果用户按如下方式传递表名：

EXEC usp_query 'production.brands;DROP TABLE sales.tests';

这种技术称为SQL注入。一旦执行该语句，则sales.tests表被删除，因为存储过程usp_query会执行这两个语句：

SELECT * FROM production.brands;DROP TABLE sales.tests

为了防止这种SQL注入，可以使用QUOTENAME()函数将架构(schema)和表名分离，如以下查询所示：

	CREATE OR ALTER PROC usp_query
	(
	@schema NVARCHAR(128),
	@table NVARCHAR(128)
	)
	AS
	BEGIN
	DECLARE
	@sql NVARCHAR(MAX);
	-- construct SQL
	SET @sql = N'SELECT * FROM '
	+ QUOTENAME(@schema)
	+ '.'
	+ QUOTENAME(@table);
	-- execute the SQL
	EXEC sp_executesql @sql;
	END;

现在，如果将架构和表名传递给存储过程：

EXEC usp_query 'production','brands';

但是，如果尝试注入其他语句，例如：

	EXEC usp_query
	'production',
	'brands;DROP TABLE sales.tests';

会报如下错误：

Invalid object name 'production.brands;DROP TABLE sales.tests'.

有关sp_executesql存储过程的更多信息

语法：

	EXEC sp_executesql
	sql_statement
	parameter_definition
	@param1 = value1,
	@param2 = value2,
	...

• sql_statement是包含T-SQL语句的Unicode字符串。sql_statement可以包含参数，比如：SELECT * FROM table_name WHERE id=@id中的@id就是参数。
• parameter_definition是一个字符串，包含sql语句中嵌入的所有参数的定义。每个参数定义都包含一个参数名及其数据类型，例如@id INT。参数定义用逗号(,)分隔。
• @param1 = value1, @param2 = value2,…为parameter_definition字符串中定义的每个参数指定一个值。

示例:使用sp_executesql存储过程查找标价大于100且类别为1的产品：

	EXEC sp_executesql
	N'SELECT *
	FROM
	production.products
	WHERE
	list_price> @listPrice AND
	category_id = @categoryId
	ORDER BY
	list_price DESC',
	N'@listPrice DECIMAL(10,2),
	@categoryId INT'
	,@listPrice = 100
	,@categoryId = 1;

 

分类: SQL Server

标签: SQL Server , SQL Server高级 , 数据库