SQL Injection -- A Comment

  • Article

Kumar comments here and I think he has some questions/concerns that are worth addressing.  I'm going to add my own comments (and, please note, the comments I make here are my own and do not necessarily reflect Microsoft's corporate opinions).

---------------------------------------------------------------------------------------

My site extensively uses asp and sql server. My site ranking is good with google for certain keywords searches.

Friday morning I found that the bad people (nmidahena) had updated text fields in almost all of the tables with a <script> some thing.js </script>. This has created a nightmare for me. 

[nbc] This is consistent with what we've seen elsewhere.  You had one or more SQL injection vulnerabilities on an ASP page that used a query string as the SQL query.  The attackers found you on Google and ran their scripted attack against you, resulting in all your text getting the <script> tag appended to it.

Fortunately, I had a backup that came to my rescue. I also downloaded all asp and html files to my local machines and searched for "nmidahena" - nothing came up.

[nbc] You won't find anything in the code files, at least not in this attack. 

This is what I have done:

a) Restore sql server tables from the backup.

[nbc] This is a very good step as long as you also fix the holes that were used to compromise it in the first place. 

b) Rewrite my asp forms to not to accept any character or words that could be used for sql injection.

[nbc] This one is more problematic.  Just blacklisting a set of characters/words is probably going to break this attack but it's not likely to prevent future attacks from working.  I am not really qualified to answer this as I'm not a developer by trade.  Thankfully, Michael Howard and David LeBlanc spent a lot of time answering it in Writing Secure Code.  I have the 2nd edition on my desk and pages 399-411 cover this at great length.

[nbc] There are almost certainly other issues to be considered here.  The first one is that, in most of these attacks, it looks like the web app is using a user with sysadmin privileges in SQL.  This is as bad an idea as putting IUSR_MYSERVER in the administrators group.  Your web app should have the least possible privilege in SQL.  Running as sysadmin in SQL enables things such as xp_cmdshell, which is a great way for attackers to run sniffers, password-stealing utilities, and other malware on your SQL Server.

[nbc] Another thing to consider is the possibility that this isn't the first SQL injection attack against you. Another attacker might not have defaced your website; instead, they might have silently stolen every bit of data in your database. If your web app was running as sysadmin, they might have silently stolen every bit of data in EVERY database on your SQL Server. It is entirely possible that every name, e-mail address, password, credit-card number, etc that is in your database server's possession is now in somebody else's possession as well. If so, your IIS logs should tell the story.

Do you think this would be sufficient to prevent future attack?

[nbc] There are two answers to this question:

  1. I think this is sufficient to prevent future iterations of this same attack...until the attackers change the script they're using.
  2. I don't think this is sufficient to prevent all future SQL injection attacks.  The only approach that is going to help with that is to make sure that you are using least privilege in SQL and build secure SQL statements/stored procedures.  Even then, of course, there may be other vulnerabilities in your site that can lead to compromise.

I dont know where to look for help. The hosting agency has no good answers.

[nbc] I don't envy your hosting company or, for that matter, any hosting company.  In general, I don't think it's going to be their responsibility to ensure that your code is secure, so I'm not surprised that they don't have good answers.  On the other hand, if the security bugs in your code are severe enough, you might be opening their facility up to further compromise. 

---------------------------------------------------------------------------------------

Other people may have better advice than I, and I'd love to hear it.  Since I'm seeing another 11K (and rising) servers compromised this month via this attack, I think you're not the only person asking these questions.  I've spent a lot of time thinking about this lately from a support/incident response perspective and I have some very large questions that I can't seem to answer.

  • Since my team operates from a reactive position (customer gets compromised, customer calls us, we investigate and offer suggestions), how can we help customers remediate this problem when we work with them?  Realistically, the customer needs to do a security code review; however, this is beyond the scope of my team.  There are offerings that cover this but, given the amount of work and expertise involved, they are costly.
  • Even if I had the opportunity to be proactive, how could I contact the large number of people who appear to be vulnerable due to their own code defects (67K+ from last month's attack, 11K+ so far this month)?

Comments

  • Anonymous
    April 26, 2008
    OK cub scouts, it looks like enough people have dissected the attack, but don't have a clean effiecient way to reverse the injected code. If any truncation took place, a restore from backup is your only hope. However, if there is no truncation of data, use the same attack vector to pull the inhected text back out. Here's how (This example pulls the www.nihaoor1.com version out. I'll explain how to alter it for the www.aspder.com and www.414151.com versions at the end.): 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+']=substring(rtrim(convert(varchar,['+@C+'])), 1, len(['+@C+']) - 50) where ['+@C+'] like ''%<script src=http://www.nihaorr1.com/1.js></script>''') FETCH NEXT FROM  Table_Cursor INTO @T,@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor TO alter this for www.aspder.com, simply change '%<script src=http://www.nihaorr1.com/1.js></script>' to '%<script src=http://www.aspder.com/1.js></script>' and change len(['+@C+']) - 50 to len(['+@C+']) - 48 (the length of the other string). This works by rewriting the column that has the text at the end of it with itself minus the number of characters of the injected text. Now that you've removed the offending string, use the IIS logs to find the ASP pages that were used for the injection and sanitize the input. Additionally, you might think about denying select priveledges to syscolumns and sysobjects to the SQL user that the application uses to connect. That way this script cannot enumerate the text columns in user tables. I hope this helps someone out there. The Nose