SQL 注入與參數化查詢：把資料和指令分開

💡 先搞懂問題

虛構的「晴空咖啡」有一個會員後台，客服可以輸入帳號查會員資料。工程師寫得很快：sql = f"SELECT id, username, role FROM members WHERE username = '{name}'"，再交給 conn.execute(sql)。測 amy、bob 都查得到，測一個不存在的帳號也正確地回傳空結果，於是就上線了。

幾天後客服回報：查一位叫 O'Brien 的會員，頁面直接出錯，畫面上還印出一串 sqlite3.OperationalError: near "Brien": syntax error。O'Brien 是再正常不過的姓氏，問題在於名字裡的單引號，剛好和 SQL 字串的引號是同一個字元。資料庫讀到 'O' 就以為字串結束了，接下來的 Brien 被當成 SQL 語法去解析，當然看不懂。同一個缺口，如果輸入是刻意設計過的，就不只是出錯，而是 WHERE 條件被改變，查詢回傳的筆數和程式原本的設計不同。這就是 SQL 注入（SQL injection）：使用者提供的資料，被資料庫當成 SQL 指令的一部分執行。

新手最常卡在三個想法。第一是「我有加引號，所以它是字串」：引號是你寫在 SQL 文字裡的，使用者也能輸入引號把它提早關掉。第二是「我有檢查長度、只有內部人員會用」：一個姓氏就足以弄壞它，惡意輸入也不需要很長。第三是「要把特殊字元都過濾掉」：過濾清單永遠列不完，而且 O'Brien 這種合法資料本來就含有引號，不能一律刪掉。真正的成因只有一個：SQL 語法和資料被混在同一個字串裡送給資料庫，資料庫的解析器只看到一整段文字，分不出哪幾個字是你寫的、哪幾個字是使用者給的。

解法是參數化查詢（parameterized query，也常叫 prepared statement）：SQL 文字裡只放一個佔位符（placeholder），例如 sqlite3 的 ?，值放在另一個參數裡交給驅動程式（driver）。資料庫先把 SQL 範本解析成固定的結構，值之後才被填進那個位置，不論內容是什麼，它都只會被拿去比對，不會再被當成語法讀一次。

生活比喻：銀行臨櫃的存款單

到銀行臨櫃存錢，行員會給你一張印好欄位的存款單：「帳號」一格、「金額」一格。你在帳號那格寫什麼，行員就拿去系統裡查那個帳號；就算你在格子裡寫「0012-345，另外再幫我加辦一件事」，行員也只會把這整串當成一個帳號去查，查不到就退件。格子的位置和用途是銀行事先印好的，你能決定的只有格子裡的內容。

危險的做法，是讓客戶拿一張白紙自己寫整段指示，行員照著念、照著做。大部分客戶只會寫「請存 5,000 元到 0012-345」，看起來和存款單沒有差別；但只要有人在句子後面多加一句，行員就會連同那一句一起執行。更麻煩的是，連善意的客戶也會出事：有人在白紙上寫了一個行員看不懂的符號，整張單子就辦不下去，這正是 O'Brien 遇到的狀況。

這個比喻有兩個地方要小心。第一，真正的行員看到奇怪的備註會起疑、會打電話問主管，資料庫不會，它只照語法執行，所以「應該不會有人這樣輸入吧」不能當成防線。第二，存款單只解決了「值」的問題；如果連「要查哪一張表、依哪個欄位排序」都讓客戶決定，就像讓客戶自己決定單子上要印哪些欄位，這時佔位符幫不上忙，要改用白名單，下面原理補完會說明。

🎮 互動實驗室一：組字串觀察器

按下方三個輸入之一，看同一個值經過兩種寫法會送出什麼給資料庫。左邊是字串拼接實際組出來的 SQL，輸入的每個字都被標色：綠底代表它還在引號裡、只是資料，紅底代表它落在引號外、被資料庫當成 SQL 語法。右邊是參數化寫法送出的兩樣東西：固定不變的 SQL 範本，以及另外傳的參數值。最下面的 5 筆虛構會員表會標出兩種寫法各回傳哪幾列，結果都和 Python 3 的 sqlite3 實際執行一致。

sql = f"SELECT id, username, role FROM members WHERE username = '{name}'"
rows = conn.execute(sql).fetchall()

sql = "SELECT id, username, role FROM members WHERE username = ?"
rows = conn.execute(sql, (name,)).fetchall()

🎮 互動實驗室二：安全寫法判斷卡

每張卡是一段讀取或寫入資料庫的程式，conn 連到上面那張 5 筆的 members 表，name、kw、form[...] 都來自使用者。請判斷它屬於哪一種：安全、有注入風險，或者「不是注入，但會出錯或結果不對」。判斷時只問一件事：使用者的輸入最後是變成 SQL 文字的一部分，還是經由佔位符當成值送出？答完會說明原因，並附上實際執行的結果。

rows = conn.execute(
    f"SELECT id FROM members WHERE username = '{name}'").fetchall()

sql = "SELECT id FROM members WHERE username = '%s'" % name
rows = conn.execute(sql).fetchall()

sql = "SELECT id FROM members WHERE username = '{}'".format(name)
rows = conn.execute(sql).fetchall()

rows = conn.execute(
    "SELECT id FROM members WHERE username = ?", (name,)).fetchall()

rows = conn.execute(
    "SELECT id FROM members WHERE username = ?", (name)).fetchall()

new_rows = [(6, "ed", "member"), (7, "O'Hara", "member")]   # 來自上傳的名單
conn.executemany("INSERT INTO members VALUES (?, ?, ?)", new_rows)

col = form["sort"]                  # 使用者選的排序欄位，例如 "username"
rows = conn.execute(
    "SELECT username FROM members ORDER BY ?", (col,)).fetchall()

SORT = {"name": "username", "newest": "id DESC"}
order = SORT.get(form["sort"], "username")      # 不在清單內就用預設值
rows = conn.execute(
    f"SELECT username FROM members ORDER BY {order}").fetchall()

rows = conn.execute(
    "SELECT username FROM members WHERE username LIKE ?",
    (f"%{kw}%",)).fetchall()

rows = conn.execute(
    f"SELECT username FROM members WHERE username LIKE '%{kw}%'").fetchall()

from sqlalchemy import text
stmt = text("SELECT id, role FROM members WHERE username = :u")
rows = conn.execute(stmt, {"u": name}).fetchall()

from sqlalchemy import text
stmt = text(f"SELECT id FROM members WHERE username = '{name}'")
rows = conn.execute(stmt).fetchall()

# PostgreSQL＋psycopg 3，cur 是 conn.cursor()
cur.execute("SELECT id FROM members WHERE username = %s", (name,))
rows = cur.fetchall()

table = form["table"]               # 使用者選的資料表，例如 "members"
rows = conn.execute("SELECT * FROM ?", (table,)).fetchall()

import pandas as pd
df = pd.read_sql("SELECT username FROM members WHERE role = ?",
                 conn, params=(role,))

🎮 互動實驗室三：縱深防禦配對

參數化查詢是根本修正，但一套系統不會只靠一道防線。下面 8 張卡是晴空咖啡開發團隊在程式碼審查時記下的狀況，下方有 10 個防禦做法，其中 2 個是常見但不可靠的做法。先點一張狀況卡，再點你認為最適合的防禦做法；也可以先點防禦做法再點卡片，電腦上可以直接把防禦做法拖到卡片上。配對成功的卡片會標出它屬於哪一層：根本修正、限制損害，或偵測與預防回歸。

📘 原理補完

實驗室裡的直覺可以濃縮成一句話：使用者的輸入只要變成 SQL 文字的一部分，就有機會被當成語法；只要它經由佔位符當成值送出，就只會是資料。下面把這句話接回正式用語，再補上參數化管不到的地方，以及上線前怎麼檢查自己的程式。

1. 正式說法：為什麼會被當成語法

資料庫收到 SQL 時，第一步是詞法與語法分析（parsing）：把文字切成關鍵字、欄位名、運算子、字串常值，再組成查詢的結構。字串常值靠單引號標出開頭和結尾，所以只要資料裡有單引號，而它又是被直接接進 SQL 文字，引號就會改變「字串到哪裡結束」，後面的字也就換了身分。OWASP Top 10:2025 把這類問題歸在 A05 Injection，對應的弱點編號是 CWE-89。Python 官方 sqlite3 文件也直接提醒：不要用 Python 的字串操作組查詢，要用 DB-API 的參數替換。

同樣的機制換成教科書示範輸入時，結果不是出錯，而是查詢條件被改變。下圖把拼接後的 WHERE 條件拆開來看：原本只有「username 等於某個值」一個比較，現在多了一個 OR，而 OR 右邊是一個對每一列都成立的比較，所以回傳筆數和原本的設計不同。

2. 參數化為什麼有效

參數化查詢把「決定查詢結構」和「提供資料」拆成兩個步驟。SQL 範本先被解析，結構在這時就固定了：要查哪張表、比對哪個欄位、條件怎麼組合。值是之後才綁定（bind）到佔位符的位置，它不會再經過一次語法分析，所以裡面有沒有引號、有沒有 OR 都不影響結構。這也是它比「把危險字元過濾掉」可靠的原因：你不需要預測使用者會輸入什麼，因為不論輸入什麼，它都沒有機會變成語法。

每個驅動程式支援的佔位符寫法叫 paramstyle，由 Python 的 DB-API 規範（PEP 249）定義。下表是 AI 與資料分析最常碰到的幾種：

3. 參數化做不到的地方：識別字與排序

佔位符只能代入值，也就是會出現在比較、寫入或 LIMIT 裡的資料。表名、欄位名這類識別字（identifier），以及 ORDER BY 的欄位與 ASC／DESC，屬於查詢結構本身，不能用佔位符。實驗室二的兩張卡示範了後果：ORDER BY ? 不會出錯，但它是依一個固定字串排序，等於沒有排序；FROM ? 則直接是語法錯誤。這時的正確做法是白名單對照（allowlist）：使用者只能從選項裡挑，程式用字典把選項對應到寫死的 SQL 片段，不在清單裡就用預設值。使用者的輸入只拿來「查表」，從頭到尾沒有進入 SQL 文字。PostgreSQL 的 psycopg 另外提供 psycopg.sql.Identifier，可以安全地組出識別字，但「哪些欄位允許排序」仍然應該由白名單決定。

LIKE 搜尋是另一個常見盲點。"... LIKE ?", (f"%{kw}%",) 沒有注入問題，因為 f-string 組的是參數值，整串仍經由佔位符送出；但使用者輸入的 % 和 _ 在 LIKE 裡是萬用字元，搜尋「%」會比對到每一列。若希望它們被當成普通字元，就在程式裡先把它們跳脫，並在 SQL 加上 ESCAPE '\'，範例二會示範。這屬於正確性與效能問題，不是注入，但也會讓搜尋結果和使用者預期不同。另外 SQLite 的 LIKE 預設不分 ASCII 大小寫，其他資料庫依定序（collation）而定。

4. ORM 也不是自動安全

ORM（Object-Relational Mapping，把資料表對應成 Python 類別的工具）和 SQLAlchemy Core 在你用它們的查詢 API 時，會自動把值綁定成參數，例如 select(Member).where(Member.username == name)。風險出現在「回到手寫 SQL」的那幾個入口：SQLAlchemy 的 text()、Django 的 raw()、pandas 的 read_sql。這些函式都支援參數（text() 用 :name，raw(sql, params)，read_sql(..., params=...)），但它們不會檢查你傳進來的字串是怎麼組成的。text(f"... '{name}'") 在交給 text() 之前就已經拼好了，SQLAlchemy 看到的只是一段普通的 SQL 文字。審查程式時，與其問「有沒有用 ORM」，不如問「SQL 文字裡有沒有使用者的字」。

5. 縱深防禦：最小權限與錯誤訊息處理

參數化與白名單是根本修正，但大型系統裡總有漏網之魚：舊程式、別人寫的模組、新人一時順手。縱深防禦（defense in depth）的意思是再加幾層，讓萬一有一處沒寫好時損害有限、而且會被發現。

第一層是最小權限（least privilege）：網站連資料庫用專屬帳號，只拿到它真正需要的權限，例如對 members 表只有 SELECT、INSERT、UPDATE，不使用資料庫管理員帳號，也不讓它成為資料表的擁有者；報表功能另開唯讀帳號。第二層是輸入驗證：會員編號就只收 1～6 位數字，格式不對直接拒絕；它擋得住大部分格式錯誤，但不能取代參數化，因為合法資料（像 O'Brien）本來就可能含有引號。第三層是錯誤訊息不外洩：查詢失敗時，完整的錯誤與 SQL 只寫進伺服器日誌，畫面上給通用訊息。實驗室一裡 O'Brien 造成的錯誤訊息若直接顯示在網頁上，等於把查詢的寫法告訴所有人；OWASP Top 10:2025 也把例外狀況處理不當列為 A10。最外層是日誌與告警：短時間內大量查詢失敗，通常代表程式有錯或有人在試探，兩種情況都值得有人去看。

6. 自我檢查：Bandit B608 與測試

人工審查很難每一行都注意到。靜態分析（SAST，不執行程式、直接讀原始碼）可以在每次提交時自動找出「用字串組 SQL」的寫法。Python 專用的 Bandit 有一條規則 B608 hardcoded_sql_expressions，用 f-string、%、.format() 或 + 組出看起來像 SQL 的字串時就會標出來，並對應 CWE-89。下面是用 Bandit 1.9.4 掃描一段示範程式的實際輸出（節錄）：

pip install bandit
bandit -r src -ll        # 掃描 src 目錄，只列出中、高嚴重度
# >> Issue: [B608:hardcoded_sql_expressions] Possible SQL injection vector
#    through string-based query construction.
#    Severity: Medium   Confidence: Low
#    CWE: CWE-89
#    Location: ./app_bad.py:4:10

Bandit 是比對寫法，不懂語意，所以白名單版本的 f"... ORDER BY {order}" 也會被標出來（誤報，false positive）。確認安全後，可以在那一行加上 # nosec B608 並寫明理由，讓之後的審查者知道這是看過的決定，而不是隨手關掉警告；反過來說，零警告也不代表沒有問題，它看不到存取控制這類邏輯錯誤。第二個工具是單元測試：把 O'Brien 這種含引號的正常資料，以及教科書示範輸入寫進測試，斷言回傳的筆數與內容。只要以後有人把查詢改回拼接，測試會立刻失敗。範例一最後兩行就是這種測試。

寫法比較與判斷步驟

1. 先找出這個函式裡所有來自外部的值：表單、網址參數、API、檔案，也包括 LLM 的輸出。
2. 追它流到哪裡：只要最後會組成 SQL 文字，就看它是怎麼進去的。
3. 是比較或寫入的值：必須經由佔位符或 ORM 的查詢 API，SQL 文字裡不能出現它。
4. 是欄位、表名、排序方向：必須經過白名單對照，進入 SQL 的只能是程式自己寫的字串。
5. 是 LIKE 的搜尋字：用佔位符，並視需要跳脫 % 與 _。
6. 最後檢查周邊：帳號權限、錯誤訊息是否外洩、有沒有含引號資料的測試。

完整範例一：sqlite3 的兩種寫法與回歸測試

這段程式建立和實驗室一相同的 5 筆虛構資料，用三個輸入比較兩種寫法，最後用 assert 寫成測試。可以直接貼到 Python 3.11 以上執行，不需要任何外部檔案。

import sqlite3

def make_db():
    conn = sqlite3.connect(":memory:")                 # 記憶體資料庫，示範用
    conn.execute("CREATE TABLE members(id INTEGER, username TEXT, role TEXT)")
    conn.executemany("INSERT INTO members VALUES (?, ?, ?)", [   # 寫入也用佔位符
        (1, "amy", "admin"), (2, "bob", "staff"), (3, "O'Brien", "member"),
        (4, "chen", "member"), (5, "dora", "member")])
    return conn

def find_unsafe(conn, name):                           # ❌ 示範用：輸入被拼進 SQL 文字
    return conn.execute(f"SELECT id FROM members WHERE username = '{name}'").fetchall()

def find_safe(conn, name):                             # ✅ ? 佔位符，值另外傳
    return conn.execute("SELECT id FROM members WHERE username = ?", (name,)).fetchall()

conn = make_db()
for name in ["amy", "O'Brien", "' OR '1'='1"]:         # 一般、含引號的正常姓名、教科書示範輸入
    try:
        bad = len(find_unsafe(conn, name))
    except sqlite3.OperationalError as e:              # 合法姓名也會讓拼接版壞掉
        bad = f"OperationalError: {e}"
    print(f"{name!r:16} 拼接 → {bad}｜參數化 → {len(find_safe(conn, name))}")

# 把這些輸入寫成測試：以後有人改回拼接，測試會立刻失敗
assert find_safe(conn, "O'Brien") == [(3,)]
assert find_safe(conn, "' OR '1'='1") == []
print("測試通過")

完整範例二：SQLAlchemy 的綁定參數、排序白名單與 LIKE 跳脫

一個常見的搜尋功能同時需要三種處理：角色與關鍵字是值，用 :name 綁定；排序欄位用白名單；關鍵字裡的 % 與 _ 先跳脫。

from sqlalchemy import create_engine, text

engine = create_engine("sqlite://")                    # 記憶體 SQLite；正式環境的連線字串從環境變數讀
with engine.begin() as conn:                           # begin()：區塊結束自動 commit
    conn.execute(text("CREATE TABLE members(id INTEGER, username TEXT, role TEXT)"))
    conn.execute(text("INSERT INTO members VALUES (:i, :u, :r)"), [   # list of dict → 逐筆綁定
        {"i": 1, "u": "amy", "r": "admin"}, {"i": 2, "u": "bob", "r": "staff"},
        {"i": 3, "u": "O'Brien", "r": "member"}, {"i": 4, "u": "chen", "r": "member"},
        {"i": 5, "u": "dora", "r": "member"}])

SORT = {"name": "username", "newest": "id DESC"}       # 白名單：使用者選項 → 寫死的 SQL 片段

def like_pattern(kw: str) -> str:
    kw = kw.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")  # % 與 _ 變成普通字元
    return f"%{kw}%"                                   # 前後的 % 才是我們要的萬用字元

def search(conn, kw, role, sort):
    order = SORT.get(sort, "username")                 # 不在清單內就用預設值，絕不直接用輸入
    sql = text("SELECT username FROM members "
               "WHERE role = :role AND username LIKE :pat ESCAPE '\\' "
               f"ORDER BY {order}")  # nosec B608（已用白名單限制）
    return conn.execute(sql, {"role": role, "pat": like_pattern(kw)}).scalars().all()

with engine.connect() as conn:
    print(search(conn, "o", "member", "newest"))      # SQLite 的 LIKE 不分 ASCII 大小寫
    print(search(conn, "%", "member", "name"))        # % 被當成普通字元，沒有人名裡有 %
    print(search(conn, "o", "member", "role DESC"))   # 不在白名單 → 依 username 排序

完整範例三：格式驗證、錯誤訊息處理與最小權限

驗證放在最前面擋掉格式不對的輸入，驗證過的值仍然走佔位符；資料庫出錯時，細節進日誌，使用者只看到通用訊息。

import logging, re, sqlite3
log = logging.getLogger("cafe")
ID_RULE = re.compile(r"[0-9]{1,6}")                    # 白名單：1～6 位半形數字

def get_member(conn, member_id: str):
    if not ID_RULE.fullmatch(member_id):               # 先驗證格式，不合格直接拒絕
        return {"error": "會員編號格式不正確"}
    try:
        row = conn.execute("SELECT username, role FROM members WHERE id = ?",
                           (int(member_id),)).fetchone()   # 驗證過仍然用佔位符
    except sqlite3.Error:
        log.exception("查詢會員失敗 id=%s", member_id)  # 細節只寫進伺服器日誌
        return {"error": "系統忙碌中，請稍後再試"}       # 對外只給通用訊息，不洩漏 SQL 與表名
    return {"member": row}

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE members(id INTEGER, username TEXT, role TEXT)")
conn.execute("INSERT INTO members VALUES (3, ?, 'member')", ("O'Brien",))
print(get_member(conn, "3"))                           # 正常查詢
print(get_member(conn, "3 OR 1=1"))                    # 格式不符，直接拒絕
print(get_member(sqlite3.connect(":memory:"), "3"))    # 沒有 members 表 → 通用錯誤訊息

權限則是在資料庫端設定。以 PostgreSQL 為例，應用程式用專屬帳號連線，只授予需要的權限：

-- PostgreSQL 示意：應用程式專屬帳號，只拿需要的權限
CREATE ROLE cafe_app LOGIN;                -- 密碼由機密管理服務另外設定，不寫進程式
GRANT SELECT, INSERT, UPDATE ON members TO cafe_app;
-- 不讓 cafe_app 成為資料表擁有者：擁有者才能 DROP、ALTER 資料表
-- 報表功能另開一個只有 SELECT 權限的唯讀帳號

容易寫錯或考錯的地方

✅ 自我檢測

6 題原創程式閱讀題。除了特別註明，conn 都是 sqlite3 連線，members 表有 5 筆虛構資料，依 id 1～5 依序是 amy、bob、O'Brien、chen、dora。所有輸出都用 Python 3 實際執行確認過。目前得分：0 / 6

name = "' OR '1'='1"                      # 教科書示範輸入
rows = conn.execute(f"SELECT id FROM members WHERE username = '{name}'").fetchall()
print(len(rows))

name = "dora"
print(conn.execute("SELECT role FROM members WHERE username = ?", (name)).fetchall())

rows = conn.execute("SELECT username FROM members ORDER BY ?", ("username",)).fetchall()
print([r[0] for r in rows][:2])

kw = "%"
sql = "SELECT count(*) FROM members WHERE username LIKE ?"
print(conn.execute(sql, (f"%{kw}%",)).fetchone()[0])

def list_members(conn, role, sort):
    SORT = {"name": "username", "newest": "id DESC"}            # (A)
    order = SORT.get(sort, "username")                          # (B)
    sql = f"SELECT username FROM members WHERE role = '{role}' ORDER BY {order}"  # (C)
    return conn.execute(sql).fetchall()                         # (D)

🎯 重點整理

1. SQL 注入的成因是 SQL 語法與資料混在同一個字串：使用者的字只要變成 SQL 文字的一部分，就可能改變查詢條件，連 O'Brien 這種合法輸入都會讓查詢出錯。
2. 參數化查詢讓資料庫先固定查詢結構、再綁定值，值不會再被當成語法讀一次。sqlite3 用 ? 或 :name，psycopg 與 PyMySQL 用 %s，SQLAlchemy text() 用 :name，單一值寫成 (name,)。
3. 欄位名、表名、ORDER BY 不能參數化，要用白名單對照；LIKE 的 % 與 _ 視需要跳脫。
4. ORM 只有在用它的查詢 API 時才自動綁定；text()、raw()、read_sql 裡自己拼字串一樣有風險。
5. 縱深防禦：最小權限帳號、輸入格式驗證、錯誤訊息只寫日誌，再加上 Bandit B608 與含引號輸入的單元測試，防止修好的程式又被改壞。