If you do any programming that takes user input to make dynamic SQL statments, 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.

We suggest the following techniques to minimize the risk¹:

1. Validate user input. If you are expecting a number, check that you receive a number.
2. 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

http://php.net/manual/en/security.database.sql-injection.php

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
$sql = 'SELECT * FROM table WHERE field = "' . mysql_real_escape_string($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
$sql = 'SELECT * FROM table WHERE username = "' . mysql_real_escape_string($username) . '";';

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

• Contact ITS