Home >Database >Mysql Tutorial >How to Securely Use Parameters in VBA for Microsoft Access Queries?
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.
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 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>
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>
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>
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>
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!