Categories	Resources
Photo Gallery	Contact

SQL_Injection

By Bee

Last updated: November 12, 2005

How to prevent SQL Injection Attacks with PHP and MySQL

Security of your website information is probably the most important thing. If your database contains valuable data, you might lose your data or your data could be stollen.

Not every web developer has heard about SQL Injection. I know, you will say "Who is going to hack my website?", "Why should anyone hack my website?" or "No one is gonna hack my website".

You will be an ignorant until it's gonna happen to you, so you'd better prevent this thing happen, as you will see it's not so hard.

The main iddea is to make your website portable, on different platforms with different configurations.

How SQL Injection is possible?

This is possible through user input ( POST, GET )

With SQL Injection a hacker can retrieve your data, insert, delete, so basicly can do anything with your database.

You need to sanitize input data, before being used in a sql query.
PHP has two functions for mysql that sanitize user input: addslashes( older ) and mysql_real_escape_string( recommended ). This function comes from PHP >= 4.3.0, so you should check first if this function exists. Mysql_real_escape_string prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

This is a customized function I use to sanitize input data before using it into a sql query:

function sql_quote( $value )
{: if( get_magic_quotes_gpc() ); {; $value = stripslashes( $value );; }; //check if this function exists; if( function_exists( "mysql_real_escape_string" ) ); {; $value = mysql_real_escape_string( $value );; }; //for PHP version < 4.3.0 use addslashes; else; {; $value = addslashes( $value );; }; return $value;
}

A brief explanation

If get_magic_quotes_gpc function is On, then all the POST,GET,COOKIE data is escaped automatically.
This function was set to On, to protect beginner developers, but from next releases of PHP this function will be Off.
So if get_magic_quotes_gpc is enabled, we need to remove slashes, with stripslashes function, and then apply mysql_real_escape_string or addslashes, the one that is available.
You cannot rely on magic quotes, as it depends on php installation.

Using sql_quote function

$username = $_POST['username'];

query = "SELECT * FROM users WHERE username='" . sql_quote($username) . "'";

Of course you need to validate user input, for example you must check if username contains only digits, alphanumeric and underscore.
This is an extra security measure, you should apply it on every field, depending of field type: email, text, number and so on.

I use the Validate class from PEAR to validate my input. Just download Validate class, and copy it in PEAR's directory.
Here is how you validate an email, a string and a number using this class:

//init validate object
include_once('your_path_to_pear_directory/Validate.php');
$validate = &new Validate();
//get POST variables
$username = $_POST['username'];
$email = $_POST['email'];
$age = $_POST['age'];
//validate username, only alphanumeric and space characters are allowed
//VALIDATE_ALPHA, VALIDATE_NUM, VALIDATE_SPACE constants are defined in Validate class.
if( !$validate->string( $username, array('format'=>VALIDATE_ALPHA . VALIDATE_NUM . VALIDATE_SPACE ) ) )
{: //throw some username error
}
//validate email
if( !$validate->email( $email ) )
{: //throw some email error
}
//validate age, only numbers between 0 and 100 are allowed
if( !$validate->number( $age, array( 'min'=>0, 'max'=>100 ) ) )
{: //throw some age error
}

From this point, only if there is no error from the above validations, only then we can execute the query.

Examples of sql injection:

' or 1=1 --
' OR '1'='1

Let's see what happens if a username is: ' OR '1'='1
Quotes are included.

//query without sql_quote function

query = "SELECT * FROM users WHERE username='' OR '1'='1'";

//query with sql_quote function, see how single quotes are escaped

query = "SELECT * FROM users WHERE username='\' OR \'1\'=\'1'";

Are you paying more than $170 per month on your credit cards?
Let us help you. Free Debt Relief !

Host your website now
Unlimited Hosting

Bee recommends:

www.averagedesign.com