How to avoid SQL injection attacks

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 risk1:

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


1Because 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.

Please rate the quality of this answer: PoorFairOkayGoodExcellent
Not the answer you were looking for? Try different keyword combinations and if you still can’t find your answer, please contact us.
Article ID: 1111
Created: Wed, 03 Mar 2010 3:22pm
Modified: Thu, 02 Sep 2010 4:59pm