学步园

动态SQL

不好的地方:
1.动态SQL是非常危险的,因为动态SQL一般是根据用户的输入来构造你的整个SQL语句,也就是我们平时看到一些参数参与了SQL语句,一般的黑客也会
 利用这个空洞进行注入,这是我对注入最简单理解,利用手写进去的一些代码,改变本来的SQL语句结构.
2.动态语句生涩难搞,经常会出现引号错误,参数转换错误等.

好的地方:
它能处理一些静态的SQL语句不能处理的查询任务.比如平时遇到的行转列的时候,转换成列的时候你不知道你要转哪几个列.这个时候用动态就不需要担心了.

执行动态语句的方式: EXEC(execute)和 sp_executesql
由于后者提供了输入/输出接口;后者更有可能重用执行计划;后者可以编写更加安全的代码；
所以没有特殊的情况下,一般我们就是用sp_executesql。。。切忌
关于这2个口令在动态语法中的应用的基础知识：(http://blog.csdn.net/roy_88/article/details/3020586)

    --动态语句语法
    /******************************************************************************************************************************************************
    动态语句语法:exec/sp_executesql语法
    整理人：中国风(Roy)
    日期:2008.06.06
    ******************************************************************************************************************************************************/
    动态语句语法:
    --方法1查询表改为动态
    select * from sysobjects
    exec('select ID,Name from sysobjects')
    exec sp_executesql N'select ID,Name from sysobjects'--多了一个N为unicode
    --方法2:字段名，表名，数据库名之类作为变量时,用动态SQL 
    declare @FName varchar(20)
    set @FName='ID'
    exec('select '+@FName+' from sysobjects where '+@FName+'=5' )
    declare @s varchar(1000)
    set @s=N'select '+@FName+' from sysobjects where '+@FName+'=5'
    exec sp_executesql @s--会报错
    declare @s nvarchar(1000)--改为nvarchar
    set @s=N'select '+@FName+' from sysobjects where '+@FName+'=5'
    exec sp_executesql @s--成功
    --方法3:输入参数
    declare @i int,@s nvarchar(1000)
    set @i=5
    exec('select ID,Name from sysobjects where ID='+@i)
    set @s='select ID,Name from sysobjects where ID=@i'
    exec sp_executesql @s,N'@i int',@i--此处输入参数要加上N
    --方法4:输出参数
    declare @i int,@s nvarchar(1000)
    set @s='select @i=count(1) from sysobjects'
    --用exec
    exec('declare @i int '+@s+' select @i')--把整个语句用字符串加起来执行
    --用sp_executesql
    exec sp_executesql @s,N'@i int output',@i output--此处输出参数要加上N
    select @i
    --方法5:输入输出
    --用sp_executesql
    declare @i int,@con int,@s nvarchar(1000)
    set @i=5
    select @s='select @con=count(1) from sysobjects where ID>@i'
    exec sp_executesql @s,N'@con int output,@i int',@con output ,@i 
    select @con
    --用exec
    declare @i int,@s nvarchar(1000)
    set @i=5
    select @s='declare @con int select @con=count(1) from sysobjects where ID>'+rtrim(@i)+' select @con'
    exec(@s)

几点注意的地方:

 
1.EXEC

 a.不要在exec()括号里面用case 或者一些函数 比如rtrim(),quotename()。这是不允许的

 
 --这里是会发生错误的.

 b.批处理内定义的局部变量是不可以在动态语句里面访问的--这个就是所谓的EXEC不提供接口了
 

 /*
   必须声明标量变量 "@i"。
  */
  ---处理方法就是将这个变量串联到字符串里去

 
  ------可惜-----这么做不但不太安全,而且串联变量上去后,sql会为每个唯一的查询字符串创建新的执行计划.
  --比如你这个@i 10348 10349 10897 等 它就会为此产生三个执行计划 多么浪费成本.

 c.本来exec的优势
  exec以前比sp_executesql 可以支持更长的代码.因为一个是varchar(8000) 一个是nvarchar(4000),而且exec(@s1+@s2+@s3)这里的
  括号里的每个变量可以都是varchar(8000)多么庞大啊.可惜到了SQL2005 varchar(max),nvarchar(max)的出现 彻底改变了这个笨拙
  繁琐的方法.一个nvarchar(max) 可以包含二十亿的字符....是20亿...即使是nvarchar(max) 也要10来亿..你还需要拼接么?.....- -||
  所以用05的朋友尽量使用max来定义吧.
  
  
 2.sq_executesql

  a.提供了很强大的借口--避免了串联变量的问题 还可以输出 LOOK
   DECLARE @i AS INT;
   SET @i = 10248;
   DECLARE @sql AS NVARCHAR(46);
   SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = @oid;';
   -----请注意,上面的@oid就不要在转化成字符串联进去了 直接上 直接上 直接上
   EXEC sp_executesql
     @stmt = @sql,--这个相当存储过程一个查询主体 ,就是AS后面的东西
     @params = N'@oid AS INT',--这个相当于存储过程的传入参数声明,就是AS前面的东西
     @oid = @i;--这里相当于你调用存储过程的时候 后面那个参数列表
   ------这里如果你给@id赋值三个不同的值 10348 10349 10897 它为此产生的执行计划只有一个哦~只有一个 不是三个！！！节约成本

  b.它可以通过传入的参数检查,用来防止SQL注入.
   我的理解就是通过传入的参数的长度类型之类的进行判断吧.
   
 3.关于会话环境和动态语句的相互影响问题
  我直接上例子进行说明问题：

 
  -----总结起来说:动态批处理的处理只在动态处理自己的级别和它内部的级别有用,但是无法影响外面的批处理的---------
  
 4.动态语句和临时表

   
 5.动态语句和临时变量
    use tempdb
    go
  exec ('declare @s int select isnull(@s,1)')
  --可以执行
  exec ('declare @s int ; exec(''select isnull(@s,1)'')')
  --在动态语句内部建立的临时变量在其内部级别是不可见的
  /*
  必须声明标量变量 "@s"。
  */
  
 6.非常浅得说说SQL注入
  通俗说：就是通过输入恶意代码串联到动态语句中 做坏事-- - -||书上例子我说明下
  
  a.客户端输入：
   CREATE TABLE dbo.Users
  (
    username VARCHAR(30) NOT NULL PRIMARY KEY,
    pass     VARCHAR(16) NOT NULL
  );
  
  INSERT INTO Users(username, pass) VALUES('user1', '123');
  INSERT INTO Users(username, pass) VALUES('user2', '456');
  GO
  
  -- 通常客户端程序里使用下面代码来查询验证用户的身份
  /*
  sql = "SELECT COUNT(*) AS cnt FROM dbo.Users WHERE username = '" _
    & InputUserName & "' AND pass = '" & InputPass & "';"
  
  InputUserName = "user1"
  InputPass     = "123"
  */
  --通过上面的输入,产生下面的句子
  SELECT COUNT(*) AS cnt FROM dbo.Users WHERE username = 'user1' AND pass = '123';
  --如果你是黑客 输入下面的用户名 密码
  /*
  InputUserName = "' OR 1 = 1 --"
  InputPass = ""
  */
  --它的查询语句就变成这样
  SELECT COUNT(*) AS cnt FROM dbo.Users WHERE username = '' OR 1 = 1 --' AND pass = '';
  --好吧 ,现在你可以不用身份 畅通无阻 可怕~
  GO
  
  b.服务端输入:
  USE Northwind;
  GO
  --创建存储过程 通过传入订单号 查询ORDERS表的记录
  IF OBJECT_ID('dbo.usp_getorders') IS NOT NULL
    DROP PROC dbo.usp_getorders;
  GO
  
  CREATE PROC dbo.usp_getorders
    @orders AS VARCHAR(1000)
  AS
  
  DECLARE @sql AS NVARCHAR(4000);
  
  SET @sql = 'SELECT OrderID, CustomerID FROM dbo.Orders WHERE OrderID IN('
    + @orders + ');';
  
  EXEC sp_executesql @sql;
  GO
  --输入这个 OK 普通查询
  EXEC dbo.usp_getorders '10248,10249,10250';
  --输入这个--
  EXEC dbo.usp_getorders ' --';
  --会弹出错误
  /*
  Msg 102, Level 15, State 1, Line 1
  '(' 附近有语法错误。
  */
  --这里就会发现是动态语句,并且左边括号后面可以输入值，随便输入个,它会返回空值,只有表结构
  EXEC dbo.usp_getorders '-1) --';
  /*
  OrderID     CustomerID
  ----------- ----------
  
  */
  --拼接下面字符串 可以搜索到你的sysobjects理的一些你不想让别人看到的记录
  EXEC dbo.usp_getorders '-1) UNION ALL SELECT id, name FROM sysobjects --';
  --更加可怕的是：如果你输入一些很坏的语句 进行破坏 那你就悲剧了 如
  EXEC usp_getorders '-1) UPDATE dbo.Customers SET Phone = ''9999999'' WHERE CustomerID = ''ALFKI'' --';
  --上面这个语句将把你的的Customers表 CustomerID = ''ALFKI''的用户的电话都改掉
  

  ------关于SQL注入更多信息,看水哥的整理贴http://topic.csdn.net/u/20081205/09/3dd06076-bcbe-45d4-998c-8999fdbe6fae.html