In Part 1 I discussed Cross Site Scripting attacks and how to prevent and avoid it while coding, In this part I will talk about SQL Injection attacks and how to prevent it by using Drupal API functions

SQL Injection Attacks

While Drupal is a secure CMS, We don't have to use another functions to avoid such expoits we just need to follow the Drupal way in development, That's why i'm going to focus on the bad and good behaviors of development.

Use Drupal Database API functions

Drupal Provide a set of functions that support Database layer which should be used rather than using the default PHP functions

We Should Use

  • db_query()
  • db_select()
  • db_update()
  • db_insert
  • ...etc

We Shouldn't use

  • mysql_query()
  • mysql_fetch_array()
  • ...etc




Lets discuss the basic query operations (select, update, insert, delete) and how to create ur own query in a secure way.

Select In a Secure Way

Insecure Way:
php
db_query("SELECT title FROM {node} t WHERE t.nid = ". $_GET['id']);  // Insecure way
Secure Way:
php
db_query("SELECT title FROM {node} t WHERE t.nid = :nid ", array(':nid' => $_GET['id']));  // Secure way
Another Secure Way :
php
$query = db_select('node', 'n');
$query
  ->fields('n', array('title'))
  ->condition('n.nid', $_GET['id']);
$result = $query->execute() ;

Update In a Secure Way

Insecure Way:
php
db_query("UPDATE {node} set title = ".$_POST['newtitle']." WHERE nid =". $_POST['nid']);  // Insecure way
Secure Way:
php
db_query("UPDATE {node} set title = :title WHERE nid = :nid ", array(':title' => $_POST['newtitle'],':nid' => $_POST['nid']));  // Secure way
Another Secure Way :
php
$number_of_updated = db_update('node') // Secure way
  ->fields(array(
    'title' => $_POST['newtitle'],
  ))
  ->condition('nid', $_POST['nid'])
  ->execute();

Insert In a Secure Way

Insecure Way:
php
db_query("INSERT INTO {images} (nid, url, dimensions) VALUES (".$image['nid'].", ".$image['url'].", ".$image['dimensions']) ;   // Insecure way
Secure Way:
php
db_query("INSERT INTO {images} (nid, url, dimensions) VALUES (:nid, :url, :dimentions) ", array(':nid' => $image['nid'], ':url' => $image['url'], ':dimentions' => $image['dimensions'] )) ;   // Secure way
Another Secure Way :
php
$inserted_imageid = db_insert('images') // Secury way
  ->fields(array(
    'nid' => $image['nid'],
    'url' => $image['url'],
    'dimensions' => $image['dimensions'],
  ))
  ->execute();

Delete In a Secure Way

Insecure Way:
php
db_query("DELETE FROM {images} WHERE nid = ".$image['nid']) ;   // Insecure way
Secure Way:
php
db_query("DELETE FROM {images} WHERE nid = :nid", array(':nid' => $image['nid']) ) ;   // Secure way
Another Secure Way :
php
$num_deleted = db_delete('images') // Secury way
 ->condition('nid', $image['nid'])
  ->execute();

If you want to know more about Drupal Database Layer and how to use them you can read this Great Documentation , Good luck.