防止 SQL 注入和其他攻擊：PREPARE、EXECUTE、parameterized queries

好啦，先來搞清楚我們到底在防什麼，還有防誰。在這個等級一開始我們就有提到 SQL 注入，這可是資料庫界最熱門、最破壞力強的攻擊之一。攻擊的原理就是：壞人把惡意 SQL 程式碼塞進你的查詢裡，想要「騙」過你的系統，拿到他本來不能看的資料。現在我們來細講一下這東西。

SQL 注入範例

假設你有個網頁應用，讓使用者輸入帳號密碼。後端會執行 SQL 查詢，檢查資料庫裡有沒有這個人：

SELECT * FROM users WHERE username = 'admin' AND password = 'password123';

這查詢看起來沒問題……只要使用者輸入的資料是正常的。但如果有人這樣輸入：

• 使用者名稱：admin' --
• 密碼：（這欄空著）

結果查詢就會變成這樣：

SELECT * FROM users WHERE username = 'admin' -- AND password = 'password123';

注意那個 --，在 SQL 裡是註解的意思，後面全部都被忽略了。這樣密碼檢查直接被跳過，壞人就能用 admin 身份登入啦！

SQL 注入可能造成的後果

SQL 注入會帶來超慘的後果：

1. 未經授權就能存取資料。比如壞人可以看到用戶密碼這種機密資料。
2. 刪除或亂改資料。有人可能直接把整個表砍掉，或亂七八糟改一堆東西。
3. 執行任意 SQL 程式碼。想像一下壞人執行 DROP DATABASE……超恐怖的吧。

但我們才不怕呢！PostgreSQL 給我們一堆工具，可以幫你防這種攻擊。

怎麼防？防止 SQL 注入的方法

1. 用 prepared statements（PREPARE 和 EXECUTE）

prepared statements（預備查詢）就像你 SQL 程式碼的食譜。你先「準備」好查詢，然後再把資料分開傳進去。這樣壞人就沒辦法塞惡意程式碼進來了。

正確做法範例：

用 PREPARE 先準備查詢。

PREPARE user_login (text, text) AS
SELECT * 
FROM users 
WHERE username = $1 AND password = $2;

這查詢有兩個參數 $1 跟 $2，等下才會把真的值傳進去。

用 EXECUTE 執行查詢。

EXECUTE user_login('admin', 'password123');

這時 PostgreSQL 會自動幫你處理所有使用者資料，惡意 SQL 程式碼根本塞不進來。

這種做法的好處：

• SQL 注入根本沒戲，因為參數只會被當成資料，不會變成 SQL 程式碼的一部分。
• 查詢更安全，執行速度也更快，因為執行計畫會被快取。

2. parameterized queries（參數化查詢）

這方法在 Python、Java 這種語言的應用程式裡超常見。你不用自己寫 PREPARE 跟 EXECUTE，直接用 library 或 ORM 幫你自動處理參數。

Python + psycopg2 範例：

import psycopg2

connection = psycopg2.connect(
    dbname="your_db",
    user="your_user",
    password="your_password",
    host="localhost",
    port="5432"
)

cursor = connection.cursor()

# 用參數化查詢
username = "admin"
password = "password123"
query = "SELECT * FROM users WHERE username = %s AND password = %s"
cursor.execute(query, (username, password))

# 你的資料超安全！
result = cursor.fetchall()
print(result)

注意 SQL 查詢裡的 %s，這就是參數要插進去的地方。psycopg2 會自動幫你安全處理資料。

3. 檢查輸入資料

如果你要用使用者輸入的資料，記得先檢查一下是不是你要的格式。比如：

• 文字資料可以用正則表達式，確保沒有奇怪的符號。
• 數字資料就要確定真的只有數字。

Python 範例：

import re

username = input("請輸入使用者名稱: ")

# 只允許字母、數字和底線
if re.match(r"^\w+$", username):
    print("使用者名稱沒問題")
else:
    print("危險的使用者名稱！")

4. 用最小權限原則

記得讓執行查詢的角色只有最基本的權限。比如沒必要就不要給 DROP TABLE 或 ALTER TABLE 的權限。

5. 記錄可疑行為

你可以用 PostgreSQL 的參數來追蹤使用者行為：

• log_statement = 'all' — 記錄所有查詢。
• log_connections = on — 記錄所有資料庫連線。

這些設定可以幫你發現有沒有壞人在搞事。

實作範例

範例 1：用 SQL 寫 prepared statement

-- 建立 prepared statement
PREPARE check_credentials (text, text) AS
SELECT * FROM users WHERE username = $1 AND password = $2;

-- 用安全參數執行查詢
EXECUTE check_credentials('admin', 'password123');

範例 2：Python 參數化查詢

query = "UPDATE users SET last_login = NOW() WHERE username = %s"
username = "admin"
cursor.execute(query, (username,))

安全建議

一定要檢查輸入資料。 千萬不要相信使用者送來的資料。

只用 prepared statements 或 parameterized queries。 這兩招是你防 SQL 注入的主力武器。

角色權限只給最基本的。 這樣就算壞人入侵，損失也能降到最低。

設定好 log，常常檢查紀錄。 隨時掌握資料庫的動態。

SQL 注入真的很恐怖，但只要你用 prepared statements、parameterized queries，再加上好習慣，你的資料庫就很安全，可以安心睡覺，不怕有人「不小心」把你所有表都砍掉。PostgreSQL 工具都給你了，衝吧！