什么是參數(shù)化查詢?

一，定義

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

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

原理

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

SQL 指令撰寫方法

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

Microsoft SQL Server

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

SELECT * FROM myTable WHERE myID = @myID

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

Microsoft Access

Microsoft Access 不支持具名參數(shù)，只支持匿名參數(shù) "?"。

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

MySQL

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

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

Oracle

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

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

PostgreSQL

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

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

PostgreSQL也支持Oracle的參數(shù)表示形式

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

總結(jié)一下各數(shù)據(jù)庫對于參數(shù)符號的定義：

SQLSERVER @

Access，MySQL ?

Oracle ：

PostgreSQL $

上面的這些符號是各數(shù)據(jù)庫內(nèi)部原生支持的方式，但是具體到ADO.NET調(diào)用的時候，采用各數(shù)據(jù)庫原生的.NET驅(qū)動程序，發(fā)現(xiàn)除了Oracle，各種數(shù)據(jù)庫都可以在SQL語句中用@符號表示參數(shù);

采用各數(shù)據(jù)庫的OleDB或者ODBC驅(qū)動程序，都要求使用 ?符號表示參數(shù)。

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

二，抽象SQL參數(shù)化查詢

在PDF.NET數(shù)據(jù)開發(fā)框架中，對參數(shù)的定義統(tǒng)一采用##來處理，具體格式如下：

#參數(shù)名字[:參數(shù)類型],[數(shù)據(jù)類型],[參數(shù)長度],[參數(shù)輸出輸入類型]#

上面定義當中，中括號里面的內(nèi)容都是可選的。

詳細內(nèi)容，請參看“SQL-MAP規(guī)范”

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

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

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

程序在運行時，會根據(jù)當前具體的數(shù)據(jù)庫訪問程序?qū)嵗?，?#內(nèi)部的參數(shù)替換成合適的參數(shù)內(nèi)容。

上面這種參數(shù)形式是寫在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> 

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

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

三，抽象SQL查詢：SQL-MAP技術(shù)

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

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

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

下面我們介紹一下PDF.NET數(shù)據(jù)開發(fā)框架對于存儲過程的操作思路，當然對于單條SQL也是如此。當然，單條SQL語句的操作我們不必請出SQL-MAP這種“重量級”的方式，還是使用框架中的ORM技術(shù)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");    
           //參數(shù)賦值，推薦使用該種方式；    
           cmdInfo.DataParameters[0].Value = Type;    
           cmdInfo.DataParameters[1].Value = Name;    
           cmdInfo.DataParameters[2].Value = isAIP;    
           //參數(shù)賦值，使用命名方式；    
           //cmdInfo.SetParameterValue("@Type", Type);    
           //cmdInfo.SetParameterValue("@Name", Name);    
           //cmdInfo.SetParameterValue("@isAIP", isAIP);    
           //執(zhí)行查詢    
           return CurrentDataBase.ExecuteDataSet(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText , cmdInfo.DataParameters);    
       //    
   }//End Function   

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

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

題外話：

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

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

【編輯推薦】

1. 淺述遠程Service Broker的實現(xiàn)
2. Service Broker基礎(chǔ)應(yīng)用(下）
3. Service Broker基礎(chǔ)應(yīng)用(上）
4. 簡述Service Broker事件通知功能
5. 優(yōu)化你的DiscuzNT，讓它跑起來