【个人学习笔记14之--浅谈动态SQL】

动态SQL

 

 

 

动态的SQL和游标一样也是一个很有争议的东西,不过我觉得貌似大家对他的排斥不是很高.

 

 

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

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

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

我就在提几点注意的地方:

 

1.EXEC

 a.不要在exec()括号里面用case 或者一些函数 比如rtrim(),quotename()。这是不允许的
DECLARE @schemaname AS NVARCHAR(128), @tablename AS NVARCHAR(128); SET @schemaname = N'dbo'; SET @tablename = N'Order Details'; EXEC(N'SELECT COUNT(*) FROM ' + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N';'); 
 --这里是会发生错误的.

 b.批处理内定义的局部变量是不可以在动态语句里面访问的--这个就是所谓的EXEC不提供接口了
  DECLARE @i AS INT; SET @i = 10248; DECLARE @sql AS VARCHAR(52); SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = @i;'; EXEC(@sql);   

 /*
   必须声明标量变量 "@i"。
  */
  ---处理方法就是将这个变量串联到字符串里去
DECLARE @i AS INT; SET @i = 10248; DECLARE @sql AS VARCHAR(52); SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = ' + rtrim(@i) + N';'; EXEC(@sql); 
  ------可惜-----这么做不但不太安全,而且串联变量上去后,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.关于会话环境和动态语句的相互影响问题
  我直接上例子进行说明问题：
USE Northwind; DECLARE @db AS NVARCHAR(258); SET @db = QUOTENAME(N'pubs'); EXEC(N'USE ' + @db + ';'); --虽然在动态批处理中改变了数据库名,但是并没有能影响到外面的批处理的db_name()函数 SELECT DB_NAME(); GO /* --------------- Northwind */ USE Northwind; DECLARE @db AS NVARCHAR(258); SET @db = QUOTENAME(N'pubs'); EXEC(N'USE ' + @db + N' SELECT DB_NAME();'); --这里在动态批处理内部处理调用db_name()函数,当然是有效的. GO /* ----------------- pubs */ USE Northwind; DECLARE @db AS NVARCHAR(258); SET @db = QUOTENAME(N'pubs'); EXEC(N'USE ' + @db + N'; EXEC(''SELECT DB_NAME();'');'); --这里在动态批处理内部的内部调用db_name(),说明动态处理是能影响其内部级别的. /* ----------------- pubs */ 
  -----总结起来说:动态批处理的处理只在动态处理自己的级别和它内部的级别有用,但是无法影响外面的批处理的---------
  
 4.动态语句和临时表
use tempdb go exec ('create table #(a int ) insert # select 1 ') --在动态语句内部建立的临时表 在外面是不可见的 select * from # /* 对象名 '#' 无效。 */ use tempdb go exec ('create table #(a int ) insert # select 1 ;exec (''select * from #'')') --在动态语句内部建立的临时表在动态语句的内部级别包括它自己这个级别是可见的 /* a ----------- 1 */    
 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--