【轉】淺析Sql Server參數化查詢

標籤：

轉載至： http://www.cnblogs.com/lzrabbit/archive/2012/04/21/2460978.html

 

錯誤認識1.不需要防止sql注入的地方無需參數化
　　參數化查詢就是為了防止SQL注入用的，其它還有什麼用途不知道、也不關心，原則上是能不用參數就不用參數，為啥？多麻煩，我只是做公司內部系統不用擔心SQL注入風險，使用參數化查詢不是給自己找麻煩，簡簡單單拼SQL，萬事OK

錯誤認識2.參數化查詢時是否指定參數類型、參數長度沒什麼區別
　　以前也一直都覺的加與不加參數長度應該沒有什麼區別，僅是寫法上的不同而已，而且覺得加參數類型和長度寫法太麻煩，最近才明白其實兩者不一樣的，為了提高sql執行速度，請為SqlParameter參數加上SqlDbType和size屬性，在參數化查詢代碼編寫過程中很多開發人員忽略了指定查詢參數的類型，這將導致Managed 程式碼在執行過程中不能自動識別參數類型，進而對該欄位內容進行全表掃描以確定參數類型並進行轉換，消耗了不必要的查詢效能所致。根據MSDN解釋：如果未在size參數中顯式設定Size，則從dbType參數的值推斷出該大小。如果你認為上面的推斷出該大小是指從SqlDbType類型推斷，那你就錯了，它實際上是從你傳過來的參數的值來推斷的，比如傳遞過來的值是"username"，則size值為8，"username1"，則size值為9。那麼，不同的size值會引發什麼樣的結果呢？且經測試發現，size的值不同時，會導致資料庫的執行計畫不會重用，這樣就會每次執行sql的時候重建新的執行計畫，而浪費資料庫執行時間。

下面來看具體測試

首先清空查詢計劃

DBCC FREEPROCCACHE

傳值username，不指定參數長度，產生查詢計劃

using (SqlConnection conn = new SqlConnection(connectionString)){    conn.Open();    SqlCommand comm = new SqlCommand();    comm.Connection = conn;    comm.CommandText = "select * from Users where UserName=@UserName";    //傳值 username，不指定參數長度    //查詢計劃為(@UserName varchar(8))select * from Users where UserName=@UserName    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = "username" });    comm.ExecuteNonQuery();}

 

傳值username1，不指定參數長度，產生查詢計劃

using (SqlConnection conn = new SqlConnection(connectionString)){    conn.Open();    SqlCommand comm = new SqlCommand();    comm.Connection = conn;    comm.CommandText = "select * from Users where UserName=@UserName";    //傳值 username1，不指定參數長度    //查詢計劃為(@UserName varchar(9))select * from Users where UserName=@UserName    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = "username1" });    comm.ExecuteNonQuery();}

傳值username，指定參數長度為50，產生查詢計劃

using (SqlConnection conn = new SqlConnection(connectionString)){    conn.Open();    SqlCommand comm = new SqlCommand();    comm.Connection = conn;    comm.CommandText = "select * from Users where UserName=@UserName";    //傳值 username，指定參數長度為50    //查詢計劃為(@UserName varchar(50))select * from Users where UserName=@UserName    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,50) { Value = "username" });    comm.ExecuteNonQuery();}

 

傳值username1，指定參數長度為50，產生查詢計劃

using (SqlConnection conn = new SqlConnection(connectionString)){    conn.Open();    SqlCommand comm = new SqlCommand();    comm.Connection = conn;    comm.CommandText = "select * from Users where UserName=@UserName";    //傳值 username1，指定參數長度為50    //查詢計劃為(@UserName varchar(50))select * from Users where UserName=@UserName    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,50) { Value = "username1" });    comm.ExecuteNonQuery();}

 

使用下面語句查看執行的查詢計劃

SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql LIKE ‘%Users%‘  and sql not like ‘%syscacheobjects%‘

結果如所示

 可以看到指定了參數長度的查詢可以複用查詢計劃，而不指定參數長度的查詢會根據具體傳值而改變查詢計劃，從而造成效能的損失。

這裡的指定參數長度僅指可變長資料類型，主要指varchar，nvarchar,char,nchar等，對於 int,bigint,decimal,datetime等定長的實值型別來說，無需指定(即便指定了也沒有用)，詳見下面測試,UserID為int類 型，無論長度指定為2、20、-1查詢計劃都完全一樣為(@UserIDint)select*from Users where UserID=@UserID

using (SqlConnection conn = new SqlConnection(connectionString)){    conn.Open();    SqlCommand comm = new SqlCommand();    comm.Connection = conn;    comm.CommandText = "select * from Users where UserID=@UserID";    //傳值 2，參數長度2    //執行計畫(@UserID int)select * from Users where UserID=@UserID    comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 2) { Value = 2 });    comm.ExecuteNonQuery();}using (SqlConnection conn = new SqlConnection(connectionString)){    conn.Open();    SqlCommand comm = new SqlCommand();    comm.Connection = conn;    comm.CommandText = "select * from Users where UserID=@UserID";    //傳值 2，參數長度20    //執行計畫(@UserID int)select * from Users where UserID=@UserID    comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 20) { Value = 2 });    comm.ExecuteNonQuery();}using (SqlConnection conn = new SqlConnection(connectionString)){    conn.Open();    SqlCommand comm = new SqlCommand();    comm.Connection = conn;    comm.CommandText = "select * from Users where UserID=@UserID";    //傳值 2，參數長度-1    //執行計畫(@UserID int)select * from Users where UserID=@UserID    comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, -1) { Value = 2 });    comm.ExecuteNonQuery();}

這裡提一下，若要傳值varchar(max)或nvarchar(max)類型怎麼傳，其實只要設定長度為-1即可

using (SqlConnection conn = new SqlConnection(connectionString)){    conn.Open();    SqlCommand comm = new SqlCommand();    comm.Connection = conn;    comm.CommandText = "select * from Users where UserName=@UserName";    //類型為varchar(max)時，指定參數長度為-1    //查詢計劃為 (@UserName varchar(max) )select * from Users where UserName=@UserName    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,-1) { Value = "username1" });    comm.ExecuteNonQuery();}

當然了若是不使用參數化查詢，直接拼接SQL，那樣就更沒有查詢計劃複用一說了，除非你每次拼的SQL都完全一樣

總結，參數化查詢意義及注意點

1.可以防止SQL注入

2.可以提高查詢效能(主要是可以複用查詢計劃)，這點在資料量較大時尤為重要

3.參數化查詢參數類型為可變長度時(varchar,nvarchar,char等)請指定參數類型及長度，若為實值型別(int,bigint,decimal,datetime等)則僅指定參數類型即可

4.傳值為varchar(max)或者nvarchar(max)時，參數長度指定為-1即可

5.看到有些童鞋對於預存程序是否要指定參數長度有些疑惑，這裡補充下，若調用的是預存程序時，參數無需指定長度，如果指定了也會忽略，以預存程序 中定義的長度為準，不會因為沒有指定參數長度而導致重新編譯，不過還是建議大家即便時調用預存程序時也加上長度，保持良好的變成習慣

【轉】淺析Sql Server參數化查詢