萬盛學電腦網

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

ASP 新增資料完整實作:Recordset AddNew 與 SQL INSERT 兩大模式對比

兩大新增模式對比

模式優點缺點適用場景
Recordset.AddNew自動處理資料型態、單引號轉義、欄位對應直觀效能較差、需開啟可更新游標 (1,3)、鎖定資源較久欄位多、型態複雜、開發效率優先
SQL INSERT效能最佳、網路傳輸少、支援批次、可精確控制需手動處理單引號、日期格式、NULL 值、字串拼接易出錯高併發、大量寫入、效能敏感

模式一:Recordset.AddNew (推薦一般開發)

<%
' 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 "新增成功"
%>

模式二:SQL INSERT (高效能)

<%
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 "新增成功"
%>

模式三:Command 參數化 INSERT (最佳安全+效能)

<%
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") 完全對應
  • 建議加上 HTML5 驗證屬性 (required, type="email") 降低無效提交

安全強化清單

  1. 所有輸入經 Server.HTMLEncode (防 XSS) 或參數化 (防 SQLi)
  2. 密碼**絕不**明文儲存 → 使用 BCrypt/Argon2/PBKDF2 雜湊
  3. 敏感欄位 (Email、電話) 考慮加密儲存
  4. 驗證碼/Token 防 CSRF 與機器人提交
  5. 資料庫帳號最小權限 (僅 INSERT/SELECT 權限)

依專案規模與安全等級選擇模式:中小專案用 AddNew 開發快,大型高併發用 Command 參數化最穩健。

copyright © 萬盛學電腦網 all rights reserved