Home / Linux / Prevent SQL injection vulnerabilities in PHP applications and fix them

Prevent SQL injection vulnerabilities in PHP applications and fix them

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: %sn", $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.

Note — Do not sanitize user input by escaping or removing special characters. An attacker can use encoding to bypass such protection.

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.

Ian Muscat

Acunetix developers and tech agents regularly contribute to the blog. All the Acunetix developers come with years of experience in the web security sphere.



Source link

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.

x

Check Also

What Are Injection Attacks | Acunetix

What is Code Injection | Acunetix

Code Injection or Remote Code Execution (RCE) enables the attacker to execute malicious code as ...