TroyGrosfield.com TroyGrosfield.com

Headline

Preventing an SQL Injection Attack in PHP

Author
by Troy Grosfield
Date
December 15th, 2010
Category
Developer
Story

I recently was asked to fix some bugs on a particular website.  While looking through the state of the code I noticed the site was susceptible to an SQL injection attack.

Issue

The site is taking the user input and directly inserting it into the SQL statement without escaping.

PHP Code Issue

$sql = "SELECT id, username, password
	FROM   users
   	WHERE  username LIKE '".$_POST['username']."'
	  AND  password LIKE '".$_POST['password']."';
$result = $this->runQuery('execute',$sql);

Issues

  1. ISSUE: Inserting user input directly into the SQL statement
  2. ISSUE: A user could run any query they wanted against the database (even drop the database!)
    1. SOLUTION: for issues 1 and 2, PHP has a function to assist with escaping user input: mysql_real_escape_string($input)
  3. ISSUE: If a user’s username is known, all I need for the password is the percent sign (%)
  4. ISSUE: Using LIKE operatior instead of “=”.  It allows me to use wildcards so I don’t even have to know a user’s full username.  I can just start guessing.  If I try logging in with username = "tr%" and password = "%" I will be authenticated for any single user whose username starts with “tr”.
    1. SOLUTION: for issues 3 and 4, never use LIKE when comparing a username or password in MySQL. You will leave yourself very open to attacks. Instead, use “=”.
  5. ISSUE: Password is case insensitive. By default in MySQL, all string comparisons are case insensitive.
    1. SOLUTION: First off, don’t store your passwords in plain text!  If this has already been done MySQL has a BINARY() function that will make the comparison case sensitive.
  6. ISSUE: Comparing plain text password (actually how they are stored in the database)
    1. SOLUTION: Store the passwords using an MD5 hash or
    2. SOLUTION: Use MySQL’s PASSWORD() function

Solution

Always escape user input!

PHP Code Solution

$sql = sprintf("SELECT id, username, password
		FROM   member_master
		WHERE  username = '%s'
		  AND  passwd = Binary('%s')",
	       mysql_real_escape_string($_POST['username']),
	       mysql_real_escape_string($_POST['password']));
$result = $this->runQuery('execute',$sql);

Follow these tips and prevent SQL injection attacks from happening!

Tags
Comments
No Comments »

No Comments Yet

Leave a reply