Testing for SQL Injection Vulnerabilities

SQL Injection attacks pose tremendous risks to web applications that depend upon a database backend to generate dynamic content. In this type of attack, hackers manipulate a web application in an attempt to inject their own SQL commands into those issued by the database. In this article, we take a look at several ways you can test your web applications to determine whether they're vulnerable to SQL Injection attacks.

Automated SQL Injection Scanning

One possibility is using an automated web application vulnerability scanner, such as HP's WebInspect, IBM's AppScan or Cenzic's Hailstorm. These tools all offer easy, automated ways to analyze your web applications for potential SQL Injection vulnerabilities. However, they're quite expensive, running at up to $25,000 per seat.

Manual SQL Injection Tests

What’s a poor application developer to do? You can actually run some basic tests to evaluate your web applications for SQL Injection vulnerabilities using nothing more than a web browser. First, a word of caution: the tests I describe only look for basic SQL Injection flaws. They won't detect advanced techniques and are somewhat tedious to use. If you can afford it, go with an automated scanner. However, if you can't handle that price tag, manual testing is a great first step. 

The easiest way to evaluate whether an application is vulnerable is to experiment with innocuous injection attacks that won't actually harm your database if they succeed but will provide you with evidence that you need to correct a problem. For example, suppose you had a simple web application that looks up an individual in a database and provides contact information as a result. That page might use the following URL format:

 http://myfakewebsite.com/directory.asp?lastname=trehern&firstname=jeff 

We can assume that this page performs a database lookup, using a query similar to the following:

 SELECT phone

 FROM directory

 WHERE lastname = 'trehern' and firstname= 'jeff' 

Let's experiment with this a bit. With our assumption above, we can make a simple change to the URL that tests for SQL injection attacks:

 http://myfakewebsite.com/directory.asp?lastname=trehern&firstname=jeff'+AND+(select+count(*)+from+fake)+%3e0+OR+'1'%3d'1 

If the web application hasn't been properly protected against SQL injection, it simply plugs this fake first name into the SQL statement it executes against the database, resulting in:

 SELECT phone

 FROM directory

 WHERE lastname = 'trehern' and firstname='jeff'

 AND (select count(*) from fake)> 0

 OR '1'='1'

 

You'll notice that the syntax above is a little different than that in the original URL. I took the liberty of converting the URL-encoded variable for their ASCII equivalents to make it easier to follow the example. For example, %3d is the URL-encoding for the '=' character. I also added some line breaks for similar purposes.

Evaluating the Results

The test comes when you try to load the webpage with the URL listed above. If the web application is well-behaved, it will strip out the single quotes from the input before passing the query to the database. This will simply result in a weird lookup for someone with a first name that includes a bunch of SQL! You'll see an error message from the application similar to the one below:

 Error: No user found with name jeff+AND+(select+count(*)+from+fake)+%3e0+OR+1%3d1 trehern! 

On the other hand, if the application is vulnerable to SQL injection, it will pass the statement directly to the database, resulting in one of two possibilities. First, if your server has detailed error messages enabled (which you shouldn't!), you'll see something like this:

 Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

 [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'fake'. 

 /directory.asp, line 13

 

On the other hand, if your web server doesn't display detailed error messages, you'll get a more generic error, such as:

 Internal Server Error

 The server encountered an internal error or misconfiguration and was unable to complete your request.

 Please contact the server administrator to inform of the time the error occurred and of anything you might have done that may have caused the error.

 More information about this error may be available in the server error log.

 

If you receive either one of the two errors above, your application is vulnerable to SQL injection attack! Some steps that you can take to protect your applications against SQL Injection attacks include:

Implement parameter checking on all applications. For example, if you're asking someone to enter a customer number, make sure the input is numeric before executing the query.

Limit the permissions of the account that executes SQL queries. The rule of least privilege applies. If the account used to execute the query doesn't have permission to execute it, it will not succeed!

Use stored procedures (or similar techniques) to prevent users from directly interacting with SQL code.

SQL Injection

The vast majority of databases in use today have some form of web interface, allowing internal and/or external users easy access through familiar browser software. If you're security-conscious, you've undoubtedly spent a significant amount of time setting appropriate security permissions on your databases and web servers. Have you also considered the security of the code that powers the database-web interface? 

One common type of database attack, the SQL Injection, allows a malicious individual to execute arbitrary SQL code on your server.  Let's take a look at how it works by analyzing a very simple web application that processes customer orders. Suppose Acme Widgets has a simple page for existing customers where they simply enter their customer number to retrieve all of their current order information. The page itself might be a basic HTML form that contains a textbox called CustomerNumber and a submit button. When the form is submitted, the following SQL query is executed: 

SELECT *

FROM Orders

WHERE CustomerNumber = CustomerNumber 

The results of this query are then displayed on the results page. During a normal customer inquiry, this form works quite well. Suppose John visits the page and enters his customer ID (14). The following query would retrieve his results: 

SELECT *

FROM Orders

WHERE CustomerNumber = 14 

However, the same code can be a dangerous weapon in the hands of a malicious user. Imagine that Mal comes along and enters the following data in the CustomerNumber field: “14; DROP TABLE Orders”. This would cause the following query to execute: 

SELECT *

FROM Orders

WHERE CustomerNumber = 14; DROP TABLE Orders 

Obviously, this is not a good thing! There are several steps that you can take to protect your server against SQL Injection attacks:

Implement parameter checking on all applications. For example, if you’re asking someone to enter a customer number, make sure the input is numeric before executing the query. You may wish to go a step further and perform additional checks to ensure the customer number is the proper length, valid, etc.

Limit the permissions of the account that executes SQL queries. The rule of least privilege applies. If the account used to execute the query doesn’t have permission to drop tables, the table dropping will not succeed!

Use stored procedures (or similar techniques) to prevent users from directly interacting with SQL code.

As with many security principles, an ounce of prevention is worth a pound of cure. Take the time to verify the code running on your servers before disaster strikes!

Some Thoughts

I am often plagued by brief moments of clarity.  Why does man obsess over what he cannot have?  Why does man perpetually chase what he can never catch?  Is it the never ending struggle to achieve the unattainable or is it rather the human condition that craves the constant pain and agony of disappointment?  This is the question that must be, but can never be answered.  This is the truest form of irony.  

Is this the way the animal brain works?  Always searching for what it cannot find?  Can it not be said then that it is not the object for which we seek that is paramount.  It is however, the journey itself.  For in endeavors such as these, man can truly know himself.  He can learn his limitations.  He can learn how he acts in times of extreme pressure and sadness.  In this self awakening, he will find his true self and therefore his path maybe shown to him.  

It has been said that we only appreciate happiness because we have been sad.  We can only appreciate goodness because we have known evil.  If this is the case, then let us not frown upon times of heartache.  Let us instead intricately examine every facet of these emotions, no matter how painful it may be, so that we may better appreciate times of brevity and happiness.  Using this same logic, the person that has known the most heartache is the one that can know the most joy. Let us pray for the joyous times.   

God is a God of order and balance.  Both sides of the equation must equal each other.  Good and bad, Yin and Yang.  I have had a large amount of darkness.  I am ready for the light.  I have been in darkness for so long that I don't know how to enjoy the light.  This is a sadness.  Our world is in such turmoil and chaos that it affects every living thing.  Some are more aware than others.  Some are aware of the world behind the world.  Sometimes I envy the ignorant.