Preventing SQL Injection Attacks with MySQL and PHP

Most new web developers have heard of SQL injection attacks, but not very many know that it is fairly easy to prevent an attacker from gaining access to your data by filtering out the vulnerabilities using MySQL extensions found in PHP. An SQL injection attack occurs when a hacker or cracker (a malicious hacker) attempts to dump the data in a database table in a database-driven web site. In an unprotected and vulnerable site, this is pretty easy to do.

In order for an SQL injection attack to work, the site must use an unprotected SQL query that utilizes data submitted by a user to lookup something in a database table. The data could be from a search box, a login form or any type of query used to look up data.

For Example:

Generally when you login, the query like this :

SELECT * FROM tbl_name WHERE username=’value1‘ and password=’value2

Normally, you would expect a user to submit a username and password. But what if someone used the following instead of a password?

‘  OR  ‘1’ =’1

That would make the query used to look for the password look like this:

SELECT * FROM tbl_name WHERE username=’value1‘ and password=’value2′ OR ‘1’ = ‘1’

Your username and password is rightor wrong but this would always return a true.

Prevention of SQL injection

Use the following function to add backslashes to suspect characters and filter any data that is input by a user.

function cleanQuery($string)
{
if(get_magic_quotes_gpc())  // prevents duplicate backslashes
{
$string = stripslashes($string);
}
if (phpversion() >= ‘4.3.0’)
{
$string = mysql_real_escape_string($string);
}
else
{
$string = mysql_escape_string($string);
}
return $string;
}

You can filter a data like this :

SELECT * FROM tbl_name WHERE username=CleanQuery(‘value1‘) and password=CleanQuery(‘value2′ OR ‘1’ = ‘1’)

The built-in function that we want to use is called mysql_real_escape_string. This MySQL function only exists in PHP version 4.3.0 or newer. If you are using an older version of PHP, another MySQL function is used called mysql_escape_string.

mysql_escape_string is not as effective as the newer mysql_real_escape_string. The newer version escapes the string according to the current character set. The character set is ignored by mysql_escape_string, which can leave some vulnerabilities ope for sophisticated hackers. If you find that you are using an older version of PHP and you are trying to protect sensitive data, you really should upgrade to a current version of either PHP 4 or PHP 5.

So what does mysql_real_escape_string do?

This PHP library function prepends backslashes to the following characters: \n, \r, \, \x00, \x1a, ‘ and “. The important part is that the single and double quotes are escaped, because these are the characters most likely to open up vulnerabilities.

For those who do not know what an escape is, it is a character that is pre-pended to another character. When a character is escaped, it is ignored by the database. In other words, it makes that character ineffective in a query. In the case of PHP, an escaped character is treated differently by the PHP parser. The standard escape character used by PHP and MySQL is the backslash.

In the case of the SQL query example used above, after running it through the routine, it now looks like this, which breaks the query :

SELECT * FROM tbl_name WHERE username=CleanQuery('value1') and password=CleanQuery 
('value2\'  OR \'1\' = \'1')

 

This method should stop the bulk of the SQL injection attacks, but crackers and hackers are 
very creative and are always finding new methods to break into systems. There are additional
 steps that can be taken to filter out certain words, such as drop, grant, union, etc.,
 but using this method will strip these words from searches performed by you users. 
However, if you want to add another level of security and do not have an issue with 
certain words being deleted from queries, you can add the following just before 
if (phpversion() >= ‘4.3.0′).
  • http://www.mabwi.com/ Matt

    mysql_real_escape_string is the very minimal option for preventing SQL injection. I would classify it as the option to use when you have nothing better.

    If you’re serious about stopping injection, you should be looking in to using prepared statements – such as those offered by the PDO library – or stored procedures in your database. Both of these are massively more effective at stopping SQL injection than mysql_real_escape_string, because they’re compiled by the database before any parameters are added, which prevents the simplistic attack you demonstrated completely.

  • http://www.mabwi.com Matt

    mysql_real_escape_string is the very minimal option for preventing SQL injection. I would classify it as the option to use when you have nothing better.

    If you’re serious about stopping injection, you should be looking in to using prepared statements – such as those offered by the PDO library – or stored procedures in your database. Both of these are massively more effective at stopping SQL injection than mysql_real_escape_string, because they’re compiled by the database before any parameters are added, which prevents the simplistic attack you demonstrated completely.

  • admin

    You are right Matt. This is easy way. Thanks for your comment. I will write another post about SQL Injection with encryption.
    Thanks

  • admin

    You are right Matt. This is easy way. Thanks for your comment. I will write another post about SQL Injection with encryption.
    Thanks

  • Pingback: Preventing SQL Injection Attacks with MySQL and PHP-2 | php genious()

  • Pingback: 網站製作學習誌 » [Web] 連結分享()

  • http://buying-advice.com/ Bernard Fourie

    How about something like this:

    if (get_magic_quotes_gpc())

    {

    $_REQUEST = array_map(‘stripslashes’, $_REQUEST);

    $_POST = array_map(‘stripslashes’, $_POST);

    $_GET = array_map(‘stripslashes’, $_GET);

    $_COOKIE = array_map(‘stripslashes’, $_COOKIE);

    }

    $_REQUEST = array_map(‘mysql_real_escape_string’, $_REQUEST);

    $_POST = array_map(‘mysql_real_escape_string’, $_POST);

    $_GET = array_map(‘mysql_real_escape_string’, $_GET);

    $_COOKIE = array_map(‘mysql_real_escape_string’, $_COOKIE);

    then continue with preparing the data to insert in MySQL