SQL injection is a well-known way to attack used to get, insert, delete or update data from a particular database. Without security, your data is on high risk and might get lost or changed. This kind of attack is done thought the user input in the browser, POST and GET. Before you publish your website, you have to test it against SQL injection attacks to prevent any future failure.
How to prevent SQL injections attacks? By using sanitized input data before a sql query being executed. In PHP, there are two function to sanitize user/browser input: addslashes and mysql_real_escape_string.
Here is a function that sanitizes input data before using it in a sql query:
Function clean_sql_query($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;
}
This function can be used in the following format from a form:
if (isset($_POST[‘ID'])) $ID = clean_sql_query($_POST['ID']);
Or use it inside an sql query:
SELECT * FROM Users WHERE ID = '".clean_sql_query ($ID)."' "
Step by step explanation
First is to check if the is On from php ini file, then the POST, GET and COOKIES data is automatically escaped. Then, we need to remove all slashes from the $string variable using the stripslashes function.
Depending on the php version, it applies the mysql_real_escape_string or mysql_escape_string to the $string variable.
What does mysql_real_escape_string do?
mysql_real_escape_string prepends back slashes to the following characters: \n, \r, \, \x00, \x1a, ‘ and “. Thus, it will be ignored by the database.
Example:
After assigning the function to the input data, the query:
SELECT * FROM Users WHERE ID = 1)
Will look like:
SELECT * FROM items WHERE ID = '\' OR \'1\' = \'1'Which is invalid and gonna be ignored by the database.
Without using the mysql_real_escape_string, this query will be valid for the database, so any user can use it to mess with your database.
