动态sql的魔鬼和天使

 本文是对
http://www.sommarskog.se/
其中3片文章的浅显总结
掺杂了不少个人实践中得到的观点
希望能给一些人带来帮助
有问题欢迎提出

经常使用动态的情况是:

SELECT * FROM @tablename

SELECT @colname FROM tbl

SELECT * FROM tbl WHERE x IN (@list)

但是这的确是最差劲的方法.

使用动态sql不仅带来了

安全上的问题,比如sql注入,以及对表的权限的设置困难

而且返回结果集也会带来困难

当然,有问题就有解决问题的办法

比如使用限制权限,使用quotename()函数解决注入的问题

使用随机临时表解决数据集的问题

动态SQL优势呢?

对于动态查询尤其有用,比如过滤条件都是可选的情况

有时候是必须用,

比如按表名查询(但是往往这都是因为表的设计不合理造成的),

按列名查询其实就不必使用了,因为可以作为where条件来用.

但是对于除了动态查询的其他情况,怎样避免使用动态sql的麻烦呢?

比如in (@str) 的情况

那就是使用表的join操作.这里有个很有用的返回表值得函数.

下面详细叙述:

动态sql的使用:

	Sp_executesql	Exec()
优势	可以使用output参数导出对象	没有长度限制
不足	有长度的限制,除非使用nvarchar(max)	不方便导出变量 (后来了解到使用insert 表名或临时表  exec(@sql) 可以导出.) 不能在exec()括号内使用函数.如quotename()
注意:	Exec sp_executesql @sql , @sql 中的参数 @传入(出)的参数 这里的第二项用处不是太大.尤其表名,列名更是不能在这里设置.而要在@sql 中就连接好.
	l        执行的语句中的变量都只在这个语句块中有效. l        如果不是执行一个nvarchar()的变量,而是一个单引号的字符串(被当作varchar)的话,会造成错误提示,所以要在单引号边上加上N,成为unicode. l        避免引号的问题使用quotename(),避免全球语言的问题使用N’’.但是quotename(,’’’’)会把字符串两端的空格删除. l        查询的表名前面最好加上dbo.对提高效率有帮助,尤其在sql2000里,不加的话它会先查询用户数据库,再查询dbo. l        在单引号字串中的单引号要用两个单引号表示:’’’’表示一个单引号. l        Print 执行的sql以便于查错 l        不要使用select * 要明确列名.

Sql注入

(参数化可以完全解决注入,以及引号问题,

关键是动态sql要防范这2个问题.

尤其在动态sql的参数方面注意就可以了.参数要按列分别对待.否则不容易避免注入.

如果不使用quotename(),那么对单引号输入就会报错,从而让别有目的的人知道你的sql语句结构.

但是这个不能从根本上解决注入问题.

进而可以加双短线 -- 把你以后的句子注释掉

不仅输入框可以注入,url地址,cookies也是攻击的武器.

方法:

给予访问者适当的权限,不要太大.

不要让iis报明确的错误给用户.

用sp_executesql  不用exec().因为前者可以参数化sql语句.

declare @sql nvarchar(100)

set @sql = N'select * from tblList where List_FileName like  '+@id

 exec sp_executesql @sql
  
  

当输入:@id = N'75 ; select * from tblDic'
   
   

则会把tblDic 选出来.
  
  

如是delete呢?后果不堪设想.
  
  

可行的方法有两个
  
  

一是参数化 
  
  

declare @sql nvarchar(100)

set @sql = N'select * from tblList where List_FileName like   @id'

 exec sp_executesql @sql ,N'@id nvarchar(100)',@id
  
  

这样输入什么都可以
  
  

二是 使用quotename(,’’’’)进行字符类的转化.注意,不能是quotename()必须是quotename(,’’’’)

否则就转化成列名了.

declare @sql nvarchar(100)

set @sql = N'select * from tblList where List_FileName like   ' + quotename(@id,'''')

 exec sp_executesql @sql
  
  

但是这第一个解决方案有个弊端,
  
  

就是对于表名或者列名,它们是不能参数化的.而只能字符串组合构成sql句.解决方法就只能是第二个了.
  
  

declare @sql nvarchar(100)

set @sql = N'select * from ' +quotename(@id)

 exec sp_executesql @sql
  
  

quotename()

而第二个使用quotename()也并非完美
  
  

只能处理nvarchar(128)以内的,长度超过128就会报错了,那么用个自定义的吧
  
  

CREATE FUNCTION quotestring(@str nvarchar(1998)) RETURNS nvarchar(4000) AS

BEGIN

   DECLARE @ret nvarchar(4000),

           @sq  char(1)

   SELECT @sq = ''''

   SELECT @ret = replace(@str, @sq, @sq + @sq)

   RETURN(@sq + @ret + @sq)

END

务必注意的是,quotename()在表名是动态的时候使用,
  
  

因为相当于加上了[ ],如果用quotename(,’’’’)那么就相当于一个常数串了,显然不行.
  
  

同样的,
  
  

如果你的字串是对列的操作,比如某个varchar型的列等于几: col = quotename (,’’’’) ,这样就可以把sql 句变成
  
  

Col = ‘a bc…’,显然是没有错误的
  
  

而,如果是某个非以上意义的串,
  
  

比如对’select * ’这个字串使用quotename(,’’’’)那么就只能等待报错了.因为你得到的是
  
  

‘select * ’ from ….引号成为了多余.
  
  

动态sql不能用于用户定义函数

自定义函数不能更改表的状态.

如果希望这样的话,放弃吧.

 

动态游标

利用Output参数

 

DECLARE @my_cur CURSOR

EXEC sp_executesql

     N'SET @my_cur = CURSOR STATIC FOR 

       SELECT name FROM dbo.sysobjects; 

       OPEN @my_cur',

     N'@my_cur cursor OUTPUT', @my_cur OUTPUT

FETCH NEXT FROM @my_cur

看似必须用动态sql的情况

l         Select * from @tblName    最好不要用,肯定是数据库设计错误才会把表名当错误.

l         …order by @colName       可以用case代替

SELECT col1, col2, col3

FROM   dbo.tbl

ORDER  BY CASE @col1

               WHEN 'col1' THEN col1

               WHEN 'col2' THEN col2

               WHEN 'col3' THEN col3

           END

但,如果col1,2,3是不同数据类型的列,那么就要换个样子了

SELECT col1, col2, col3

FROM   dbo.tbl

ORDER  BY CASE @col1 WHEN 'col1' THEN col1 ELSE NULL END,

          CASE @col1 WHEN 'col2' THEN col2 ELSE NULL END,

          CASE @col1 WHEN 'col3' THEN col3 ELSE NULL END

l         Select top @number from…

Server2005 可以直接参数化只用加个括号,select top (@topnumber) …

对于2000, 

CREATE PROCEDURE get_first_n @n int AS

SET ROWCOUNT @n

SELECT au_id, au_lname, au_fname

FROM   authors

ORDER  BY au_id

SET ROWCOUNT 0

注意,这里不是set rowcount = @n

而是set rowcount @n

Rowcount 不同于@@rowcount,一个是输入值,一个是输出值.

 
 l        
 
 Select * from tbl where @conditions

除了动态查询有这个必要.

  
  l        
  
  SELECT * FROM tbl WHERE col IN (@list)

下面介绍后两项的解决方案

In (@filter)

还是先迫不及待的宣告这个方法

CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))

   RETURNS @tbl TABLE (number int NOT NULL) AS

BEGIN

   DECLARE @pos        int,

           @nextpos    int,

           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0

   BEGIN

      SELECT @nextpos = charindex(',', @list, @pos + 1)

      SELECT @valuelen = CASE WHEN @nextpos > 0

                              THEN @nextpos

                              ELSE len(@list) + 1

                         END - @pos - 1

      INSERT @tbl (number)

         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))

      SELECT @pos = @nextpos

   END

  RETURN

END

 

图中的红色就是需要根据自己的数据类型更改的.

 

CREATE PROCEDURE get_product_names_iter @ids varchar(50) AS

   SELECT P.ProductName, P.ProductID

   FROM   Northwind..Products P

   JOIN   iter$simple_intlist_to_tbl(@ids) i ON P.ProductID = i.number

go

EXEC get_product_names_iter '9, 12, 27, 37'

 

这样就不需要只要碰到in()参数化,就要用动态sql了.

 

上述方法存在一个问题,

如果遇到 连续的逗号  ,, 就完了

而且返回的列的类型是nvarchar() 还是varchar()需要考虑好.否则会带来效率上的问题

因为nvarchar()级高于varchar()

 

有时候你会处理这样的表

固然这是一个很烂的表,

但是为了查询,你可能需要换成

A200   blue

A200   green

A200   magenta

….

Server2005有个新办法  apply

 

SELECT m.modelid, t.str AS colour

FROM   models m

CROSS  APPLY  iter_charlist_to_tbl(m.colours, ',') AS t

ORDER  BY m.modelid, t.str

Apply 和join很相似,但是join的对象不能是表值函数,而apply可以

Apply 也有cross apply 和 outer apply,含义很明显.

动态查询

使用动态语句:

CREATE PROCEDURE search_orders_1                                   --  1

                 @orderid   int          = NULL,                   --  2

                 @fromdate  datetime     = NULL,                   --  3

                 @todate    datetime     = NULL,                   --  4

                                                                   -- 15

DECLARE @sql        nvarchar(4000),                                -- 16

        @paramlist  nvarchar(4000)                                 -- 17

                                                                   -- 18

SELECT @sql =                                                      -- 19

    'SELECT ..                                                     -- 24

     FROM   dbo.Orders o                                           -- 25

     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID       -- 26

     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID         -- 27

     JOIN   dbo.Products p ON p.ProductID = od.ProductID           -- 28

     WHERE  1 = 1'                                                 -- 29

                                                                   -- 30

IF @orderid IS NOT NULL                                            -- 31

   SELECT @sql = @sql + ' AND o.OrderID = @xorderid' +             -- 32

                        ' AND od.OrderID = @xorderid'              -- 33

                                                                   -- 34

IF @fromdate IS NOT NULL                                           -- 35

   SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate'           -- 36

                                                                   -- 37

IF @todate IS NOT NULL                                             -- 38

   SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate'             -- 39

                                                                   -- 40

                                                                   -- 74

SELECT @paramlist = '@xorderid   int,                              -- 75

                     @xfromdate  datetime,                         -- 76

                     @xtodate    datetime,                         -- 77

                                                                   -- 87

EXEC sp_executesql @sql, @paramlist,                               -- 88

                   @orderid, @fromdate, @todate, @minprice,        -- 89

                   @maxprice,  @custid, @custname, @city, @region, -- 90

                   @country, @prodid, @prodname                    -- 91

 

 

如果使用静态的呢,也不是没办法:

方法一  X =@x or @x is null

  (o.OrderID = @orderid OR @orderid IS NULL)

  AND  (o.OrderDate >= @fromdate OR @fromdate IS NULL)

  AND  (o.OrderDate <= @todate OR @todate IS NULL)

这样为Null时也成立了.只是只有那些非Null得才有意义.

但是问题是,在caching的情况下,不同的查询之间会相互影响很大;

所以在alter procedure 

参数后面

加上一句话: with recompile

第二个问题是,引起全表扫描,因为你用的是or.

方法二 用coalesce

o.orderID = coalesce(@orderid, o.OrderID)

方法三 用between

o.OrderID BETWEEN coalesce(@orderID, @MinInt) AND

                  coalesce(@orderID, @MaxInt)

方法四  用x = @x and @x is not null

总的来说,还是使用动态sql比较好,尽可能减少判断的次数.