How to avoid SQL injection attacks
If you do any programming that takes user input to make dynamic SQL statements, be aware of SQL injection attacks. If you do not take precautions against this type of attack, a malicious user can insert SQL statements that can return more data than intended or cause damage to your database. This could lead to an exploit that compromises the web server.
Minimizing Risk
We suggest the following techniques to minimize the risk1:
- Validate user input. If you are expecting a number, check that you receive a number.
- Use functions like htmlspecialchars, stripslashes, and mysql_real_escape_string to clean malicious input.
External Links:
http://en.wikipedia.org/wiki/SQL_injection
http://us3.php.net/manual/en/function.mysql-real-escape-string.php
https://secure.php.net/manual/en/security.database.sql-injection.php
At a minimum, data that will be incorporated into SQL queries for a MySQL database should be sanitized using the mysqli_real_escape_string function. Ideally, prepared statements with placeholders should be used when querying a database.
The Open Web Application Security Project (OWASP) has a cheat sheet on methods to prevent SQL injection vulnerabilities:
https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet
PHP Sample code
- Remove non-alphanumeric characters from input
//get raw input
$input = $_REQUEST['input']; //characters not allowed in input
$pattern = "/[^a-zA-Z0-9]/"; // regex pattern means: all characters NOT in a-z, A-Z and 0-9
//remove all characters that are not allowed
$input = preg_replace($pattern, "", $input);
//build SQL statement with validated input
//$dbconnect is the database connection
$sql = 'SELECT * FROM table WHERE field = "' . mysqli_real_escape_string($dbconnect, $input) . '";';
- Only run query if input is numeric
if (is_numeric($_REQUEST['input'])) {
//build SQL statement with validated input
$sql = 'SELECT * FROM table WHERE field = ' . $input . ';';
}
- Clean malicious input with htmlspecialchars, stripslashes, and mysql_real_escape_string
// you can nest the functions
$username = htmlspecialchars(stripslashes($_REQUEST['username']));
// or use them individually
// $dbconnect is the database connection
$sql = 'SELECT * FROM table WHERE username = "' . mysqli_real_escape_string($dbconnect, $username) . '";';
1 Because the ITS hosted web sites uses PHP, this article references PHP functions, has PHP external links and the examples are in PHP. Regardless of the programming language you use, there should be similar functionality available.
