动态SQL

执行动态SQL，可以使用EXEC(@sql)，也可以存储过程sys.sp_executesql，由于参数是动态SQL暴露出来的，为了避免参数注入的风险，必须对参数做检查。相比EXEC(@sql)，sys.sp_executesql是更安全的实现方式，因为后者会做参数的检查。

一，简单的SQL注入

简单举例，对于下面的动态SQL，由于没有对参数@tablename做检查，很容易导致SQL注入。

SET @sql = N'SELECT * FROM dbo.' + @tablename;
EXEC sys.sp_executesql @sql;

假如用户传递的参数值是：Users; DROP TABLE dbo.Users;

执行该动态SQL，会把删掉Users表。如何避免这种类型的SQL注入呢？解决方法有多种多样，比较简单的一种是使用QUOTENAME()，例如：

SET @sql = N'SELECT * FROM dbo.' + QUOTENAME(@tablename);

二，EXEC

把要执行的SQL语句转换成字符串，通过EXEC()函数来执行该语句，这是常规的方式，需要用户对参数进行检查：

EXEC(@sql) 

根据我的经验，在编写动态SQL时，使用QUOTENAME()和REPLACE()更加安全和有效。当参数表示的表对象、视图对象或者字段时，建议使用QUOTENAME()；当动态SQL的主题比较长时，可以设置占位符，比如##SQL_Block##，##Table_Name##，然后使用REPLACE()函数进行替换。

举个例子，先写好动态SQL的总体，把动态变化的参数用一个占位符来表示，最后用实际的参数来替换占位符：

set @sql = N'select * from ##table_name## ....';
set @sql=replace(@sql,N'##table_name##',N'[dbo].'+quotename(@tablename))

三，sys.sp_executesql

该存储过程实际上是对查询进行参数化处理，使得执行查询的主体不变，只有参数发生变化。该存储过程在执行动态SQL时会对参数和参数类型进行检查，可以避免通过参数传值引发的注入风险，该存储过程有三个参数，前两个参数是不变的，从第三个参数开始的参数，是动态变化的参数值：

sys.sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
] 

参数注释：

• @stmt：动态SQL主体，是NVARCHAR(max)类型
• @params：对@stmt中使用的参数进行声明，是NVARCHAR(max)类型
• @param1=value1, @param2=value2, ...：对参数传值

在编写动态SQL的主体时，查询中用到的变量可以不需要声明，在第二个参数中对@stmt中用到的变量进行声明，这种方式实际上是强制把参数抽取出来，把整个查询语句强制参数化处理，如下所示：

DECLARE @sqlCommand nvarchar(1000)
SET @sqlCommand = N'SELECT * FROM Person.Address WHERE City = @city'
EXECUTE sys.sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = 'Shanghai'

使用抽取动态SQL主题，好处是提高查询性能，通过传递参数获得动态的结果，缺点是不够灵活，只能对变量进行参数化处理。

 

 

参考文档：

sp_executesql Microsoft Docs