說說抽象SQL(參數化)的查詢

什麼是參數化查詢?

一，定義

參數化查詢(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，看下面的抽象過程：

這個思想，就是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代碼。