# 防範 SQL injection

[toc]

:::success
#### 基礎措施
1. 將資料庫與網站伺服器分別安裝在不同的機器上，確保機器維持在最新的更新狀態。
2. 建立專用的帳號，給予執行任務所需的最小權限。在不需要使用 insert 或 update 的情形下，使用 view 的方式來存取資料庫。
4. 採用錯誤處理和記錄功能，確保資料庫發生錯誤時的訊息不會因此洩漏。
5. 選擇不易猜測的資料庫表格和欄位名稱。
> 雖然可以減少受到SQL注入的機會，但將表格和欄位名稱改成無意義的名詞，對於開發人員造成的困擾可能大於其所帶來利益
> [name=Krixi]

:::

---

## 一、ORM

ORM全名是Object-Relational Mapping(物件關係對映)。
就是將關聯式資料庫的資料，映射到物件(Object)之中，反之亦然。

關聯式資料庫與物件導向的交互有不少不契合的地方，computer science就有一個專有名詞去形容關聯式資料庫與物件導向設計之間之不協調，也就是Object-relational impedance mismatch。為數不少的物件導向概念例如接口、繼承等，在資料庫的世界，完全沒有相對應的概念。

ORM的存在意義，就是為了撫平兩者中間的不協調，將資料庫中的資料，映射到記憶體的物件之中。


Model

> ORM 就像是工程師與資料庫中間的口譯人員，有了 ORM，就不用自己寫又長又麻煩的 SQL 了。
常見的 ORM 像是 Node.js 的Mongoose (with MongoDB) 以及 Sequelize (with MySQL/PostgreSQL)，C# Entity Framework(with MySQL/PostgreSQL/MS-SQL)

舉例來說，想找出特定餐廳的資料和其關聯的相關資料，程式碼寫起來會像是下面這樣（需要先在 model 定義其關聯性）：

Restaurant.findByPk( req.params.id, {
  include: [
    Category,
    { model: User, as: 'FavoritedUsers' },
    { model: User, as: 'LikedUsers' },
    { model: User, as: 'CommentedUsers', }
  ],
})

實際執行之後，你可以在 console 裡面看到 sequelize 幫我們產生的 SQL query string 長得像下面這樣，這就是 sequelize 默默幫我們做的事情。

### 使用 ORM 的利弊

現在專案的主流很依賴框架，使用ORM感覺非常方便直覺，所以一般人不太注意 ORM 跟 SQL injection 的關聯及問題。
你有想過 ORM 除了你覺得很直覺方便外，有什麼缺點嗎?

- 優點：
> 對於習慣操作物件的工程師來說
1. 容易使用
2. 切換資料庫時方便
3. 提升安全性

- 缺點：
1. 效能問題：在普通狀況下操作還算可以，但是在 high-volume low-latency 的環境下效能不佳。

2. 在需要複雜 query 的情況下，ORM 可能會「誤判」使用者的操作，導致產出的結果與預期不同

3. 無法支援所有 SQL 處理資料的方法，導致在某些情況下，最後還是得要自己寫 SQL 來達到自己想要的目標

> [N+1問題](https://dosmanthus.medium.com/rails-n-1-queries-problem-73dfe5f99182)
> N+1 Query 會在當有 parent-child 關係的情況下(one-to-many)，載入children(many)時發生。因爲大部分的 ORM 預設使用lazy-loading，一筆 child 資料就會產生一筆 query，拖累了資料庫的效能。

### 結論
ORM 雖然方便但有其局限性，因此還是要學一下 SQL，才能夠應付 ORM 無法處理的狀況。

SQL injection 長期被列為常見網站資安漏洞，雖然它很好防範，但是很多人會忽略這個細節，因為平常使用的 ORM 幾乎防範了一切。

試想如果你用的 ORM 程式庫其底層有 SQL injection 而你卻不知道呢？

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

## 二、參數化 (Parameterized )

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

例如：

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

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

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

一般 SQL Injection 是利用傳統習慣使用動態字串結合的方式組成查詢語法，並將查詢語法結合程式碼，針對資料庫系統進行查詢或操作(給駭客竄改資料並植入攻擊字串的機會)。

若能在撰寫 SQL 查詢語法時，讓**使用者輸入的變數不是直接動態結合到 SQL 查詢語法，而是通過參數來傳遞變數**的話，就可以有效的避免 SQL Injection 。

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

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

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

為了避免 SQL injection，可以用參數化查詢方式：

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

#### 我的看法:
Stored Procedures是一個預先定義好的程序，裡面包含了一系列SQL操作。這些程序可以執行特定的資料庫任務，通常用來執行複雜的資料操作和業務邏輯。

大量使用儲存程序會使程式的邏輯隱含在資料庫內部，**對於系統功能的模組化是一大傷害。**

因此我認為儲存程序要如何使用，使用到什麼程度，**不應該從安全的角度來加以考量，而是應該回歸到開發與系統本身的需求去做考量**。

---
#### Reference
https://dosmanthus.medium.com/rails-n-1-queries-problem-73dfe5f99182
https://tech-blog.cymetrics.io/posts/nick/sqli/
https://medium.com/tds-note/orm-v-s-sql-91e003089a61
https://coding-ontheway.coderbridge.io/2022/02/02/n-plus-1-problem/