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

Navatante

New member
Local time
Today, 20:42
Joined
Jan 8, 2024
Messages
4
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:

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
Code:
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.

Well-known member
Local time
Today, 20:42
Joined
Feb 2, 2023
Messages
847
If you ensure that a string remains a string, SQL injection via the input of values by the user is not possible.
Code:
strSQL = "INSERT INTO operations.dim_event (event_type, event_name) " & _
             "VALUES (" & TextToSql(Me.Cb_event_type.Value) & ", " & TextToSql(Me.cb_event_name.Value) & ");"

Code:
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.
 
Last edited:

Navatante

New member
Local time
Today, 20:42
Joined
Jan 8, 2024
Messages
4
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?
 

Navatante

New member
Local time
Today, 20:42
Joined
Jan 8, 2024
Messages
4
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

AWF VIP
Local time
Today, 19:42
Joined
Jul 21, 2014
Messages
2,329
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

Super Moderator
Staff member
Local time
Tomorrow, 04:42
Joined
Jan 20, 2009
Messages
12,859
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

Super Moderator
Staff member
Local time
Today, 19:42
Joined
Feb 19, 2013
Messages
16,681
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

Super Moderator
Staff member
Local time
Today, 14:42
Joined
Feb 19, 2002
Messages
43,512
@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

New member
Local time
Today, 20:42
Joined
Jan 8, 2024
Messages
4
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

Super Moderator
Staff member
Local time
Tomorrow, 04:42
Joined
Jan 20, 2009
Messages
12,859
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

Super Moderator
Staff member
Local time
Today, 14:42
Joined
Feb 19, 2002
Messages
43,512
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

Sr. Application Developer
Local time
Today, 14:42
Joined
Jun 23, 2011
Messages
2,631
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"

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"

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

I am thankful,
 

Users who are viewing this thread

Top Bottom