How To Stop SQL Injection Attacks

Like many other sites, I’ve been hit by the recent spate of SQL Injection Attacks.

My point here is not to go into great detail about how the exploit works, but rather to offer a relatively simple solution which, so far, seems to have worked for me.

The basic’s of the exploit work because of the fact that SQL Commands can contain “comment delimiters” or “string delimiters” that, if used in a certain way terminate a SQL Statement prematurely and split it into two separate commands.

Consider this statement:

SELECT username, password FROM Users WHERE user_name='kirby'

Suppose you are prompting for the user name on your webpage. The name is passed either on the URL or in the http form headers, and is received and inserted into the statement:

"SELECT username, password " & _
" FROM Users " & _
" WHERE user_name='" & request("uname") & "'"

Look carefully at it and imagine what would happen if the following came in from the text field on the webpage:

' OR username like '%

You’d end up with this SQL Statement being executed:

SELECT username, password
FROM Users
WHERE user_name='' OR username like '%'

That’s the basics of the problem.

Other variants try to send along complete SQL Statements and inject them into your webpage’s valid SQL statements in the same way.

Examples I’ve seen recently are:

1;DECLARE @S NVARCHAR(4000);SET @S=CAST(0x4400...) AS NVARCHAR(4000));EXEC(@S);--


220435552534F5220464F522053454C45435420612E6E616D652C622 (omitted extra code)

These are attempts to CAST an encoded string into a SQL command.

In looking at my logs, I notice that there was a consistent pattern. First the attack, then about 10-15 seconds later, a return visit to the normal URL to see if the attack worked or not.

Here’s what I did to stop it. I catch the attempt. Add the IP address to a “banned list”, and redirect the requst to the London Metropolitan Police Computer Crime office in London. (Nice touch, I think). And for five minutes thereafter, I redirect all traffic from this IP address to the same:

At the top of any page that gets form data POSTed to it, I added the following code. This one catches attempts to do the SQLI on the url (address).

' catch the initial attack...
if instr(ucase(request.querystring),"DECLARE") > 0 or instr(ucase(request.querystring),"CAST") > 0 Then
on error resume next
cn.Execute "INSERT INTO tbl_Banned_IP (Banned_IP) VALUES ('" & Request.ServerVariables("REMOTE_ADDR") &"')"
cn.Execute "INSERT INTO tbl_BitBucket (USERTEXT) VALUES ('" & Request.ServerVariables("REMOTE_ADDR") & " SQL Injection Attempt" & "')"
on error goto 0
response.redirect "" & request.querystring
end if

' Catch the return visit (seeing if their exploit worked)
if datediff("s",nz(kLookupQuick(cn,rs,"max(Banned_DateTime)","tbl_Banned_IP","Banned_IP='" & Request.ServerVariables("REMOTE_ADDR") & "'"),cDate("1/1/2001")),now) < 300 Then
response.redirect "" & request.querystring
end if

For attacks that come from field input in web forms, I use the following: (in this example, I'm looking for the defacing code that gets added to the field data " attempt.

for each frmField in request.form

if instr(lcase(request.form(frmField))," 0 OR _
instr(lcase(request.form(frmField))," 0 then

  response.redirect ""
end if


...followed by the same banning process above.


About combatdba

I'm a production DBA at a terabyte-class SQL Server Shop
This entry was posted in S.W.A.T.. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s