April 26, 2008 | SQL Server

Call a spade a spade! (SQL injection, or IIS vulnerability?)

In a recent blog post, Dancho Danchev mis-labeled a recent IIS vulnerability as a "massive SQL injection attack."

Let's be honest here.  Yes, this alert needs attention.  But this is not a new SQL injection vulnerability.  It is simply an exploit in IIS that lets malicious users access your source code.  If your database is already open to SQL injection attacks by anyone who can access the file system on your web servers, then yes, SQL injection is just waiting for the next vulnerability to your file system.  However, if you protect your database server(s) from SQL injection in the first place, then no IIS vulnerability will magically become known as a SQL injection attack.

Never mind that half the IIS servers in the world probably don't even connect to SQL Server, and of the remainder, not all are vulnerable to SQL injection.  The ones that are vulnerable are that way because the web developers and/or DBAs have been sloppy and allowed for practices that help make SQL injection possible.

Call it what it is; don't sensationalize it.  And instead of trying to create panic, provide a little education!  How do you prevent an IIS vulnerability from becoming a SQL injection attack?  There are plenty of things you can do.  Some of them are pretty obvious, or have been discussed previously, but I'll recap the ones on my list:

  • Do not expose your SQL Server to the Internet directly
    While in some cases you can't avoid this (shared database servers at a hosting provider, for example), if your server-side code yields a public address, or enough information that the public address can be easily determined, then you are opening yourself up.  All someone needs is read access to your config file or ASP page in order to obtain credentials to access your SQL Server from anywhere.  Talk to your network administrator about keeping SQL Server behind your firewall.
  • Make your passwords strong
    Ideally, your applications will use Windows authentication, but if you must use mixed authentication modes, then make sure your SQL Authentication passwords are "strong" passwords.  It is very hard to be completely immune to a dictionary attack, but you can make it much more difficult by using a 16-character password with mixed case and alphanumerics, like '$QL$erver_r0ck$!', as opposed to an "easier" password like 'tweetybird.'
  • Follow the principle of least privilege
    Do not use sa as the login in the connection strings for your application.  Use a low-privileged user that can only execute (certain) stored procedures.  There is no reason someone should be able to add a query like "SELECT * FROM sys.objects" to your server-side code, or launch extended procedures like xp_cmdshell, or drop objects, because that user should not have sufficient access to do so… the application user should not be sa or db_owner.  Lock down your applications, and only give them the rights they need.

    Similarly, do not use a domain administrator or otherwise privileged user as the service account.  This would mean that anything that runs under the context of SQL Server has free reign over your server or even entire network, using a variety of tools like extended procedures.

  • Always use stored procedures, or at least parameterized statements
    If you build ad hoc SQL in your applications, then you are asking for SQL injection attacks, and I strongly suggest you become familiar with using stored procedures or parameterized queries.  Otherwise, all input becomes suspect, since it is very easy to use comments or semi-colons to change the meaning of queries or to append additional queries to be executed.  With a query that uses strongly typed parameters, however, this technique becomes fruitless.  This does not mean something like:
    sql = "EXEC dbo.foo @param1 = '" & Request.QueryString("bar") & "'"
    conn.execute(sql)

    This is still vulnerable to SQL injection, because I can now call the page using ?bar=';drop table blat;–

    Instead you should use a command object and pass the inputs to parameters.  (This also prevents you from having to escape apostrophes in names like O'Hagan, delimit date literals correctly, etc.)

  • Use TRY/CATCH to return more generic error messages
    In order to prevent revealing your database structure, do not let errors like foreign key violations or other errors bubble up to the application.  This just gives your potential attacker more information about your database structure than they need to have.  Instead use error handling to say "That user does not exist" instead of the default error message SQL Server provides — which gives specific table and column information back to the user.  If you are using ASP.Net, then you can make sure that you turn CustomErrorsMode to "On" or "RemoteOnly" and set compilation debug to "false"…
  • Do not store passwords in your Users table
    A lot of web applications store usernames and passwords so that their users can log in to the application.  Instead of storing a password in plain text, which can then be read easily by anyone who manages to gain read access to the Users table, store a hash of the password (using MD5 or something similar).  When the user attempts to login (hopefully via SSL), you use the same technique to hash their entry and compare the hashed values, instead of a clear text comparison.  Even if the user has read access to the stored procedure that implements the hash, all they can do with it is try and try and try… they cannot reverse engineer the data if you use a proper hashing technique.

9 comments on this post

    • Jason - April 28, 2008, 7:35 AM

      MS is saying it is SQL injection. http://blogs.technet.com/msrc/archive/2008/04/25/questions-about-web-server-attacks.aspx
      If so, they wrote one hell of a dynamic injection engine.

    • Denis Gobo - April 29, 2008, 4:06 AM

      I was just browsing slashdot and reading the comments
      here is what I found
      The exploit in question has nothing to do with IIS, period, whatsoever. It's being targeted at servers that run IIS because those are the ones most likely to have SQL Server as their database back-end
      You must be running Microsoft SQL Server as your database platform
      Your web application must be vulnerable to SQL injection
      The SQL Server user that your web application authenticates as must have SELECT and UPDATE access to the sysobjects table
      Now ask yourself, does your web user have select permissions to the sysobjects table? If so, why?

    • AaronBertrand - April 29, 2008, 4:48 AM

      I guess my point was, a lot of blogs out there are saying, "Hey, look how insecure SQL Server is!  Here is a new SQL Server injection attack!"  When in fact, this attack *could* have been targeted at any database platform.  The problem is that people use highly privileged accounts for their web applications, and this is not isolated to SQL Server… but the exploit *does* have to do with the web server, to some degree, because that's the only way code injected into a text field could be run on the server.  Some of the comments seem to imply that the code runs on the client's PC, which would merely mean that a few surfers here and there would have some kind of code executed against their own local copy of SQL Server Express or Developer Edition.  From what I read this attack seems to affect the SQL Server database(s) that the server-side web server code communicates with — of course it would help if they were more clear without revealing any methodology.

    • Denis Gobo - April 29, 2008, 4:57 AM

      I agree, this is nothing new here

    • Brian Clark - May 4, 2008, 2:44 AM

      I think this shows that there is an opportunity for Microsoft to build/configure some additional security and anti-sql injection features into SQL Server and/or .Net. For example, maybe there is some way that Microsoft could prevent the ability to launch extended procedures like xp_cmdshell as part of the default install. Maybe create a setting that, by default, only allows access to sys.objects from the local host. I'm not saying to disable this functionality, just make the DBA/Sys Admin explicitly enable some of this stuff rather than have it available by default.
      This is definitely "protecting people from themselves", but I think that it would be valuable for Microsoft to spend some time thinking about how they could actively prevent SQL injection. Maybe even include the functionality in a SQL 2005 SP3 and/or 2008 SP1.

    • AaronBertrand - May 5, 2008, 4:48 PM

      Brian, xp_cmdshell is already disabled by default… you need to enable it explicitly.  This is how SQL Server 2005 shipped, and SQL Server 2008 will ship the same way.
      As for read access to sys.objects etc., this has to be allowed for tools like SSMS etc. to work.  I don't know if you have compared the experience of running SSMS locally against a remote server, vs. using RDP to act on the box directly, but I know my preference is to use my local copy of SSMS.  (This also makes it easier to share code between servers and have a single repository for templates/scripts etc.)  On top of that, note that a lot of servers don't even have the client tools installed locally.  So for this setting, I am not opposed to adding it, but I think disabling it by default is going to be more trouble than it is worth.  In SQL Server 2008, you can probably enforce this out of the box using the new Policy-Based Management feature, if you wanted to do it yourself.  (For SQL Server 2005, I can almost guarantee that you won't be seeing any new functionality in a service pack.)

    • Raju Lalvani - May 6, 2008, 1:08 PM

      Most of the attacks in the Web World are due to poor coding practices, an incomplete or forgotten input validation.

    • Steve G. - May 19, 2008, 6:38 PM

      I guess what the writer is really saying is the poor web coding practices are Microsoft's fault. None of the suggested items have anything to do with IIS, or in fact are specific to MS products. Yet, somehow this is IIS's fault….
      FUD is everywhere.

    • DBA Rob - May 21, 2008, 12:28 AM

      This attack is definitely preventable by any one of three simple techniques: 1) type-safe parameter encoding, 2) use of stored procedure with a SQL login (windows or SQL Server authenticated) that only has execute on the stored procedures and not update on tables 3) a simple input check that looks for single quotes and/or semi colons.  The bottom line is this attack hit sites with poor development practices, include the company where I work.  I had a fix ready within the hour once we realized what had happened.  The bottom line is that there are more people using MS products for web sites so the attackers go after MS products/apps.  I call this problem the Microsoft Mentality = developers expect to have all permissions availble to them so then they have no problems developing apps.  Unfortunately the training center and schools all teach with this same mindset so it continues to spread.

Comments are closed.