什么是參數化查詢?

一，定義

參數化查詢(Parameterized Query 或 Parameterized Statement)是指在設計與數據庫鏈接并訪問數據時，在需要填入數值或數據的地方，使用參數 (Parameter) 來給值，這個方法目前已被視為最有效可預防SQL注入攻擊 (SQL Injection) 的攻擊手法的防御方式。

有部份的開發人員可能會認為使用參數化查詢，會讓程序更不好維護，或者在實現部份功能上會非常不便，然而，使用參數化查詢造成的額外開發成本，通常都遠低于因為SQL注入攻擊漏洞被發現而遭受攻擊，所造成的重大損失。

原理

在使用參數化查詢的情況下，數據庫服務器不會將參數的內容視為SQL指令的一部份來處理，而是在數據庫完成 SQL 指令的編譯后，才套用參數運行，因此就算參數中含有具有損的指令，也不會被數據庫所運行。

SQL 指令撰寫方法

在撰寫 SQL 指令時，利用參數來代表需要填入的數值，例如：

Microsoft SQL Server

Microsoft SQL Server 的參數格式是以 "@" 字符加上參數名稱而成，SQL Server 亦支持匿名參數 "?"。

SELECT * FROM myTable WHERE myID = @myID

INSERT INTO myTable (c1, c2, c3, c4) VALUES (@c1, @c2, @c3, @c4)

Microsoft Access

Microsoft Access 不支持具名參數，只支持匿名參數 "?"。

UPDATE myTable SET c1 = ?, c2 = ?, c3 = ? WHERE c4 = ?

MySQL

MySQL 的參數格式是以 "?" 字符加上參數名稱而成。

UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4

Oracle

Oracle 的參數格式是以 ":" 字符加上參數名稱而成。

UPDATE myTable SET c1 = :c1, c2 = :c2, c3 = :c3 WHERE c4 = :c4

PostgreSQL

PostgreSQL 的參數格式是以 "$" 字符加上參數順序號而成。

UPDATE myTable SET c1 = $1, c2 = $2, c3 = $3 WHERE c4 = $4

PostgreSQL也支持Oracle的參數表示形式

--------------------------------------------------------------------------------

總結一下各數據庫對于參數符號的定義：

SQLSERVER @

Access，MySQL ?

Oracle ：

PostgreSQL $

上面的這些符號是各數據庫內部原生支持的方式，但是具體到ADO.NET調用的時候，采用各數據庫原生的.NET驅動程序，發現除了Oracle，各種數據庫都可以在SQL語句中用@符號表示參數;

采用各數據庫的OleDB或者ODBC驅動程序，都要求使用 ?符號表示參數。

還有其它本文未說到的數據庫，他們的SQL語句表示參數的符號可能都是不一樣的，怎么樣在程序里面統一處理呢?本文主題開始了：

二，抽象SQL參數化查詢

在PDF.NET數據開發框架中，對參數的定義統一采用##來處理，具體格式如下：

#參數名字[:參數類型],[數據類型],[參數長度],[參數輸出輸入類型]#

上面定義當中，中括號里面的內容都是可選的。

詳細內容，請參看“SQL-MAP規范”

對本文第一部分的示例，可以改寫成下面的方式：

UPDATE myTable SET   
c1 = #c1#,   
c2 = #c2:String#,   
c3 = #c3:String,Sring,50#   
WHERE c4 = #c4:Int32#  

如果不指定參數的類型，默認為String類型，例如c1參數。

程序在運行時，會根據當前具體的數據庫訪問程序實例，將##內部的參數替換成合適的參數內容。

上面這種參數形式是寫在SQL-MAP配置文件里面的，例如下面的一個實際的SQL-MAP查詢腳本：

<Select CommandName="GetStatisticsAnalysis_SalerRoleStatistics" CommandType="Text" Method="" Description="" ResultClass="DataSet"> 
       <![CDATA[  
    SELECT a.角色,a.銷售金額/10000 銷售金額,a.占比 FROM [GetStatisticsAnalysis_SalerRoleStatistics] (  
   #manageid:Int32#, #min:String#, #max:String#) a]]> 
     </Select> 

通過這種方式，完全屏蔽了不同種類的數據庫查詢的參數問題，將SQL參數化查詢抽象了出來。

看到這里本文似乎該結束了，但本文的標題“參數化”加了一個括號，說明我們抽象的不僅僅是參數，我們還可以抽象整個SQL查詢。

三，抽象SQL查詢：SQL-MAP技術

在本文第二部分，我們將SQL中的參數“抽象化”了，我們還可以進一步抽象整個SQL，看下面的抽象過程：

1. 編寫任意形式的合法SQL查詢語句;
2. 抽象SQL中的參數;
3. 將整個SQL語句抽象成一個唯一名字為CommandName;
4. 將一組CommandName映射到一個DAL類文件;
5. 將這個CommandName映射到一個DAL類的方法名稱;
6. 將SQL語句中的參數名稱映射到該DAL類的當前方法中的參數名稱;
7. 將整個SQL腳本文件映射到一個DAL程序集。

這個思想，就是SQL-MAP，將SQL語句映射為程序的。

下面我們介紹一下PDF.NET數據開發框架對于存儲過程的操作思路，當然對于單條SQL也是如此。當然，單條SQL語句的操作我們不必請出SQL-MAP這種“重量級”的方式，還是使用框架中的ORM技術OQL吧，但這不是本文討論的話題。

首先，在SQL-MAP配置文件里面寫下面的腳本：

<Select CommandName="GetProductManage_FundSaleAndAIP" Method="" CommandType="Text" Description="獲取XXX列表" ResultClass="DataSet">    
<![CDATA[    
select * from GetProductManage_FundSaleAndAIP(#Type:String#,#Name:String#,#isAIP:String#)    
]]>   
</Select>    

注意腳本中的ResultClass屬性，它可以將查詢映射成為單值，DataSet，實體類，實體類集合。

有了這個SQL-MAP文件，我們可以使用代碼工具自動生成下面的代碼(當然你也可以手寫)：

/// <summary>    
   /// 獲取XXXXX列表    
   /// </summary>    
   /// <param name="Type"></param>    
   /// <param name="Name"></param>    
   /// <param name="isAIP"></param>    
   /// <returns></returns>    
   public DataSet GetProductManage_FundSaleAndAIP(String Type  , String Name  , String isAIP   )     
   {     
           //獲取命令信息    
           CommandInfo cmdInfo=Mapper.GetCommandInfo("GetProductManage_FundSaleAndAIP");    
           //參數賦值，推薦使用該種方式；    
           cmdInfo.DataParameters[0].Value = Type;    
           cmdInfo.DataParameters[1].Value = Name;    
           cmdInfo.DataParameters[2].Value = isAIP;    
           //參數賦值，使用命名方式；    
           //cmdInfo.SetParameterValue("@Type", Type);    
           //cmdInfo.SetParameterValue("@Name", Name);    
           //cmdInfo.SetParameterValue("@isAIP", isAIP);    
           //執行查詢    
           return CurrentDataBase.ExecuteDataSet(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText , cmdInfo.DataParameters);    
       //    
   }//End Function   

從上面的過程可以看出，框架采用SQL-MAP技術，將SQL語句(包括各種查詢的單條SQL語句和存儲過程等)映射成了DAL層代碼，整個過程不需要了解.NET開發技術，所以DAL層的代碼完全可以由DBA來寫，而業務開發人員只要調用DAL代碼即可。

采用這種技術，DBA可以寫高效的有數據庫特性的SQL，如果要換數據庫，只需要換一個配置文件即可，不需要重寫程序。

題外話：

SQL-MAP思想并非PDF.NET數據開發框架獨有，實際上，該思想也是從著名的iBatis框架借鑒而來的，但與iBatis不同的是，PDF.NET的SQL-MAP參數不需要定義專門的“參數類”，也不需要寫額外的XML文件指明查詢結果如何與實體類映射，所以整個開發過程大大簡化，簡化到你只需要會寫SQL語句，就可以寫DAL代碼。

原文鏈接：http://www.cnblogs.com/bluedoctor/archive/2011/05/06/2038727.html

【編輯推薦】

1. 淺述遠程Service Broker的實現
2. Service Broker基礎應用(下）
3. Service Broker基礎應用(上）
4. 簡述Service Broker事件通知功能
5. 優化你的DiscuzNT，讓它跑起來