萬盛學電腦網

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

ASP Access 資料庫 Insert / Update / Delete 通用函數封裝

通用函數

Function add_del_update(tablename, action, id)
    Select Case action
        Case "insert"
            sql = "SELECT * FROM [" & tablename & "] WHERE id IS NULL"
            rs.Open sql, conn, 1, 3  ' adOpenKeyset, adLockOptimistic
            rs.AddNew
            For Each key In Request.Form
                rs(CStr(key)) = Request(key)
            Next
            rs.Update
            rs.Close
            
        Case "update"
            sql = "SELECT * FROM [" & tablename & "] WHERE id=" & id
            rs.Open sql, conn, 1, 3
            For Each key In Request.Form
                If key <> "id" Then rs(CStr(key)) = Request(key)
            Next
            rs.Update
            rs.Close
            
        Case "delete"
            sql = "DELETE FROM [" & tablename & "] WHERE id IN (" & id & ")"
            rs.Open sql, conn, 1, 3
    End Select
End Function

呼叫範例

<%
' 新增
Call add_del_update("table1", "insert", "")

' 修改
id = Request("id")
Call add_del_update("table1", "update", id)

' 刪除
id = Request("id")
Call add_del_update("table1", "delete", id)
%>

風險與改進

  • 大量賦值漏洞:For Each key In Request.Form 直接寫入所有欄位,攻擊者可偽造隱藏欄位(如 isAdmin、price)。必須維護白名單欄位清單。
  • SQL Injection:id 直接串入 SQL。
  • 資料型別:Request.Form 全為字串,寫入數值/日期欄位需轉型驗證。
  • 錯誤處理:無 On Error、交易控制。
  • 連線管理:conn 為全域變數,建議函數內建立/關閉或傳入連線物件。

改良版雛形

Function SafeUpdate(tablename, id, allowedFields)
    ' allowedFields: Array("name", "email", "age")
    Dim sql, cmd, field
    Set cmd = Server.CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    If id = "" Then
        sql = "INSERT INTO [" & tablename & "] ("
        ' 動態建立欄位與參數
    Else
        sql = "UPDATE [" & tablename & "] SET "
        ' 動態建立 SET 子句
        sql = sql & " WHERE id=?"
    End If
    cmd.CommandText = sql
    ' 依 allowedFields 附加參數...
    cmd.Execute
End Function
copyright © 萬盛學電腦網 all rights reserved