Access Mail Merge: Security Implications

Question

I posted a question earlier concerning a mail merge operation where I am adding the parameter into the mail merge directly:

sqlstatement:="SELECT * FROM [Customer Data] 
WHERE [Customer Data].[Status]='Complete' 
AND [Customer Data].[CompletedBy] = '" & userID & "' 
AND [Customer Data].[Date Completed] Between #" & date1 & "# 
And #" & date2 & "#;"

Changing SQL select query to VBA

It was suggested in the comments that this is a bad idea; I know this should be avoided for database operations because it can be exploited to run code altering the database, but is this a bad idea for printing mail merges? Could this result in a real-world exploit? It seems that SQL only chooses what will populate onto the letter merge. What other options are available in order to populate a mail merge correctly?

Set objword = GetObject("S:\Share with Bob\Administration 2010 Repository\Mail Merge\AIB.doc")
'Make Word visible
objword.Application.Visible = True
'Set the mail merge data source to the database
objword.MailMerge.OpenDataSource _
        Name:="C:\Database.mdb", _
        LinkToSource:=True, _
        Connection:="[TABLE - TRANSACTION]", _
        sqlstatement:="SELECT * FROM [TABLE - TRANSACTION] ORDER BY [TABLE - TRANSACTION].[Transaction Number]"
'Execute the mail merge
objword.MailMerge.Destination = wdSendToNewDocument
objword.MailMerge.Execute
objword.Application.Options.PrintBackground = False
'Print a copy
objword.Application.ActiveDocument.PrintOut
'Close Word and do not save the changes
objword.Application.Quit SaveChanges:=wdDoNotSaveChanges
'Clear the objWord Object
Set objword = Nothing

lets say my userID is ';Drop [Customer Data];-- what would happen? — xQbert, Jun 04 '13 at 00:31
@xQBert: "What would happen?" In MS Access (more specifically, Jet/ACE)? Actually...not what you're thinking. You would receive a syntax error because the Jet/ACE db engine does not allow you to include multiple SQL statements in a single command. But it would not execute the `Drop [Customer Data];` statement. — mwolfe02, Jun 07 '13 at 20:12

score 0 · Answer 1 · answered Jun 06 '13 at 11:58

0

Access databases normally are not exposed to Internet or really malicious users. Therefore there not many tools were developed to protect from SQL Injection.

If you think your application is vulnarable, for this statement is can suggest an easy fix. it may be not bullet proof for all cases, but should suffice for you.

For non-character types you can cast the variable into its type. So casting a malicious string into integer will cause an error.

sqlstatement:="SELECT * FROM [Customer Data] WHERE [Customer Data].[Status]='Complete' AND [Customer Data].[CompletedBy] = '" & cast ( cast (userID as int) as varchar (50))& "' AND [Customer Data].[Date Completed] Between #" & cast ( cast ( date1 as date) as varchar (12)) & "# And #" & cast ( cast ( date2 as date) as varchar (12)) & "#;"

answered Jun 06 '13 at 11:58

Stoleg

8,972
1
21
28

Thanks for the answer but unfortunately, this still does not answer the question, ie, "If you think your application is vulnarable".. I'm looking to find out what the vulnerabilities are in doing this, and if they exist, what can happen, not whether I think it is vulnerable or not, as its not a typical SQL event, I dont believe. – Jason Bayldon Jun 06 '13 at 12:18
@JasonBayldon, Oh, sorry for that. In such case you need to provide much more information about your appliaction, describe its purpose, users, technologies used, data handeled. Risk assesment of an application and how to avoid SQL Injection in this piece of code are two very different questions. Eg. you may have a button for mail merge in Excel with hardcoded username and password to enterprise production database. – Stoleg Jun 06 '13 at 12:39
See the coding in my original post, I have provided an example mail merge in my original post. The merge is VBA code and should not matter 'how' its called, only that its called. What I am asking about is, in a merge like above, is the SQL vulnerable, because it looks like the selection for population is handled by word, the question then is can word drop or alter tables from a database with that method in mind? – Jason Bayldon Jun 06 '13 at 13:40
Software does not make any harm itself. It is the users, who may do things, that result in harm. If your VBA is called from a macro in Word document, which is available on the internet - it is one case. If the macro in Word document is called on regular basis by an automated process without user interaction - it is completely different. I'm saying this, to show that it would be very difficult to get s sensible answer to such broad question. "I have some VBA code calling SQL database. How is it vulnerable?" - is not a valid question. What is your "real-world"? – Stoleg Jun 06 '13 at 13:50
Thanks for replying, perhaps i can clarify. A user supplies an ID via VBA in Access (Textbox). This text then appends userID into the sql statement set to select items into the mail merge. There is no macro inside Word, the documents are already set up to be merged with table data. What I am inquiring is, can this userID inside the sqlstatement be used maliciously, i.e. UserID = ';Drop [Customer Data]; (per the commenter above). This is a mail merge, and not a regular SQL command. This is the scenario I am asking about, which is very specific. – Jason Bayldon Jun 06 '13 at 17:10

Gord Thompson · Accepted Answer · 2013-06-07T20:21:15.360

The particular statement in your question may not be especially vulnerable to SQL Injection, but the comments to your previous question were probably trying to convince (or remind) you that "gluing together" SQL statements like that is a Bad Habit™ that you should try very hard to break. If you develop the Good Habit™ of using parameterized queries (or recordset updates, or other safer alternatives) wherever possible then you are systematically eliminating potential exploit vectors (or just potential points of failure) as you write your code.

Unless you live WAY out in the country you are likely in the habit of locking your house when you leave. You probably don't stop and assess the likelihood of your house being broken into every time you go out, you just lock the door. Good coding habits are like that: You just do them and your application is better (i.e., more secure and more reliable) for it.

Edit re: comment

For this particular circumstance, creating a temporary table is a perfectly acceptable way to provide a merge data source for Word. I'll assume that...

the Access application is split into a front-end database file (with queries, forms, reports, and macro/module code) that uses linked tables to update a back-end database file (with the shared persistent tables), and
each user has their own copy of the front-end file on their local hard drive (pointing to the shared back-end file on the network)

...because that is absolutely essential for the successful deployment of a multi-user Access application.

In that case, creating a temporary table in the front-end file should not cause any problems between different users because each user has their own copy of the front-end file, so they cannot conflict with each other.

The code to create the local temporary table would look something like this:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.CreateQueryDef("", _
        "PARAMETERS prmUserID Text ( 255 ), prmDate1 DateTime, prmDate2 DateTime; " & _
        "SELECT [Customer Data].* INTO temp_tbl_forWordMerge " & _
        "FROM [Customer Data] " & _
        "WHERE ((([Customer Data].Status)='completed') " & _
            "AND (([Customer Data].CompletedBy)=[prmUserID]) " & _
            "AND (([Customer Data].[Date Completed]) Between [prmDate1] And [prmDate2]))")
qdf!prmUserID = "user1"                 '' actually these values would come
qdf!prmDate1 = DateSerial(2013, 6, 10)  ''   from controls on a form where the
qdf!prmDate2 = DateSerial(2013, 6, 20)  ''     user can specify the values they want
qdf.Execute
Set qdf = Nothing

Then, when you pass the specifics to Word you can just use something like this

objword.MailMerge.OpenDataSource _
        Name := Application.CurrentProject.FullName, _
        LinkToSource := True, _
        Connection := "[temp_tbl_forWordMerge]", _
        sqlstatement := "SELECT * FROM [temp_tbl_forWordMerge] ORDER BY [Transaction Number]"

...where Application.CurrentProject.FullName in Access VBA returns the fully-qualified path of the front-end file where the code is executing (and the temporary table resides).

Thanks for the response, I acknowledge it is not a good habit to have. How would you parameterize an access mail merge? I think it would have to be hard-coded at some point, with selections rather than dynamic input (Select Case [cboDropDownID] for example). The way around this before me, was to append items to a "Letters" Table, print the table with the merge, and delete the table out. However, that seems unreliable when people end up printing the same things at the same time. It was suggested earlier to query the data with a parameter instead, and this is where I am kind of at. — Jason Bayldon, Jun 06 '13 at 13:54
I have chosen your answer since it was the most direct and in-depth answer provided; You did not clarify my original question, but you provided a great alternative to anyone trying my example. Thank you for taking the time to share your thoughts on the correct practices/habits to get into. — Jason Bayldon, Jun 07 '13 at 23:03

Access Mail Merge: Security Implications

2 Answers2

Edit re: comment