How to Prevent SQL Injection Vulnerabilities in PHP Applications

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.

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.

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

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.

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.

How to Prevent SQL Injection in PHP – Step By Step

To prevent SQL Injection vulnerabilities in PHP, use PHP Data Objects (PDO) to create parametrized queries (prepared statements).

	Step 1: Validate input If possible, validate the data supplied by the user against a whitelist: if (is_numeric($id) == true) { ... }
	Step 2: Prepare a query Create your query using parameter names preceded with colons as placeholders: $q = "SELECT username FROM users WHERE id = :id";
	Step 3: Create the prepared statement Create the prepared SQL statement: $sth = $dbh->prepare($q);
	Step 4: Bind the parameters to the prepared statement Bind your parameters to the query. For example, to bind the value of ID, use: $sth->bindParam(':id', $id);
	Step 5: Execute your query After you pass the parameters, you may execute your query. For example: $sth->execute();
	Step 6: Fetch the result After you execute the query, you may fetch its result to use further on. For example: $result = $sth->fetchColumn();
	Step 7: Validate your application To make sure that your application is secure, use Acunetix and run a website vulnerability scan for your website.

Frequently asked questions

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.