PHP:How to prevent SQL Injection Attacks with PHP and MySQL

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.

function sql_quote( $value )

{

$value = htmlspecialchars($value, ENT_QUOTES);

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;

}

 

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.

$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.

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'";