MySQL: Dealing with SQL Injection

The best table design of any database can be ruined in a moment when omitting the related security aspects. This is a brief note how to deal with it…

For now, this post is related to MySQL only. This post does not deal with REGEX validation of the input data in PHP.

// a general parameter initiation common for all examples
$dbname = "test";
$hostname = "localhost";
$username = "testuser";
$dbpass = "testpass";
// the following two variables are vulnerable since they may contain a source code
// inserted directly in the input HTML entities by a malicious user
$email = $_POST['email']; 
$password = $_POST['password'];

MySQL Extension

This approach is based on the mysql_real_escape_string() extension.

if(mysql_connect($hostname, $username, $dbpass) && mysql_select_db($dbname)){
  // SQL injection prevention
  $query = sprintf("SELECT * FROM users WHERE email='%s' AND password=SHA('%s')",
  mysql_real_escape_string($email), mysql_real_escape_string($password));
  // results
  if($result = mysql_query($query))
    // do what's necessary when user is successfully verified
  }else{
    // do what's necessary when user provided an incorrect login information
  }
}

Remember one thing: This extension is deprecated as of PHP 5.5.0, and will be removed in the future.

MySQL Improved Extension

This approach is based on the mysqli extension. As a replacement to the deprecated one, the mysqli::real_escape_string() extension (object or procedural) can be used.
The object oriented example is as follows while leveraging the mysqli::prepare method to prevent the possible injection attack.

$mysqli = new mysqli($hostname, $username, $dbpass, $dbname);
if($statement = $mysqli->prepare("SELECT * FROM users WHERE email = ? AND password = SHA( ? )")){
  if($statement->bind_param("ss", $email, $password)){
    // results
    if($statement->affected_rows()){
      // do what's necessary when user is successfully verified
    }else{
      // do what's necessary when user provided an incorrect login information
    }
  }
}

The mysqli_stmt::bind_param method syntax defines types of variables and which variables to be inspected.

PHP Data Objects (PDO)

The PDO extension defines a lightweight, consistent interface for accessing databases in PHP. A comprehensive how-to can be obtained on the php.net site. The PDO provides a database independent solution. It’s only necessary to specify an appropriate database PDO driver.
Beside the driver independency, remeber one thing: The benefit of using the PDO is to not worry about the escape stuff since PDO will do the dirty job. The example code can be found as follows:

// a PDO connection to the MySQL DB
try {
  $db = new PDO("mysql:host=".DBHOST.";dbname=".DBNAME, DBUSER, DBPASS);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}catch (PDOException $e) {
  die ("PDO Connect Error (". $e->getMessage() .") n");
}
// SQL injection prevention
$sql = "SELECT * FROM users WHERE (email = :email AND password = SHA(:password))";
// it doesn't make sense here, but if there is a need to use the "LIKE" keyword,
// usage of "LIKE CONCAT(:email, '%')" becomes inevitable
$statement = $db->prepare($sql);
$statement->bindParam(":email", $email, PDO::PARAM_STR, 12); // 12 characters max
$statement->bindParam(":password", $password, PDO::PARAM_STR, 20); // 20 max
$statement->execute();
// or using array
// $statement->execute(array(":email" => $email, ":password" => $password));
// results
if ($statement->rowCount()){
  // do what's necessary when user is successfully verified
}else{
  // do what's necessary when user provided an incorrect login information
}
// or alternatively
//foreach ($statement->fetchAll(PDO::FETCH_ASSOC) as $row) {
//  echo $row['DB_COLUMN_NAME'];
//}

Sources:
http://25yearsofprogramming.com/blog/2011/20110205.htm
http://php.net/manual/en/

Advertisements
This entry was posted in Linux, Security, Server and tagged , , , , , . Bookmark the permalink.