An introduction to preventing SQL Injection in Drupal 7 modules

If there is one fear that most developers experience, it is the fear of security vulnerabilities with the code you have written. Bugs are one thing, but security holes that can be used to expose user data or wreck havoc on the database are the cause of many a nightmare. One of the most common forms of attack is SQL Injection. SQL Injection involves injecting malicious commands into a query, usually via some form of user entry.

Fortunately, Drupal provides the tools to protect your website or application against SQL Injection, as long as you follow best practice.

This is best illustrated with an example. This is a simple, harmless query that selects everything from the node table:

  1. $query = db_query("SELECT * FROM node");

Let’s say you want to only select a specific node, by its node ID (nid) and display the title of that node in a Drupal message. The nid is found as a query string in the URL. The URL is example.com/?nid=12

To simulate this, you can create a simple implementation of hook_init() - please note that this insecure code!

  1. function blogs_init() {
  2. if (!empty($_GET['nid'])) {
  3. $nid = $_GET['nid'];
  4. $title = db_query("SELECT title FROM node WHERE nid = $nid")->fetchField();
  5. drupal_set_message($title);
  6. }
  7. }

I have added this function to a custom module called blogs. hook_init() is run at the start of every page request, if you add ?nid=4 to any page on the site, you will see the title for the node with a nid of 4 in a Drupal message.

Show node title

There is a serious security vulnerability in this code.
Take a close look at the following:

  1. $title = db_query("SELECT title FROM node where nid = $nid");

At first glance, this might appear harmless enough, but it is actually very dangerous. A user with malicious intent could add a SQL query to the query string in the URL.

Here is an example where the user deletes the first node from the node table:
example.com/?nid=4;DELETE FROM node WHERE nid = 1;

This will turn the SQL query into:

  1. SELECT title FROM node where nid = 4;
  2. DELETE FROM node WHERE nid = 1;

Let's look at another example, where a user with malicious intent get's the title for the first node (node ID 1) and then deletes the same node.

A normal search without any malicious commands:
Normal query

A malicious search:
Bad query

So the attacker will delete the node with node ID (nid) of 1. Even worse, they could delete all data from any table! Not good at all.

Fortunately it is very easy to protect your site against this sort of attack.

Securing against SQL Injection

If you use Drupal’s database layer correctly, you can prevent SQL Injection attacks. Then underlying database system used by Drupal separates the SQL query from any variable data it contains by using prepared statements and variable data is added to the query securely. Always use Drupal’s functions to access the database to protect yourself against SQL injection.

Here is a secure way of getting a specific node title:

  1. $nid = $_GET['nid']
  2. $title = db_query('SELECT title FROM node WHERE nid = :nid', array(':nid' => $nid))->fetchField();

In the above example, we are passing the $nid variable through a placeholder, ':nid'. Drupal then sanitises it to prevent SQL Injection. The use of placeholders ensures that any user supplied data is separated from the query itself, which avoids SQL injection.

Let's add that to blogs_init() and remove the SQL Injection vulnerability:

  1. function blogs_init() {
  2. if (!empty($_GET['nid'])) {
  3. $nid = $_GET['nid'];
  4. $title = db_query('SELECT title FROM {node} WHERE nid = :nid', array(':nid' => $nid))->fetchField();
  5. drupal_set_message($title);
  6. }
  7. }

Exclude unpublished nodes

The code snippet above will return titles of unpublished nodes as well as published nodes (thanks larowlan for spotting this). In most cases, you will not want to return unpublished nodes. In order to exclude unpublished nodes, add AND status = 1 to the query. A status of 1 simply means the node is published, and 0 means it is unpublished.

  1. function blogs_init() {
  2. if (!empty($_GET['nid'])) {
  3. $nid = $_GET['nid'];
  4. $title = db_query('SELECT title FROM {node} WHERE nid = :nid AND status = 1', array(':nid' => $nid))->fetchField();
  5. drupal_set_message($title);
  6. }
  7. }

Further reading

This is a fairly simple example of creating secure code that is safe from SQL Injection. For more information, check out the following resources:

If you liked this, you'll love my book, Master Drupal Module Development.

"..the must have drupal developers book"


Feeling stuck with Drupal 8 module dev?

Get the free 7 lesson course that will help you get started today without feeling overwhelmed.

  • Create Drupal modules with just a few commands using the Drupal Console
  • Create custom pages
  • Create custom blocks
  • Create admin forms
  • Demystify routers and controllers
  • Bonus material

Find out more


Comments

Nice simple explanation, but you have a slight typo... you start out with:

Here is an example where the user deletes the first node from the node table:
example.com/?nid=4;DELETE FROM node WHERE nid = 1;

But you say this results in:

SELECT * FROM node where nid = 1;
DELETE FROM node WHERE nid = 1;

No... the first where clause above should be "where nid = 4;" ... before the injected DELETE statement.

Blair Wadman's picture

Thanks for spotting that. Correction made.

Hey this example would allow unpublished node titles to be accessed

Blair Wadman's picture

Good point - thanks! I have updated the article to exclude unpublished nodes.

Hi Blair Wadman,

This is very nicely explained and helped me a lot to understand concept of sql injections. Will you please explain if i am using placeholder in query and i passed malicious query through url then how drupal handles such requests.
e.g. I am using placeholder and user requests example.com/?nid=4;DELETE FROM node WHERE nid = 1; then what will happen in background.

Thanks,
Omkar

Add new comment