vb.net oledb使用参数化查询来防止 SQL 注入攻击并提高性能

发布于：2025-08-29 ⋅ 阅读:(17) ⋅ 点赞:(0)

在 VB.NET 中使用 OLEDB 执行带参数的 Access 查询有多种方法，以下是详细的实现方式：

一、使用 OleDbCommand 参数化查询（推荐）

方法1：使用命名参数

Imports System.Data.OleDb

Public Sub ExecuteParameterQuery()
    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\你的数据库.accdb;"
    Dim userId As Integer = 123 ' 你的参数值
    
    Using conn As New OleDbConnection(connectionString)
        conn.Open()
        
        Dim sql As String = "SELECT * FROM 用户表 WHERE 用户ID = ?"
        
        Using cmd As New OleDbCommand(sql, conn)
            ' 添加参数 - Access 使用问号占位符，按顺序添加参数
            cmd.Parameters.AddWithValue("?", userId)
            
            Using reader As OleDbDataReader = cmd.ExecuteReader()
                While reader.Read()
                    Console.WriteLine(reader("用户名").ToString())
                End While
            End Using
        End Using
    End Using
End Sub

方法2：明确指定参数类型

Public Sub ExecuteParameterQueryWithType()
    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\你的数据库.accdb;"
    
    Using conn As New OleDbConnection(connectionString)
        conn.Open()
        
        Dim sql As String = "SELECT * FROM 订单表 WHERE 订单金额 > ? AND 用户ID = ?"
        
        Using cmd As New OleDbCommand(sql, conn)
            ' 明确指定参数类型和值
            cmd.Parameters.Add("?", OleDbType.Double).Value = 100.0
            cmd.Parameters.Add("?", OleDbType.Integer).Value = 123
            
            Using reader As OleDbDataReader = cmd.ExecuteReader()
                While reader.Read()
                    Console.WriteLine($"订单号: {reader("订单号")}, 金额: {reader("订单金额")}")
                End While
            End Using
        End Using
    End Using
End Sub

二、执行存储查询（QueryDef）

如果 Access 中已经定义了参数查询：

Public Sub ExecuteStoredParameterQuery()
    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\你的数据库.accdb;"
    
    Using conn As New OleDbConnection(connectionString)
        conn.Open()
        
        ' 执行已存储的参数查询
        Using cmd As New OleDbCommand("你的查询名称", conn)
            cmd.CommandType = CommandType.StoredProcedure
            
            ' 添加参数 - 参数名称必须与Access查询中的参数名完全匹配
            cmd.Parameters.AddWithValue("查用户ID", 123)
            
            Using reader As OleDbDataReader = cmd.ExecuteReader()
                While reader.Read()
                    Console.WriteLine(reader("用户名").ToString())
                End While
            End Using
        End Using
    End Using
End Sub

三、使用 DataAdapter 填充 DataTable

Public Function GetDataTableWithParameters(userId As Integer) As DataTable
    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\你的数据库.accdb;"
    Dim dataTable As New DataTable()
    
    Using conn As New OleDbConnection(connectionString)
        Dim sql As String = "SELECT * FROM 用户表 WHERE 用户ID = ? AND 状态 = ?"
        
        Using cmd As New OleDbCommand(sql, conn)
            cmd.Parameters.AddWithValue("?", userId)
            cmd.Parameters.AddWithValue("?", "激活")
            
            Using adapter As New OleDbDataAdapter(cmd)
                adapter.Fill(dataTable)
            End Using
        End Using
    End Using
    
    Return dataTable
End Function

四、执行非查询操作（INSERT, UPDATE, DELETE）

Public Function UpdateUser(userId As Integer, userName As String) As Integer
    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\你的数据库.accdb;"
    Dim rowsAffected As Integer = 0
    
    Using conn As New OleDbConnection(connectionString)
        conn.Open()
        
        Dim sql As String = "UPDATE 用户表 SET 用户名 = ? WHERE 用户ID = ?"
        
        Using cmd As New OleDbCommand(sql, conn)
            cmd.Parameters.AddWithValue("?", userName)
            cmd.Parameters.AddWithValue("?", userId)
            
            rowsAffected = cmd.ExecuteNonQuery()
        End Using
    End Using
    
    Return rowsAffected
End Function

五、处理多参数复杂查询

Public Sub ComplexParameterQuery()
    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\你的数据库.accdb;"
    
    Using conn As New OleDbConnection(connectionString)
        conn.Open()
        
        Dim sql As String = "PARAMETERS 开始日期 DateTime, 结束日期 DateTime, 最小金额 Double; " & _
                           "SELECT * FROM 订单表 " & _
                           "WHERE 订单日期 BETWEEN ? AND ? " & _
                           "AND 订单金额 >= ? " & _
                           "ORDER BY 订单日期"
        
        Using cmd As New OleDbCommand(sql, conn)
            ' 按顺序添加参数
            cmd.Parameters.AddWithValue("?", New DateTime(2023, 1, 1))
            cmd.Parameters.AddWithValue("?", New DateTime(2023, 12, 31))
            cmd.Parameters.AddWithValue("?", 500.0)
            
            Using reader As OleDbDataReader = cmd.ExecuteReader()
                While reader.Read()
                    Console.WriteLine($"订单: {reader("订单号")}, 日期: {reader("订单日期")}, 金额: {reader("订单金额")}")
                End While
            End Using
        End Using
    End Using
End Sub

六、完整示例类

Imports System.Data.OleDb

Public Class AccessDatabaseHelper
    Private connectionString As String
    
    Public Sub New(dbPath As String)
        connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dbPath};"
    End Sub
    
    ' 执行带参数的查询并返回DataTable
    Public Function ExecuteParameterQuery(sql As String, ParamArray parameters() As Object) As DataTable
        Dim dt As New DataTable()
        
        Using conn As New OleDbConnection(connectionString)
            Using cmd As New OleDbCommand(sql, conn)
                ' 添加参数
                For Each param In parameters
                    cmd.Parameters.AddWithValue("?", param)
                Next
                
                Using adapter As New OleDbDataAdapter(cmd)
                    adapter.Fill(dt)
                End Using
            End Using
        End Using
        
        Return dt
    End Function
    
    ' 执行存储的参数查询
    Public Function ExecuteStoredQuery(queryName As String, ParamArray parameters() As Object) As DataTable
        Dim dt As New DataTable()
        
        Using conn As New OleDbConnection(connectionString)
            Using cmd As New OleDbCommand(queryName, conn)
                cmd.CommandType = CommandType.StoredProcedure
                
                ' 添加参数
                For Each param In parameters
                    cmd.Parameters.AddWithValue("?", param)
                Next
                
                Using adapter As New OleDbDataAdapter(cmd)
                    adapter.Fill(dt)
                End Using
            End Using
        End Using
        
        Return dt
    End Function
    
    ' 执行非查询操作
    Public Function ExecuteNonQuery(sql As String, ParamArray parameters() As Object) As Integer
        Dim rowsAffected As Integer = 0
        
        Using conn As New OleDbConnection(connectionString)
            conn.Open()
            
            Using cmd As New OleDbCommand(sql, conn)
                For Each param In parameters
                    cmd.Parameters.AddWithValue("?", param)
                Next
                
                rowsAffected = cmd.ExecuteNonQuery()
            End Using
        End Using
        
        Return rowsAffected
    End Function
End Class

七、使用示例

' 使用示例
Dim dbHelper As New AccessDatabaseHelper("C:\你的数据库.accdb")

' 执行参数查询
Dim dt As DataTable = dbHelper.ExecuteParameterQuery(
    "SELECT * FROM 用户表 WHERE 用户ID = ? AND 状态 = ?",
    123, "激活")

' 执行存储查询
Dim dt2 As DataTable = dbHelper.ExecuteStoredQuery("你的查询名称", 123)

' 执行更新操作
Dim affectedRows As Integer = dbHelper.ExecuteNonQuery(
    "UPDATE 用户表 SET 最后登录时间 = ? WHERE 用户ID = ?",
    DateTime.Now, 123)

注意事项

  1. 参数顺序：Access OLEDB 使用问号 ? 作为占位符，参数必须按顺序添加

  2. 参数类型：使用 AddWithValue 时，.NET 会自动推断类型，但明确指定类型更安全

  3. 连接管理：始终使用 Using 语句确保资源正确释放

  4. 错误处理：添加适当的 Try-Catch 块处理数据库异常

  5. Provider版本：根据你的 Access 版本选择合适的 Provider（ACE.OLEDB.12.0 或 Jet.OLEDB.4.0）

推荐使用参数化查询来防止 SQL 注入攻击并提高性能

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布