How to Securely Use Parameters in VBA for Microsoft Access Queries?

Using VBA parameters in different contexts in Microsoft Access

Many Microsoft Access developers rely on vulnerable code due to a lack of understanding of how to use parameters correctly. This short guide will seek to shed some light on this topic.

Forms and Reports

Access provides a unique way to reference values ​​from forms and reports directly in SQL code, in most cases without parameters:

<code>DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT " & _
    "Forms!MyForm!MyTextbox" '插入单个值</code>

TempVars

TempVars provides a way to store and reuse values ​​from multiple queries:

<code>TempVars!MyTempVar = Me.MyTextbox.Value
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT " & _
    "Field1 FROM Table2 WHERE ID = TempVars!MyTempVar"</code>

Custom Function (UDF)

UDF can be used to store and retrieve values:

<code>Option Compare Database
Option Explicit

Private ThisDate As Date

Public Function GetThisDate() As Date
    If ThisDate = #12:00:00 AM# Then
        ' 设置默认值。
        ThisDate = Date
    End If
    GetThisDate = ThisDate
End Function

Public Function SetThisDate(ByVal NewDate As Date) As Date
    ThisDate = NewDate
    SetThisDate = ThisDate
End Function

'设置值：
SetThisDate SomeDate</code>

Use DoCmd.SetParameter

DoCmd.SetParameter allows setting parameters for DoCmd.OpenForm, DoCmd.OpenReport and some other DoCmd statements:

<code>DoCmd.SetParameter "MyParameter", Me.MyTextbox
DoCmd.OpenForm "MyForm",,, "ID = MyParameter"</code>

DAO

In DAO, we can use the DAO.QueryDef object to create queries and set parameters before opening the recordset or executing the query:

<code>'执行查询，未命名参数
With CurrentDb.CreateQueryDef("", "INSERT INTO Table1(Field1)" & _
    " SELECT Field1 FROM Table2 WHERE Field1 = ?p1 And " & _
    "Field2 = ?p2")
    .Parameters(0) = Me.Field1
    .Parameters(1) = Me.Field2
    .Execute
End With

'打开记录集，命名参数
Dim rs As DAO.Recordset
With CurrentDb.CreateQueryDef("", "SELECT Field1 FROM Table2" & _
    " WHERE Field1 = FirstParameter And Field2 = " & _
    "SecondParameter")
    .Parameters!FirstParameter = Me.Field1 '感叹号表示法
    .Parameters("SecondParameter").Value = Me.Field2 '更明确的表示法
    Set rs = .OpenRecordset
End With</code>

ADO

In ADO, we can use the ADODB.Command object to create parameters and attach them to the Command.Parameters collection:

<code>'执行查询，未命名参数
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection '使用与当前数据库的连接
    .CommandText = "INSERT INTO Table1(Field1) SELECT " & _
    "Field1 FROM Table2 WHERE Field1 = ? And Field2 = ?"
    .Parameters.Append .CreateParameter(, adVarWChar, adParamInput, Len(Me.Field1), Me.Field1) 'adVarWChar 用于可能包含 Unicode 的文本框
    .Parameters.Append .CreateParameter(, adInteger, adParamInput, 8, Me.Field2) 'adInteger 用于整数（长整数或整数）
    .Execute
End With

'打开记录集，隐式参数
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection '使用与当前数据库的连接
    .CommandText = "SELECT Field1 FROM Table2 WHERE " & _
    "Field1 = @FirstParameter And Field2 = @SecondParameter"
    Set rs = .Execute(,Array(Me.Field1, Me.Field2))
End With</code>

By following these methods, developers can eliminate SQL injection vulnerabilities and improve the security of Access applications.

The above is the detailed content of How to Securely Use Parameters in VBA for Microsoft Access Queries?. For more information, please follow other related articles on the PHP Chinese website!