ASP ADO
Connection / Recordset / Command 三个核心对象，查询、参数化、事务。
三个核心对象
| Object | Role |
|---|---|
ADODB.Connection | 建立与数据库的连接，执行 SQL，管事务 |
ADODB.Recordset | 承载查询结果，可遍历、可更新 |
ADODB.Command | 带参数的 SQL，防注入 |
连接与查询
<%
Dim conn, rs, connStr
connStr = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=app;" & _
"User ID=appuser;Password=secret;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr
Set rs = conn.Execute("SELECT id, name, email FROM users ORDER BY id")
Do While Not rs.EOF
Response.Write rs("id") & " — " & _
Server.HTMLEncode(rs("name")) & " — " & _
Server.HTMLEncode(rs("email")) & "<br>"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>1 — Ada — ada@example.com
2 — Bob — bob@example.com
3 — Cy — cy@example.comconn.Execute returns a Recordset directly, fine for simple queries. Parameterisation requires a Command, the only reliable defence against injection.
参数化查询
<%
Dim cmd, prm
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT id, name FROM users WHERE name = ? AND status = ?"
cmd.CommandType = 1 ' 1 = adCmdText
cmd.Parameters.Append cmd.CreateParameter("name", 200, 1, 100, Request.Form("name"))
cmd.Parameters.Append cmd.CreateParameter("status", 3, 1, , 1)
Set rs = cmd.Execute
%>| Param | Meaning |
|---|---|
name | 参数名 |
200 | adVarWChar（宽字符串） |
1 | adParamInput 输入 |
100 | 最大字符数 |
值 | 实际传的值 |
With parameters the input is always a value, never SQL. Passing ' OR '1'='1 simply matches a user with that literal name and returns nothing.
<%-- 危险：拼接 --%>
sql = "SELECT * FROM users WHERE name = '" & Request.Form("name") & "'"
<%-- 输入 ' OR '1'='1 后变成：WHERE name = '' OR '1'='1' → 返回全表 --%>事务
<%
conn.BeginTrans
On Error Resume Next
conn.Execute "UPDATE accounts SET balance = balance - 100 WHERE id = 1"
If Err.Number = 0 Then
conn.Execute "UPDATE accounts SET balance = balance + 100 WHERE id = 2"
End If
If Err.Number <> 0 Then
conn.RollbackTrans
Response.Write "转账失败：" & Err.Description
Err.Clear
Else
conn.CommitTrans
Response.Write "转账成功"
End If
On Error GoTo 0
%>转账成功BeginTrans / CommitTrans / RollbackTrans guarantee all-or-nothing. Without a transaction, a successful debit plus a failed credit makes money vanish.
连接字符串
<%
' SQL Server（推荐用新版驱动）
"Provider=MSOLEDBSQL;Server=localhost;Database=app;UID=app;PWD=secret;"
' Access（.mdb / .accdb）
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/data/app.mdb")
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("/data/app.accdb")
' MySQL（需装 ODBC 驱动）
"Driver={MySQL ODBC 8.0 Driver};Server=localhost;Database=app;User=app;Password=secret;"
%>An Access file inside the web root can be downloaded by anyone — the whole database leaks. Keep it outside the root or block the extension in IIS request filtering.
conn.asp outside the web root, or initialise it into Application from Global.asa.Recordset 的常用操作
<%
Response.Write "字段数：" & rs.Fields.Count & "<br>"
Response.Write "记录数：" & rs.RecordCount & "<br>" ' 需游标支持，否则为 -1
Dim fld
For Each fld In rs.Fields
Response.Write fld.Name & " (" & fld.Type & ")<br>"
Next
rs.MoveFirst
rs.MoveLast
rs.Move 2
%>RecordCount returns -1 with the default forward-only cursor. To get a real count, open with CursorType = 3 (static) or issue a separate SELECT COUNT(*).
分页
<%
Dim page, size, offset, sql
page = CLng(Request("page") & "")
If page < 1 Then page = 1
size = 20
offset = (page - 1) * size
' SQL Server 2012+ 的写法
sql = "SELECT id, name FROM users ORDER BY id " & _
"OFFSET " & offset & " ROWS FETCH NEXT " & size & " ROWS ONLY"
%>OFFSET ... FETCH requires SQL Server 2012+. Older versions need a ROW_NUMBER() subquery. Even numeric paging parameters must be cast with CLng and range-checked, or they can carry injection.
三个核心对象
| 对象 | 作用 |
|---|---|
| 连接 | 建立与数据库的连接，执行 SQL，管事务 |
| 记录集 | 承载查询结果，可遍历、可更新 |
| 命令 | 带参数的 SQL，防注入 |
连接与查询
<%
Dim conn, rs, connStr
connStr = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=app;" & _
"User ID=appuser;Password=secret;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open connStr
Set rs = conn.Execute("SELECT id, name, email FROM users ORDER BY id")
Do While Not rs.EOF
Response.Write rs("id") & " — " & _
Server.HTMLEncode(rs("name")) & " — " & _
Server.HTMLEncode(rs("email")) & "<br>"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>1 — Ada — ada@example.com
2 — Bob — bob@example.com
3 — Cy — cy@example.comconn.Execute 直接返回 Recordset，适合简单查询。要参数化就必须用 Command，那是防注入的唯一可靠手段。
参数化查询
<%
Dim cmd, prm
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT id, name FROM users WHERE name = ? AND status = ?"
cmd.CommandType = 1 ' 1 = adCmdText
cmd.Parameters.Append cmd.CreateParameter("name", 200, 1, 100, Request.Form("name"))
cmd.Parameters.Append cmd.CreateParameter("status", 3, 1, , 1)
Set rs = cmd.Execute
%>| 参数 | 含义 |
|---|---|
| 名称 | 参数名 |
| 类型 | adVarWChar（宽字符串） |
| 方向 | adParamInput 输入 |
| 长度 | 最大字符数 |
| 值 | 实际传的值 |
参数化之后，用户输入永远被当成「值」而不是「SQL 片段」。传 ' OR '1'='1 只会去匹配一个叫这个名字的用户，查不到任何行。
<%-- 危险：拼接 --%>
sql = "SELECT * FROM users WHERE name = '" & Request.Form("name") & "'"
<%-- 输入 ' OR '1'='1 后变成：WHERE name = '' OR '1'='1' → 返回全表 --%>事务
<%
conn.BeginTrans
On Error Resume Next
conn.Execute "UPDATE accounts SET balance = balance - 100 WHERE id = 1"
If Err.Number = 0 Then
conn.Execute "UPDATE accounts SET balance = balance + 100 WHERE id = 2"
End If
If Err.Number <> 0 Then
conn.RollbackTrans
Response.Write "转账失败：" & Err.Description
Err.Clear
Else
conn.CommitTrans
Response.Write "转账成功"
End If
On Error GoTo 0
%>转账成功BeginTrans / CommitTrans / RollbackTrans 保证「要么都成功，要么都不做」。少了事务，扣款成功而加款失败时，钱就凭空消失了。
连接字符串
<%
' SQL Server（推荐用新版驱动）
"Provider=MSOLEDBSQL;Server=localhost;Database=app;UID=app;PWD=secret;"
' Access（.mdb / .accdb）
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/data/app.mdb")
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Server.MapPath("/data/app.accdb")
' MySQL（需装 ODBC 驱动）
"Driver={MySQL ODBC 8.0 Driver};Server=localhost;Database=app;User=app;Password=secret;"
%>Access 数据库文件放在 Web 根下时，任何人都能直接下载 .mdb——等于整个数据库泄露。必须放到 Web 根之外，或用 IIS 请求筛选禁止下载该扩展名。
conn.asp，或用 Application 由 Global.asa 初始化，能降低泄露风险。Recordset 的常用操作
<%
Response.Write "字段数：" & rs.Fields.Count & "<br>"
Response.Write "记录数：" & rs.RecordCount & "<br>" ' 需游标支持，否则为 -1
Dim fld
For Each fld In rs.Fields
Response.Write fld.Name & " (" & fld.Type & ")<br>"
Next
rs.MoveFirst
rs.MoveLast
rs.Move 2
%>RecordCount 在默认的「仅向前」游标下返回 -1。要拿到真实数量，打开 Recordset 时指定 CursorType = 3（静态游标），或干脆另发一条 SELECT COUNT(*)。
分页
<%
Dim page, size, offset, sql
page = CLng(Request("page") & "")
If page < 1 Then page = 1
size = 20
offset = (page - 1) * size
' SQL Server 2012+ 的写法
sql = "SELECT id, name FROM users ORDER BY id " & _
"OFFSET " & offset & " ROWS FETCH NEXT " & size & " ROWS ONLY"
%>OFFSET ... FETCH 是 SQL Server 2012 之后的标准写法。老版本要用 ROW_NUMBER() 子查询。分页参数虽然是数字，也要 CLng 转换并校验范围，否则能拼出注入。