淺談動態SQL—轉載學習

動態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()。這是不允許的

[c-sharp]
view plaincopyprint?

1. DECLARE @schemaname AS NVARCHAR(128), @tablename AS NVARCHAR(128);  
2. SET @schemaname = N'dbo';  
3. SET @tablename = N'Order Details';  
4. EXEC(N'SELECT COUNT(*) FROM '  
5.      + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N';');  

 
 --這裡是會發生錯誤的.

 b.批處理內定義的局部變數是不可以在動態語句裡面訪問的--這個就是所謂的EXEC不提供介面了
 

[c-sharp]
view plaincopyprint?

1. DECLARE @i AS INT;  
2.  SET @i = 10248;  
3.    
4.  DECLARE @sql AS VARCHAR(52);  
5.  SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = @i;';  
6.  EXEC(@sql);  

  

 /*
   必須聲明標量變數 "@i"。
  */
  ---處理方法就是將這個變數串聯到字串裡去

[c-sharp]
view plaincopyprint?

1. DECLARE @i AS INT;  
2. SET @i = 10248;  
3.   
4. DECLARE @sql AS VARCHAR(52);  
5. SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = '  
6.   + rtrim(@i) + N';';  
7. 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.關於會話環境和動態語句的相互影響問題
  我直接上例子進行說明問題：

[c-sharp]
view plaincopyprint?

1. USE Northwind;  
2. DECLARE @db AS NVARCHAR(258);  
3. SET @db = QUOTENAME(N'pubs');  
4. EXEC(N'USE ' + @db + ';');     
5. --雖然在動態批處理中改變了資料庫名,但是並沒有能影響到外面的批處理的db_name()函數  
6. SELECT DB_NAME();  
7. GO  
8. /* 
9. --------------- 
10.  Northwind 
11. */   
12. USE Northwind;  
13. DECLARE @db AS NVARCHAR(258);  
14. SET @db = QUOTENAME(N'pubs');  
15. EXEC(N'USE ' + @db + N' SELECT DB_NAME();');   
16. --這裡在動態批處理內部處理調用db_name()函數,當然是有效.  
17. GO  
18. /* 
19. ----------------- 
20.   pubs 
21. */  
22. USE Northwind;  
23. DECLARE @db AS NVARCHAR(258);  
24. SET @db = QUOTENAME(N'pubs');  
25. EXEC(N'USE ' + @db + N'; EXEC(''SELECT DB_NAME();'');');  
26. --這裡在動態批處理內部的內部調用db_name(),說明動態處理是能影響其內部層級的.  
27. /* 
28. ----------------- 
29.   pubs 
30. */  

 
  -----總結起來說:動態批處理的處理只在動態處理自己的層級和它內部的層級有用,但是無法影響外面的批處理的---------
  
 4.動態語句和暫存資料表

[c-sharp]
view plaincopyprint?

1. use tempdb  
2. go  
3. exec ('create table #(a int ) insert # select 1 ')  
4. --在動態語句內部建立的暫存資料表 在外面是不可見的  
5. select * from #  
6. /* 
7. 對象名 '#' 無效。 
8. */  
9. use tempdb  
10. go  
11. exec ('create table #(a int ) insert # select 1 ;exec (''select * from #'')')  
12. --在動態語句內部建立的暫存資料表在動態語句的內部層級包括它自己這個層級是可見的  
13. /* 
14. a 
15. ----------- 
16. 1 
17. */  

   
 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