| 模式 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|
| Recordset.AddNew | 自動處理資料型態、單引號轉義、欄位對應直觀 | 效能較差、需開啟可更新游標 (1,3)、鎖定資源較久 | 欄位多、型態複雜、開發效率優先 |
| SQL INSERT | 效能最佳、網路傳輸少、支援批次、可精確控制 | 需手動處理單引號、日期格式、NULL 值、字串拼接易出錯 | 高併發、大量寫入、效能敏感 |
<%
' 1. 取得表單資料 (已做 HTMLEncode 防 XSS)
Dim username, password, email, phone
username = Server.HTMLEncode(Request.Form("username"))
password = Server.HTMLEncode(Request.Form("password")) ' 實務應加密儲存
email = Server.HTMLEncode(Request.Form("email"))
phone = Server.HTMLEncode(Request.Form("telphone"))
' 2. 開啟可更新 Recordset
Dim conn, rs, sql
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr
sql = "SELECT username, password, email, telphone FROM users WHERE 1=0" ' 只取結構
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 1, 3 ' adOpenKeyset, adLockOptimistic
' 3. 新增記錄
rs.AddNew
rs("username") = username
rs("password") = password
rs("email") = email
rs("telphone") = phone
rs.Update
' 4. 釋放資源
rs.Close : Set rs = Nothing
conn.Close : Set conn = Nothing
Response.Write "新增成功"
%>
<%
Dim conn, sql, username, email
username = Request.Form("username")
email = Request.Form("email")
' 關鍵:單引號轉義 (雙單引號)
username = Replace(username, "'", "''")
email = Replace(email, "'", "''")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr
sql = "INSERT INTO users (username, email, reg_date) VALUES ('" & username & "', '" & email & "', GETDATE())"
conn.Execute sql
' 取得新增自增 ID (SQL Server)
' Dim newId : newId = conn.Execute("SELECT SCOPE_IDENTITY()")(0)
conn.Close
Set conn = Nothing
Response.Write "新增成功"
%>
<%
Dim conn, cmd, username, email
username = Request.Form("username")
email = Request.Form("email")
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO users (username, email, reg_date) VALUES (?, ?, GETDATE())"
cmd.CommandType = 1
cmd.Prepared = True
cmd.Parameters.Append cmd.CreateParameter("@username", 200, 1, 50, username) ' adVarChar, adParamInput, size 50
cmd.Parameters.Append cmd.CreateParameter("@email", 200, 1, 100, email)
cmd.Execute
cmd.ActiveConnection = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
%>
<form method="post" action="add.asp">
<input type="text" name="username" required />
<input type="password" name="password" required />
<input type="email" name="email" required />
<input type="tel" name="telphone" />
<button type="submit">送出</button>
</form>
name 屬性必須與伺服端 Request.Form("name") 完全對應required, type="email") 降低無效提交Server.HTMLEncode (防 XSS) 或參數化 (防 SQLi)依專案規模與安全等級選擇模式:中小專案用 AddNew 開發快,大型高併發用 Command 參數化最穩健。