Can Vba in Ms Access Using Parameter to Prevent SQL Injection

Can VBA in Ms Access using parameter to prevent sql injection?

This INSERT statement declares a Date/Time PARAMETER using a text box "txtStartDate" on an open form named "frmDatePicker", and inserts that value into MyTable.

PARAMETERS [Forms]![frmDatePicker]![txtStartDate] DateTime;
INSERT INTO MyTable ( date_field )
VALUES ([Forms]![frmDatePicker]![txtStartDate]);

Avoiding SQL Injection in MS Access

Using a prepared statement with positional parameters eliminates the chance that someone may SQL inject you:

strSQL = "INSERT INTO tblCustomer (CustName, Phone, Address) " &
    "VALUES ([str_name], [str_phone], [str_address]);"

Set qdf = db.CreateQueryDef(vbNullString, strSql)
With qdf
    .Parameters("str_name").Value = strName
    .Parameters("str_phone").Value = strPhone
    .Parameters("str_address").Value = strAddress
    .Execute dbFailOnError
End With

How do I use parameters in VBA in the different contexts in Microsoft Access?

There are many ways to use parameters in queries. I will try to provide examples for most of them, and where they are applicable.

First, we'll discuss the solutions unique to Access, such as forms, reports and domain aggregates. Then, we'll talk about DAO and ADO.

---

Using values from forms and reports as parameters

In Access, you can directly use the current value of controls on forms and reports in your SQL code. This limits the need for parameters.

You can refer to controls in the following way:

Forms!MyForm!MyTextbox for a simple control on a form

Forms!MyForm!MySubform.Form!MyTextbox for a control on a subform

Reports!MyReport!MyTextbox for a control on a report

Sample implementation:

DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Forms!MyForm!MyTextbox" 'Inserts a single value
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = Forms!MyForm!MyTextbox" 'Inserts from a different table

This is available for the following uses:

When using DoCmd.RunSQL, normal queries (in the GUI), form and report record sources, form and report filters, domain aggregates, DoCmd.OpenForm and DoCmd.OpenReport

This is not available for the following uses:

When executing queries using DAO or ADODB (e.g. opening recordsets, CurrentDb.Execute)

---

Using TempVars as parameters

TempVars in Access are globally available variables, that can be set in VBA or using macro's. They can be reused for multiple queries.

Sample implementation:

TempVars!MyTempVar = Me.MyTextbox.Value 'Note: .Value is required
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = TempVars!MyTempVar"
TempVars.Remove "MyTempVar" 'Unset TempVar when you're done using it

Availability for TempVars is identical to that of values from forms and reports: not available for ADO and DAO, available for other uses.

I recommend TempVars for using parameters when opening forms or reports over referring to control names, since if the object opening it closes, the TempVars stay available. I recommend using unique TempVar names for every form or report, to avoid weirdness when refreshing forms or reports.

---

Using custom functions (UDFs) as parameters

Much like TempVars, you can use a custom function and static variables to store and retrieve values.

Sample implementation:

Option Compare Database
Option Explicit

Private ThisDate As Date

Public Function GetThisDate() As Date
    If ThisDate = #12:00:00 AM# Then
        ' Set default value.
        ThisDate = Date
    End If 
    GetThisDate = ThisDate
End Function

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

and then:

SetThisDate SomeDateValue ' Will store SomeDateValue in ThisDate.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeDateField] = GetThisDate()"

Also, a single function with an optional parameter may be created for both setting and getting the value of a private static variable:

Public Function ThisValue(Optional ByVal Value As Variant) As Variant
    Static CurrentValue As Variant
    ' Define default return value.
    Const DefaultValue  As Variant = Null

    If Not IsMissing(Value) Then
        ' Set value.
        CurrentValue = Value
    ElseIf IsEmpty(CurrentValue) Then
        ' Set default value
        CurrentValue = DefaultValue
    End If
    ' Return value.
    ThisValue = CurrentValue
End Function

To set a value:

ThisValue "Some text value"

To get the value:

CurrentValue = ThisValue

In a query:

ThisValue "SomeText"  ' Set value to filter on.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeField] = ThisValue()"

---

Using DoCmd.SetParameter

The uses of DoCmd.SetParameter are rather limited, so I'll be brief. It allows you to set a parameter for use in DoCmd.OpenForm, DoCmd.OpenReport and some other DoCmd statements, but it doesn't work with DoCmd.RunSQL, filters, DAO and ADO.

Sample implementation

DoCmd.SetParameter "MyParameter", Me.MyTextbox
DoCmd.OpenForm "MyForm",,, "ID = MyParameter"

---

Using DAO

In DAO, we can use the DAO.QueryDef object to create a query, set parameters, and then either open up a recordset or execute the query. You first set the queries' SQL, then use the QueryDef.Parameters collection to set the parameters.

In my example, I'm going to use implicit parameter types. If you want to make them explicit, add a PARAMETERS declaration to your query.

Sample implementation

'Execute query, unnamed parameters
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

'Open recordset, named parameters
Dim rs As DAO.Recordset
With CurrentDb.CreateQueryDef("", "SELECT Field1 FROM Table2 WHERE Field1 = FirstParameter And Field2 = SecondParameter")
    .Parameters!FirstParameter = Me.Field1 'Bang notation
    .Parameters("SecondParameter").Value = Me.Field2 'More explicit notation
    Set rs = .OpenRecordset
End With

While this is only available in DAO, you can set many things to DAO recordsets to make them use parameters, such as form recordsets, list box recordsets and combo box recordsets. However, since Access uses the text, and not the recordset, when sorting and filtering, those things may prove problematic if you do.

---

Using ADO

You can use parameters in ADO by using the ADODB.Command object. Use Command.CreateParameter to create parameters, and then append them to the Command.Parameters collection.

You can use the .Parameters collection in ADO to explicitly declare parameters, or pass a parameter array to the Command.Execute method to implicitly pass parameters.

ADO does not support named parameters. While you can pass a name, it's not processed.

Sample implementation:

'Execute query, unnamed parameters
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection 'Use a connection to the current database
    .CommandText = "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE Field1 = ? And Field2 = ?"
    .Parameters.Append .CreateParameter(, adVarWChar, adParamInput, Len(Me.Field1), Me.Field1) 'adVarWChar for text boxes that may contain unicode
    .Parameters.Append .CreateParameter(, adInteger, adParamInput, 8, Me.Field2) 'adInteger for whole numbers (long or integer)
    .Execute
End With

'Open recordset, implicit parameters
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection 'Use a connection to the current database
    .CommandText = "SELECT Field1 FROM Table2 WHERE Field1 = @FirstParameter And Field2 = @SecondParameter"
     Set rs = .Execute(,Array(Me.Field1, Me.Field2))
End With

The same limitations as opening DAO recordsets apply. While this way is limited to executing queries and opening recordsets, you can use those recordsets elsewhere in your application.

Microsoft Access SQL Injection

If you're doing string concatenation, you're vulnerable.

SQL injection and Access macros (not VBA)

Certainly, though because macros offer very limited capabilities, such cases are mostly academic.

Let's say I'm trying to implement a login system that disallows access to the database, without relying on VBA. I'm creating a form, adding two text boxes for username and password, and I'm making the form modal and removing close buttons to disallow the user to dismiss the form, making my macro the only way to gain access to the database (way insecure in many ways).

The macro:

If DCount("*","tblUsers","UserName = '" & Forms![LoginForm]!Username & "' AND [Password] = '" & Forms![LoginForm]!Password & "'") <> 0
    CloseWindow
        Object type = Form
        Object Name = LoginForm
        Save = Prompt

My injection payload is a classic: enter ' OR 1=1 OR 'A' = ' as a username, blank password, you're in.

However, this example is pretty ridiculous. The string delimiters and concatenation operators be omitted to use form-based parameters, and injection is suddenly impossible.

The RunSQL macro action, however, doesn't support dynamic SQL at all, so isn't vulnerable to SQL injection afaik. You can use form parameters in it, though.

As Zev Spitz pointed out himself in the comments, SetProperty can't modify row or record source so can't be used for SQL injection attacks.

How to rewrite VBA SQL statement to prevent SQL Injection?

You remove the inline dynamic SQL statements where you just inject variables into the SQL Statement.

You create Stored Procedures for each database actions and your VB simply passes variables into the Input Parameters of those procedures.

So using the Delete as an example, you create a stored procedure called, "DeleteShipment"

DROP PROC IF EXISTS dbo.sproc_DeleteShipment
GO
CREATE PROC dbo.sproc_DeleteShipment(@ShipmentID INT)
AS
BEGIN
DELETE dbo.tbl1Shipments WHERE ShipmentID = ShipmentID 
END

then your VB DB calls will change to

Dim conn as New SqlConnection(YourConnectionString)
Dim cmd as SqlCommand = conn.createcommand
conn.open()
cmd.CommandType = CommandType.StoreProcedure
cmd.Parameters.Add(New SqlParameter("@ShipmentID ", intShipmentID)
cmd.CommandText = sproc_DeleteShipment
cmd.ExecuteNonQuery() 
conn.close()

In theory, your code is open to SQL Injection but just from that example, all your variables are INTs which makes it harder. However if your shipment ID was a string, and a hacker managed to get the string, "1 OR 1=1" into the shipment id, the whole table would get deleted.

If that same string found it's way into a input parameter for a SPROC, that can't happen.

Is SQL injection possible when using MS Access linked tables to a SQL server back-end

In the classic sense of adding code, commands or additional SQL code that will run on the server the answer is no in regards to standard injection when using linked tables. This is because any T-SQL code on the client side is not passed though the ACE database engine and multiple commands (say separated by a ;) are not passed to the server side.
However there are some “possible” injections that can occur in an access application, and the SAME applies to linked tables. In other words the introduction of a SQL server into this mix, or using the built in database engine is not subject to classical injection.

So context here is VERY important. As a result, the real question are access applications subject to SQL injection? If one finds a proof of working example that works with the built in ACE engine, then that same injection will exist if you using linked tables to SQL server.

There is no working proof of concept of a SQL injection that will run server side T-SQL code that is possible, or I am aware of being possible.

However if VBA code is using pass-through queries, then the answer is most certainly yes because the Access client would be passing raw t-sql to the server side and not touching such t-sql commands and t-sql statements. So a form that prompts for some parameter value and then is concatenated in VBA to some raw t-sql statement and then sent to SQL server would MOST certainly be subject to SQL injection.

So linked tables, bound forms to those linked tables are not subject to server side injections.

There are some possible cases where an expression pulled from a forms text box used as a parameter in Access sql could modify the intent of a developer.

Eg:

strSQL = "select * from tblCustomers where InvoiceID = " me.TextBoxPrompt

In the above it is expected that the user types in 234324 (a invoice number). However, the user could type in a known VBA function. Depending on the sandbox mode settings you have, then an expression could be entered that is not expected. This could in fact thus cause some VBA to run. (the user would have to be aware of the public VBA function to run).

So in the above, it would not really be sql injection, but VBA injection (a bit of semantics here, but the distinction is important).

So some limited forms of sql injection can occur client side, but such expression will NEVER make it to the server.

At the end of the day this means that by adopting SQL server you do NOT increase in any way the possibility of SQL injection over that of the standard Access product (of which I pointed out some limited types of injection can occur).

The only exception to this rule is if the Access application makes use of pass-through queries that are sent raw to SQL server and user parameters are taken in raw format from the form and passed directly to SQL server. Because I often use pass-through queries that are the result of strings and user prompts then this is an issue. And I often use strings and NOT true ADO parameters (too much work). To prevent injection then I have a small function that removes the “;”, and also () characters from that given string. This small routine thus prevents additional commands being sent to SQL server separated by a “;” for example.

Non-web SQL Injection

If you're building SQL in your macro, it's vulnerable to SQL injection. Even if you trust the people who will be using the thing, you should at least watch for the basics, like people trying to put single-quote and semicolon characters into database fields. this isn't so much a security issue in your case as just data validation.

How to use a SQL with Parameters as a Combobox Row Source in MS Access/VBA

Unfortunately, you can't directly use parameters in a rowsource.

You can, however, use parameters in a querydef, use that to create a recordset, and use that recordset to fill a combobox, e.g.:

With CurrentDb.CreateQueryDef("", strSql)
    .Parameters!CompanyID = compID
    Set myCombobox.Recordset = .OpenRecordset
End With

You can also use either TempVars or form-based parameters in row sources, e.g.

TempVars!CompID = compID
strSql = "SELECT NAME, BUSINESS_NUMBER, FILE_ID FROM COMPANY WHERE COMPANY_ID = TempVars!CompID ORDER BY CREATION_DATE;"

See How do I use parameters in VBA in the different contexts in Microsoft Access? for more info.

How to put a variable into SQL code in access

Create a query where you pass the Machine as parameter.

For example:

PARAMETERS [prmMachine] Text (255);
INSERT INTO Machines ([Machine])
SELECT [prmMachine] AS _Machine;

Then, call the query in VBA:

With CurrentDb().QueryDefs("YourQueryName")
    .Parameters("[prmMachine]").Value = Machine_keuze 
    .Execute dbFailOnError
End With

---

Related Topics