SQL Injection (SQLi) is a type of injection attack. An attacker can use it to make a web application process and execute injected SQL statements as part of an existing SQL query.

This article assumes that you have a basic understanding of SQL Injection attacks and the different variations of SQL Injection.

SQL Injection in PHP

The following code is a very simple PHP application that accepts an id and shows the name of the user. The application uses GET but it could use POST or any other HTTP method. This example is based on the MySQL database but the same principles apply for other databases. The sample database is called users and has the following structure and content.

id username password first_name last_name
1 johnsmith $2a$10$SakFH.Eatq3QnknC1j1uo.rjM4KIYn.o8gPb6Y2YBnNNNY.61mR9K John Smith
2 maryjohnson $2a$10$hA/hwCzhr6F23BsbRZBjdOA5eqTgV01cv30sy/O2EcL2/zG9k0aGy Mary Johnson
3 jameswilliams $2a$10$OkV5tCMMsy91pkkMXHa94OgcunNtuhxsQcxaOW6tJimuaCO0FMDZm James Williams
4 lindabrown $2a$10$2NgAjstT9NcN58zMcF/Rq.pYt5bg3iQ6OmdRgR3YWfT.ZVgmJR4FK Linda Brown

The following is the PHP code of the application that contains an SQL Injection vulnerability.

<?php/*
* Check if the 'id' GET variable is set
* Example - http://localhost/?id=1
*/
if (isset($_GET['id'])){
$id = $_GET['id'];

/* Setup the connection to the database */
$mysqli = new mysqli('localhost', 'dbuser', 'dbpasswd', 'sql_injection_example');

/* Check connection before executing the SQL query */
if ($mysqli->connect_errno) {
printf("Connect failed: %s\n", $mysqli->connect_error);
exit();
}

/* SQL query vulnerable to SQL injection */
$sql = "SELECT username
FROM users
WHERE id = $id";

/* Select queries return a result */
if ($result = $mysqli->query($sql)) {
while($obj = $result->fetch_object()){
print($obj->username);
}
}
/* If the database returns an error, print it to screen */
elseif($mysqli->error){
print($mysqli->error);
}
}

The following is an example of a legitimate HTTP request that could be made to the vulnerable application above.

http://localhost/?id=1
> johnsmith

The following is an example of a malicious HTTP request that could be made to the vulnerable application above.
http://localhost/?id=-1 UNION SELECT password FROM users where id=1
> $2a$10$SakFH.Eatq3QnknC1j1uo.rjM4KIYn.o8gPb6Y2YBnNNNY.61mR9K

This application has a few issues. All of them contribute to the SQL Injection vulnerability.

Issue Description Suggested Remediation
Input Validation We know that the id that is passed will always be a number. However, the code does not validate user input at all.

Validating user input is not a direct solution to SQL Injection, but it helps us avoid malicious user data being interpreted by the database.

Before even processing the database query, validate user input.

In this case, we need to check that the input value is a number.

The code allows for SQL Injection The code accepts user input (in this case, from a GET parameter) and includes it directly in the SQL statement.

This allows an attacker to inject SQL into the query, therefore tricking the application into sending a malformed query to the database.

When dealing with SQL queries that contain user input, use prepared statements also known as parameterized queries.

A parameterized query specifies parts of the SQL query that should be treated as user input.

Errors are displayed to the user If errors are displayed, an attacker may get information that could lead to a compromise.

Information such as the database type and version makes it easier to exploit an SQL Injection vulnerability.

Do not display SQL errors to the user. If you need to show the user an error, use a generic error message that does not give away sensitive information.
Errors are not logged Error logs help you solve issues. They also let you find out if someone attempted to attack your application.

If you don’t keep a log of database errors, you miss the opportunity to gather information. This information could help you improve the security of your application before an attacker takes advantage of a vulnerability.

Instead of showing database errors to the user, log them to a file. The file must not be accessible to an attacker via the web server.

You can log errors to the PHP error log or to another file of your choice.

If you scan the application using the SQL Injection scan type in Acunetix, it confirms the vulnerability.

prevent SQL injection
prevent sql injection

SQL Injection Prevention in PHP

Parameterized queries

To prevent and/or fix SQL Injection vulnerabilities, start by reading advice in our Defence in Depth series: Parameterize SQL queries. Parameterized queries are simple to write and understand. They force you to define the SQL query and use placeholders for user-provided variables in the query. After the SQL statement is defined, you can pass each parameter to the query. This allows the database to distinguish between the SQL command and data supplied by a user. If an attacker inputs SQL commands, the parameterized query treats them as untrusted input and the database does not execute injected SQL commands. If you properly parametrize SQL queries, all user input that is passed to the database is treated as data and can never be confused as being part of a command.

PHP Data Objects (PDO)

Many PHP developers access databases using mysql or mysqli extensions. It is possible to use parameterized queries with the mysqli extension but PHP 5.1 introduced a better way to work with databases: PHP Data Objects (PDO). PDO provides methods that make parameterized queries easy to use. It also makes the code easier to read and more portable – it works with several databases, not just MySQL.

The following example represents the same application as the one presented at the beginning of the article. This improved code uses PDO with parameterized queries to prevent the SQL Injection vulnerability.

<?php

/**
 * Check if the 'id' GET variable is set
 * Example - http://localhost/?id=1
 */
if (isset($_GET['id'])){
  $id = $_GET['id'];
  /**
   * Validate data before it enters the database. In this case, we need to check that
   * the value of the 'id' GET parameter is numeric
   */
   if ( is_numeric($id) == true){
    try{ // Check connection before executing the SQL query 
      /**
       * Setup the connection to the database This is usually called a database handle (dbh)
       */
      $dbh = new PDO('mysql:host=localhost;dbname=sql_injection_example', 'dbuser', 'dbpasswd');
      
      /**
       * Use PDO::ERRMODE_EXCEPTION, to capture errors and write them to
       * a log file for later inspection instead of printing them to the screen.
       */
      $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      
      /**
       * Before executing, prepare statements by binding parameters.
       * Bind validated user input (in this case, the value of $id) to the
       * SQL statement before sending it to the database server.
       *
       * This fixes the SQL injection vulnerability.
       */
      $q = "SELECT username 
          FROM users
          WHERE id = :id";
      // Prepare the SQL query string.
      $sth = $dbh->prepare($q);
      // Bind parameters to statement variables.
      $sth->bindParam(':id', $id);
      // Execute statement.
      $sth->execute();
      // Set fetch mode to FETCH_ASSOC to return an array indexed by column name.
      $sth->setFetchMode(PDO::FETCH_ASSOC);
      // Fetch result.
      $result = $sth->fetchColumn();
      /**
       * HTML encode our result using htmlentities() to prevent stored XSS and print the
       * result to the page
       */
      print( htmlentities($result) );
      
      //Close the connection to the database.
      $dbh = null;
    }
    catch(PDOException $e){
      /**
       * You can log PDO exceptions to PHP's system logger, using the
       * log engine of the operating system
       *
       * For more logging options visit http://php.net/manual/en/function.error-log.php
       */
      error_log('PDOException - ' . $e->getMessage(), 0);
      /**
       * Stop executing, return an Internal Server Error HTTP status code (500),
       * and display an error
       */
      http_response_code(500);
      die('Error establishing connection with database');
    }
   } else{
    /**
     * If the value of the 'id' GET parameter is not numeric, stop executing, return
     * a 'Bad request' HTTP status code (400), and display an error
     */
    http_response_code(400);
    die('Error processing bad or malformed request');
   }
}

If you now scan the application using the SQL Injection scan type in Acunetix, it confirms that the code is not vulnerable to SQL Injection.
prevent sql injection

Conclusions

Parameterized queries solve SQL Injection vulnerabilities. This example uses PDO to fix the vulnerability but you can still use mysqli functions to prevent SQL Injection. However, PDO is easier to use, more portable, and supports the use of named parameters (in this example, we used :id as a named parameter).

For more information about preventing SQL Injections, see the OWASP SQL Prevention Cheat Sheet.

 

Frequently asked questions

Most web programming languages offer the option to use prepared statements (parameterized queries). Most database management systems support stored procedures. Therefore, these two key methods for avoiding SQL Injections are available in almost all web development environments. You should not try any other methods unless necessary.

Learn how to prevent SQL Injections in Java.

In the very unlikely event that your programming language does not support prepared statements and your SQL engine does not support stored procedures, avoid using user input directly in SQL strings at any cost. If you can, instead of using user input, use fixed lists of values. Simple filtering and escaping is not enough because attackers easily circumvent it.

Bobby Tables contains a detailed list of SQLi prevention techniques for many languages.

Every type of vulnerability has to be prevented using different techniques but a general rule is: never trust user input. To learn how to prevent a specific vulnerability, first, scan your web application with Acunetix and see what vulnerabilities your application has. For every vulnerability, Acunetix gives you helpful links including information on how to prevent a specific type of vulnerability.

Read our PHP security guide.

Only good programming practices prevent SQL Injection attacks. A web application firewall is like a band-aid. If you find an SQL Injection vulnerability but you cannot fix it immediately, you may use a WAF to create temporary rules protecting from an attack. You can even export such rules automatically from Acunetix. However, such rules are often circumvented and you must fix your application as soon as possible.

Learn more about Acunetix export to web application firewalls.

SQL Injections may be detected either by manual penetration testing of the website or web application or by using an automated web vulnerability scanner. The best practice is to first find most vulnerabilities using a scanner like Acunetix and only then perform additional manual penetration testing. This is because manual testing is very costly in comparison to automated scans.

Read about best practices for vulnerability scanning and penetration testing.

SHARE THIS POST
THE AUTHOR
Ian Muscat

Ian Muscat used to be a technical resource and speaker for Acunetix. More recently, his work centers around cloud security and phishing simulation.