SQL Injection Attack Discussion

[ChipR]

Since we're talking about filters, make sure you also use a filter for semicolons (at the minimum) on any input that is going directly into an SQL statement to prevent your entire database from being deleted.
See SQL Injection Attack.

Admin Edit.
This discussion was split off from the original thread, which can be found at Force .DefaultValue to be a string.

[NeoPa]

I see you're developing mind-reading abilities too now Chip.

I was thinking about bringing that into the thread. Now you've found the link I will add it to my frequently used list. This is certainly something that bears repeating.

[FishVal]

SQL Injection Attack.

Did anybody perform it successfully in Access?

[NeoPa]

Did anybody perform it successfully in Access?

Are you asking if it's possible to hack into an Access database, or whether anyone has managed to protect an Access database using the techniques suggested?

PS. I will move this to a new thread to avoid swamping the original with this (quite important) discussion.

[NeoPa]

Did anybody perform it successfully in Access?

I've done some checking (assuming you're asking if it's possible to hack in that way) and it appears that Access's syntax checking seems to block any attempts I try, but remember this is most often used via a web interface. In that case (using an Access database simply as a Back-End, it is very likely possible as the syntax checking would not be active.

[FishVal]

I have a strong feeling that Access back-end cannot execute multiple SQL commands.

P.S. Human beings has many problem that other animals don't have, but at least tail curvature by no means threatens us. :D

[NeoPa]

I have a strong feeling that Access back-end cannot execute multiple SQL commands.

It wouldn't need to necessarily (although you may well be right).

If some Access SQL were looking for a matching name in an authority table with :

SELECT 9 AS [AuthLevel]
FROM [tblSecurity]
WHERE [Password]='%ValueHere%'

Assume now that the value entered (to replace %ValueHere%) were :

' OR 'A'='A

The real life code would be a little more complicated, but this illustrates the point succinctly I feel.

[NeoPa]

P.S. Human beings has many problem that other animals don't have, but at least tail curvature by no means threatens us. :D

At the time humans were losing their tails into those vestigial stubs we now have, do you think they weren't worried?!!?

[NeoPa]

OK. I managed to break into a very basic system.

Assume a table :
Table=[tblSecurity]

AuthID    AutoNumber  (PK)
AuthName  Text        (Account name)
AuthPW    Text        (password)
AuthCode  Numeric     (payload)

Data is as follows :

[B]AuthID  AuthName  AuthPW  AuthCode[/B]
  1     NeoPa     Ooops     90
  2     Admin     Secret    99
  3     Other     LowLevel   1

Next I ran some code in the immediate window to simulate checking a name and password passed via InputBox() :

strN=InputBox("Enter Name:") : _
strP=InputBox("Enter PW:") : _
?DLookup("[AuthCode]", _
         "[tblSecurity]", _
         "[AuthName]='" & strN & "' AND " & _
         "[AuthPW]='" & strP & "'")

The data I entered for strN & strP were as follows :

Admin
' OR 'A'='A

The result, of course, was 99. A full break-in at the highest authority level.

[FishVal]

At the time humans were losing their tails into those vestigial stubs we now have, do you think they weren't worried?!!?

Doctors say - there is much stuff in human body which could and should be cut out. Medical purveyed humor ... I hope.

[NeoPa]

Doctors say - there is much stuff in human body which could and should be cut out. Medical purveyed humor ... I hope.

You'll never hear more dodgy or worrying humour than from doctors (unless it's from soldiers of course).

[FishVal]

....

strN=InputBox("Enter Name:") : _
strP=InputBox("Enter PW:") : _
?DLookup("[AuthCode]", _
         "[tblSecurity]", _
         "[AuthName]='" & strN & "' AND " & _
         "[AuthPW]='" & strP & "'")

The data I entered for strN & strP were as follows :

Admin
' OR 'A'='A

The result, of course, was 99. A full break-in at the highest authority level.

Definitely makes sense.

A way to prevent such kind of attack could be preevaluation of entered criteria with some dummy value which will never occur in the table.

If Eval("'<impossible password>'='" & strP) Then MsgBox "Cheater, run up and kill yourself against wall"

[FishVal]

...
A way to prevent such kind of attack could be preevaluation of entered criteria with some dummy value which will never occur in the table.
...

Well. A good example of how one can outsmart himself.
Actually, it is sufficient to replace text delimiters in user input.
...With doubled delimiters for example.

[NeoPa]

Well. A good example of how one can outsmart himself.
Actually, it is sufficient to replace text delimiters in user input.
...With doubled delimiters for example.

And so we come back full-circle.

The linked article includes a paragraph :

To protect against SQL Injection by stripping user input of harmful SQL commands, it is only necessary to remove any quotes from the data. Literal text values (What is required from the user in this example) are surrounded by quotes when used in a SQL string. Generally, this refers only to single-quotes ('), but most versions of Access will work with double-quotes (") just as happily. It is therefore advisable to remove all quotes from the input before attempting to use it to form the SQL string. Without a quote in the input, it will all be treated as data and is therefore quite safe.

This assumes quotes of any kind are not acceptable in the string, but doubling them instead allows them in safely.

See Force .DefaultValue to be a string (Post #13) for the code for such a solution.

[servantofone]

This article is incorrect. SQL doesn't stand for "Structured Query Language". Its official name is Database Lanugage SQL. SQL isn't an acronym. See page XV of SQL by Chris Fehily (ISBN 0321334175).

-Kyle