today I was asking myself if SQL-Injection is a topic for Access (and maybe for linked SQL-Server tables/views).
I expect problems for example in the filter-property and when sql-statements are 'glued' together in VBA and finally used as a query:
Sample: strSQL = "SELECT Field1, Field2, WHERE Field3 = '" & strCriteria & "'"
What do you think about?
Did you ever think about?
Where else could be risks like that?
What do you do to prevent it?
BTW:
I couldn't find any thread exclusive for this topic here, but it's worth it, in my opinion.
Well, it's possible but in many cases, it's non-issue because of two important factors:
1) Most of SQL-injection attacks take advantage of backend's ability to execute multiple statements. That's why it may have the form of "; <do something nasty> --" or similar flavors. Jet does not support multiple statements or even comments in a SQL statement so that rules out several flavors. However, it is still possible by perhaps doing something like:
Code:
"SELECT * FROM foo WHERE a_Col = " & myStr & ";"
with myStr containing this:
Code:
bar" OR Shell("Cmd.exe") OR "" = "
2) Most SQL injection attacks comes from outside - maybe a script kiddie out to make a name for himself by taking down a popular website. In contrast, the overwhelming majority of Access applications out there are not exposed to the world; they're meant to be used within a local LAN and has no external access. For remote users, it's usually expected they'd connect via VPN, use Terminal Server if it's Access backend or use a different RDBMS as a backend, etc. etc. So in the same cases, the users are typically known, authenticated and generally has an incentive to keep the application working. If employees were sufficiently disgruntled and didn't feel compelled to keep it working, it's likely that no amount of security will stop the same employee from doing bad things - in fact that is no longer a IT problem but now a HR problem.
So, that's why the subject doesn't really come up very often in context of Access. Even so, we do kind of touch on that subject when many people come on here and say, "we have problems with our users trying to enter last name like O'Reilly or need to put in quotations in the title but we get SQL errors." In this cases, here are what we can do:
1) Personally my most preferred solution is to use a parameter query:
Code:
PARAMETERS param TEXT;
INSERT INTO foo (bar) VALUES ([param]);
The advantage of this approach is that you can type in this:
Code:
Mike's and Bob's "Good" Cars
and it'll be entered exactly so. The above example of SQL injection would likewise fail and thus appear as a record containing the same snippet. All of that is done without any additional coding to clean up the strings and thus is "fool-proof." It's a bit tricky when one want to use parameter query with say, form, since this now requires VBA:
Code:
With qdf
.Parameters("param") = Me.MyTextbox
Me.subShowTheSearchResultSubForm.Form.Recordset = .OpenRecordset(dbOpenSnapshot)
End With
But unfortunately, parameter queries suffer from a fatal flaw: They are at risk of performing horribly because the engine is unable to optimize since it cannot optimize a unknown parameter and thus a dynamically-built SQL would outperform since it's "hard-coded" and thus can be assured that it uses the most optimal path to retrieve the path. This is somewhat irrelevant when one does a INSERT INTO query but I suspect the most use would be a search/find query which is a SELECT and thus stand to lose the most optimization. This is why several developers may choose to use dynamic SQL anyway.
2) One way to use dynamic SQL safely is to write a function that will escape the delimiters properly. So instead of this:
Code:
"SELECT ... FROM foo WHERE bar = " & myVar & ";"
we should do this:
Code:
"SELECT ... FROM foo WHERE bar = " & SanitizeString(myVar) & ";"
Where we call a function:
Code:
Public Function SanitizeString(strInput As String) As String
SanitizeString = Replace(strInput, """", """""")
End Function
The effect of sanitizing string is to double up the delimiter so it get stored in a string as single delimiter.
So if someone typed in this:
Code:
Mike's & Bob's "Good" Cars
Running it through the function would make it:
Code:
Mike's & Bob's ""Good"" Cars
which when SQL is executed, the doubled-up marks are collapsed into a single mark and thus does not prematurely delimit the string.
The only problem here is that you have to make sure you are sanitizing the right delimiter. If we happened to use a single quote instead of double quote, this would fail:
Code:
'SELECT ... FROM foo WHERE bar = ' & SanitizeString(myVar) & ';'
Note that also applies when we may be using ADO, SQL Server or different context where it is expect that the single quote be used.
So if I sort all out, the consequence is:
If I want to prevent SQL-injection, I would have to use a parameter query, which unfortunately has a bad performance in many cases.
One question I have now:
In your sample of a parameter query
Code:
PARAMETERS param TEXT;
INSERT INTO foo (bar) VALUES ([param]);[/quote]
you ARE using a typed parameter, or not?
So the reason for the problem of "cannot optimize a unknown parameter" does not come from missing parameter-type, or?
What else is the reason?
It's not the data type that's the issue, but rather the actual value that is then supplied to the parameter. Generally speaking, there are different ways to execute the query, though those are done behind the scene and we typically don't concern on which methods is being used, at least not if performance is satisfactory. Anyway, one value may be best answered using this one method while other might be be best resolved using different plan. But the thing is that the execution plan is created at time of query's compilation. That would be done at the time of saving the query, or in case of dynamic SQL, at the VBA line of "CurrentDb.Execute" or "CurrentDb.OpenRecordset". But since parameter doesn't contain the actual values, Access has to make a best guess on what is "probable" but that may not be always the optimal plan for a given specific value.
At least for dynamic SQL, you always get the optimal plan since you're hard-coding the value that's supplied at run-time which requires re-compilation.
Well, if you want to know more about the execution plans, you can do a google on "Jet SHOWPLAN". This let you peeks into the belly of the database engine and see what different queries does and what plan they may use, etc.
It's also documented in the white paper discussing Jet performance, on page 18.
