SQL oversights, a recipe for disaster
For many of us, Web applications have become as much a part of our daily life as the meals we eat. (Some of us even eat while we work on Web apps.) Unfortunately, just as these applications are something else that we have to cook up, they're just as susceptible to poisoning as our food is. In this column, we'll consider how too many cooks can spoil the application, but first, let's look at what a typical recipe for a Web app would be:
Ingredients:
1 Network operating system
1 Web server
1 Database server
Application programs (to taste)
1 Firewall for the extraspicy flavor
Optional garnishes:
Load balancers
Reverse proxy servers
Cache systems
Depending upon the urgency of the project, a Web application can take several days to cook up and will serve medium to heavy loads of requests when finished, depending on the nature and popularity of the application.
Preparation
Harden the network operating system by carefully applying the proper configurations until no security holes are visible. If the network operating system is not fresh, apply the latest vendor-supplied patches until secure. Install the database server on top of the network operating system, stirring gently until blended. Add the desired schemas and tables to the database, depending on the application demands.
Now take the Web server and configure it so that it forms a tough, crisp layer above the network operating system and the database server. Add the application programs in such a manner that they bind the Web server and the database server together. Finally, wrap the system with the firewall and make sure that only the required areas of the system are visible to the outside world, carefully shielding any other sensitive areas that might burn if exposed.
Your system is now ready to be served. Here's how the finished system should look.
click for full size
If you've been slaving over a hot keyboard, it looks like a delicious dish, doesn't it? However, attackers can get their fingers in your pie if you aren't careful. Even with the utmost diligence, sometimes seemingly minor oversights can end up causing the most serious damage, as we'll show you.
One place where you can find these oversights is in the application's database interface--the part of the application code that is used to pass data back and forth from the application to the database server using SQL queries. If these SQL queries are not carefully screened, the security of the whole system can be compromised. A tiny security hole such as this can get wider and wider and eventually lead to total system compromise.
For our example, let's consider a typical Web application, perhaps a Web site that hosts online recipes. The administrators have IIS 5.0 running on Windows 2000, interfacing with a back end database running on SQL Server 2000. The application programs are in the form of Active Server Pages, which are responsible for taking user inputs from the browser requests and passing the results back in HTML.
We won't go into the complete details of the code and architecture in this article, but we will look at the important details relevant to this topic. Our Web site is called YummyRecipesOnline. The main page looks like this...
click for full size
...and has four recipes. The HTML source code of this page is as follows:
<html>
<head><title>YummyRecipesOnline!</title></head>
<body>
<font color="#3333FF"><font size=+4>YummyRecipesOnline</font></font>
<p>Welcome to YummyRecipesOnline!
<p>Click the recipe below to get a full description.
<ul>
<li>
<a href="http://yummyrecipesonline/scripts/recipe.asp?recipe_id=1">
Penne pasta with marinara sauce</a>
<li>
<a href="http://yummyrecipesonline/scripts/recipe.asp?recipe_id=2">
Spicy Schezuan eggplant</a>
<li>
<a href="http://yummyrecipesonline/scripts/recipe.asp?recipe_id=3">
Yellow curry with oyster mushrooms</a>
<li>
<a href="http://yummyrecipesonline/scripts/recipe.asp?recipe_id=4">
French fries</a>
</ul>
Click <a href="http://yummyrecipesonline/scripts/submit.asp">here</a>
to submit your own recipe.
</body>
</html>
Notice the URLs for each recipe link. Recipes are retrieved by an ASP file called recipe.asp. Each recipe is identified by a recipe identifier, which is passed on via the parameter recipe_id.
Sounds simple enough, right? Now let us look at the insides of recipe.asp. Only the relevant code is displayed here:
01: Const CONNECT_STRING =
02: "Provider=SQLOLEDB;SERVER=10.0.0.3;UID=sa;PWD=xyzzy"
03:
04: Set objCon = Server.CreateObject("ADODB.Connection")
05: objCon.ConnectionString = CONNECT_STRING
06: objCon.Open
07:
08: SQLQuery = "SELECT * FROM RECIPES WHERE ID = " &
09: Request.QueryString("recipe_id")
10:
11: Set RS = objCon.Execute(SQLQuery)
This is the heart of the code that interacts with the database. The rest of the ASP code would be for formatting and printing the contents. Let's analyze what this code does.
The first two lines define the CONNECT_STRING, which describes the database connection. In this case, the data is provided by a back end SQL Server, which is on a server with IP address 10.0.0.3. The database user ID is sa, which is the system administrator, and the password is xyzzy.
The next three statements (lines 4 to 6) establish a database connection and link the object objCon to the established database connection. Now we can use objCon to perform database transactions with the back end database.
Recipes are stored on the SQL Server in a table called RECIPES. Each field in the table contains recipe details that will be retrieved, formatted, and printed. The next statement in the ASP file (lines 8 and 9) forms the SQL query that will be executed on the back end database for retrieving the recipe details. recipe_id is the parameter that is passed from the URL, and it contains the recipe identifier. This parameter is directly plugged into the SQL query.
Finally, the query is executed with the objCon.Execute() method, and the resulting data is returned in an object called RS.
The above setup looks error-free, doesn't it? If we assume that the Web server, the underlying operating system, and the database server are well configured and patched, then from a systems standpoint, we are secure. However, there are two errors. They seem insignificant at first, but as we will illustrate, they can compromise the entire system.
The first error lies in the way the SQL query is constructed:
SQLQuery = "SELECT * FROM RECIPES WHERE ID = " &
Request.QueryString("recipe_id")
If we click the first link, http://yummyrecipesonline/scripts/recipe.asp?recipe_id=1, the resultant query becomes:
SELECT * FROM RECIPES WHERE ID = 1
The error lies in the fact that there is absolutely no validation performed on the contents of recipe_id. The user can pass absolutely any value to recipe_id and make the ASP code produce erroneous results. For example, we can set recipe_id to -1, and the database query will result in a Not Found error. That is not as dangerous, however, as what comes next.
If an attacker figures out how the SQL queries get passed to the back end, the SQL query can be poisoned, with quite deadly effects. Users of YummyRecipesOnline don't know what the SQL query looks like. Sometimes, it is not hard to recover the actual query statement. The URL below contains an extra single-quotation mark (') at the end of the URL.
http://yummyrecipesonline/scripts/recipe.asp?recipe_id=1'
If this URL is sent to the Web server, the attacker gets the following error returned back in the browser:
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string ''.
SQL:SELECT * FROM RECIPES WHERE ID = 1'
Voilà! The attacker now knows the SQL query string from the error that was generated. For the attacker (but not for you) the rest is easy. The next attack would be to extend the SQL query with valid SQL syntax so that it does something more than what it was designed to do. Notice that in this URL, http://yummyrecipesonline/scripts/recipe.asp?recipe_id=1+OR+1=1, recipe_id gets set to the value 1 OR 1=1. A plus sign (+) in the URL represents a space. Now the SQL query that gets fired off to the database looks like this: SELECT * FROM RECIPES WHERE ID = 1 OR 1=1. Because 1=1 will always evaluate to be TRUE, this SQL query will return all the rows from the table RECIPES. Now consider the possibility of such a query being fired off on an online bank, but instead of retrieving all the recipe descriptions, the attacker ends up retrieving the details of all the bank accounts!
If the SQL query is extended further, multiple SQL statements can be executed instead of just one. The example below illustrates how an attacker can erase the entire RECIPES table from the database with the following URL: http://yummyrecipesonline/scripts/recipe.asp?recipe_id=1;DROP+TABLE+RECIPES. The resultant SQL query will now actually be two queries: SELECT * FROM RECIPES WHERE ID = 1; DROP TABLE RECIPES.
The possibilities are endless.
The recipe for disaster, part two
So far, we've looked at just one error. The other error is very subtle, but when coupled with this input-validation error, it results in total system compromise. In our next column, we'll discuss this error and also cover some dos and don'ts for Web application programmers. Meanwhile, readers are encouraged to send us e-mail if they notice the second subtle error.
We await your responses at securityissues@foundstone.com. Until then, be safe.
Chris Prosise is the vice president of consulting at Foundstone, a network security firm specializing in consulting and training. Formerly a U.S. Air Force officer and a Big 5 consultant, Prosise has led dozens of security penetrations and incident response engagements on government and Fortune 500 networks. Prosise 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).