动态SQL与静态SQL－安全（第一部分）

翻译：原文地址：http://www.sqlservercentral.com/articles/Security/dynamicsqlversusstaticsqlp1/617/
概述

熟悉MS SQL Server的每一个人都应该意识到避免使用动态SQL的代价是较佳的。由于与静态SQL相比而言，动态SQL赋予了更多的权限。正因为你使用了动态SQL，所以会执行一些意想不到的代码。动态SQL通常需要较少的代码，与静态SQL相比，似乎不需要太多的维护。

本文将对SQL Server是如何处理动态SQL和静态SQL的原理作一介绍，在本部分，主要讨论一些安全问题，在后续部分将讨论使用动态SQL和静态SQL的适用环境。

在本系列，我计划要对以上不使用或使用动态SQL的原因通过一些范例来说明来避免。许多静态SQL需要的代码要比动态SQL多，这无疑给我们一种使用动态SQL的诱惑，不过请暂时忍耐一下，静态SQL的使用对于日后所产生的问题降低许多。

不过这篇文章也不是推荐大家不要使用动态SQL，只是给大家一种参考：在决定使用时或考虑是否真的不使用静态SQL时多一些思考。

下面通过pubs数据库来进行描述：

动态SQL需要更多的权限

首先打开查询分析器，并使用一个用户暂且叫PubsUser连接至Pubs数据库，该用户除了能访问数据库外，并无任何的读、写权限。现在使用sa账号打开另一连接，我们使用该连接来创建一个存储过程。

将下面的代码在sa连接查询中执行：

CREATE PROCEDURETestPermissions
(
    @ExecSectionint
)
AS
DECLARE @Queryvarchar(200)
IF @ExecSection= 1
BEGIN
      SELECT *
     FROM Authors
END
ELSE
BEGIN
      SET @Query = 'SELECT * FROM Authors'
       EXEC (@Query)
END
GO

GRANT EXECUTE ON [dbo].[TestPermissions] TOPubsUser
GO

现在回到PubsUser的查询中来执行如下参数的存储过程：

EXEC TestPermissions @ExecSection = 1

执行上述语句后，您将会看到authors表的所有记录，现在对输入参数进行修改，将其值更改为2，再执行。此时将接收到如下的错误提示：
Server:Msg229, Level 14, State 5, Line 0
SELECT permission denied on object 'authors', database 'pubs', owner 'dbo'.

         下面让我们一起对使用相同权限对存储过程的两次执行作一分析。对于第一执行，是静态SQL，由于代码是静态SQL，其所有者及其存储程引用的对象都是相同的，因此SQL Server则认为执行存储过程只需具有“执行”权限即可。
        而对于第二次执行，存储过程调用的是动态SQL，动态SQL执行时对其有限执行存储过程的上下文进行了校验，sQL Server发现PubsUser并不具有对authors表的SELECT权限，因此产生了错误。
        要解决上述的问题，只需赋予PubsUser 在Authors表的SELECT权限，回到sa的连接中执行如下代码：
        GRANT SELECT ON [dbo].[authors] TOPubsUser
        GO
       现在切换到刚才的查询窗口再次以@ExecSection = 2来执行，此时将成功执行。由于动态SQL执行前，你需要为其工作的对象授予SELECT权限，如果我们在存储过程中使用DELETE，INSERT，UPDATE操作时，相应的权限也要授权。

      对于使用静态SQL的好处是你可以对用户的操作进行控制（相对于存储过程来说），仅需要授权对存储过程的“执行”权限而无需其他的权限操作。然而当采用动态SQL时，你则必须授权用户执行存储过程的相应权限，否则会执行失败;这就使得任何用户可以在存储过程之外来操作数据，这将是一个严重的安全问题。倘若某个用户具有对authors表的UPDATE权限，你不能够确定在authors表上执行了任何种UPDATE操作。无UPDATE权限的用户也不能更新任何操作，除非授权了对存储过程的UPDATE权限。

执行未授权代码

当动态SQL和一些用户提供的参数一起执行时会出现其他的涉及危险操作的可能性。

在查询分析中执行如下代码：
CREATE PROCEDURE TestDynamic
(
@LastName varchar(50) = ''
)
AS
DECLARE @Query varchar(500)
SET @Query = 'SELECT * FROM authors'
IF @LastName <> ''
SET @Query = @Query + CHAR(13) + 'WHERE au_lname like ''' + @LastName + '%'''
SET @Query = @Query + CHAR(13) + 'ORDER BY au_lname'
PRINT @Query
EXEC (@Query)
GO
GRANT EXECUTE ON [dbo].[TestDynamic] TO PubsUser
GO

对于此例，我们使用了动态SQL，以仅需要查询和修改操作。最简单的方法赋予用户固定数据库角色（db_datareader和db_datawriter）。正如上面提到的，当使用动态SQL时，需要授权的操作要比静态SQL多，因此也要为PubsUser赋予读和写的权限。

执行以下代码前，需要知道它将对authors表进行修改操作。
Exec TestDynamic @LastName = ’‘’update authors set phone = ‘’‘’ --'

最为安全的避免此种情况发生的方法是不要使用动态SQL，可是，如果你必须使用动态SQL，也有一些方法来降低用户执行示授权代码的风险性。
首先，一定要对接收输入的text字符进行长度校验，在我们的例子中，一个人的last name最大为50个字符，由于用户可能会做一些搜索性的操作;其次，可以使用REPLACE函数来查找单引号来添加另一个单引号。这确保了用户输入的单引号不会完成诸如LIKE比较的运算;最后不要授权UPDATE/INSERT/DELETE权限。
下面是对上述的存储过程进行了修改：
ALTER PROCEDURE TestDynamic
(
@LastName varchar(30) = ‘’)
AS
DECLARE @Query varchar(500)
SET @LastName = REPLACE (@LastName,'''','''''')
SET @Query ='SELECT * FROM authors'
IF @LastName <>''
SET @Query = @Query + CHAR(13) +'WHERE au_lname like'''+ @LastName +'%'''
SET @Query = @Query + CHAR(13) + 'ORDER BY au_lname'
PRINT @Query
EXEC (@Query)
GO

执行完上述存储过程后，再执行前面的参数存储过程：
Exec TestDynamic @LastName ='''update authors set phone =''''--'
将会产生错误。

结论：
避免动态SQL的使用，由于它需要更多的权限。若必须使用动态SQL，必须对权限格外小心，仅授权必须的权限，务必你能保证用户仅执行您期望的操作，否则你不能保证未授权代码的执行。