Preventing an SQL Injection Attack in PHP

by Troy Grosfield
December 15th, 2010

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.


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);


  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


Always escape user input!

PHP Code Solution

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

Follow these tips and prevent SQL injection attacks from happening!

No Comments »

No Comments Yet

Leave a reply

You must be logged in to post a comment.