SQL Injection Programming Help and Protection
June 4th, 2008 - Hello fellow programmers:
There are many articles on how SQL injection attack is delivered but not too much actual code for protection. I wanted to post here simple filter functions that can help other programmers to secure their ASP (or other type of code - the functions can be easily translated to any other language.)
The idea is that ANY form field where data can be entered (with a form action to an ASP page that process the form and does SQL) or any query string parameter you can pass to an asp (in the form of bla.asp?p1=somedata&p2=somedata etc.) must be filtered by the function for evidence of SQL attack. The following SqFilter function (below) has been working pretty well against the said SQL attacks which also hit our applications daily since late May!
Your job as the programmer is to grab ANY parameter coming from query string or from a form and run that thru the SqFilter function. This function calls some other ones including logging etc.
So, say you have a simple form for users to log into your site with 2 fields called username & password and the action is some ASP that reads those values, constructs an SQL statement and checks against your user's table to see if the user exists. This is classic grounds for SQL Injection attacks!
In the ASP action for the form, where you retrieve the fields, you should have something like:
username = SqFilter(Request.Form("username"))
password = SqFilter(Request.Form("password"))
if the ASP takes Query String parameters like: someasp.asp?par1=value1&par2=value2 you must also protect those parameters by running it via the filter like:
par1 = SqFilter(Request.QueryString("par1"))
par2 = SqFilter(Request.QueryString("par2"))
Now for the actual filter system:
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
| ' replace ' with '' - very important to replace single quote with doublequote
' replace " with "
Function SqFilter(s)
If ((Not IsNull(s)) And (s <> "")) Then
tmp = Replace(Trim(s), "'", "''")
tmp = Replace(tmp, """", """)
SqFilter = CompareInput(tmp)
End If
End Function
' check against all known bad things that can be used in SQL injection
' and for good measure, check for b.js as this is what the current round
' of hacks uses…
' Now, note that from the logs captured, the hacks are nearly 100% in hex
' but still some keywords must be plain and this function should catch it
function CompareInput(str)
dim tmp
tmp = str
tmp = filterInput(tmp, "/script")
tmp = filterInput(tmp, "insert into")
tmp = filterInput(tmp, "delete from")
tmp = filterInput(tmp, "drop table")
tmp = filterInput(tmp, "exec(")
tmp = filterInput(tmp, "declare")
tmp = filterInput(tmp, "cast(")
tmp = filterInput(tmp, "varchar")
tmp = filterInput(tmp, "sp_")
tmp = filterInput(tmp, "xp_")
tmp = filterInput(tmp, "@@")
tmp = filterInput(tmp, "--")
tmp = filterInput(tmp, "update <ANY TABLE NAME YOU HAVE IN YOUR DATABASE>")
tmp = filterinput(tmp, "b.js")
CompareInput = tmp
end function
' if any of the things checked against ARE in the user data that
' came from the form or query string, log the hack and redirect hacker
' so your code does not continue and does the SQL. But if data is
' clean the function returns
function filterInput(str, filterStr)
if instr(lcase(str), filterStr) <> 0 then
logTheHack(str)
Response.Redirect("http://www.somesite.com") ' redirect hacker
else
filterInput = str
end if
end function
' this function will log the hack with all server variables
' so you can get lots of info on the hacker
sub logTheHack(s)
set fso = server.createobject("scripting.filesystemobject")
set wf = fso.opentextfile(server.mappath("logHack.txt"), 8, true)
wf.writeline(Now)
wf.writeline("----------------------------")
for each x in Request.ServerVariables
wf.writeline(x & ": " & Request.ServerVariables(x))
next
wf.writeline("----------------------------")
wf.writeline(")" & vbcrlf & s & vbcrlf)
wf.writeline("============================")
wf.close
set wf = nothing
set fso = nothing
end sub |
I hope this helps!
Amir Segal, programmer
asegal21@hotmail.com
