Revision as of 22:23, 3 June 2008

Overview

Example 1

Here follows a tipical Login Forms to authenticate user. Credentials are retrieved on a backend Database by using connection parameters stored in a .inc file.

auth.php

<?php
include('./db.inc');

function sAuthenticateUser($username, $password){
  $authenticatedUserName="";
  if ($link = iMysqlConnect()) {

    $query  = "SELECT username FROM users";
    $query .=                " WHERE username = '".$username."'";
    $query .=                " AND   password = md5('".$password."')";
    $result = mysql_query($query);

    if ($result) {
      if ($row = mysql_fetch_row($result)) {
         $authenticatedUserName =  $row[0];
      }
    }
  }

  return $authenticatedUserName;

}

if ($sUserName = sAuthenticateUser($_POST["username"], 
				   $_POST["password"])) {

  /* successfull authentication code goes here */
   ...
   ...
 } else {

  /* unsuccessfull authentication code goes here */
   ...
   ...
 }

?>

db.inc

<?php

define('DB_HOST',     "localhost");
define('DB_USERNAME', "user");
define('DB_PASSWORD', "password");
define('DB_DATABASE', "owasp");


function iMysqlConnect(){
  $link = mysql_connect(DB_HOST,
  	  DB_USERNAME,
			DB_PASSWORD);

  if ($link && mysql_select_db(DB_DATABASE))
    return $link;
  return FALSE;
}

?>

The above example has two vulnerability:

• Authentication Bypass

  by exploiting a SQL Injection vulnerability Authentication you can authenticate as :

  username ' OR 1=1 #

  password anything

• Information Disclosure

  an attacker may retrieve db.inc on unproper configured WEB Server

Example 2

The following sample code cames from a online book catalog.

getbook.php 


function aGetBookEntry($id) {
  $aBookEntry = NULL;
  $link = iMysqlConnect();

  $query = "SELECT * FROM books WHERE id = $id";
  $result = mysql_query($query);

  if ($result) {
    if ($row = mysql_fetch_array($result)) {
      $aBookEntry = $row;
    }
  }
  return $aBookEntry;
}

....
$id = $_GET['id'];
$aBookEntry = aGetBookEntry($id);

/* Display retrieved book information */
...
...

The above example is vulnerable to Blind SQL Injection attack. An attacker exploiting this vulnerability may backup all Database, or interact with DBMS underlying Operating System.

Description

PHP preventing SQL Injection

When talking about preventing SQL Injection in PHP we shall take in consideration whatever to use old Database connector of newset PHP Portable Data Objects interface. To this aim will take in consideration both of them since:

• PHP PDO has been introduced in PHP 5.1
• PHP PDO represents a Database abstract layer
• Experienced developers may want to develop a custom Abstract Layer for theire demand

Reader should be aware that at the moment PDO OCI used as a Oracle Driver is experimental.

DBMS authentication credentials

When working with a DBMS through an authenticated connection application developers should be very carefull on how, and subsequently where, store authentication credentials to query such a backend engine. A configuration file with .inc extension should be avoided if left world wide readable by a web server since it's content can be easy retrieved. Such issue can be avoided by :

• Denying remote access to .inc files

     <Files ~ “\.inc$”>
        Order allow,deny
        Deny from all
     </Files>

requires user intervention on Apache configuration!

• Adding a security token

<?php

if (defined('SECURITY_INCLUDE_TOKEN') && SECURITY_INCLUDE_TOKEN != 'WfY56#!5150'){
     define ('DBMS_CONNECTION_STRING','mysql://owaspuser:owasppassword@localhost:3306');
     ....
}

<?php
define('SECURITY_INCLUDE_TOKEN', 'WfY56#!5150');
include 'dbms_handler.php';
..
?>

• configuring php_ini settings in apache

/etc/apache2/sites-enabled/000-owasp 

<VirtualHost *>
     DocumentRoot /var/www/apache2/
     php_value mysql.default_host 127.0.0.1
     php_value mysql.default_user owaspuser
     php_value mysql.default_password owasppassword
     ....
     ....
     ....
</VirtualHost>

dbmshandler.php 

<?php
    function iMySQLConnect() {
        return mysql_connect();

    }
?> 

at the moment of write it only works when backend Database Engine is MySQL

• using Apache SetEnv directive

/etc/apache2/sites-enabled/000-owasp 

<VirtualHost *>
     DocumentRoot /var/www/apache2/
     SetEnv DBHOST "127.0.0.1"
     SetEnv DBUSER "owaspuser"
     SetEnv DBPASS "owasppassword"
   
     ....
     ....
     ....
</VirtualHost>

dbmshandler.php 

<?php
    function iMySQLConnect() {
        return mysql_connect(getenv("DBHOST"),
                             getenv("DBUSER"),
                             getenv("DBPASS"));
    }
?>

requires user intervention on Apache configuration

Wich one to use is up to you. You should take into account whetever a user interventation is needed on WEB Server or not and if you want to guarantee application portability as well between different DB Backend Engine.

Example using PDO MySQL driver:

/etc/apache2/sites-enabled/000-owasp 

<VirtualHost *>
     DocumentRoot /var/www/apache2/
     SetEnv PDO_DSN "mysql:host=localhost;dbname=owasp"
     SetEnv PDO_USER "owaspuser"
     SetEnv PDO_PASS "owasppassword"
   
     ....
     ....
     ....
</VirtualHost>

dbmshandler.php 

<?php
function SQLConnect() {
    $oPdo = NULL;
    try {
            $oPdo = new PDO(getenv("PDO_DSN"),
                            getenv("PDO_USER"),
                            getenv("PDO_PASS"));

            /* Throws an exception when subsequent errors occour */
            $oPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            /* handle PDO connection success */
               ...
               ...
            return $oPdo;
   } catch (PDOException $e) {
            /* handle PDO connection error */
               ...
               ...
           return NULL;
   }
} 
?>

Escaping Quotes

magic_quotes_gpc escapes quotes from HTTP Request by examing both GET/POST data and Cookie value. The truth is that any other data in HTTP Request isn't escaped and an evil user may attempt to exploit a SQL Injection vulnerability on other HTTP Request data such as User-Agent value. Another drawback is that while it performs well on MySQL (as example) it doesn't works with Microsoft SQL Server where single quote should be escaped with ''' rather than \'

Since every application should be portable across WEB Servers we want to rollaback from magic_quotes_gpc each time a php script is running on WEB Server:

function magic_strip_slashes() {
    if (get_magic_quotes()) {

       // GET
       if (is_array($_GET)) {
           foreach ($_GET as $key => $value) {
               $_GET[$key] = stripslashes($value);
           }
       }

       // POST
       if (is_array($_POST)) {
           foreach ($_GET as $key => $value) {
               $_POST[$key] = stripslashes($value);
           }
       }

       // COOKIE
       if (is_array($_COOKIE)) {
           foreach ($_GET as $key => $value) {
               $_COOKIE[$key] = stripslashes($value);
           }
       }
    }
}

and use a DBMS related function to escape quotes such as:

• MySQL: mysql_real_escape_string
• PostgreSQL: pg_escape_string

function sEscapeString($sDatabase, $sQuery) {
    $sResult=NULL;
 
    switch ($sDatabase) {
    case "mysql":
        $sResult = mysql_real_escape_string($sQuery);
        break;
  
    case "postgresql":
        $sResult = pg_escape_string($sQuery);
        break;

    case "mssql":
        $sResult = str_replace("'", "''",$sQuery);
        break;

    case "oracle":
        $sResult = str_replace("'", "''",$sQuery);
        break;
    }

    return $sResult;
}
}

Since both Oracle and Microsoft SQL Server connectors doesn't have a real escape_string function software developer can create his own escapeing functions or use addslasshes().

With properly quotes escaping we can prevent Authentication Bypass vulnerability in Example 1:

auth.php

<?php
include('./dbmshandler.php);

function sAuthenticateUser($username, $password){
  $authenticatedUserName="";
  if ($link = iMysqlConnect()) {

    $query  = "SELECT username FROM users";
    $query .=                " WHERE username = '".$username."'";
    $query .=                " AND   password = md5('".$password."')";

    /* escape quotes */
    $result = sEscapeString("mysql", $query);

    if ($result) {
      if ($row = mysql_fetch_row($result)) {
         $authenticatedUserName =  $row[0];
      }
    }
  }

  return $authenticatedUserName;
}

/* start by rollback magic_quotes_gpc action (if any) */

magic_strip_slashes();


if ($sUserName = sAuthenticateUser($_POST["username"], 
                                   $_POST["password"])) {

  /* successfull authentication code goes here */
   ...
   ...
 } else {

  /* unsuccessfull authentication code goes here */
   ...
   ...
 }

PHP Portable Data Objects implements a quote() method on PDO class but it's worst noticing that not all underlying PDO Drivers implements this method. On the other side PDO::query() method by defualt escape quotes on SQL query string as shown in following example.

Example using PDO MySQL driver:

auth.php

<?php
include('./dbmshandler.php');

function sAuthenticateUser($username, $password){
  $authenticatedUserName=NULL;
  if ($oPdo = SQLConnect()) {

    $query  = "SELECT username FROM users";
    $query .=                " WHERE username = '".$username."'";
    $query .=                " AND   password = md5('".$password."')";

    try {
         $row = $oPdo->query($query)->fetch();
         if ($row) {
              return $row['username'];
         } 
    } catch (PDOException e) {
         /* handle execption and SQL Injection Attempt */
         ....
         ....
         return NULL;
    }    
}


/* start by rollback magic_quotes_gpc action (if any) */

magic_strip_slashes();


if ($sUserName = sAuthenticateUser($_POST["username"], 
                                   $_POST["password"])) {

  /* successfull authentication code goes here */
   ...
   ...
} else {

  /* unsuccessfull authentication code goes here */
   ...
   ...
}

Prepared Statements

Prepared Statements is the ability to preparse and generate an execution plan for SQL Queries. Such an execution plan will be instantiated with typed parameters. If params are of incorrect type or contains a nested query the execution of plan will fails.

<?php
function getBookByID($id) {
    $aBook = NULL;
    $link = mysqli_connect();

    $stmt = $link->stmt_init();
    if ($stmt->prepare("SELECT * FROM books WHERE ID =?")) {
        $stmt->bind_param("i",$id);
        $stmt->execute();
        
        /* Retrieves book entry and fill $aBook array */
        ...
        ...

        /* Free prepared statement allocated resources */
        $stmt->close();
    }

    return $aBook;

} 

/* MAIN */

/* Cast GET 'id' variable to integer */
$iID = (int)$_GET['id'];

$aBookEntry = getBookByID($iID);

if ($aBookEntry) {
    /* Display retrieved book entry */
    ...
    ...
}

?>

PHP Portable Data Objects emulate prepared statements for drivers with no natively supports. Here follows an example of prepared statements usage with PHP PDO

Example using PDO:

<?php
include('./dbmshandler.php');

function getBookByID($id) {
    $aBook = NULL;
    $oPdo = SQLConnect();

    if ($oPdo) {
        $stmt = $oPdo->prepare("SELECT * FROM books WHERE ID =?");
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
        if ($smmt->execute()) {
            $aBook = $stmt->fetch(PDO::FETCH_ASSOC);
        }
    }

    return $aBook;

} 

Data Validation

Logging Errors

PHP preventing LDAP Injection

LDAP Authentication Credentials

Data Validation

Logging Errors

Detecting Intrusions from WEBAPP

Defeating Automated Tools

References

• Ilia Alshanetsky : "architect's Guide to PHP Security" - http://dev.mysql.com/tech-resources/articles/guide-to-php-security-ch3.pdf
• OWASP : "OWASP Guide Project" - http://www.owasp.org/index.php/OWASP_Guide_Project
• PHP : "MySQL Improved Extension" - http://it2.php.net/manual/en/book.mysqli.php