ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 翻译:动态SQL与静态SQL-安全(第二部分)

翻译:动态SQL与静态SQL-安全(第二部分)

原创 Linux操作系统 作者:bigholy 时间:2008-12-14 10:39:03 0 删除 编辑

原文地址:http://www.sqlservercentral.com/articles/Performance+Tuning/dynamicvsstatic2/623/
在前一篇文章:动态SQL与静态SQL的第一部分中,我简要地介绍了有关使用动态SQL涉及到的安全问题。在这篇文章我打算介绍一些方法,既使有静态SQL的解决方法,但是还存在诱使你使用动态SQL的情况。

许多人之所以选择使用动态SQL,其原因在于开发和部署不需要太多的考虑,代码少。换言之,相对于静态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。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/355374/viewspace-510376/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 小知识:迭代器
请登录后发表评论 登录
全部评论

注册时间:2008-11-08

  • 博文量
    43
  • 访问量
    84622