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:

  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:

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:


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.

Please rate the quality of this answer: Poor Fair Okay Good Excellent
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: Fri, 08 Mar 2019 3:06pm