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

一，定義

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

有部份的開(kāi)發(fā)人員可能會(huì)認(rèn)為使用參數(shù)化查詢，會(huì)讓程序更不好維護(hù)，或者在實(shí)現(xiàn)部份功能上會(huì)非常不便，然而，使用參數(shù)化查詢?cè)斐傻念~外開(kāi)發(fā)成本，通常都遠(yuǎn)低于因?yàn)镾QL注入攻擊漏洞被發(fā)現(xiàn)而遭受攻擊，所造成的重大損失。

原理

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

SQL 指令撰寫(xiě)方法

在撰寫(xiě) SQL 指令時(shí)，利用參數(shù)來(lái)代表需要填入的數(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ù)順序號(hào)而成。

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

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

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

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

SQLSERVER @

Access，MySQL ?

Oracle ：

PostgreSQL $

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

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

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

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

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

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

上面定義當(dāng)中，中括號(hào)里面的內(nèi)容都是可選的。

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

對(duì)本文第一部分的示例，可以改寫(xiě)成下面的方式：

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

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

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

上面這種參數(shù)形式是寫(xiě)在SQL-MAP配置文件里面的，例如下面的一個(gè)實(shí)際的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> 

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

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

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

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

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

這個(gè)思想，就是SQL-MAP，將SQL語(yǔ)句映射為程序的。

下面我們介紹一下PDF.NET數(shù)據(jù)開(kāi)發(fā)框架對(duì)于存儲(chǔ)過(guò)程的操作思路，當(dāng)然對(duì)于單條SQL也是如此。當(dāng)然，單條SQL語(yǔ)句的操作我們不必請(qǐng)出SQL-MAP這種“重量級(jí)”的方式，還是使用框架中的ORM技術(shù)OQL吧，但這不是本文討論的話題。

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

<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，實(shí)體類，實(shí)體類集合。

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

/// <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   

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

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

題外話：

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

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

【編輯推薦】

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