Q&A: SQL injection

Justin Clarke is a co-founder and Director at Gotham Digital Science. He has over twelve years of experience in assessing the security of networks, web applications, and wireless networks. He is the author of the open source SQLBrute blind SQL injection testing tool, several books, and is the Chapter Leader for the London chapter of OWASP. In this interview he discusses SQL injection and his latest book.

Exactly how big of a problem is SQL injection? Can you provide a rough picture to our readers not familiar with the issue?
SQL injection is a huge problem. It is both fairly common, as well as having the potential to be a huge risk to a business either directly or indirectly. A good example of the risk of SQL Injection is the recent revelation that SQL injection was a key part in a number of the recent large credit card data breaches – Heartland, Hannaford, TJX and others. In these cases, SQL injection was used in order to get further into an organization and do something else, however in a lot of cases SQL injection can be serious enough all on its own. I’ve seen examples such as a small US bank that had over 5,000 mortgage applications stolen from an application via SQL Injection, and hence all of the information needed to steal all of those people’s identities.

For those not familiar with SQL injection, it occurs when unvalidated user input is included within Structured Query Language (SQL) statements that are dynamically assembled within the application. For example, say the application is assembling a query for product information with a “productid” variable supplied by the user with a query something like this:

string query = “SELECT * from products WHERE productid =” + productid

Where the “productid” variable is as the developer expects, this works fine. However if the “productid” variable contains SQL language statements instead of the expected values, an attacker can modify what the query does, and even execute totally different database functionality in some cases. For example, on Microsoft SQL Server the attacker may be able to execute operating system commands like the following:

Attacker supplies the following value for “productid” -> 1; exec xp_cmdshell(‘cmd.exe /c ping 10.11.12.13’)–

What’s are the main motivations behind SQL injection attacks? Precisely what are the attackers after?
The main motivation is usually to access the data in the application or applications that are present on the database server supporting the application. This could take the form of obtaining access to data in the application (for example, getting people’s usernames and password), or modifying or deleting information in the database (for example, inserting a link to a malicious JavaScript file into the application’s front page).

We are also seeing an increasing amount of reports where SQL injection is used as a method of getting into an organization’s network. In this case, its likely they are using common database functionality to run underlying operating system content in order to gain a foothold on the database server. As database servers have a tendency to be in a network segment closer to the organization’s internal network (or may be on the internal network in some cases), this can allow an attacker to then gain access further into the organization, and then attack other systems they are interested in.

What are the basic steps developers can take in order to prevent SQL injection? Is there a solution to the problem or is it just an ongoing battle?
Preventing the majority of cases of SQL injection is actually quite straightforward – avoid the use of dynamic SQL with user input included in it. The use of parameterized SQL statements (also known as bind variables) is usually the best way of doing this, and in many cases also has the benefit of better database performance as well. Using parameterized SQL means that the values passed in as parameters are not treated as part of the executable SQL statement, and therefore can’t cause SQL Injection to happen. If that isn’t possible, the other alternative is to ensure that dangerous characters in user input included in dynamic SQL is encoded to prevent SQL injection. This approach is far more prone to errors in the implementation of the encoding though, and therefore you might want to use something like OWASP ESAPI as a reference implementation on how to do this safely.

The difficulty with taking either of these approaches is in identifying all of the places where your applications are vulnerable to SQL Injection, or alternatively where all of the database access occurs and then changing how this is done. This is potentially a massive effort for even a small organization, so I think we are likely to have SQL injection being a problem for a long time to come.

What software solutions (both free and commercial) would you recommend for security professionals working to uncover SQL injection issues?
For commercial solutions, you will probably want to look at either the dynamic scanners (such as HP WebInspect or IBM AppScan) and/or static code analysis tools such as Fortify SCA, or Ounce (now owned by IBM) depending on your preferred approach for finding SQL injection issues.

On the free side, the list if far more limited. From the source code analysis side of things if you’re on .NET, Microsoft has released their CAT.NET static analyzer. Also available are tools such as YASCA (Yet Another Source Code Analyzer) and OWASP Orizon that look promising. On the dynamic scanning side, the best free tools for finding SQL Injection are penetration testing tools such as OWASP SQLiX, although most of the tools available assume that you’ve managed to find the issue yourself manually, and only help you exploit them.

What challenges did you face when writing “SQL Injection Attacks and Defense“? What did you learn?
The main challenge with “SQL Injection Attacks and Defense” was to make sure that we covered as much as possible. There is a huge amount of SQL Injection knowledge out there, and we made the effort to capture as much as possible in the book, but that is always a moving target based on the timescales you work with in a book project. The team of contributing authors on the book were a huge help here – there was a lot of cross review back and forth during the process of writing the book to ensure we’d covered what we wanted to in each chapter.

Are you satisfied with the response from the technical reviewers and the audience? What would you do differently next time around?
We’ve had a very positive reception to the book so far. I’ve had a few folks drop me a note via email thanking us for the book, and I’m always careful to note that this was a team effort with a lot of smart folks pulling together to get it written. One thing that I would do differently next time would be to ship example applications with the book (or have them available on a supporting site) so that readers would be able to walk through examples themselves. This is something that we did discuss during writing, but with the deadlines we were working to this just wasn’t possible this time around.

There are many highly knowledgeable security professionals out there that still haven’t tackled the challenge of writing a book. What advice would you give them based on your writing experience?
The one thing I would advise people of is that it takes a lot of time to write a book, so the idea of writing a book on your own in your spare time is usually not realistic. My advice would be to assemble a small group of people you trust, preferably with at least one of you having been involved in a book project before, and write the book as a collaboration. That is also another way to get started – contribute a chapter to a book someone else is writing, so if someone you know is writing a book on a topic you know a lot about, ask is you can get involved.