Drupal is an incredibly powerful open source CMS that allows you to create, manage, and serve content. Unfortunately, so can others if you don’t properly sanitize all user input in order to prevent a malicious attack! Here are some tips on how to stop one of the most common vulnerabilities: SQL injections.
Motivation: Why CMS Security Matters
Regardless of whether your site is a simple blog or a top 50 web property, they all represent an investment of time, money, and creative energy. And, just like any investment of value, it’s important to secure it in order to maintain its integrity.
Now, imagine a situation where all of your hard work can be compromised from a single, well-crafted attack. As a member of the Drupal security team, I can assure you that we’re still receiving email reports every week regarding websites that were hacked from the now infamous “Drupageddon”. Notonly was such an attack possible, it was exploited worldwide within hours of the published disclosure. Of course, this is a particularly extreme example that happened to affect Drupal core. It’s far more common to find vulnerabilities in custom code written by individuals that did not have the time and/or expertise to address.
That’s the doom and gloom. Now let’s imagine a different scenario in which you can sanitize all user input to ensure that you’re protected how a user tries to interact with your website. This is exactly what we’re about to go over for one of the most common forms of attack: a SQL injection.
What is a SQL Injection?
A SQL Injection is similar to “riders” in the US Federal government. A “rider” is a somewhat frustrating legislative procedure where an unrelated provision is attached to another piece of legislation. This tactic is often used to sneak in something unpopular or controversial onto an otherwise legitimate piece of legislation.
Similarly, a SQL injection is where a legitimate operation (e.g. insert a piece of content) has a malicious instruction added to it (e.g. create a new user and give it root access).
Here is a basic example that could theoretically come from a form submission:
$user_input = “JohnDoe”;
$SQL = “Select * FROM {users} WHERE username = ” . $user_input;
// Resulting query = “Select * FROM {users} WHERE username = JohnDoe”;Now most users submitting the form would cause no harm. However, it doesn’t take much for a knowledgeable individual to create a malicious payload.
$user_input = “JohnDoe; UPDATE {users} SET pass = qwerty WHERE uid = 1”;
$SQL = “Select * FROM {users} WHERE username = ” . $user_input;
// Resulting query = "Select * FROM {users} WHERE username = JohnDoe; UPDATE {users} SET pass = qwerty WHERE uid = 1";Notice that the hacker can essentially create any arbitrary command by following this pattern. All an attacker needs to do is place any arbitrary command after the semicolon and they are off to the races. And because a CMS like Drupal relies heavily on the database, an attacker is then able to change just about anything (content, users, configuration, etc).
Sanitizing Data
The key principle to follow in preventing SQL attackes is to not trust user input. Instead, all user input should be sanitized such that no additional or unintentional database changes can be introduced.
With Drupal, there are a few ways to achieve this:
- Manually Sanitize
- Drupal’s Database Abstraction Layer (db_query())
- Drupal Query Builder (DBTNG)
Let’s review each.
Manually Sanitize
Even though this is the first approach we discuss, it is not a recommended approach. In this scenario you are either going around Drupal’s database abstraction layer; OR, you are creating queries as strings of text and performing your own sanitation to remove riders (e.g. additional commands appended to the end of a legitimate command) as well as changes in logic (e.g. alterations to the existing query’s logic to make it pass or fail).
The challenge here is you’re essentially replicating what Drupal provides out of the box with its database abstraction layer. Worse, if you haven’t thought through all the possible attack vectors, you may miss something important.
Bottom line, proceed at your own risk if you decide to go it alone.
Drupal Database Abstraction Layer
Here we use placeholders that properly escape portions of the user input that could add an additional payload/rider or change its intended logic. Returning to our previous example:
$user_input = “JohnDoe; UPDATE {users} SET pass = qwerty WHERE uid = 1”;
db_query(“SELECT * FROM {users} WHERE username = :name”, array(“:name” => $user_input));
// Resulting query = “Select * FROM {users} WHERE username = ‘JohnDoe; UPDATE {users} SET pass = qwerty WHERE uid = 1’“;
You’ll notice a major difference in that last line. Now the user input is no longer appending a new query to the end of an existing query. Instead, Drupal is ensuring the entirety of the user input is being used where it’s supposed to be used (i.e. as a comparison to find a record within the user table). And since there is no username that matches this arbitrary SQL command, the query will return NULL. More importantly, it will do nothing more than what it was designed to do.
It’s also important to note that it is still possible to introduce vulnerabilities when using commands from the database abstraction layer. If one doesn’t use placeholders, the malicious code can be easily reintroduced. For example:
$user_input = “JohnDoe; UPDATE {users} SET pass = qwerty WHERE uid = 1”;
db_query(“Select * FROM {users} WHERE username = ” . $user_input);
// Resulting query = “Select * FROM {users} WHERE username = JohnDoe; UPDATE {users} SET pass = qwerty WHERE uid = 1”;
The takeaway message is to always use placeholders when passing in variables into a query regardless of if they came from user input or from the system. Not only will it ensure consistency within your code, but it will significantly reduce the risk of a SQL injection.
Drupal Query Builder (DBTNG)
One of the new features in Drupal 7 core is the introduction of DBTNG (Database The Next Generation). In this new feature, placeholders are essentially mandatory based on how they are constructed. Let’s rework the example we’ve been using:
$user_input = “JohnDoe; UPDATE {users} SET pass = qwerty WHERE uid = 1”;
$query = db_select(‘users’, ‘u’);
$query->condition(‘name’, $user_input);
$results = $query->execute();
// Resulting query = “Select * FROM {users} WHERE username = ‘JohnDoe; UPDATE {users} SET pass = qwerty WHERE uid = 1’“;By using DBTNG we are getting user input sanitizing out of the box (SA-CORE-2014-005 aside). And similar to using the existing database abstraction layer, this can be used to ensure a consistent, secure codebase.
Detecting Trouble Spots
Reviewing an existing codebase for vulnerabilities can be a daunting task. Luckily, the coder review module can make that process a lot easier. It scans for common patterns and flags them by severity. This includes db_query() statements that attempt to insert variables directly into the query parameter instead of using placeholders.
If you don’t already use the coder review module as part of your workflow, I can’t recommend it enough. The module also scans for other vulnerabilities (e.g. XSS), coding standards, comment standards, and more. At a minimum, it will help you keep your codebase tidy. If used consistently, it will make you a better developer!
Finally, if you ever find a potential issue in a contrib module in your CMS, please file an issue with the Drupal security team! Or, if you need help with your Drupal, don’t hesitate to contact the NEWMEDIA team for a Drupal security audit.