02-3. SQL Injection 防禦:Prepared Statements 與最佳實踐
完整的 SQL Injection 防禦指南:Django ORM、Prepared Statements、安全檢查清單
目錄
02-3. SQL Injection 防禦:Prepared Statements 與最佳實踐
⏱️ 閱讀時間: 12 分鐘 🎯 難度: ⭐⭐ (中等) ⚠️ 重要: 本篇是 SQL Injection 系列最重要的一篇!
🎯 本篇重點
學習如何完全防禦 SQL Injection:Prepared Statements 原理、Django ORM 最佳實踐、輸入驗證、最小權限原則,以及完整的安全檢查清單。
🛡️ 防禦的核心原則
黃金法則
永遠不要將用戶輸入直接拼接到 SQL 查詢中!為什麼字串拼接危險?
# ❌ 危險:字串拼接
username = "admin' OR '1'='1"
query = f"SELECT * FROM users WHERE username = '{username}'"
# 執行:SELECT * FROM users WHERE username = 'admin' OR '1'='1'
# ↑
# 輸入改變了 SQL 結構
# ✅ 安全:參數化查詢
username = "admin' OR '1'='1"
query = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, [username])
# 執行:SELECT * FROM users WHERE username = 'admin\' OR \'1\'=\'1\''
# ↑
# 資料庫自動轉義,把整個字串當成「資料」1️⃣ Prepared Statements(參數化查詢)
什麼是 Prepared Statements?
Prepared Statements = 預編譯的 SQL 語句,將「SQL 結構」與「資料」分離
傳統方式:
SQL 結構 + 資料 混在一起 → 資料庫解析 → 執行
問題:用戶輸入可能改變 SQL 結構
Prepared Statements:
Step 1:先發送 SQL 結構給資料庫(預編譯)
SELECT * FROM users WHERE username = ?
↑
佔位符
Step 2:再發送資料
參數:['admin']
Step 3:資料庫執行
資料庫知道「?」的位置是「資料」,不是「指令」
不會被用戶輸入改變 SQL 結構原理說明
生活比喻:填空題 vs 開放式問答
開放式問答(字串拼接):
老師:「請問 1+1=___」(用戶可以填任何東西)
學生:「2,然後把我的分數改成 100 分」
結果:不只回答問題,還執行了其他指令
填空題(Prepared Statements):
老師:「請問 1+1=___」(只能填答案格)
學生:「2,然後把我的分數改成 100 分」
結果:整個字串被當成答案,不會執行指令Python 原生寫法
import mysql.connector
# ❌ 危險:字串拼接
def get_user_unsafe(username):
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydb"
)
cursor = conn.cursor()
# 直接拼接用戶輸入
query = f"SELECT * FROM users WHERE username = '{username}'"
cursor.execute(query)
user = cursor.fetchone()
cursor.close()
conn.close()
return user
# ✅ 安全:Prepared Statements
def get_user_safe(username):
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydb"
)
cursor = conn.cursor()
# 使用佔位符 %s
query = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, (username,)) # 注意:參數是 tuple
user = cursor.fetchone()
cursor.close()
conn.close()
return user
# 測試:
get_user_safe("admin' OR '1'='1")
# 資料庫會搜尋用戶名為 "admin' OR '1'='1" 的用戶
# 找不到 → 安全!重要細節
# ✅ 正確:使用 tuple 或 list
cursor.execute("SELECT * FROM users WHERE username = %s", (username,))
cursor.execute("SELECT * FROM users WHERE username = %s", [username])
# ✅ 正確:多個參數
cursor.execute(
"SELECT * FROM users WHERE username = %s AND age > %s",
(username, age)
)
# ❌ 錯誤:直接傳字串
cursor.execute("SELECT * FROM users WHERE username = %s", username)
# 這會被當成多個參數,導致錯誤
# ❌ 錯誤:使用 % 格式化
query = "SELECT * FROM users WHERE username = '%s'" % username
cursor.execute(query)
# 這仍然是字串拼接,不安全!
# ❌ 錯誤:使用 f-string
query = f"SELECT * FROM users WHERE username = '{username}'"
cursor.execute(query)
# 字串拼接,不安全!不同資料庫的佔位符
# MySQL、PostgreSQL
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
# SQLite
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))
# Oracle
cursor.execute("SELECT * FROM users WHERE id = :id", {'id': user_id})
# 注意:佔位符語法因資料庫而異
# 但原理相同:都是參數化查詢2️⃣ Django ORM(推薦!)
為什麼 Django ORM 安全?
Django ORM 自動使用 Prepared Statements
# Django ORM(自動安全)
from django.contrib.auth.models import User
username = "admin' OR '1'='1"
user = User.objects.filter(username=username).first()
# Django 實際執行:
# SELECT * FROM users WHERE username = %s
# 參數:["admin' OR '1'='1"]
#
# 整個字串被當成「資料」,不是「指令」
# 結果:找不到這個用戶名,安全!Django ORM 最佳實踐
✅ 安全:使用 ORM 查詢
from django.contrib.auth.models import User
from myapp.models import Product, Order
# ✅ 簡單查詢
user = User.objects.filter(username=username).first()
user = User.objects.get(id=user_id)
# ✅ 複雜查詢
users = User.objects.filter(
email__contains=email_domain,
is_active=True
).order_by('-date_joined')
# ✅ 使用 Q 物件(複雜條件)
from django.db.models import Q
users = User.objects.filter(
Q(username=username) | Q(email=email)
)
# ✅ JOIN 查詢
orders = Order.objects.select_related('user').filter(
user__username=username
)
# ✅ 聚合查詢
from django.db.models import Count, Avg
stats = Product.objects.filter(category=category).aggregate(
total=Count('id'),
avg_price=Avg('price')
)✅ 安全:使用 raw() 時的正確方法
from django.db import connection
# ✅ 安全:使用參數
def get_users_safe(username):
users = User.objects.raw(
"SELECT * FROM auth_user WHERE username = %s",
[username] # 參數列表
)
return list(users)
# ✅ 安全:多個參數
def get_orders_safe(user_id, min_amount):
with connection.cursor() as cursor:
cursor.execute(
"SELECT * FROM orders WHERE user_id = %s AND amount > %s",
[user_id, min_amount]
)
return cursor.fetchall()❌ 危險:錯誤的 raw() 用法
# ❌ 危險:字串拼接
def get_users_unsafe(username):
users = User.objects.raw(
f"SELECT * FROM auth_user WHERE username = '{username}'"
)
return list(users)
# ❌ 危險:使用 format
def get_users_unsafe2(username):
query = "SELECT * FROM auth_user WHERE username = '{}'".format(username)
users = User.objects.raw(query)
return list(users)
# ❌ 危險:使用 % 格式化
def get_users_unsafe3(username):
query = "SELECT * FROM auth_user WHERE username = '%s'" % username
users = User.objects.raw(query)
return list(users)Django extra() 的安全使用
# ✅ 安全:使用 params
users = User.objects.extra(
where=["age > %s"],
params=[18]
)
# ✅ 安全:複雜條件
products = Product.objects.extra(
where=["price BETWEEN %s AND %s"],
params=[min_price, max_price]
)
# ❌ 危險:直接拼接
users = User.objects.extra(
where=[f"age > {age}"] # 危險!
)Django 常見陷阱
# ❌ 陷阱 1:在 extra() 中使用 f-string
def search_products(keyword):
# 危險!
products = Product.objects.extra(
where=[f"name LIKE '%{keyword}%'"]
)
return products
# ✅ 正確做法:使用 ORM
def search_products_safe(keyword):
products = Product.objects.filter(name__icontains=keyword)
return products
# ❌ 陷阱 2:直接執行 SQL
def delete_user(user_id):
from django.db import connection
cursor = connection.cursor()
# 危險!
cursor.execute(f"DELETE FROM users WHERE id = {user_id}")
# ✅ 正確做法
def delete_user_safe(user_id):
User.objects.filter(id=user_id).delete()3️⃣ 輸入驗證(縱深防禦)
為什麼需要輸入驗證?
Prepared Statements 是核心防禦
但輸入驗證提供額外保護層
縱深防禦(Defense in Depth):
第 1 層:輸入驗證(阻擋明顯惡意輸入)
第 2 層:Prepared Statements(核心防禦)
第 3 層:最小權限(限制損害)
第 4 層:監控與日誌(偵測攻擊)白名單驗證
# ✅ 最佳實踐:白名單驗證
def get_users_by_column(column_name):
# 只允許特定欄位
allowed_columns = ['username', 'email', 'created_at']
if column_name not in allowed_columns:
raise ValueError("不合法的欄位名稱")
# 這裡可以安全使用,因為已驗證
# 注意:欄位名不能用 Prepared Statements(會變成字串)
users = User.objects.order_by(column_name)
return users
# ✅ 白名單:排序方向
def get_products_sorted(sort_by, order):
allowed_sort = ['price', 'name', 'created_at']
allowed_order = ['asc', 'desc']
if sort_by not in allowed_sort or order not in allowed_order:
raise ValueError("不合法的排序參數")
if order == 'desc':
sort_by = f'-{sort_by}'
products = Product.objects.order_by(sort_by)
return productsDjango Form 驗證
from django import forms
from django.core.validators import EmailValidator, MinLengthValidator
# ✅ 使用 Django Form 驗證輸入
class UserSearchForm(forms.Form):
username = forms.CharField(
max_length=150,
validators=[MinLengthValidator(3)],
required=False
)
email = forms.EmailField(
validators=[EmailValidator()],
required=False
)
age_min = forms.IntegerField(min_value=0, max_value=120, required=False)
def search_users(request):
form = UserSearchForm(request.GET)
if not form.is_valid():
return JsonResponse({'error': 'Invalid input'}, status=400)
# 清理過的資料
username = form.cleaned_data.get('username')
email = form.cleaned_data.get('email')
age_min = form.cleaned_data.get('age_min')
# 安全查詢
users = User.objects.all()
if username:
users = users.filter(username__icontains=username)
if email:
users = users.filter(email=email)
if age_min:
users = users.filter(age__gte=age_min)
return JsonResponse({'users': list(users.values())})正則表達式驗證
import re
# ✅ 驗證用戶名格式
def validate_username(username):
# 只允許字母、數字、底線,長度 3-20
pattern = r'^[a-zA-Z0-9_]{3,20}$'
if not re.match(pattern, username):
raise ValueError("用戶名格式不正確")
return username
# ✅ 驗證數字 ID
def validate_id(user_id):
if not isinstance(user_id, int) or user_id <= 0:
raise ValueError("ID 必須是正整數")
return user_id
# 使用:
def get_user(user_id):
user_id = validate_id(user_id) # 驗證
user = User.objects.get(id=user_id) # 安全查詢
return user4️⃣ 最小權限原則
資料庫帳號權限
# ❌ 危險:使用 root 或高權限帳號
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'mydb',
'USER': 'root', # 危險!
'PASSWORD': 'password',
'HOST': 'localhost',
}
}
# 如果發生 SQL Injection:
# '; DROP DATABASE mydb; --
# → root 有權限刪除整個資料庫!
# ✅ 正確:使用最小權限帳號
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'mydb',
'USER': 'webapp_user', # 只有基本權限
'PASSWORD': 'strong_password',
'HOST': 'localhost',
}
}建立最小權限帳號
-- 建立專用帳號
CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'strong_password';
-- 只給必要權限(SELECT, INSERT, UPDATE, DELETE)
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'webapp_user'@'localhost';
-- 不給這些危險權限:
-- ❌ DROP(刪除表格)
-- ❌ CREATE(建立表格)
-- ❌ ALTER(修改表格結構)
-- ❌ GRANT OPTION(授予權限)
-- ❌ FILE(讀寫檔案)
-- ❌ SUPER(超級權限)
-- 套用權限
FLUSH PRIVILEGES;分離讀寫權限
# 進階:唯讀帳號與讀寫帳號分離
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'mydb',
'USER': 'webapp_write', # 讀寫權限
'PASSWORD': 'password1',
'HOST': 'localhost',
},
'readonly': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'mydb',
'USER': 'webapp_read', # 只有 SELECT 權限
'PASSWORD': 'password2',
'HOST': 'localhost',
}
}
# 唯讀查詢使用 readonly
def get_public_data():
users = User.objects.using('readonly').filter(is_public=True)
return users5️⃣ 額外防護層
WAF(Web Application Firewall)
# Django Middleware 範例:簡單的 SQL Injection 檢測
class SQLInjectionDetectionMiddleware:
def __init__(self, get_response):
self.get_response = get_response
# 常見 SQL Injection 關鍵字
self.sql_keywords = [
'union', 'select', 'insert', 'update', 'delete',
'drop', 'create', 'alter', 'exec', 'execute',
'--', '/*', '*/', 'xp_', 'sp_', 'waitfor'
]
def __call__(self, request):
# 檢查 GET 參數
for key, value in request.GET.items():
if self.contains_sql_injection(value):
return HttpResponseBadRequest("檢測到潛在的 SQL Injection")
# 檢查 POST 參數
for key, value in request.POST.items():
if self.contains_sql_injection(value):
return HttpResponseBadRequest("檢測到潛在的 SQL Injection")
response = self.get_response(request)
return response
def contains_sql_injection(self, value):
value_lower = str(value).lower()
for keyword in self.sql_keywords:
if keyword in value_lower:
return True
return False
# settings.py
MIDDLEWARE = [
'django.middleware.security.SecurityMiddleware',
'myapp.middleware.SQLInjectionDetectionMiddleware', # 加入自訂 Middleware
# ... 其他 Middleware
]監控與日誌
import logging
logger = logging.getLogger(__name__)
def get_user_with_logging(username):
# 記錄所有查詢
logger.info(f"查詢用戶:{username}")
try:
user = User.objects.get(username=username)
logger.info(f"找到用戶:{username}")
return user
except User.DoesNotExist:
logger.warning(f"用戶不存在:{username}")
return None
except Exception as e:
# 記錄異常(可能是攻擊)
logger.error(f"查詢錯誤:{username}, 錯誤:{str(e)}")
raise
# 監控異常模式
# 如果短時間內大量 "用戶不存在" 日誌
# 可能是在進行 SQL Injection 攻擊Django 安全設定
# settings.py
# 生產環境關閉 Debug(防止洩漏錯誤訊息)
DEBUG = False
# 設定 ALLOWED_HOSTS
ALLOWED_HOSTS = ['example.com', 'www.example.com']
# 資料庫連線超時設定
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'mydb',
'USER': 'webapp_user',
'PASSWORD': 'password',
'HOST': 'localhost',
'OPTIONS': {
'connect_timeout': 10, # 連線超時
'options': '-c statement_timeout=30000' # 查詢超時 30 秒
}
}
}
# 啟用安全 Middleware
MIDDLEWARE = [
'django.middleware.security.SecurityMiddleware',
# ...
]
SECURE_SSL_REDIRECT = True # 強制 HTTPS
SESSION_COOKIE_SECURE = True
CSRF_COOKIE_SECURE = True6️⃣ 代碼審查檢查清單
尋找潛在漏洞
# 搜尋這些危險模式:
# ❌ 字串格式化
cursor.execute(f"SELECT * FROM users WHERE username = '{username}'")
cursor.execute("SELECT * FROM users WHERE username = '%s'" % username)
cursor.execute("SELECT * FROM users WHERE username = '{}'".format(username))
# ❌ 字串拼接
query = "SELECT * FROM users WHERE username = '" + username + "'"
cursor.execute(query)
# ❌ Django raw() 拼接
User.objects.raw(f"SELECT * FROM auth_user WHERE username = '{username}'")
# ❌ extra() 拼接
User.objects.extra(where=[f"age > {age}"])
# 使用工具搜尋:
# grep -r "f\".*SELECT" .
# grep -r "\.format(" .
# grep -r "% (" .自動化檢查工具
# Bandit(Python 安全分析工具)
pip install bandit
bandit -r your_project/
# 會檢測到:
# [B608:hardcoded_sql_expressions] Possible SQL injection vector through string-based query construction.
# Severity: Medium Confidence: Low
# Django 內建安全檢查
python manage.py check --deploy
# 會檢查:
# - DEBUG = True(生產環境應為 False)
# - SECRET_KEY 外洩風險
# - ALLOWED_HOSTS 設定
# - 安全性相關設定🎓 面試常考題
Q1:如何防禦 SQL Injection?
A:核心方法是使用 Prepared Statements(參數化查詢)
原理:
將「SQL 結構」與「資料」分離
資料庫會把參數當成「資料」,不是「指令」
Django 最佳實踐:
1. 優先使用 ORM
User.objects.filter(username=username)
2. 必須用 Raw SQL 時,使用參數
cursor.execute("SELECT * FROM users WHERE username = %s", [username])
3. 絕對不要字串拼接
❌ f"SELECT * FROM users WHERE username = '{username}'"
額外防護:
- 輸入驗證(白名單)
- 最小權限原則
- WAF 防火牆
- 關閉 Debug 模式
- 監控與日誌
縱深防禦:
即使某一層被繞過,其他層仍能保護Q2:Django ORM 會自動防禦 SQL Injection 嗎?
A:是的,但有例外情況
✅ 自動防禦(安全):
User.objects.filter(username=username)
User.objects.get(id=user_id)
User.objects.exclude(email=email)
❌ 需要小心(可能不安全):
User.objects.raw(f"SELECT * WHERE username = '{username}'")
User.objects.extra(where=[f"age > {age}"])
connection.cursor().execute(f"SELECT * WHERE id = {id}")
正確做法:
User.objects.raw("SELECT * WHERE username = %s", [username])
User.objects.extra(where=["age > %s"], params=[age])
cursor.execute("SELECT * WHERE id = %s", [id])
特殊情況:
欄位名、表格名不能用 Prepared Statements
需要使用白名單驗證:
allowed_fields = ['name', 'email', 'created_at']
if field not in allowed_fields:
raise ValueError("不合法的欄位")
User.objects.order_by(field) # 安全Q3:為什麼需要最小權限原則?
A:限制 SQL Injection 的損害範圍
情境對比:
使用 root 帳號:
攻擊:'; DROP DATABASE mydb; --
結果:整個資料庫被刪除!
原因:root 有 DROP 權限
使用最小權限帳號:
攻擊:'; DROP DATABASE mydb; --
結果:ERROR 1044: Access denied
原因:webapp_user 沒有 DROP 權限
最小權限設定:
CREATE USER 'webapp_user'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'webapp_user';
-- 不給 DROP, CREATE, ALTER, FILE 等危險權限
結論:
即使發生 SQL Injection
攻擊者也無法:
❌ 刪除表格
❌ 讀取系統檔案
❌ 執行作業系統指令
✅ 只能操作資料(損害有限)
縱深防禦的一環:
Prepared Statements(第 1 層防護)
+ 最小權限(第 2 層防護)
→ 雙重保障✅ 安全檢查清單
代碼層面
□ 所有 SQL 查詢使用 Prepared Statements
□ 優先使用 Django ORM
□ Raw SQL 必須使用參數化
□ 沒有使用 f-string、format、% 拼接 SQL
□ extra() 使用 params 參數
□ 欄位名/表格名使用白名單驗證
□ 有輸入驗證(長度、格式、類型)
□ 使用 Django Form 驗證用戶輸入配置層面
□ 生產環境 DEBUG = False
□ 資料庫帳號使用最小權限
□ 沒有使用 root 帳號連線
□ 設定查詢超時時間
□ 啟用 HTTPS(SECURE_SSL_REDIRECT = True)
□ 設定 ALLOWED_HOSTS
□ 使用強密碼
□ 定期更新 Django 和套件監控層面
□ 記錄所有資料庫查詢日誌
□ 監控異常查詢模式
□ 設定告警機制
□ 定期審查日誌
□ 使用自動化掃描工具(Bandit)
□ 定期進行滲透測試💡 實戰範例:重構不安全代碼
範例 1:登入功能
# ❌ 不安全版本
def login_unsafe(request):
username = request.POST.get('username')
password = request.POST.get('password')
# 危險!字串拼接
with connection.cursor() as cursor:
query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
cursor.execute(query)
user = cursor.fetchone()
if user:
return JsonResponse({'success': True})
else:
return JsonResponse({'success': False})
# ✅ 安全版本
from django.contrib.auth import authenticate
from django import forms
class LoginForm(forms.Form):
username = forms.CharField(max_length=150)
password = forms.CharField(widget=forms.PasswordInput)
def login_safe(request):
form = LoginForm(request.POST)
if not form.is_valid():
return JsonResponse({'error': 'Invalid input'}, status=400)
username = form.cleaned_data['username']
password = form.cleaned_data['password']
# Django 內建認證(自動防禦 SQL Injection)
user = authenticate(request, username=username, password=password)
if user is not None:
login(request, user)
return JsonResponse({'success': True})
else:
return JsonResponse({'success': False, 'error': '帳號或密碼錯誤'})範例 2:搜尋功能
# ❌ 不安全版本
def search_products_unsafe(request):
keyword = request.GET.get('q', '')
category = request.GET.get('category', '')
# 危險!字串拼接
query = f"SELECT * FROM products WHERE name LIKE '%{keyword}%' AND category = '{category}'"
products = Product.objects.raw(query)
return render(request, 'products.html', {'products': products})
# ✅ 安全版本
class ProductSearchForm(forms.Form):
q = forms.CharField(max_length=100, required=False)
category = forms.ChoiceField(
choices=[('electronics', '電子產品'), ('clothing', '服飾')],
required=False
)
def search_products_safe(request):
form = ProductSearchForm(request.GET)
if not form.is_valid():
return JsonResponse({'error': 'Invalid input'}, status=400)
keyword = form.cleaned_data.get('q', '')
category = form.cleaned_data.get('category', '')
# 使用 Django ORM(自動安全)
products = Product.objects.all()
if keyword:
products = products.filter(name__icontains=keyword)
if category:
products = products.filter(category=category)
return render(request, 'products.html', {'products': products})✅ 重點回顧
核心防禦:Prepared Statements
- ✅ 將「SQL 結構」與「資料」分離
- ✅ 資料庫自動處理轉義
- ✅ 用戶輸入無法改變 SQL 結構
Django 最佳實踐:
- 🥇 第一選擇:Django ORM(自動安全)
- 🥈 第二選擇:raw() 搭配參數
- ❌ 絕對禁止:字串拼接(f-string、format、%)
縱深防禦:
- 輸入驗證(白名單)
- Prepared Statements(核心)
- 最小權限原則(限制損害)
- WAF 防火牆(額外防護)
- 監控與日誌(偵測攻擊)
安全檢查清單:
- □ 使用 ORM 或 Prepared Statements
- □ 輸入驗證與 Django Form
- □ 資料庫最小權限
- □ DEBUG = False
- □ 定期掃描(Bandit、check –deploy)
記憶口訣: 「ORM參驗權」= ORM、參數化、驗證、權限控制
恭喜你完成 SQL Injection 三部曲! → 02-1 基礎:理解攻擊 → 02-2 技巧:學習手法 → 02-3 防禦:保護系統
上一篇: 02-2. SQL Injection 攻擊技巧 下一篇: 02-4. NoSQL Injection
最後更新:2025-01-16