I'd like to expand on the comment I made above in response to onedaywhen's post outlining how to exploit a SELECT statement in MS Access. Keep in mind that these are not generalized comments about how to protect from SQL injection, but apply specifically to programming in MS Access.
I've never seen any example code for Access that would allow the kind of exploit of a SELECT that onedaywhen outlined. The reason for this is that there's almost never a situation where you would use such simple methods for collecting criteria without some validation of the input somewhere along the way, not to avoid SQL injection, but to avoid bugs caused by invalid SQL.
Here's code implementing the simplest version of this:
Public Sub TestSQLExploit()
Dim strSQL As String
strSQL = "SELECT tblInventory.* FROM tblInventory WHERE InventoryID = "
strSQL = strSQL & InputBox("Enter InventoryID")
Debug.Print strSQL
End Sub
So, passing "10036 or 'a' = 'a'" produces this SQL:
SELECT tblInventory.*
FROM tblInventory
WHERE InventoryID=10036 Or 'a'='a'
And that's definitely not good!
Now, I would never write my code that way because I always want to allow for multiple values. Instead, if I were using the InputBox() function to collect the user input (which I honestly never do, since it's too hard to validate), I'd use Application.BuildCriteria to write the WHERE clause, since that would allow me to handle multiple criteria values. That would result in this code:
Public Sub TestSQLExploit1()
Dim strSQL As String
Dim strWhere As String
strSQL = "SELECT tblInventory.* FROM tblInventory "
strWhere = "WHERE " & Application.BuildCriteria("tblInventory.InventoryID", _
dbLong, InputBox("Enter InventoryID"))
strSQL = strSQL & strWhere
Debug.Print strSQL
End Sub
I honestly thought that Application.BuildCriteria would throw an error on this, but it doesn't, and when passed "10036 or 'a' = 'a'" produces exactly the same SQL. And because of the way the Jet expression service works, it would be wide open, as you say.
Now, I never ever actually write on-the-fly SQL like this, because I just don't like the InputBox() function, precisely because you have to write a bunch of code to validate the input. And if you used it like the code above, you'd have to do a lot to make sure it was valid.
I have never seen any Access code examples for this kind of operation that does not recommend using parameterized SQL (which would, of course, avoid the problem) or a Query-By-Form interface. I generally don't use saved parameter queries in Access, because I like to write my saved queries to be usable everywhere. This means they mostly don't have WHERE clauses that have criteria that change at runtime. When I use these saved queries I provide the WHERE clause for the appropriate situation, whether as a recordsource in a form or a rowsource for a listbox or dropdown list.
Now, the point here is that I'm not asking the user for input in these cases, but drawing the criteria values from Access objects, such as a control on a form. Now, in most cases, this would be a control on a form that has only one purpose -- to collect criteria for some form of filtering. There would be no unvalidated free-text fields on that form -- date fields would have input masks (which would restrict input to valid dates), and fields that have a limited number of valid values would have control types that restrict the choices to valid data. Usually that would be something like a dropdown or an option group.
The reason for that kind of design is not necessarily to avoid SQL injection (though it will prevent that), but to make sure that the user is not frustrated by entering criteria that are invalid and will produce no results.
Now, the other consideration is that sometimes you do want to use some plain text fields so the user can put in certain kind of data that is not already restricted (such as looking up names). Just looking at some of my apps that have name lookup routines with unvalidated text fields, I find that I'm OK, because I don't use BuildCriteria in those cases, because it's designed to collect only one criterion at a time (though the user can input "*" to retrieve multiple records).
If I have a textbox where the user enters "fent* or 'a' = 'a'", and I use that in a WHERE clause:
WHERE tblDonor.LName Like "fent* or 'a' = 'a'"
The result is that nothing is found. If the user entered "fent* or a = a", it will still not work, because it's a text field and I'm using double quote around it. If the user entered:
fent* or "a" = "a"
that will break, too, because when my code puts double quotes around it, the WHERE clause will be invalid.
Now, with the case of just taking use input and putting double quotes around it, it's clear that inputting this:
" Or "fent*" or "a" = "a" Or "
would result in:
WHERE tblDonor.LName Like "" Or "fent*" or "a" = "a" Or ""
and that would be very bad, since it would return everything. But in my existing applications, I'm already cleaning double quotes out of the user input (since double quotes are theoretically valid within the LName field), so my apps construct this WHERE clause:
WHERE tblDonor.LName Like "? Or ?fent*? or ?a? = ?a? Or ?*"
That won't return any rows.
But the reason it doesn't is not because I was trying to avoid SQL injection, but because I want the user to be able to look up names that have double quotes embedded in them.
======
Some conclusions:
never accept free-form input from users when filtering data -- instead, use controls that pre-validate input (e.g., textboxes with input masks, dropdown lists, options groups) and limit it to values that you know are valid.
when accepting data from a textbox with no restrictions, avoid Application.BuildCriteria, which will process the input in such a way that the user could trick your app into returning all rows (though that's the extent of what the exploit could do).
What this means on a practical basis is that if you want to collect multiple criteria, you need to do it in a way that the user can only choose from preselected values. The simplest way to do that is with a multiselect listbox (or a pair of them with ADD>> and <<REMOVE command buttons in between).
Of course, whether or not you need to worry about this kind of SELECT exploit depends on the importance and privacy level of the data being retrieved, and exactly what is being returned to the user. It might be no problem to risk returning all rows of non-sensitive data when presenting the data in an uneditable form (e.g., a report), whereas it might be problematic if you presented it in an editable form and someone changed data that oughtn't be edited.
But with non-sensitive data, it will often simply not matter if the user gets too much data returned (except for performance issues, e.g., overloading a server -- but that's better handled in other ways).
So, my takeaway on all of this:
never use InputBox() to collect criteria (this one I already avoid).
always use the most limiting control types possible for collecting critiria (this is already something I do regularly).
if using a textbox to collect string data, treat it as a single criterion no matter what's put in by the user.
This does mean that I have some apps out there where a user could input "Or 'a' = 'a'" along with a valid criterion and return all rows, but in those apps, this is simply not an issue, as the data is not sensitive.
But it's a good reminder to me not to be complacent. I had thought that Application.BuildCriteria would protect me, but now realize that the Jet expression service is way too forgiving in what it accepts in a WHERE clause.
2009/12/08 EDIT: Just found these links on SQL Injection in MS Access. All of these are targetted at web injection, so not directly applicable to a discussion of Non-Web SQL injection (many of them would be a waste of time in interactive Access, as you already have access to a lot of the information being brute-forced, e.g., information about file system, paths, executables, etc.), but many of the techniques would also work in an Access application. Also, executing from Access opens up a lot of functions that would not be runnable from ODBC/OLEDB. Food for thought.