Drupageddon: SQL Injection, Database Abstraction and Hundreds of Thousands of Web Sites

Drupal is a very widely used open-source content management system. It initially was released in 2001, and recent statistics show Drupal as the third-most popular content management system, with just less than 800,000 Web sites utilizing Drupal as a content management system.

Drupal is written in PHP, and it is architected to use a database back end to store Web site content and settings, whether a full-fledged database management system (such as MySQL) or an embedded DBMS (such as SQLite). In recent versions, Drupal has provided a database abstraction layer in order to facilitate the use of any of a number of database management systems to support a given Drupal installation. Database abstraction layers provide a consistent programming interface that can be used to communicate with a variety of database systems without development of code specific to a given database management system.

Due to vulnerabilities in the database abstraction layer introduced in version 7 of Drupal, Drupal 7 prior to version 7.32 was vulnerable to an SQL injection attack. This article provides an introduction to SQL injection attacks, an examination of the Drupageddon vulnerability specifically and an explanation of a number of potential defenses against SQL injection attacks.

SQL injection is an attack methodology in which malicious SQL code is included in user input, leading to the execution of said SQL code as part of SQL statements used by an application. SQL injection attacks can lead to privilege bypass and/or escalation, disclosure of confidential information and corruption of database information, among other effects.

Command injection attacks, such as SQL injection, routinely place at or near the top of the OWASP (Open Web Application Security Project) Top Ten List of Web application security risks. SQL injection attacks are likely the most well-known type of command injection attacks, but injection attacks can occur any time data is supplied to an interpreter by an application. The recent Bash vulnerability known as Shellshock is an example of a command injection attack that is not related to SQL injection.

An example SQL injection attack starts with code utilizing an SQL statement, such as:

$db_statement = "SELECT COUNT(1) FROM `users` WHERE 
 ↪`username` = '$username' AND `password` ='$password'";

In an SQL injection attack against code such as this, the attacker supplies input, such as the following, to the application:

$username = "badUser";
$password = "' OR '1'='1";

Using this example, the SQL statement executed becomes the following:

SELECT COUNT (1) FROM `users` WHERE `username`='badUser' 
 ↪AND `password`='' OR '1'='1';

In the above example, this results in returning a count of all rows in the “users” table, regardless of the user name or password supplied, since the conditional '1'='1' always returns as true. If the query shown in this example is used for authentication purposes, the example SQL injection attack has just bypassed the authentication process for the application in question.

SQL injection attacks, and other command injection attacks in general, represent a significant risk for Web applications. Exploitation of SQL injection vulnerabilities is relatively easy for an attacker to perform, and both the attack itself and searches for vulnerable code are easily automated. Additionally, the impact of SQL injection attacks is quite often very severe, as is seen in the authentication example above, as well as in the specific example of Drupageddon.

The Drupageddon vulnerability officially was discovered by SektionEins GmbH while the company was performing a security audit for a customer that was utilizing Drupal as a content management system. SektionEins GmbH reported the vulnerability to Drupal developers on September 16, 2014. The vulnerability was disclosed publicly by Drupal on October 15, 2014, using the Drupal advisory identifier DRUPAL-SA-CORE-2014-005 and the CVE identifier CVE-2014-3704. The public disclosure included a description of the vulnerability, as well as recommendations for vulnerability mitigation. The primary recommendation for mitigation of this vulnerability was an immediate upgrade to Drupal version 7.32. For administrators of Web sites that could not be upgraded to Drupal version 7.32 immediately, a patch was provided to resolve the SQL injection vulnerability.

SektionEins nicknamed this vulnerability Drupageddon due to the potential impact of exploitation of this vulnerability upon Drupal-based Web sites. (Note: in a number of instances in the press, this vulnerability was referred to as “Drupalgeddon”, which is inaccurate. The term “Drupalgeddon” refers to a diagnostic tool intended to be used in order to diagnose Drupal instances that may have been compromised due to the Drupageddon vulnerability.)

Successful exploitation of this attack could result in execution of arbitrary PHP commands, privilege escalation, installation of system backdoors and other exploits. Additionally, exploitation of this vulnerability did not require any sort of successful authentication to the target Drupal instance(s) prior to exploitation.

It was estimated that within several hours of the announcement of the Drupageddon vulnerability, active and automated exploits for this vulnerability were being utilized by attackers to compromise Drupal-based Web sites.

On October 29, 2014, the Drupal Security Team released advisory identifier DRUPAL-PSA-2014-003. This advisory informed administrators of Drupal-based Web sites that all Drupal-based Web sites utilizing vulnerable versions of Drupal should be considered compromised if they were not patched/upgraded before 2300 UTC on October 15, 2014 (seven hours following the initial announcement of the vulnerability in SA-CORE-2014-005).

In the case of the Drupageddon vulnerability, the database abstraction layer provided by Drupal included a function called expandArguments that was used in order to expand arrays that provide arguments to SQL queries utilized in supporting the Drupal installation. Due to the way this function was written, supplying an array with keys (rather than an array with no keys) as input to the function could be used in order to perform an SQL injection attack.

A potential (non-malicious) use of the expandArguments function would be as follows:

$query = "SELECT COUNT(1) FROM `users` WHERE `id` IN (:userids)"; 
$args = [ 'userids' => [ 1, 2, 3, ] ]; 
$db->expandArguments($query, $args);

This would result in the following SQL statement:

SELECT COUNT(1) FROM `users` WHERE `id` IN 
 ↪(:userids_0, :userids_1, :userids_2);

However, by supplying a carefully crafted argument array, an attacker could perform an SQL injection attack:

$query = "SELECT COUNT(1) FROM `users` WHERE `id` 
 ↪IN (:userids)";
$args = [ 'userids' => [ '0); DROP TABLE 
 ↪importantInformation; --' => 1 ], ];
$db->expandArguments($query, $args);

This would result in the following SQL statement:

SELECT COUNT (1) FROM `users` WHERE `id` IN (:userids_0); 
 ↪DROP TABLE importantInformation; --)

The -- marks the remainder of the line as an SQL comment, avoiding the syntax error due to the unmatched right parenthesis. The results of the execution of a malicious query such as this obviously could be catastrophic.

A number of strategies can be used to minimize the risk of SQL command injection attacks. These include input sanitization and whitelisting, use of parameterized queries and defense in depth.

Input Sanitization and Whitelisting:

One strategy that can be used for prevention of SQL injection attacks is the sanitization and whitelisting of user input. This strategy is implemented by analyzing both expected or valid input that will be provided by users as well as input that may be provided by attackers attempting to compromise your Web-based application. Following this initial analysis, sanitization code will need to be added in order to remove or escape any harmful/unwanted input by a user prior to use of said input in any database queries. In the case of the SQL injection example given earlier in this article, there are two potential sanitization and whitelisting processes that could be utilized.

In the SQL injection example given earlier, let's assume you previously have told users of the Web application that valid characters for user names are a–z, A–Z, 0–9 and “.”. This would represent an excellent opportunity for the use of whitelist-based input validation. In this method of input validation, you would construct a whitelist of allowed characters for the user input and would allow only user input limited to those characters to be passed to the database for processing. In this case, you either would discard any characters provided by users as their user name aside from a–z, A–Z, 0–9 and “.”, or you simply would refuse to perform any processing following user input that includes any characters that are not included in your whitelist of allowed characters. Using this example, an attack in which the following is provided as input for the username value:

$username = "x'; DROP TABLE importantInformation; 
 ↪SELECT * FROM users WHERE username = 'badUser'";
$password = "Test";

either would be refused or would be sanitized (if inappropriate characters are discarded) to the following:

$username = "xDROPTABLEimportantInformation
↪SELECTFROMusersWHEREusernamebadUser";

If sanitization is used in this example, this would result in the following SQL statement being executed:

SELECT COUNT(1) FROM `users` WHERE `username`=
↪'xDROPTABLEimportantInformationSELECTFROMusersWHEREusernamebadUser' 
 ↪AND `password`='Test';

Without sanitization, the following SQL statements would be executed:

SELECT COUNT(1) FROM users WHERE username='$username = 
 ↪"x'; DROP TABLE importantInformation; SELECT COUNT(1) 
 ↪FROM users WHERE username = 'badUser' AND password = Test';

This results in two SELECT statements being executed, and also results in the deletion of the importantInformation table.

(Note: this SQL example also represents other security problems in that passwords in the database appear to be stored in plain text. However, that is outside the scope of this article, and encryption of passwords would have no impact on vulnerability to SQL injection attacks.)

In the user authentication example, additional processing is needed in order to handle the password provided by the user, however. Assuming that you allow all keyboard characters in an effort to allow for as complex passwords as possible, you cannot simply refuse password input containing potentially dangerous characters. In this case, you will want to sanitize user input by escaping said input prior to query processing. In this example, the following input:

$username = "badUser";
$password = "' OR '1'='1";

would become escaped to the following:

$username = "badUser";
$password = "\' OR \'1\'=\'1";

This then would result in the following SQL statement being executed:

SELECT COUNT (1) FROM users WHERE username='badUser' 
 ↪AND password='\' OR \'1\'=\'1';

This version of the SQL statement would result in returning a count of the number of rows in the users table where the contents of the user name field is equal to the string “badUser”, and the contents of the password field are equal to the literal string “' OR '1'='1” (that is, the attack has been blocked).

Use of Parameterized Queries

Another strategy for guarding against SQL injection is the use of parameterized queries. With parameterized queries, SQL statements are predefined and stored on the database server with placeholders representing the parameters that will be utilized in the query. When it comes time for the SQL statement(s) in question to be executed, relevant user input is added to the queries prior to execution, with any relevant escaping of user input being handled automatically by the database server.

In the user authentication example shown previously, the parameterized version of the query would resemble something like the following:

SELECT * FROM users WHERE username=?un? AND password=?pw?;

When it comes time for the SQL statement to be executed, the database management system performs any escaping needed for the parameter(s) in question, and the SQL statement then is executed with the escaped parameter(s) taking the place of the placeholders.

Defense in Depth:

At a very high level, the best plan for preventing SQL injection attacks is an overall strategy of defense in depth. This approach relies on deploying a number of different defense mechanisms simultaneously, with the overall strategy being that if an attacker is able to defeat one of the defense mechanisms, the other defense mechanisms still will be in place and still will be able to defend against/detect attempted attacks. In the example of Drupageddon, the following parallel defense strategies in addition to the previously mentioned defense strategies would have helped to minimize the risk of system compromise due to SQL injection.