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

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.

  • System and Application Updates: Keeping systems and applications current with updates is one of the first lines of defense that should be implemented by individuals and organizations in order to prevent system and application compromises. In the case of Drupageddon, either upgrading to Drupal version 7.32 or installing the patch provided by Drupal developers would have mitigated against the Drupageddon vulnerability immediately. Although the Drupageddon vulnerability existed since 2011, there is no evidence of any significant exploitation of the vulnerability until after the public disclosure of Drupageddon by SektionEins.

  • Intrusion Detection Systems: Most current intrusion detection systems include functionality to detect attempted SQL injection attacks. These systems can provide early warnings of attempted SQL injection attacks, and if paired with intrusion prevention functionality, often can prevent the attacks from occurring.

  • Limitation of Database Privileges: Privileges of database users used for applications should be limited to as restrictive a set of privileges as possible that will allow for the performance of required database activities in order to support the functionality of the application. For instance, if the only database activities that are required for the application in question are reads from the database, consider limiting the database account used by the application to a read-only account. This will not prevent SQL injection attacks aimed at inappropriate access to information, but it will prevent SQL injection attacks that are intended to cause unauthorized changes to the database.

  • System and Application Monitoring: Although system and application logging and monitoring will not, in and of themselves, prevent an SQL injection attack from occurring, they will help in the process of detecting attempted attacks. Additionally, use of functionality like file integrity monitoring can help detect the results of system compromises due to SQL injection attacks.

  • Code Audits: Auditing of source code utilized in an application can help identify security vulnerabilities in the application in question. In fact, the Drupageddon vulnerability was discovered due to a source code audit that was performed against the Drupal code base. Source code audits typically should be performed by a party other than those responsible for the creation of the source code in question, as this provides a fresh perspective on the source code. Unfortunately, source code audits usually are very expensive and time-consuming.

Resources

Usage of Content Management Systems for Web Sites: http://w3techs.com/technologies/overview/content_management/all

Drupal Usage Statistics: http://trends.builtwith.com/cms/Drupal

OWASP Top Ten List: https://www.owasp.org/index.php/Top_10_2013-Top_10"

SA-CORE-2014-005 — Drupal Core — SQL Injection: https://www.drupal.org/SA-CORE-2014-005

Drupal Core — Highly Critical — Public Service announcement — PSA-2014-003: https://www.drupal.org/PSA-2014-003

Drupalgeddon: https://www.drupal.org/project/drupalgeddon

Advisory 01/2014: Drupal — Pre Auth SQL Injection Vulnerability: https://www.sektioneins.de/en/advisories/advisory-012014-drupal-pre-auth-sql-injection-vulnerability.html

Drupal 7.31 Pre Auth SQL Injection Vulnerability: https://www.sektioneins.de/en/blog/14-10-15-drupal-sql-injection-vulnerability.html

Drupal 7.32 Two Weeks Later — PoC: https://www.sektioneins.de/en/blog/14-11-03-drupal-sql-injection-vulnerability-PoC.html

______________________

Shea Nangle is an information security engineer with BlackMesh. His areas of interest include compliance, log analysis and open-source intelligence. In his spare time, you often can find him cycling or homebrewing mead.