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