萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP 程式設計

ASP 刪除資料庫記錄實戰:參數化防注入與批次刪除

單筆刪除:安全第一

<%
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
%>

安全過濾函數 SafeRequest (增強版)

<%
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 資料庫操作安全的鐵律。

copyright © 萬盛學電腦網 all rights reserved