Drupal SQLi Attack Explained With Example

Drupal is a popular content management system used to build websites, very similar
to Wordpress and Joomla. It’s written in PHP and is modular based, meaning new
functionality can be added to a Drupal site by installing a module. The Drupal community
has written thousands and made them available for free. Examples include e-commerce,
third party integration, content production, etc. However, every Drupal install contains
the same set of core modules used to run the platform and requires a connection to a
database. These are typically referred to as Drupal core.
In 2014, the Drupal security team released an urgent security update to Drupal core
indicating all Drupal sites were vulnerable to a SQL injection which could be achieved by
anonymous users. The impact of the vulnerability could allow an attacker to take over
any Drupal site that wasn’t updated.
In terms of the vulnerability, Stefan Horst had discovered that the Drupal developers
has incorrectly implemented wrapper functionality for database queries which could
be abused by attackers. More specifically, Drupal was using PHP Data Objects (PDO)
as an interface for accessing the database. Drupal core developers wrote code which
called those PDO functions and that Drupal code was to be used any time other
developers were writing code to interact with a Drupal database. This is a common
practice in software development. The reason for this was to allow Drupal to be used
with different types of databases (MySQL, Postgres, etc.), remove complexity and provide
standardization.
Now, that said, turns out, Stefan discovered that the Drupal wrapper code made an
incorrect assumption about array data being passed to a SQL query. Here’s the original
code :


foreach ($data as $i => $value) {
[...]
$new_keys[$key . '_' . $i] = $value;
}
Can you spot the error (I wouldn’t have been able to)? Developers made the assumption
that the array data would always contain numerical keys, like 0, 1, 2, etc. (the $i value)
and so they joined the $key variable to the $i and made that equal to the value. Here’s
what a typically query would look like from Drupal’s db_query function:
db_query("SELECT * FROM {users} WHERE name IN (:name)", array(':name'=>array('us\
er1','user2')));
Here, the db_query function takes a database query SELECT * FROM {users} where
name IN (:name) and an array of values to substitute for the placeholders in the query.
In PHP, when you declare an array as array(‘value’, ‘value2’, ‘value3’), it actually creates [0
⇒ ‘value’, 1 ⇒ ‘value2’, 2 ⇒ ‘value3’] where each value is accessible by the numerical key.
So in this case, the :name variable was substituted by values in the array [0 ⇒ ‘user1’, 1
⇒ ‘user2’]. What you would get from this is:
SELECT * FROM users WHERE name IN (:name_0, :name_1)
So good, so far. The problem arises when you get an array which does not have numerical
keys, like the following:
db_query("SELECT * FROM {users} where name IN (:name)",
array(':name'=>array('test) -- ' => 'user1','test' => 'user2')));
In this case, :name is an array and its keys are ‘test) –’, ‘test’. Can you see where this is
going? When Drupal received this and processed the array to create the query, what we
would get is:
SELECT * FROM users WHERE name IN (:name_test) -- , :name_test)
It might be tricky to see why this is so let’s walk through it. Based on the foreach described
above, Drupal would go through each element in the array one by one. So, for the first
iteration $i = test) – and $value = user1. Now, $key is (:name) from the query and
combining with $i, we get name_test) –. For the second iteration, $i = test and $value
= user2. So, combining $key with $i, we get name_test. The result is a placeholder with
:name_test which equals user2.

Now, with all that said, the fact that Drupal was wrapping the PHP PDO objects comes
into play because PDO allows for multiple queries. So, an attacker could pass malicious
input, like an actual SQL query to create a user admin user for an array key, which gets
interpreted and executed as multiple queries.
Takeaways
This example was interesting because it wasn’t a matter of submitting a single
quote and breaking a query. Rather, it was all about how Drupal’s code was
handling arrays passed to internal functions. That isn’t easy to spot with black
box testing (where you don’t have access to see the code). The takeaway from
this is to be on the lookout for opportunities to alter the structure of input passed
to a site. So, where a URL takes ?name as a parameter, trying passing an array
like ?name[] to see how the site handles it. It may not result in SQLi, but could

lead to other interesting behaviour.