I was first exposed to SQL injection when David Litchfield (see his blog) came and gave a talk on the subject while I was working at the NSA in the summer of 2002. SQL injection is a type of security vulnerability that occurs when some code includes untrusted input, such as a website form field, in a SQL database query without first escaping or removing special characters that may affect SQL syntax (‘, ”, \, etc.). This may subsequently allow an attacker to terminate the original query and inject another query to do something malicious, such as the following: “”; DROP TABLE users;”. After hearing about SQL injection, my friends and I proceeded to go home and type “‘”; select * from users;” into form fields on numerous websites. Though we didn’t see any database table dumps, a surprisingly large number of sites gave us responses with SQL syntax errors, indicating potential vulnerabilities.

Things have changed a lot since the advent of SQL injection attacks. Many security researchers have investigated the topic and written papers on how to correct the problem. As the title of this blog post suggests, however, the SQL injection problem has been solved. Sometimes it makes sense for people to continue researching a problem when existing solutions have serious usability constraints or add significant development overhead. That is not the case for SQL injection. The rest of this article briefly touches on some SQL injection research and then shows you how to avoid vulnerabilities on your website using a common PHP library as an example.

There has been some recent research on tracking information flow in web applications for security purposes (see Multi-Module Vulnerability Analysis of Web-Based Applications). One of the goals of this research was to prevent SQL injection attacks. The system worked by tracking user inputs and making sure that they were escaped before being passed into an SQL query. The research paper first seemed a bit fishy when the authors stated their assumption that if an input was passed through a designated sanitization function, then it was assumed to be safe for passing into an SQL query. Hopefully you see where this is going… The authors continued to present sophisticated analysis techniques for tracking information flow between different modules in web applications and concluded that their system was able to detect many SQL injection and cross-site scripting vulnerabilities. (XSS vulnerabilities are a topic for another day.)

Despite all of their hard work, researchers who study SQL injection usually fail to answer a fundamental question: Why don’t developers just escape all inputs right before passing them into SQL query calls? Maybe updating legacy code would be cumbersome, but this did not seem to stop people from replacing strcpy and strcat to get rid of buffer overflows. The real answer is that developers should, and in many cases do use safe query functions that completely eliminate the possibility SQL injection when used properly.

If you are using PHP, one popular database function library that protects against SQL injection is PearDB (similar libraries exist for other languages). In PearDB, the syntax of the query call is such that you include placeholders in the query string, and then pass in their values in a separate array. The library then automatically escapes all query parameters. An example of correct call syntax in this database library (others libraries are probably similar) is the following. The ‘?’ is a placeholder for the input.

query(‘SELECT id FROM users WHERE name=?’, array($username));

SQL injection becomes impossible if you make all of your queries in this manner. It is still possible, however, if you include inputs directly in the query string without passing them through with placeholders. The following is an incorrect use of the query function that results in SQL injection:

query(‘SELECT id FROM users WHERE name=”’ . $username . ‘”’);

It is worth mentioning that a number of SQL query interfaces exist that prevent query stacking (executing multiple queries in one call) and thus partially mitigate the effects of SQL injection vulnerabilities. An example is the PHP function mysql_query(). Without query stacking, you do not get the chance to fire off another arbitrary query. For select queries, which are most common, this constrains SQL injection to only affecting the set of returned rows, which makes exploitation much harder. However, you still need to escape query parameters for these function calls! Let us consider this example from a hypothetical password recovery query to illustrate the problem:

Query(‘SELECT password FROM users WHERE (id=’ . $userid . ‘ AND motherMaidenName=”’ . $maidenNameAnswer . ‘”)’);

In this query, the userid variable is set securely by the web application. However, the answer to the mother’s maiden name is provided by an untrusted user input from a web form. An attacker can set the mother’s maiden name to “aaa”) or username=”admin”, thus yielding the following query that steals the administrative account password:

SELECT password FROM users WHERE (id=123 AND motherMaidenName=”aaa”) or username=”admin”

The moral of the story is that there is a simple method for protecting against all SQL injection attacks on web applications. You need to use a database library with a secure query API and pass query inputs in through placeholders. Any new web applications that contain SQL injection vulnerabilities do so because of ignorance about proper database library usage. If you have any feedback about experiences with SQL injection or secure web application development, we be happy to hear your comments.

This entry was posted on Friday, May 9th, 2008 at 7:46 pm and is filed under Databases, Web Applications, Vulnerabilities, Articles. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.