註冊頁常見需求:使用者輸入帳號離開欄位時,即時檢查資料庫是否已存在。本文提供後端 ASP 端點程式碼 (Access 範例,易改 SQL Server)、前端 jQuery AJAX 呼叫範例,並說明 SQL Injection 防範要點。
<%
' 回傳 1=已存在, 0=可用
Dim conn, rs, connstr, sqlCmd, userName, exist
userName = Request.QueryString("userName")
If userName = "" Then Response.Write "-1" : Response.End
' 建立連線 (Access 範例)
Set conn = Server.CreateObject("ADODB.Connection")
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("data.mdb")
conn.Open connstr
' 參數化查詢防 SQL Injection (ADO Command 物件)
Set rs = Server.CreateObject("ADODB.Recordset")
sqlCmd = "SELECT id FROM [user] WHERE userName = ?"
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = sqlCmd
cmd.CommandType = 1 ' adCmdText
cmd.Parameters.Append cmd.CreateParameter("@uname", 200, 1, 50, userName) ' adVarChar, adParamInput
Set rs = cmd.Execute
If rs.EOF Then exist = 0 Else exist = 1
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
Response.Write exist
%>
<input type="text" id="username" name="username" onblur="checkUser()">
<span id="userMsg"></span>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
function checkUser() {
var val = $('#username').val().trim();
if (val.length < 3) { $('#userMsg').text('至少 3 字元').css('color','red'); return; }
$.get('checkUser.asp', { userName: val }, function(data) {
if (data == '1') $('#userMsg').text('帳號已存在').css('color','red');
else if (data == '0') $('#userMsg').text('帳號可用').css('color','green');
else $('#userMsg').text('參數錯誤').css('color','orange');
});
}
</script>
ADODB.Command + Parameters,完全避免 SQL Injection。userName 欄位務必建立 Unique Index,效能與資料完雙贏。connstr = "Provider=SQLOLEDB;Data Source=伺服器IP;Initial Catalog=資料庫;User ID=帳號;Password=密碼;"
其餘邏輯完全相同。