Implementing Parameterized Queries in DAO for Enhanced Security in Access- SQL Server Application (1 Viewer)

[Navatante]

I am developing a front-end application for data entry in Access. The database is hosted on SQL Server, and I only use Access to interact with it. All queries are performed through DAO via ODBC connection. However, I have realized that my code is at risk of SQL injection when concatenating values even with the use of @DECLARE. I would like assistance on how to create a parameterized query to avoid SQL injections.

I have read that an alternative is to use ADO, but I am comfortable working with DAO. I would like to know if I can pass parameterized queries with DAO and let SQL Server handle the rest.

I am sharing an example of my code:

' ---------------------------------------------------------------------------------
'   FUNCTION InsertUpdateDelete (Passes an strSQL, connects to SQL Server, and executes the query directly on the SQL Server engine)
' ---------------------------------------------------------------------------------

Public Function InsertUpdateDelete(strSQL As String, ReturnsRecords As Boolean, PTQSubform As Variant, WinLabel As Variant, Optional HorasBox As Variant) As Boolean
    On Error Resume Next
    
    Dim qdf As QueryDef
    
    ' Create a QueryDef
    Set qdf = CurrentDb.CreateQueryDef("")
    
    ' Connection to SQL Server
    qdf.Connect = "ODBC;DSN=SQL Server;Trusted_Connection=Yes;DATABASE=x;"
    qdf.SQL = strSQL
    qdf.ReturnsRecords = ReturnsRecords ' Use the provided value

    ' Execute the query
    qdf.Execute dbFailOnError
    
    ' Close the query and release resources
    qdf.Close
    Set qdf = Nothing
End Function

Private Sub event_save_bttn_Click()

    If Not ValidateControls(Me.Cb_event_type, Me.cb_event_name) Then
        Exit Sub
    End If
    
    Dim confirmation As VbMsgBoxResult
    confirmation = MsgBox("Confirm that the data is correct. Once saved, you won't be able to delete it.", vbQuestion + vbYesNo, "Addition Confirmation")

    If Not confirmation = vbYes Then
        Exit Sub
    End If
    
    Dim strSQL As String
    strSQL = "DECLARE @event_type NVARCHAR(40), @event_name NVARCHAR(40);" & _
             "SELECT @event_type =  N'" & Me.Cb_event_type.Value & "'; " & _
             "SELECT @event_name = N'" & Me.cb_event_name.Value & "'; " & _
             "INSERT INTO operations.dim_event (event_type, event_name) " & _
             "VALUES (@event_type, @event_name);"
    
    InsertUpdateDelete strSQL, False, Forms!Update_1!SUB_dim_event, event_win
End Sub

In this example, I am using DAO to execute the query directly on the SQL Server engine. If you have concerns about SQL injection, it is advisable to switch to parameterized queries to enhance security.

If you believe that the most effective solution would be to use ADO, I would like you to tell me how and why. Thank you.

 

[Josef P.]

If you ensure that a string remains a string, SQL injection via the input of values by the user is not possible.

strSQL = "INSERT INTO operations.dim_event (event_type, event_name) " & _
             "VALUES (" & TextToSql(Me.Cb_event_type.Value) & ", " & TextToSql(Me.cb_event_name.Value) & ");"

Public function TextToSql(Byval TextToConvert as String) as String
     TextToSql = "'" & Replace(TextToConvert, "'", "''") & "'"
end function

You can also take a look at ADODB.Command. This allows you to implement an SQL statement with parameters directly on the SQL server.
If only data records are to be added, you could also simply open a DAO recordset and assign the values to the data fields.

 

[Navatante]

Thank you for the quick response. I will look into the ADODB solution you mentioned. By any chance, do you know if it is compatible with Access Runtime 2013?

 

[Josef P.]

See:

Command Object Parameters - ActiveX Data Objects (ADO)

Command Object Parameters

learn.microsoft.com

(will work with Access Runtime 2013)

 

[Navatante]

I have also considered using stored procedures in SQL Server and executing them from a QueryDef, similar to my initial query. The difference is that instead of using a dynamic query with @DECLARE, I am now using a stored procedure.

I am unsure which option to choose—stored procedures, ADODB.Command, or the TextToSql function you provided. On one hand, creating stored procedures for each query might be a bit tedious. On the other hand, I am not certain whether the TextToSql function will work when inserting data of type int or double. Lastly, I would need to study how to use ADODB.Command and integrate it into my code.

Any advice is apreciated, thank you again.

 

[cheekybuddha]

I am not certain whether the TextToSql function will work when inserting data of type int or double.

You wouldn't use it when passing inputs of number datatype - only for strings and, since you are building SQL for SQLServer, dates too (though you will need to make sure they are formatted yyyy-mm-dd hh:nn:ss.

If you are worried about a user stuffing a numeric input textbox with something like 9;DROP SCHEMA operations; then validate the input before building the SQL (eg with CLng() or test with IsNumeric() )

 

[Galaxiom]

What do you perceive you are actually gaining over simply using linked tables? ODBC passes queries through where it can so it isn't likely to make significant performance differences under most circumstances.

You are certainly making a whole lot of extra work. Part of the whole reason for using Access is its rapid development capability by avoid having to write a ton of code.

 

[CJ_London]

I will look into the ADODB solution you mentioned.

ADO is fine for things like list and combo box rowsources, general listing of data etc, BUT for forms, things like filter and sort shortcut menus will not work with an ADO recordset (although you can write your own) and you cannot use ADO recordsets for reports.

One of the potential benefits of ADO is (if required) once populated you can disconnect the recordset and close the connection. User can then make changes/additions etc without affecting the underlying data in the BE. You can then reconnect and apply all changes, which will be a lot of work since you need to see if the data has since changed in the BE - if not apply change, if it has changed you need to make a decision as to which is the valid data. Basically a form of replication. Useful for what-if scenarios where you just need a snapshot of the data to manipulate or for list and combo box recordsets but otherwise a lot of work.

I see ADO as a tool in the toolbox, not the toolbox itself.

 

[Pat Hartman]

@Navatante Welcome to AWF

If you are not using the best features of Access (bound forms and reports) and you are thinking about writing stored procedures instead and you think ADO is better than DAO, why would you even be using Access as the FE? You have gained NOTHING and instead have made a lot of extra work for yourself. Pick some other platform that works the way you want to work and doesn't come with the baggage that Access comes with.

My first Access application back in the early 90's used DB2 on an IBM mainframe as the BE database and at least 80% of all the rest of my applications since then used SQL Server, DB2, Oracle, Sybase, among others. And they ALL used bound forms. A few needed Views to help make some joins more efficient and a couple of reports even used stored procedures, their queries were so complex. But updates, using anything except bound forms??? Never. The key is understanding how Access works and in using forms/reports bound to queries that select the minimum rows/columns needed to provide the necessary functionality. NEVER, EVER bind your forms/reports directly to tables or even to queries with no selection criteria.

 

[Navatante]

I understand that Access has incredible functionalities that I'm not currently utilizing in this workflow. The primary reason I use Access as the front end is due to existing limitations within my organization when it comes to installing certain software. Access is an accepted and approved software by the organization, making it a current priority. The ease it provides in connecting to SQL Server via ODBC and generating an attractive and user-friendly GUI, with extensive customization through VBA code (which I enjoy), is another reason for choosing this program.

The main goal of the application I'm developing is the straightforward and controlled insertion of data into SQL Server. Visualization of queries and charts will be supported by another software. The generation of reports is something I still need to figure out.

After some tests this morning, I've been making connections and queries to stored procedures in SQL Server using both ADO and DAO. I was surprised by the difference in performance; ADO was much slower in my tests. The same query took 3 seconds using ADO, while with DAO, it was instantaneous.

In the end, I've decided to stick with maintaining my connections through DAO and executing stored procedures. It might not be the best way to work with Access, but it seems to be working for my application.

I understand that there might be Access experts here, and this workflow might seem unconventional to them. However, from my critical perspective, it is proving to be entirely valid and efficient for my application.

 

[Galaxiom]

However, from my critical perspective, it is proving to be entirely valid and efficient for my application.

Of course it is valid and efficient in its operation.

What I am wondering is why you bother making all this extra work and what you imagine you are gaining over using ODBC linked tables and bound forms.

Many developers with a fetish for using SQL commands don't seem to realise that they are losing the functionality where Access is transparently managing any attempts at concurrent edits by multiple users.

 

[Pat Hartman]

Have you tried building the application using bound forms and cutting out all the other stuff? The code you need in the Access FE is the code that goes in the form's BeforeUpdate event that validates the data to prevent invalid data from being saved. Looks like you are doing everything but preventing bad data from being saved.

 

[mdlueck]

I am developing a front-end application for data entry in Access. The database is hosted on SQL Server, and I only use Access to interact with it. All queries are performed through DAO via ODBC connection. However, I have realized that my code is at risk of SQL injection when concatenating values even with the use of @DECLARE. I would like assistance on how to create a parameterized query to avoid SQL injections.

I have read that an alternative is to use ADO, but I am comfortable working with DAO. I would like to know if I can pass parameterized queries with DAO and let SQL Server handle the rest.

I am sharing an example of my code:

Bravo Navatante! Excellent work!

For DAO queries and settings variables, see this thread:

"Replace pass-through DAO.QueryDef with DAO execution of Stored Procedure Q's"

Replace pass-through DAO.QueryDef with DAO execution of Stored Procedure Q's

I would like to test converting a pass-through query that uses DAO.QueryDef objects with code to execute a stored procedure which will perform the same SELECT statement. Reason being, I am pushing the upper limit of Access VBA string concatenation building the SQL string. I came across this...

www.access-programmers.co.uk

I arrived at doing as much as possible with ADO objects, and only used DAO objects for things I absolutely had to. To have query results directly end up in Access FE Temp Tables, then yes DAO objects are required.

When possible I implement all BE DB SQL as Stored Procedures. Then I just pass the values as parameters to that Stored Procedure. When not possible to use a Stored Procedure, then I still use SQL statements which make use of Parameters.... some systems call such "Prepared SQL".

Concatenating values in-line into bare SQL is where the risk of SQL Injection Attacks become possible. So by avoiding that coding style completely, one advances to the next base for hardening the application against abuse.

In this post I overview my strategy for using Access in a Client/Server environment:

"Client/Server Architecture"

Access Front-Sql Back--how set user rights?

Okay, so I have my database now in Access 2010, and my tables in SQL. But, how do I get user-level security? (for most this will be read-only, for about 2 this will be read-write). I finally found this page that talks about creating a LOGIN...

www.access-programmers.co.uk

BTW: You found a very good forum to learn from! Welcome!

I am thankful,