<%
' 数据库连接与查询
Dim conn, rs, sql, totalSales, totalQuantity
totalSales = 0
totalQuantity = 0
On Error Resume Next ' 开启错误处理
' 创建数据库连接
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=.;Initial Catalog=SalesDB;Integrated Security=SSPI;"
If Err.Number <> 0 Then
Response.Write “
数据库连接失败: ” & Err.Description & “
”
Response.End
End If
‘ 构建查询 – 使用参数化查询防止SQL注入
sql = “SELECT ProductName, SUM(Quantity) as TotalQty, SUM(Price * Quantity) as TotalAmount ” & _
“FROM SalesData WHERE SaleDate BETWEEN ‘2024-01-01’ AND ‘2024-01-31’ ” & _
“GROUP BY ProductName ORDER BY TotalAmount DESC”
”
%>
5.2 数据库兼容性问题
5.2.1 不同数据库的SQL语法差异
<%
' 检测数据库类型并调整SQL
Function GetDBType()
Dim provider
provider = LCase(conn.Provider)
If InStr(provider, "sqloledb") > 0 Then
GetDBType = “SQLSERVER”
ElseIf InStr(provider, “microsoft.jet.oledb”) > 0 Then
GetDBType = “ACCESS”
ElseIf InStr(provider, “mysql”) > 0 Then
GetDBType = “MYSQL”
End If
End Function
‘ 根据数据库类型调整日期函数
Dim dateFunc
Select Case GetDBType()
Case “SQLSERVER”
dateFunc = “GETDATE()”
Case “ACCESS”
dateFunc = “Now()”
Case “MYSQL”
dateFunc = “NOW()”
End Select
sql = “SELECT * FROM SalesData WHERE SaleDate >= ” & dateFunc
%>
5.2.2 连接泄漏防护
<%
' 使用类来封装数据库连接，确保自动关闭
Class DBConnection
Private m_conn
Private Sub Class_Initialize()
Set m_conn = Server.CreateObject("ADODB.Connection")
m_conn.Open "Provider=SQLOLEDB;..."
End Sub
Private Sub Class_Terminate()
If Not m_conn Is Nothing Then
If m_conn.State = 1 Then m_conn.Close
Set m_conn = Nothing
End If
End Sub
Public Function Execute(sql)
Set Execute = m_conn.Execute(sql)
End Function
End Class
' 使用示例
Dim db
Set db = New DBConnection
Set rs = db.Execute("SELECT * FROM SalesData")
' 无需手动关闭，对象销毁时自动关闭连接
%>
5.3 编码与字符集问题
5.3.1 中文乱码解决方案
<%
' 统一设置响应头
Response.Charset = "UTF-8"
Response.ContentType = "text/html; charset=UTF-8"
' 数据库连接指定编码
connStr = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=MyDB;Integrated Security=SSPI;"
' 输出时双重转义
Function SafeOutput(text)
If IsNull(text) Then
SafeOutput = ""
Else
SafeOutput = Server.HTMLEncode(CStr(text))
End If
End Function
Response.Write "
” & SafeOutput(rs(“ProductName”)) & “
”
%>
5.3.2 文件编码声明
确保ASP文件本身保存为UTF-8无BOM格式，否则会导致首行指令解析错误。
六、性能优化与安全加固
6.1 性能优化技巧
6.1.1 缓存机制
<%
' 简单的文件缓存实现
Function GetCachedData(cacheKey, expiryMinutes)
Dim cacheFile, fso, f, content, lastModified
cacheFile = Server.MapPath("cache/" & cacheKey & ".cache")
Set fso = Server.CreateObject("Scripting.FileSystemObject")
If fso.FileExists(cacheFile) Then
Set f = fso.GetFile(cacheFile)
lastModified = f.DateLastModified
If DateDiff("n", lastModified, Now) < expiryMinutes Then
Set f = fso.OpenTextFile(cacheFile, 1)
content = f.ReadAll
f.Close
GetCachedData = content
Exit Function
End If
End If
' 缓存过期或不存在，返回空字符串
GetCachedData = ""
End Function
' 使用缓存
Dim cachedReport
cachedReport = GetCachedData("sales_report_202401", 60)
If cachedReport <> “” Then
Response.Write cachedReport
Else
‘ 生成报表并保存到缓存
‘ … 生成逻辑 …
‘ 保存到缓存文件
End If
%>
6.1.2 记录集优化
<%
' 使用游标和锁类型优化
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3 ' adUseClient
rs.Open sql, conn, 3, 1 ' adOpenStatic, adLockReadOnly
' 只读取需要的字段
sql = "SELECT ProductName, Quantity, Price FROM SalesData" ' 不要使用 SELECT *
%>
6.2 安全加固措施
6.2.1 SQL注入防护
<%
' 永远不要使用字符串拼接构建SQL
' 错误示例：
Dim userInput
userInput = Request.QueryString("search")
sql = "SELECT * FROM Products WHERE Name LIKE '%" & userInput & "%'" ' 危险！
' 正确示例：使用参数化查询
Dim cmd, param
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM Products WHERE Name LIKE ?"
Set param = cmd.CreateParameter("@Search", 200, 1, 100, "%" & userInput & "%")
cmd.Parameters.Append param
Set rs = cmd.Execute
%>
6.2.2 输入验证
<%
' 验证输入参数
Function ValidateInput(input, pattern)
Dim regEx
Set regEx = New RegExp
regEx.Pattern = pattern
regEx.IgnoreCase = True
ValidateInput = regEx.Test(input)
End Function
' 验证页码参数
Dim page
page = Request.QueryString("page")
If page = "" Then page = 1
If Not ValidateInput(page, "^\d+$") Then
Response.Write "Invalid page number"
Response.End
End If
page = CInt(page)
%>
七、实战案例：完整的企业级报表系统
7.1 系统架构设计
报表系统/
├── config.asp # 数据库配置
├── includes/
│ ├── db.asp # 数据库连接类
│ ├── utils.asp # 工具函数
│ └── security.asp # 安全函数
├── reports/
│ ├── sales_report.asp # 销售报表
│ ├── inventory_report.asp # 库存报表
│ └── export.asp # 导出功能
└── css/
└── report.css # 样式表
7.2 核心模块代码
7.2.1 数据库连接模块（includes/db.asp）
<%
Class DBManager
Private m_conn
Private m_isConnected
Private Sub Class_Initialize()
m_isConnected = False
End Sub
Private Sub Class_Terminate()
Disconnect
End Sub
Public Function Connect()
On Error Resume Next
Set m_conn = Server.CreateObject("ADODB.Connection")
' 从配置文件读取连接字符串
m_conn.Open GetConnectionString()
If Err.Number <> 0 Then
Connect = False
Err.Clear
Else
m_isConnected = True
Connect = True
End If
End Function
Public Sub Disconnect()
If m_isConnected And Not m_conn Is Nothing Then
If m_conn.State = 1 Then m_conn.Close
Set m_conn = Nothing
m_isConnected = False
End If
End Sub
Public Function Execute(sql, params)
Dim cmd, i, param
Set cmd = Server.CreateObject(“ADODB.Command”)
cmd.ActiveConnection = m_conn
cmd.CommandText = sql
cmd.CommandType = 1
If IsArray(params) Then
For i = LBound(params) To UBound(params)
Set param = cmd.CreateParameter(“@p” & i, 200, 1, 255, params(i))
cmd.Parameters.Append param
Next
End If
On Error Resume Next
Set Execute = cmd.Execute
If Err.Number <> 0 Then
Set Execute = Nothing
Err.Clear
End If
End Function
Public Function GetRecordSet(sql, params)
Set GetRecordSet = Execute(sql, params)
End Function
Private Function GetConnectionString()
‘ 可以从外部配置文件读取
GetConnectionString = “Provider=SQLOLEDB;Data Source=.;Initial Catalog=SalesDB;Integrated Security=SSPI;”
End Function
End Class
%>
7.2.2 工具函数模块（includes/utils.asp）
<%
' HTML转义
Function HTMLEncode(text)
If IsNull(text) Or IsEmpty(text) Then
HTMLEncode = ""
Else
HTMLEncode = Server.HTMLEncode(CStr(text))
End If
End Function
' SQL参数转义（备用方案，推荐使用参数化查询）
Function SQLEncode(text)
If IsNull(text) Then
SQLEncode = ""
Else
SQLEncode = Replace(CStr(text), "'", "''")
End If
End Function
' 生成分页链接
Function GeneratePagination(currentPage, totalPages, urlPattern)
Dim html, i
html = "
”
If currentPage > 1 Then
html = html & “上一页”
End If
Dim startPage, endPage
startPage = currentPage – 2
If startPage < 1 Then startPage = 1
endPage = currentPage + 2
If endPage > totalPages Then endPage = totalPages
For i = startPage To endPage
If i = currentPage Then
html = html & “” & i & “”
Else
html = html & “” & i & “”
End If
Next
If currentPage < totalPages Then
html = html & "下一页”
End If
‘ 输出表头
Dim i
For i = 0 To rs.Fields.Count – 1
Response.Write rs.Fields(i).Name
If i < rs.Fields.Count - 1 Then Response.Write ","
Next
Response.Write vbCrLf
' 输出数据
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
Dim value
value = rs.Fields(i).Value
If IsNull(value) Then value = ""
' 处理包含逗号的字段
If InStr(value, ",") > 0 Then value = “””” & value & “”””
Response.Write value
If i < rs.Fields.Count - 1 Then Response.Write ","
Next
Response.Write vbCrLf
rs.MoveNext
Loop
End Function
%>
7.3 完整报表页面示例
<%@ Language=VBScript Codepage=65001 %>
<% Option Explicit %>
<%
' 页面参数
Dim startDate, endDate, productType, page
startDate = Request.QueryString("start")
endDate = Request.QueryString("end")
productType = Request.QueryString("type")
page = Request.QueryString("page")
' 默认值
If startDate = "" Then startDate = DateAdd("m", -1, Date)
If endDate = "" Then endDate = Date
If page = "" Then page = 1
page = CInt(page)
' 初始化数据库
Dim db, rs, sql, params
Set db = New DBManager
If Not db.Connect() Then
Response.Write "数据库连接失败"
Response.End
End If
' 构建参数化查询
sql = "SELECT ProductName, ProductType, SUM(Quantity) as Qty, SUM(Amount) as Amount " & _
"FROM SalesData WHERE SaleDate BETWEEN ? AND ? "
params = Array(startDate, endDate)
If productType <> “” Then
sql = sql & “AND ProductType = ? ”
ReDim Preserve params(UBound(params) + 1)
params(UBound(params)) = productType
End If
sql = sql & “GROUP BY ProductName, ProductType ORDER BY Amount DESC”
‘ 获取数据
Set rs = db.GetRecordSet(sql, params)
If rs Is Nothing Then
Response.Write “查询失败”
Else
‘ 输出HTML
%>
<%
End If
' 清理资源
rs.Close
db.Disconnect
Set rs = Nothing
Set db = Nothing
%>
八、常见兼容性问题深度解决方案
8.1 IIS版本兼容性
8.1.1 IIS7+ 配置问题
在IIS7及以上版本中，需要在
web.config
中配置处理程序映射：
<%
' 数据库连接与查询
Dim conn, rs, sql, totalSales, totalQuantity
totalSales = 0
totalQuantity = 0
On Error Resume Next ' 开启错误处理
' 创建数据库连接
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=.;Initial Catalog=SalesDB;Integrated Security=SSPI;"
If Err.Number <> 0 Then
Response.Write “
数据库连接失败: ” & Err.Description & “
”
Response.End
End If
‘ 构建查询 – 使用参数化查询防止SQL注入
sql = “SELECT ProductName, SUM(Quantity) as TotalQty, SUM(Price * Quantity) as TotalAmount ” & _
“FROM SalesData WHERE SaleDate BETWEEN ‘2024-01-01’ AND ‘2024-01-31’ ” & _
“GROUP BY ProductName ORDER BY TotalAmount DESC”
”
%>
5.2 数据库兼容性问题
5.2.1 不同数据库的SQL语法差异
<%
' 检测数据库类型并调整SQL
Function GetDBType()
Dim provider
provider = LCase(conn.Provider)
If InStr(provider, "sqloledb") > 0 Then
GetDBType = “SQLSERVER”
ElseIf InStr(provider, “microsoft.jet.oledb”) > 0 Then
GetDBType = “ACCESS”
ElseIf InStr(provider, “mysql”) > 0 Then
GetDBType = “MYSQL”
End If
End Function
‘ 根据数据库类型调整日期函数
Dim dateFunc
Select Case GetDBType()
Case “SQLSERVER”
dateFunc = “GETDATE()”
Case “ACCESS”
dateFunc = “Now()”
Case “MYSQL”
dateFunc = “NOW()”
End Select
sql = “SELECT * FROM SalesData WHERE SaleDate >= ” & dateFunc
%>
5.2.2 连接泄漏防护
<%
' 使用类来封装数据库连接，确保自动关闭
Class DBConnection
Private m_conn
Private Sub Class_Initialize()
Set m_conn = Server.CreateObject("ADODB.Connection")
m_conn.Open "Provider=SQLOLEDB;..."
End Sub
Private Sub Class_Terminate()
If Not m_conn Is Nothing Then
If m_conn.State = 1 Then m_conn.Close
Set m_conn = Nothing
End If
End Sub
Public Function Execute(sql)
Set Execute = m_conn.Execute(sql)
End Function
End Class
' 使用示例
Dim db
Set db = New DBConnection
Set rs = db.Execute("SELECT * FROM SalesData")
' 无需手动关闭，对象销毁时自动关闭连接
%>
5.3 编码与字符集问题
5.3.1 中文乱码解决方案
<%
' 统一设置响应头
Response.Charset = "UTF-8"
Response.ContentType = "text/html; charset=UTF-8"
' 数据库连接指定编码
connStr = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=MyDB;Integrated Security=SSPI;"
' 输出时双重转义
Function SafeOutput(text)
If IsNull(text) Then
SafeOutput = ""
Else
SafeOutput = Server.HTMLEncode(CStr(text))
End If
End Function
Response.Write "
” & SafeOutput(rs(“ProductName”)) & “
”
%>
5.3.2 文件编码声明
确保ASP文件本身保存为UTF-8无BOM格式，否则会导致首行指令解析错误。
六、性能优化与安全加固
6.1 性能优化技巧
6.1.1 缓存机制
<%
' 简单的文件缓存实现
Function GetCachedData(cacheKey, expiryMinutes)
Dim cacheFile, fso, f, content, lastModified
cacheFile = Server.MapPath("cache/" & cacheKey & ".cache")
Set fso = Server.CreateObject("Scripting.FileSystemObject")
If fso.FileExists(cacheFile) Then
Set f = fso.GetFile(cacheFile)
lastModified = f.DateLastModified
If DateDiff("n", lastModified, Now) < expiryMinutes Then
Set f = fso.OpenTextFile(cacheFile, 1)
content = f.ReadAll
f.Close
GetCachedData = content
Exit Function
End If
End If
' 缓存过期或不存在，返回空字符串
GetCachedData = ""
End Function
' 使用缓存
Dim cachedReport
cachedReport = GetCachedData("sales_report_202401", 60)
If cachedReport <> “” Then
Response.Write cachedReport
Else
‘ 生成报表并保存到缓存
‘ … 生成逻辑 …
‘ 保存到缓存文件
End If
%>
6.1.2 记录集优化
<%
' 使用游标和锁类型优化
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3 ' adUseClient
rs.Open sql, conn, 3, 1 ' adOpenStatic, adLockReadOnly
' 只读取需要的字段
sql = "SELECT ProductName, Quantity, Price FROM SalesData" ' 不要使用 SELECT *
%>
6.2 安全加固措施
6.2.1 SQL注入防护
<%
' 永远不要使用字符串拼接构建SQL
' 错误示例：
Dim userInput
userInput = Request.QueryString("search")
sql = "SELECT * FROM Products WHERE Name LIKE '%" & userInput & "%'" ' 危险！
' 正确示例：使用参数化查询
Dim cmd, param
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM Products WHERE Name LIKE ?"
Set param = cmd.CreateParameter("@Search", 200, 1, 100, "%" & userInput & "%")
cmd.Parameters.Append param
Set rs = cmd.Execute
%>
6.2.2 输入验证
<%
' 验证输入参数
Function ValidateInput(input, pattern)
Dim regEx
Set regEx = New RegExp
regEx.Pattern = pattern
regEx.IgnoreCase = True
ValidateInput = regEx.Test(input)
End Function
' 验证页码参数
Dim page
page = Request.QueryString("page")
If page = "" Then page = 1
If Not ValidateInput(page, "^\d+$") Then
Response.Write "Invalid page number"
Response.End
End If
page = CInt(page)
%>
七、实战案例：完整的企业级报表系统
7.1 系统架构设计
报表系统/
├── config.asp # 数据库配置
├── includes/
│ ├── db.asp # 数据库连接类
│ ├── utils.asp # 工具函数
│ └── security.asp # 安全函数
├── reports/
│ ├── sales_report.asp # 销售报表
│ ├── inventory_report.asp # 库存报表
│ └── export.asp # 导出功能
└── css/
└── report.css # 样式表
7.2 核心模块代码
7.2.1 数据库连接模块（includes/db.asp）
<%
Class DBManager
Private m_conn
Private m_isConnected
Private Sub Class_Initialize()
m_isConnected = False
End Sub
Private Sub Class_Terminate()
Disconnect
End Sub
Public Function Connect()
On Error Resume Next
Set m_conn = Server.CreateObject("ADODB.Connection")
' 从配置文件读取连接字符串
m_conn.Open GetConnectionString()
If Err.Number <> 0 Then
Connect = False
Err.Clear
Else
m_isConnected = True
Connect = True
End If
End Function
Public Sub Disconnect()
If m_isConnected And Not m_conn Is Nothing Then
If m_conn.State = 1 Then m_conn.Close
Set m_conn = Nothing
m_isConnected = False
End If
End Sub
Public Function Execute(sql, params)
Dim cmd, i, param
Set cmd = Server.CreateObject(“ADODB.Command”)
cmd.ActiveConnection = m_conn
cmd.CommandText = sql
cmd.CommandType = 1
If IsArray(params) Then
For i = LBound(params) To UBound(params)
Set param = cmd.CreateParameter(“@p” & i, 200, 1, 255, params(i))
cmd.Parameters.Append param
Next
End If
On Error Resume Next
Set Execute = cmd.Execute
If Err.Number <> 0 Then
Set Execute = Nothing
Err.Clear
End If
End Function
Public Function GetRecordSet(sql, params)
Set GetRecordSet = Execute(sql, params)
End Function
Private Function GetConnectionString()
‘ 可以从外部配置文件读取
GetConnectionString = “Provider=SQLOLEDB;Data Source=.;Initial Catalog=SalesDB;Integrated Security=SSPI;”
End Function
End Class
%>
7.2.2 工具函数模块（includes/utils.asp）
<%
' HTML转义
Function HTMLEncode(text)
If IsNull(text) Or IsEmpty(text) Then
HTMLEncode = ""
Else
HTMLEncode = Server.HTMLEncode(CStr(text))
End If
End Function
' SQL参数转义（备用方案，推荐使用参数化查询）
Function SQLEncode(text)
If IsNull(text) Then
SQLEncode = ""
Else
SQLEncode = Replace(CStr(text), "'", "''")
End If
End Function
' 生成分页链接
Function GeneratePagination(currentPage, totalPages, urlPattern)
Dim html, i
html = "
”
If currentPage > 1 Then
html = html & “上一页”
End If
Dim startPage, endPage
startPage = currentPage – 2
If startPage < 1 Then startPage = 1
endPage = currentPage + 2
If endPage > totalPages Then endPage = totalPages
For i = startPage To endPage
If i = currentPage Then
html = html & “” & i & “”
Else
html = html & “” & i & “”
End If
Next
If currentPage < totalPages Then
html = html & "下一页”
End If
‘ 输出表头
Dim i
For i = 0 To rs.Fields.Count – 1
Response.Write rs.Fields(i).Name
If i < rs.Fields.Count - 1 Then Response.Write ","
Next
Response.Write vbCrLf
' 输出数据
Do While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
Dim value
value = rs.Fields(i).Value
If IsNull(value) Then value = ""
' 处理包含逗号的字段
If InStr(value, ",") > 0 Then value = “””” & value & “”””
Response.Write value
If i < rs.Fields.Count - 1 Then Response.Write ","
Next
Response.Write vbCrLf
rs.MoveNext
Loop
End Function
%>
7.3 完整报表页面示例
<%@ Language=VBScript Codepage=65001 %>
<% Option Explicit %>
<%
' 页面参数
Dim startDate, endDate, productType, page
startDate = Request.QueryString("start")
endDate = Request.QueryString("end")
productType = Request.QueryString("type")
page = Request.QueryString("page")
' 默认值
If startDate = "" Then startDate = DateAdd("m", -1, Date)
If endDate = "" Then endDate = Date
If page = "" Then page = 1
page = CInt(page)
' 初始化数据库
Dim db, rs, sql, params
Set db = New DBManager
If Not db.Connect() Then
Response.Write "数据库连接失败"
Response.End
End If
' 构建参数化查询
sql = "SELECT ProductName, ProductType, SUM(Quantity) as Qty, SUM(Amount) as Amount " & _
"FROM SalesData WHERE SaleDate BETWEEN ? AND ? "
params = Array(startDate, endDate)
If productType <> “” Then
sql = sql & “AND ProductType = ? ”
ReDim Preserve params(UBound(params) + 1)
params(UBound(params)) = productType
End If
sql = sql & “GROUP BY ProductName, ProductType ORDER BY Amount DESC”
‘ 获取数据
Set rs = db.GetRecordSet(sql, params)
If rs Is Nothing Then
Response.Write “查询失败”
Else
‘ 输出HTML
%>
<%
End If
' 清理资源
rs.Close
db.Disconnect
Set rs = Nothing
Set db = Nothing
%>
八、常见兼容性问题深度解决方案
8.1 IIS版本兼容性
8.1.1 IIS7+ 配置问题
在IIS7及以上版本中，需要在
web.config
中配置处理程序映射：