Prevent SQL injection vulnerabilities in PHP applications and fix them

SQL injection (SQLi) refers to an injection attack wherein an attacker can execute arbitrary SQL statements by tricking a web application in processing an attacker’s input as part of an SQL statement. This post will focus on how to prevent SQL injection vulnerabilities within PHP applications and fix them.

This post assumes you have a basic understanding of SQL injection and the different variations of SQL injection.

The problem

Before we delve into how to prevent SQL injection in PHP, we need to understand what an application vulnerable to SQL injection looks like. In this example, we’ll be using a very simple application which accepts an id inside of a GET parameter (this can very well be a POST request or any other HTTP method) and prints the name of a user on screen.

Note — This example shall be using MySQL, however, the same principles apply for other databases

Our simple application will have a database with the following table called ‘users’.

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

Warning — The following code block contains SQL Injection vulnerabilities

<?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, let’s go through each problem and see how it contributes to SQL injection.

Issue	Description	Suggested Remediation
Input Validation	Even though an id will always be a number, the example above is not validating user input at all. While validating user input is not a direct solution to SQL injection, it helps us control malicious user input into the database.	Before even processing the SQL query, validate user input. In this case, we need to check that the input is a number.
Code allows for SQL injection	The example above is accepting user input (in this case, from a GET parameter), and including 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.	Use parameterized queries when dealing with SQL queries that contains user input. A parameterized query allows the database to understand which parts of the SQL query should be considered as user input, therefore solving SQL injection.
Errors are displayed to the user	Errors allow an attacker to find out information that could lead to compromise. Information such as what database type and version is running, makes an attacker’s job easier when exploiting 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 are very valuable when trying to solve an issue, or to understand if an attempt to attack your application was made. Not keeping a log of database errors is a missed opportunity to gather information that could help you improve your application’s security before an attacker takes advantage of a vulnerability.	Instead of showing database errors to the user, log them in a file which isn’t accessible to an attacker via the web server. You can log errors to PHP’s error log or to another file of your choice.

A quick scan with the ‘SQL Injection’ Scan Type in Acunetix confirms the vulnerability.

The fix

Parameterized queries

Fixing an SQL injection vulnerability is actually quite straight-forward if you follow the advice outlined 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 beforehand, and use placeholders for the user-provided variables within the query. You can then pass in each parameter to the query after the SQL statement is defined, allowing the database to be able to distinguish between the SQL command and data inputted by a user. If SQL commands are inputted by an attacker, the parameterized query would treat these as untrusted input, and the injected SQL commands will never get to execute.

Note — Avoid sanitizing user input by escaping or removing special characters. An attacker can leverage encoding tricks to bypass such protections. Stick to using parameterized queries in order to avoid SQL injection vulnerabilities

Using PHP Data Objects (PDO)

Many PHP developers likely learned to access databases by using PHP’s mysql or mysqli extensions. While it is possible to write parameterized queries with PHP’s mysqli extension, PHP 5.1 introduced a better way to work with databases — PHP Data Objects (PDO). PDO not only provides methods that make parameterized queries easy to use, but also makes code more portable (PDO works with several databases, not just MySQL) and is easier to read.

The example below shows the same application above, but instead of being vulnerable to SQL injection and mysqli without making use of parameterized SQL queries, this example uses PDO with parameterized statements to fix the SQL injection vulnerability in the example above.

<?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');
      
      /**
       * We are going to 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 our SQL statement, we need to prepare it by 'binding' parameters.
       * We will bind our validated user input (in this case, it's the value of $id) to our
       * 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
      $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 Operating System's
       * system logging mechanism
       *
       * 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');
   }
}

By properly parameterizing SQL queries, any user input that is passed to the database is treated as data and can never be confused as being part of a command.

A scan with the ‘SQL Injection’ Scan Type in Acunetix confirms that the parameterized code is not vulnerable to SQL injection.

Wrap-up

Parameterizing queries, solves SQL injection vulnerabilities. While this example uses PDO to fix the vulnerability, you can still use mysqli PHP functions to prevent SQL injection. However, since PDO is more portable across databases and supports the use of named parameters (we used :id as a named parameter in our example), it generally makes it easier to use than the mysqli.