通用函數
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