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

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