TUTORIAL: MySQL Injection Attack Prevention in PHP

TeraTask · #1 (**permalink**) 06-14-2007, 02:33 PM

Listed as #2 in the Top 10 Web application vulnerabilities for 2007, injection flaws are something about which every developer should be especially cautious to protect their end users. In this article, I'll discuss protecting oneself from MySQL injection attacks using PHP. Specifically, we're going to look at, improve, and create a generalized function for the "Best Practice" query suggested on PHP.net at PHP: mysql_real_escape_string - Manual .

Before proceeding, you'll want to go and take a look at that box on the PHP.net site. Done? Great! Then let's get started.

So, from the code in the box, we learn how to protect data using the advantages of mysql_real_escape_string independent of the server's Magic Quotes settings. If we take what's written and put it in the form of a function, we would have something like this:

PHP Code:

  <?php
function sql_safe($value) {
  // Taken from the PHP site.

  // Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.
  if (get_magic_quotes_gpc()) {
    if(ini_get('magic_quotes_sybase')) {
      $value = str_replace("''", "'", $value);      
    } else {
      $value = stripslashes($value);
    }
  }

  return mysql_real_escape_string($value);
}
?>

That's a great start and allows us to now extend that best practice to our other php scripts. But, wait, that's not all ... We have to look at the "fine print" under that box:

Quote:

Note: mysql_real_escape_string() does not escape % and _. These are wildcards in MySQL if combined with LIKE, GRANT, or REVOKE.

So, let's beef up our function and make it a bit more intelligent by handling the escaping of these characters as well, but keep it optional. In PHP, that would then look like

PHP Code:

  <?php
function sql_safe($value,$allow_wildcards = true) {
  // Taken from the PHP site and modified for wildcards.

  // Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.
  if (get_magic_quotes_gpc()) {
    if(ini_get('magic_quotes_sybase')) {
      $value = str_replace("''", "'", $value);      
    } else {
      $value = stripslashes($value);
    }
  }
  
  //Escape wildcards for SQL injection protection on LIKE, GRANT, and REVOKE commands.
  if (!$allow_wildcards) {
    $value = str_replace('%','\%',$value);
    $value = str_replace('_','\_',$value);
  }
  
  return mysql_real_escape_string($value);
}
?>

And, wow, that's really all there is to it. Now, if you don't mind, I'd like to add another feature so that I have a one-stop shop for sql value preparation, namely adding quotes for strings and not for numbers. This will simplify SQL statement construction as you'll see in the samples below.

PHP Code:

  <?php
function sql_safe($value,$allow_wildcards = true, $detect_numeric = true) {
  // Taken from the PHP site and modified for wildcards and automatic formatting for numbers/strings.

  // Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.
  if (get_magic_quotes_gpc()) {
    if(ini_get('magic_quotes_sybase')) {
      $value = str_replace("''", "'", $value);      
    } else {
      $value = stripslashes($value);
    }
  }
  
  //Escape wildcards for SQL injection protection on LIKE, GRANT, and REVOKE commands.
  if (!$allow_wildcards) {
    $value = str_replace('%','\%',$value);
    $value = str_replace('_','\_',$value);
  }
  
  // Quote if $value is a string and detection enabled.
  if ($detect_numeric) {
    if (!is_numeric($value)) {
      return "'" . mysql_real_escape_string($value) . "'";
    }
  }
  
  return mysql_real_escape_string($value);
}
?>

The above function is designed for PHP 4! In PHP 5, $value will be passed by reference and therefore it will be changed itself which may be undesirable.
And, for those out there who are ahead of the game and have moved to PHP 5, but don't understand how PDO can help protect you, here's a PHP 5 equivalent:

PHP Code:

  <?php
function sql_safe($value,$allow_wildcards = true, $detect_numeric = true) {
  // Taken from the PHP site and modified for wildcards and automatic formatting for numbers/strings.
  $return_value = clone $value;

  // Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.
  if (get_magic_quotes_gpc()) {
    if(ini_get('magic_quotes_sybase')) {
      $return_value = str_replace("''", "'", $return_value);      
    } else {
      $return_value = stripslashes($return_value);
    }
  }
  
  //Escape wildcards for SQL injection protection on LIKE, GRANT, and REVOKE commands.
  if (!$allow_wildcards) {
    $return_value = str_replace('%','\%',$return_value);
    $return_value = str_replace('_','\_',$return_value);
  }
  
  // Quote if $value is a string and detection enabled.
  if ($detect_numeric) {
    if (!is_numeric($return_value)) {
      return "'" . mysql_real_escape_string($return_value) . "'";
    }
  }
  
  return mysql_real_escape_string($return_value);
}
?>

The above function is designed for PHP 5! In PHP 4 clone is undefined and will crash your system.

Now we have our function all setup and ready for usage, let's begin by creating a table and then view some example usage

Code:

CREATE TABLE `sample_table` (
`row_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` CHAR( 25 ) NOT NULL ,
`password` CHAR( 40 ) NOT NULL ,
`date_added` DATE NOT NULL ,
`auth_level` TINYINT NOT NULL DEFAULT '0'
)

The code above is SQL, not PHP. You can run it directly in PHPMyAdmin.

Now, let's assume that we have an HTML page that allows a user to be added. The form might look like

Code:

<h1>Add User</h1>
<form method="post" action="<?php echo $PHP_SELF;?>">
  <label for="username">Username</label> <input type="text" name="username" id="username" />
  <label for="password">Password</label> <input type="password" name="password" id="password" />
  <label for="auth_level"></label> <select name="auth_level" id="auth_level">
    <option value="1">Low Level</option>
    <option value="2" selected="selected">Med Level</option>
    <option value="3">High Level</option>
  </select>
  <input type="submit" value="Add User" />
</form>

Now, when submitted and assuming our function is available, we can then process the form above and insert using the following code:

PHP Code:

  mysql_query("insert into sample_table set username=".sql_safe($_POST['username']).",password=".sql_safe(sha1($_POST['password'])).",date_added=NOW(),auth_level=".sql_safe($value));

We also have the advantage of storing the username with the same case as the user has entered (preserving their preference) while using LIKE to allow a case-insensitive match on the username for login purposes:

PHP Code:

  mysql_query("select from sample_table where username LIKE ".sql_safe($_POST['username'],false).",password=".sql_safe(sha1($_POST['password'])));

You'll notice from the 2 commands above that our choice of true for the default on the options has really minimized what we have to type to secure our data while simultaneously providing easy customization.

Lee · #2 (**permalink**) 06-14-2007, 03:19 PM

Nice tutorial, i will have to test this sometime

HelloWorld · #3 (**permalink**) 06-14-2007, 05:42 PM

Dugged!~

Lee · #4 (**permalink**) 06-16-2007, 04:20 PM

I am just wondering what this should be used on, should it be used on all cells or just a username cell or something like that? should it be used in registration pages or/and login pages?

PHP Code:

 
   <?php
function sql_safe($value,$allow_wildcards = true, $detect_numeric = true) {
  // Taken from the PHP site and modified for wildcards and automatic formatting for numbers/strings.

  // Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.
  if (get_magic_quotes_gpc()) {
    if(ini_get('magic_quotes_sybase')) {
      $value = str_replace("''", "'", $value);      
    } else {
      $value = stripslashes($value);
    }
  }
  
  //Escape wildcards for SQL injection protection on LIKE, GRANT, and REVOKE commands.
  if (!$allow_wildcards) {
    $value = str_replace('%','\%',$value);
    $value = str_replace('_','\_',$value);
  }
  
  // Quote if $value is a string and detection enabled.
  if ($detect_numeric) {
    if (!is_numeric($value)) {
      return "'" . mysql_real_escape_string($value) . "'";
    }
  }
  
  return mysql_real_escape_string($value);
}
?>

The above code is the code for my version of php.

Thanks
Lee

Lee · #5 (**permalink**) 06-18-2007, 07:54 AM

What can i do to test this? so i know that it works?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

LinkBacks (?) LinkBack to this Thread: http://www.programmerstalk.net/thread722.html
Posted By	For	Type	Date
Content Farmer: MySQL Injection Attack Prevention in PHP Tutorial	This thread	Refback	09-08-2007 04:52 PM
gpc_magic_quotes: Blogs, Photos, Videos and more on Technorati	This thread	Refback	06-19-2007 06:10 AM
Passing a variable from a form to a MySql query	This thread	Refback	06-17-2007 10:36 AM
Digg / News / Upcoming	This thread	Refback	06-15-2007 01:08 PM
Stumble exchange! - Page 3	This thread	Refback	06-15-2007 11:59 AM
Digg / Programming / Upcoming	This thread	Refback	06-15-2007 08:13 AM
Digg - MySQL Injection Attack Prevention in PHP Tutorial	This thread	Refback	06-15-2007 05:18 AM
Digg / Programming / Upcoming	This thread	Refback	06-14-2007 10:35 PM
Digg / Programming / Upcoming	This thread	Refback	06-14-2007 08:42 PM
Digg / Technology / Upcoming	This thread	Refback	06-14-2007 05:43 PM