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:
$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!
function blogs_init() {
if (!empty($_GET['nid'])) {
$nid = $_GET['nid'];
$title = db_query("SELECT title FROM node WHERE nid = $nid")->fetchField();
drupal_set_message($title);
}
}
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.
There is a serious security vulnerability in this code. Take a close look at the following:
$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:
SELECT title FROM node where nid = 4;
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:
A malicious search:
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:
$nid = $_GET['nid']
$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:
function blogs_init() {
if (!empty($_GET['nid'])) {
$nid = $_GET['nid'];
$title = db_query('SELECT title FROM {node} WHERE nid = :nid', array(':nid' => $nid))->fetchField();
drupal_set_message($title);
}
}
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.
function blogs_init() {
if (!empty($_GET['nid'])) {
$nid = $_GET['nid'];
$title = db_query('SELECT title FROM {node} WHERE nid = :nid AND status = 1', array(':nid' => $nid))->fetchField();
drupal_set_message($title);
}
}
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:
