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]

@ChipR
Did anybody perform it successfully in Access?

[NeoPa]

@FishVal
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]

@FishVal
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]

@FishVal
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]

@FishVal
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 :

AuthID  AuthName  AuthPW  AuthCode
  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]

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

[FishVal]

@NeoPa
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]

@FishVal
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]

@FishVal
And so we come back full-circle.

The linked article includes a paragraph :
@Frinavale
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

[ChipR]

Off topic AND wrong.

Donald D. Chamberlin and Raymond F. Boyce of IBM subsequently created the Structured English Query Language (SEQUEL) to manipulate and manage data stored in System R.[6] The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company.[7]

[servantofone]

@ChipR
My apologies. I didn't realize I had migrated away from the article. I guess Chris Fehily is on his own with his claim. Every other SQL book I read agrees with you.

-Kyle