報表查詢條件的 N 種使用方式

眾所周知，大拿們在設計報表的時候，需要展現的資料很少是固定不變地，經常需要使用查詢條件過濾出我們想要的資料。簡而言之就是，同一張報表可以在不同的引數控制下呈現出不同的資料。

那麼問題來了，報表工具一般都用哪些方法來實現這一需求呢？

使用 SQL引數

首先當然必須是說 SQL, 正常情況下不同的過濾條件會對應著取數 SQL 中不同的 WHERE 條件，而報表工具能將報表引數對應成 SQL 的引數，這樣一來，輸入不同報表引數時就會產生不同的 SQL。

假如我們要做一個有查詢條件的報表，拿訂購日期作為檢索條件，查詢不同日期區間下訂單資訊表中的資料。其中的資料列包括：訂單 ID, 客戶 ID, 訂購日期, 發貨日期, 運貨商, 貨主名稱, 貨主地區, 訂單金額。

Follow me，我們拿潤乾報表來做個例子，首先設定兩個引數，取名為 startdate 與 enddate:

然後這個報表的取數 SQL 要寫成含有引數的形式，SQL 語句中使用“?”（英文問號）表示引數：

select 訂單 ID, 客戶 ID, 訂購日期, 發貨日期, 運貨商, 貨主名稱, 貨主地區, 訂單金額 from 訂單資訊表 where 訂購日期 >? and 訂購日期 <?

然後要配置 SQL 引數與報表引數的對應，即將 SQL 語句中每個“?”翻譯成對應的報表引數。

配置完成後使用者只需要透過設定不同的報表引數，就可以獲取不同的資料了。例如查詢 2014 年 1 月 1 號到 2014 年 1 月 8 號之間的訂單資訊，那麼設定引數 startdate 值為 2014-01-01，引數 enddate 值為 2014-01-08，查詢結果如下：

空值引數

有時候我們希望某個引數不輸入時報表可以聰明地理解為忽略該引數，比如上例中沒有輸入 enddate 時報表可以默默地查出從某個起始日期之後的所有訂單。那需要怎麼做呢？

這種情況下可以把沒有輸入的引數認為是取值為空的引數，我們只需要修改一下上面的 SQL 語句，增加對空值的判斷即可：

select 訂單 ID, 客戶 ID, 訂購日期, 發貨日期, 運貨商, 貨主名稱, 貨主地區, 訂單金額 from 訂單資訊表 where (? is null or 訂購日期 >?) and (? is null or 訂購日期 <?)

這樣，當引數值為空時就相當於被忽略了。

相應地，SQL 引數與報表引數的對應也要修改，這兩個引數在 SQL 中分別使用了兩次，各自對應了兩個問號：

這時候要查詢 2015 年 1 月 1 號之後、結束日期不限制的資料，將引數 startdate 設定為 2015-01-01，引數 enddate 為 null 就可以了，

查詢結果如下：

使用宏表示式

從上例可以看到，使用引數時要事先知道取數 SQL 中哪些欄位可作為查詢條件。但有時候可用於做查詢條件的欄位非常多，比如針對一個訂貨資訊表來說，還可能按客戶 ID、按地區、按運貨商等各種條件查詢。如果使用上述引數的方式，就需要設定很多的引數，取數 SQL 語句也很長，使用和維護都比較麻煩。如果實現按任意欄位查詢的話，無疑會方便許多。

有些報表工具提供了宏表示式，可以更靈活地解決這個問題。仍然以潤乾報表為例說明：

首先在報表中定義一個字串引數，比如名稱為 search。然後在取數 SQL 中使用宏表示式引用這個引數：

select 訂單 ID, 客戶 ID, 訂購日期, 發貨日期, 運貨商, 貨主名稱, 貨主地區, 訂單金額 from 訂單資訊表 where ${search}

其中 ${search} 的意思就是會將 search 的內容拼接到 SQL 的相應位置得到一句可執行的 SQL 語句。這種寫法即稱為宏表示式（下面討論時就簡稱宏）。

現在給報表傳遞不同的引數值就可以得到不同的資料了。例如要查詢訂單日期為 2014 年 1 月 1 號到 2014 年 1 月 8 號且貨主地區為華北的訂單資訊，那麼設定 search 的值為：

訂購日期 >’2014-01-01′ and 訂購日期 <‘2014-01-08′ and 貨主地區 =’華北’

查詢結果如下：

從上可以看出利用宏可以在報表中輕易替換 SQL 語句的部分甚至全部，不同引數值，在報表中可以計算出不同的表示式結果，從而得到不同的報表資訊，這樣面對大量不同型別的報表，使用者再也不用每次都做單獨的設計和維護了，大大減輕了工作量，釋放了勞動力。

引數與宏的對比

既然利用宏可以在報表中動態替換 SQL 語句的部分甚至全部，而且顯然使用宏比 SQL 引數更靈活，另外引數能做到的事情用宏都能做到，那麼報表工具還有必要提供 SQL 引數機制嗎？是不是隻要有宏就夠了？

事實上並沒有這麼簡單。宏只是簡單地替換 SQL 語句的部分，要保證最後的語句合法，有時在生成引數字串會有麻煩。數值等簡單資料型別可以直接拼進 SQL，而字串常數就需要加上引號才可以，萬一這個常數中本身已經有引號，則還需要先進行轉義動作，而不同資料庫的規則又並不完全一樣，這就會影響報表的相容性。

日期型別引數是最常出現這種麻煩的情況，例如查詢訂購日期為 2014-12-01 號的訂單資訊，如果使用 Oracle 資料庫，那麼宏值寫法是：

訂購日期 =TO_DATE(‘2014-12-01′,’yyyy-mm-dd’)

如果使用 Mysql 資料庫，那麼宏值寫法為：

訂購日期 = DATE_FORMAT(‘2014-12-01′,’yyyy-mm-dd’)

使用 SQL 引數就沒有這個問題，資料庫介面能直接識別各種資料型別的引數, 直接傳入引數值即可，沒必要再做這種拼串動作，也就不存在相容性的問題了。

宏的 SQL植入風險

使用宏還存在安全風險，WEB 中使用報表時，引數可能是網頁生成並傳到後臺的，而網頁有可能是被駭客攻擊改寫的，那麼就有可能將惡意的引數值傳到後臺拼入 SQL 語句，欺騙伺服器執行惡意 SQL 命令。這就是我們常說的 SQL 植入風險。

比如，在前面的宏表示式示例中，如果有人將 search 值改寫成：

1=1 UNION SELECT userid,password,null,… FROM user

那麼就可能造成關鍵資訊洩露。解決這個問題並不很簡單，把原始 SQL 改寫成這樣

select … where (${search})

看起來是可以擋住上面那種攻擊了，但仍然可以設計出新的植入串攻擊（讀者有興趣可以作為一個練習題來試試），要改成為這種很麻煩的樣子才行：

select…from…where(${search}) and ${search}

顯然，這種語句需要有強烈安全意識的老司機才能做到，而一般報表開發人員這方面相對會弱一些。所以宏不能亂用，能不用則不用。而使用 SQL 引數就沒有這個風險，雖然引數沒有宏靈活，但顯然安全性對報表工具是必不可少的。 個別報表工具為了省事只提供宏實現查詢條件，看起來是靈活了，但其實是相當不負責任的做法，在選擇報表工具時一定要注意。

潤乾報表解決方案

如果使用潤乾報表，上面的問題就簡單多了，潤乾報表 5 中新增了對引數檢驗的功能，這就即可享受宏的靈活性，又能很輕鬆地擋住這種攻擊。仍以上面的業務場景為例，實現步驟如下：

1、製作有宏表示式的報表

在報表中定義一個宏命名為：search，資料集 SQL 為：

select 訂單 ID, 客戶 ID, 訂購日期, 發貨日期, 運貨商, 貨主名稱, 貨主地區, 訂單金額 from 訂單資訊表 where ${search}

2、配置引數中的禁用單詞

在【安裝目錄】\esProc\config\raqsoftConfig.xml 檔案中的 disallowedParamWordList 屬性裡配置禁用字串，配置多個字串時使用逗號分隔，例如

<property name=”disallowedParamWordList” value=”union, user”/>

將會在引數中禁用“union”與“user”，即含有這些串的引數將被拒絕。

3、在 tag 標籤上設定檢查

用 tag 標籤 paramCheck 來控制報表是否要做此校驗，paramCheck=”yes”表示接受校驗，paramCheck=”no”表示不作校驗，不配置該標籤時預設為接受校驗。這樣，不是所有報表都一定會被檢查，有些可以確認安全的報表就不必再檢查以獲得靈活性。

這些都完成後，在 web 端瀏覽報表時，使用者輸入的檢索條件會在報表引數校驗透過後才傳遞給報表，最終拼接到資料集 SQL 中，不合法的引數值將被拒絕，從而避免了上例中 SQL 植入的風險。