动态SQL与静态SQL

 熟悉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，必须对权限格外小心，仅授权必须的权限，务必你能保证用户仅执行您期望的操作，否则你不能保证未授权代码的执行。

 

和先前介绍的文章一样，首先举一些动态SQL的代码片断，然后介绍使用静态SQL完成同样的功能的方法，以及静态SQL实在不能使用时，动态SQL才迫以使用的情况。
注意：示例采用Pubs数据库
案例1：搜索条件
第一种情况就是我见到过最多的、也是说明动态SQL为什么说它需要较少的考虑和实现代码的地方。这里使用Pubs数据库的authors表。我想实现的是根据传入一个参数变量来搜索满足条件的某一个author的信息。下面在查询分析器中要执行的代码：

DECLARE @names varchar(7000) SET @names = 'white' SELECT * FROM authors WHERE au_lname IN (@names)

假如你想搜索多个名字，就需要修改上面的SET语句，如下所示的：
SET @names = '''white'',''green'''

这里使用了单引号来区分多个名字，然而在执行重新执行上述代码时，则返回空记录。其原因主要是SQL Server将其认为是一个长字符串，要找满足'while','green'。由于我们不能够限制使用名字的数目，唯一能接受的是使一个变量满足我们的要求。最简单的解决方法就是将上述的整个SELECT语句转换为动态SQL，其转换后的代码如下：

DECLARE @names varchar(7000), @Query varchar(8000) SET @names = '''white'',''green''' SET @Query = ' SELECT * FROM authors WHERE au_lname IN (' + @names + ')' PRINT @Query EXEC (@Query)

只要names变量正确格式化执行上述语句就能如期地得到记录行。可是，此种方案需要授权authors表的SELECT权限。下面采用静态SQL来完成上述的功能： DECLARE @names varchar(7000), @len int, @CurPos int, @PrevPos int CREATE TABLE #names (names varchar(35)) SET NOCOUNT ON SET @names = 'white,green' SET @len = LEN(@names) + 1 SET @CurPos = 1 SET @PrevPos = @CurPos WHILE @CurPos < @len + 1 BEGIN      IF SUBSTRING(@names + ',', @CurPos, 1) = ','      BEGIN            INSERT INTO #names (names)            SELECT SUBSTRING(@names,@PrevPos,@CurPos - @PrevPos)            SET @PrevPos = @CurPos + 1      END      SET @CurPos = @CurPos + 1 END SET NOCOUNT OFF SELECT * FROM authors WHERE au_lname IN (SELECT * FROM #names) DROP TABLE #names

使用静态SQL方法另一个好处就是仅需要修改保存名字的变量即可。对于采用动态SQL，由于VARCHAR变量的字符受限（最大为8000个字符），不可能用于保存名字的变量耗尽8000个字符，同时也需要为其执行@Query代码留出空间。 通过比较上述两个方法，不难发现静态SQL代码要比动态SQL要多，另外，静态SQL需要花费较长时间来开发和测试。 案例2：排序列 假如我们想要实现根据指定的变量来实现对数据的排序，在学习如何在ORDER BY中使用CASE函数之前，通常想到实现此功能的是使用动态SQL，如下代码所示： DECLARE @Query varchar(500), @OrderBy varchar(10), @Sequence varchar(4) SET @OrderBy = 'au_lname' SET @Sequence = 'DESC' SET @Query = '             SELECT *             FROM authors             ORDER BY ' + @OrderBy + ' ' + @Sequence IF @OrderBy = 'au_lname' SET @Query = @Query + ', au_fname ' + @Sequence PRINT @Query EXEC (@Query)

而采用静态SQL则需要较多的代码，如下所示： DECLARE @OrderBy varchar(10), @Sequence varchar(4) SET @OrderBy = 'au_lname' SET @Sequence = 'DESC' SELECT * FROM authors ORDER BY CASE @OrderBy + @Sequence                                   WHEN 'au_lnameASC' THEN au_lname                                   WHEN 'au_fnameASC' THEN au_fname                      END ASC,                      CASE @OrderBy + @Sequence                                  WHEN 'au_lnameDESC' THEN au_lname                                  WHEN 'au_fnameDESC' THEN au_fname                      END DESC,                      CASE @OrderBy + @Sequence                                   WHEN 'au_lnameASC' THEN au_fname                      END ASC,                      CASE @OrderBy + @Sequence                                   WHEN 'au_lnameDESC' THEN au_fname                      END DESC

由此看出，静态SQL需要较多的代码来实现。但是，当在存储过程中执行时，用户只需要有执行权限即可，而动态SQL执行时则会失败，原因是用户在authors表无SELECT权限。 案例3：联接(Join) 假如我们想要显示author或title的信息，仅当传入值给@Author时显示author信息，传入值给@Title时显示title信息，同时传入值给@Author和@Title变量时则显示author和title信息;在为这两个变量赋值时，我们需要3张表中返回结果，若仅传入一个变量值或仅需要一张表时，以下是动态SQL的解决方法： DECLARE @Query varchar(500), @Author varchar(20), @Title varchar(20) SET @Author = '' SET @Title = 'e' SET @Query = 'SELECT * FROM ' IF @Author <> ''     SET @Query = @Query + 'authors a' IF @Author <> '' AND @Title <> ''     SET @Query = @Query + CHAR(13) +             'INNER JOIN titleauthor ta ON ta.au_id = a.au_id' + CHAR(13) + 'INNER JOIN ' IF @Title <> ''     SET @Query = @Query + 'titles t' IF @Author <> '' AND @Title <> ''     SET @Query = @Query + ' ON t.title_id = ta.title_id' IF @Author <> '' OR @Title <> ''     SET @Query = @Query + CHAR(13) + 'WHERE ' IF @Author <> ''     SET @Query = @Query + 'au_lname LIKE ''%' + @Author + '%''' IF @Author <> '' AND @Title <> ''     SET @Query = @Query + CHAR(13) + 'AND ' IF @Title <> ''     SET @Query = @Query + 'title LIKE ''%' + @Title + '%''' PRINT @Query EXEC (@Query)

下面是使用静态SQL方法实现同样的功能： DECLARE @Author varchar(20), @Title varchar(20) SET @Author = '' SET @Title = 'e' IF @Author <> '' AND @Title = '' BEGIN      SELECT *      FROM authors      WHERE au_lname LIKE '%' + @Author + '%' END IF @Title <> '' AND @Author = '' BEGIN       SELECT *       FROM titles       WHERE title LIKE '%' + @Title + '%' END IF @Title <> '' AND @Author <> '' BEGIN       SELECT *       FROM authors a          INNER JOIN titleauthor ta                  ON ta.au_id = a.au_id             INNER JOIN titles t                  ON t.title_id = ta.title_id       WHERE au_lname LIKE '%' + @Author + '%'             AND title LIKE '%' + @Title + '%' END

对于以上两种解决方案，必须经过测试方保保证返回的相同的结果集，另外需要注意的是在采用动态SQL方案时，若没有更正使用单引号问题，则会破坏查询的执行，未授权代码将得以执行。

静态SQL易于维护

     只有不为用户授权对整个数据库的读写权限时，静态SQL才变得易于维护，原因是动态SQL需要为其执行时引用的每一张表或对象进行必要的授权，而静态SQL只需授权存储过程的执行权限即可。使用静态SQL的另外一个优势就是SQL Server可以帮你检查SQL语法的有效性，而动态SQL来说，SQL Server只有在运行时才可以判断其语法的有效性。
    随着动态SQL代码的增加，编写的查询也变得更为复杂，代码可读性也较差，另外执行查询的变量也可能会受到8000个字符串的限制（虽然在SQL Server 2005中为VARCHAR(MAX)），当将动态SQL转换为静态SQL的时间也会翻倍增加。

小结：
    当编写代码时应该尽量考虑使用静态SQL，仅在找不到使用静态SQL的方法时才考虑动态SQL。