Secure development with Drupal 7- part 2

Mon, 04/01/2013 - 13:55 -- meladawy

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:
db_query("SELECT title FROM {node} t WHERE t.nid = ". $_GET['id']);  // Insecure way
Secure Way:
db_query("SELECT title FROM {node} t WHERE t.nid = :nid ", array(':nid' => $_GET['id']));  // Secure way
Another Secure Way :
$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:
db_query("UPDATE {node} set title = ".$_POST['newtitle']." WHERE nid =". $_POST['nid']);  // Insecure way
Secure Way:
db_query("UPDATE {node} set title = :title WHERE nid = :nid ", array(':title' => $_POST['newtitle'],':nid' => $_POST['nid']));  // Secure way
Another Secure Way :
$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:
db_query("INSERT INTO {images} (nid, url, dimensions) VALUES (".$image['nid'].", ".$image['url'].", ".$image['dimensions']) ;   // Insecure way
Secure Way:
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 :
$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:
db_query("DELETE FROM {images} WHERE nid = ".$image['nid']) ;   // Insecure way
Secure Way:
db_query("DELETE FROM {images} WHERE nid = :nid", array(':nid' => $image['nid']) ) ;   // Secure way
Another Secure Way :
$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.

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.