<%
Dim id, sql, conn, cmd
id = SafeRequest("id") ' 必經過安全過濾
' ❌ 危險:字串拼接 SQL
' sql = "DELETE FROM table1 WHERE id = " & id
' ✅ 安全:參數化查詢 (ADODB.Command)
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "DELETE FROM table1 WHERE id = ?"
cmd.CommandType = 1 ' adCmdText
cmd.Parameters.Append cmd.CreateParameter("@id", 3, 1, , id) ' adInteger, adParamInput
cmd.Execute
Response.Write ""
cmd.ActiveConnection = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
%>
<%
Function SafeRequest(paraName)
Dim value, sqlKeywords, kw, lcValue
value = Request(paraName)
' 數值型直接驗證
If IsNumeric(value) Then
SafeRequest = CLng(value)
Exit Function
End If
' 字串型:HTML 編碼 + SQL 關鍵字檢測
value = Server.HTMLEncode(value)
lcValue = LCase(value)
sqlKeywords = Array("select ", "insert ", "delete ", "drop ", "update ", "alter ", _
"create ", "exec ", "execute ", "xp_", "sp_", "truncate ", _
"union ", "declare ", "cast(", "convert(", "char(", "mid(", _
"substring(", "ascii(", "waitfor ", "delay ", "shutdown", _
"or ", "and ", ";", "--", "/*", "*/", "@@", "char(", "nchar(")
For Each kw In sqlKeywords
If InStr(lcValue, kw) > 0 Then
Response.Write "檢測到非法字元:" & kw
Response.End
End If
Next
SafeRequest = value
End Function
%>
<%
' 前端:checkbox name="chkId" value="記錄ID"
Dim ids, idArr, i, conn, cmd
ids = Request.Form("chkId") ' 格式: "1,5,8,12"
If ids <> "" Then
idArr = Split(ids, ",")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "DELETE FROM table1 WHERE id = ?"
cmd.CommandType = 1
cmd.Prepared = True ' 預編譯,批次執行效能佳
Dim param
Set param = cmd.CreateParameter("@id", 3, 1) ' adInteger
cmd.Parameters.Append param
For i = 0 To UBound(idArr)
If IsNumeric(idArr(i)) Then
param.Value = CLng(idArr(i))
cmd.Execute
End If
Next
Response.Write "已刪除 " & UBound(idArr) + 1 & " 筆記錄"
cmd.ActiveConnection = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
Else
Response.Write "未選擇任何記錄"
End If
%>
-- 資料庫新增欄位
ALTER TABLE table1 ADD IsDeleted BIT DEFAULT 0;
ALTER TABLE table1 ADD DeletedAt DATETIME NULL;
-- 刪除操作改為更新
UPDATE table1 SET IsDeleted = 1, DeletedAt = GETDATE() WHERE id = ?;
-- 查詢加條件
SELECT * FROM table1 WHERE IsDeleted = 0;
優點:資料可恢復、維護關聯完整性、支援稽核追蹤。
| 方案 | 安全性 | 效能 | 適用場景 |
|---|---|---|---|
| 字串拼接 SQL | 極低 (注入高風險) | 高 | 絕對禁止 |
| 參數化 Command (單筆) | 高 | 中 | 單筆刪除、管理後台 |
| 參數化 Command + Prepared (批次) | 高 | 高 | 批次刪除、大量操作 |
| 軟刪除 | 高 | 中 | 重要業務資料、需審計 |
堅持參數化查詢、輸入驗證、最小權限原則,是 ASP 資料庫操作安全的鐵律。