SQL Injection - Understanding and Protection

Category: Web Security Readings - Wed, 10 Sep 2014 - by Robert Abela

As we mentioned in our previous article on the history of SQL injections, the SQL injection web vulnerability have consistently been on the top ten list of attack styles for a solid 14 years, and it shows no sign of leaving that position any time soon.  Furthermore, 6 years ago our CEO Ferruh Mavituna has released this must have SQL Injection Cheat Sheet. Needless to say, the importance of the SQL injection vulnerability, understanding it, and protecting against it is a priority which we cannot stress enough.

Simple SQL Injection Example

In this article, we will continue our previously-discussed technical side of SQL injections, as it is imperative to ensure complete knowledge of what a SQL injection is. Otherwise, how can you protect against it? In order to completely understand what a SQL injection is, one must know how a SQL injection works.  Taken from theOWASP example page, consider if you had the following PHP code:

mysql_query("SELECT * FROM accounts WHERE custID='" . $_GET['id'] . "'");

With that query, you could go to the following URL:

http://example.com/app/accountView?id=' or '1'='1

Notice the placement of single quotes in the URL.  This turns the SQL query into the following:

mysql_query("SELECT * FROM accounts WHERE custID='' or '1'='1'");

Protecting Web Applications SQL Injection

Surely, there must be a way to simply sanitize user input and ensure an SQL injection is infeasible.  Unfortunately, that is not always the case.  There are perhaps an infinite number of ways to sanitize user input, from globally applying PHP's addslashes() to everything (which may yield undesirable results), all the way down to applying the sanitization to "clean" variables at the time of assembling the SQL query itself, such as wrapping the above $_GET['id'] in PHP's mysql_escape_string() function.  However, applying sanitization at the query itself is a very poor coding practice and difficult to maintain or keep track of.  This is where database systems have employed the use of prepared statements.

Prepared Statements

When you think of prepared statements, think of how printf works and how it formats strings.  Literally, you assemble your string with placeholders for the data to be inserted, and apply the data in the same sequence as the placeholders.  SQL prepared statements operate on a very similar concept, where instead of directly assembling your query string and executing it, you store a prepared statement, feed it with the data, and it assembles and sanitizes it for you upon execution.  Great!  Now there should never be another SQL injection again.  So why, then, are SQL injection attacks still, for over 14 years, constantly one of the biggest and most prevalent attack methods?

Insecure SQL Queries are a Problem

Simply put, it perhaps boils down to web application developer laziness and lack of education and awareness.  Insecure SQL queries are so extremely easy to create, and secure SQL queries are still mildly complex (or at least more complex than generic and typical in-line and often insecure queries).  In the example above, a malicious hacker can inject anything he or she desires in the same line as the SQL query itself.

Example and Explanation of an SQL Prepared Statement

However, with prepared statements, there are multiple steps.  No major database system operates like printf (with everything occurring within the same statement on the same line).  MySQL, directly, requires at least two commands (one PREPARE and one EXECUTE).  PHP, via the PDO library, also requires a similar stacking approach, such as the following:

$stmt = $dbh->prepare("SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?");

$stmt->execute(array($username, $password));

At first glance, this is not inherently problematic and, on average, increases each SQL query by only an extra line or two.  However, as this requires extra caution and effort on behalf of already tired and taxed developers, often times they may get a little lazy and cut corners, opting instead to just use the easy procedural mysql_query() as opposed to the more advanced object-oriented PDO prepare().

Beside of this many developers just stick with what they know to get the job done and they generally learn the easiest and most straightforward way to execute SQL queries rather than showing genuine interest in improving what they know. But this could also be an issue of lack of awareness.

Deeper Into the Rabbit Hole of SQL Injection Security

Say, however, this isn't the case of lazy developers, or even lack of prepared statements -- or, more precisely, say the software itself and its security is out of your hands.  Perhaps it is impractical or infeasible to completely secure the SQL queries in the code you use (by one comparison, Drupal has had over 20,000 lines of code committed, WordPress has had over 60,000 lines, and Joomla! has had over 180,000 lines), or, it may simply be impossible because it is encoded or so.  Whatever the case is, if you do not have control over the code you may need to employ different, more advanced "outside the box" protections.

Non Development Related SQL Injection Protection

Running Updated Software

First and foremost, always ensure you are running the most up-to-date software you can.  If you are using WordPress or any other CMS framework, keep it updated!  The same goes for PHP, your web server software such as Apache and nginx, and your database server (MySQL, Postgres, or others).  The more recent the version of  your software is, the less chance of having a vulnerability, or at least a widely-known one.  This also extends down to your other software as well, such as SSH, OpenSSL, Postfix, and even the operating system itself.

Block URLs at Web Server Level

Next, you should employ methods to ensure you are as minimally vulnerable to potential SQL injection attacks as possible.  You could perhaps go for a quick and easy match against common SQL query keywords in URLs and just simply block them.  For example, if you ran Apache as your web server, you could use the following two mod_rewrite lines in your VirtualHost directive, as explained below:

RewriteCond %{QUERY_STRING} [^a-z](declare¦char¦set¦cast¦convert¦delete¦drop¦exec¦insert¦meta¦script¦select¦truncate¦update)[^a-z] [NC]

RewriteRule (.*) - [F]

This is indeed quite clever, but it does not protect against everything.  SQL injection parameters can still be passed via POST values or other RESTful-type URLs, not to mention there are tons of different ways to bypass this kind of generic blacklisting.

Securing the Database and Privileges

You can also ensure your database itself is as secure as possible.  In the information security field, there exists a concept known as the principle of least privilege.  Effectively, this principle states that a user or program should have only the absolute very least amount of privileges necessary to complete its tasks.  We already do this practically every day with Linux file permissions, so the concept is in no way foreign, and is equally applicable to databases.  There is probably no reason why your log table should have anything beyond INSERT privileges, so you should not simply GRANT ALL PRIVILEGES because it is easier.

Segregating Sensitive and Confidential Data

Similarly, you might consider separation of data as a defense in depth approach, rather than conglomeration it into a single source.  When you step back and think about it, it is probably not a very wise idea to keep your (hopefully PCI-compliant) customer credit card data stored in the same database as your forums, which are running an outdated and highly vulnerable version of phpBB, right?  Not only would the principle of least privilege be very applicable in this situation, but even going so far as to entirely separate out your more sensitive data is a very sage approach.  To think about it another way, would you keep all your most important paperwork inside your house, or would you keep some in a safe deposit box, too?  The same concept applies with sensitive data.

Analyzing HTTP Requests Before Hitting the Web Application

Another option is the use of more detailed firewall systems.  Typically this might include some adaptive solution that rides on top of iptables or ipfw (depending if you are using Linux or a BSD variant, respectively), or perhaps a reactive Host Intrusion Detection System (HIDS) such as OSSEC, although these are often more complicated than desired and not exactly purpose-built for these uses.  Instead, you may wish to utilize a Web Application Firewall, which is designed specifically for these tasks.  While there exist several enterprise-level solutions that are both a WAF and database firewall (sitting between your web application and your database), there are many open-source solutions, such as ModSecurity and IronBee, that perform remarkably well.

The Truth about SQL Injection Web Vulnerability

There exists no real magic wand answer to fix SQL injections and protect your web applications from them, although PHP is attempting a more brute force approach of their own.  As of PHP 5.5, procedural MySQL is deprecated and soon to be removed entirely, which will require future software projects to switch either to MySQLi or PDO MySQL in order to continue to work.  This is good since it forces developers into a system that handles prepared statements with relative ease, although it still requires the use of stacking a few operations.  However, as many developers operate within a coding golf style; attempting to complete work in as few lines or characters as possible, many unfortunately will still opt for a single-line straight query over a two-line prepare.

There still exist other options to account for any development shortcomings, including but not limited to privilege limitations, data separation, web application firewalls, and many other approaches.  But until these options are as consistently employed as SQL injection attacks, we indeed may never see the day that injection-style attacks escape the OWASP's Top 10 list.  Be the change that is needed to ensure data and web application security, and keep your databases safe from SQL injections!


Dead accurate, fast & easy-to-use Web Application Security Scanner