Skip to main content

SQL Injection - Headers

Need​

Prevention of SQL injection attacks in the application headers

Context​

  • Usage of PHP for server-side scripting and web development
  • Usage of mysqli for interacting with MySQL databases in Node.js

Description​

Non compliant code​

<?php
    $idClient = $_SERVER['HTTP_IDCLIENT'];
    $sql = "SELECT * FROM clients WHERE id = $idClient";
    $result = mysqli_query($conn, $sql);
?>

The above code is a simple PHP script that uses the Laravel framework to fetch client data from a database.

The script retrieves the idClient value from the HTTP header and uses it directly in an SQL query. This is a classic example of an SQL Injection vulnerability.

The issue here is that the idClient value is not sanitized or validated before it is used in the SQL query. This means that a malicious user can modify the idClient header value to include SQL commands, which will then be executed by the server.

For example, an attacker could set the idClient header to 1; DROP TABLE clients;, which would delete the entire clients table from the database.

This is a serious vulnerability that can lead to data loss, data corruption, unauthorized access to data, and other security issues. It is strongly recommended to sanitize and validate all input data before using it in SQL queries.

Steps​

  • Sanitize the input: Always sanitize the input data coming from the user or from any untrusted source. This can be done using functions that escape special characters in the strings that are used in the SQL query.
  • Use prepared statements: Prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker. In the case of PHP, this can be done using PDO (PHP Data Objects) or MySQLi.
  • Limit privileges: Ensure that the database user used in the PHP application has only the privileges that are absolutely necessary. This can limit the potential damage.
  • Implement a Web Application Firewall (WAF): A WAF can help to detect and block SQL Injection attacks.

Compliant code​

<?php
    $idClient = $_SERVER['HTTP_IDCLIENT'];
    $stmt = $conn->prepare("SELECT * FROM clients WHERE id = ?");
    $stmt->bind_param("i", $idClient);
    $stmt->execute();
    $result = $stmt->get_result();
?>

The original code was vulnerable to SQL Injection because it directly used the value from the idClient header in the SQL query. This means that if an attacker was able to modify the idClient header, they could potentially manipulate the SQL query to their advantage.

The fixed code uses a prepared statement to prevent this type of attack. Here's how it works:

  1. Instead of directly inserting the idClient into the SQL query, a placeholder (?) is used. This placeholder will later be replaced by the actual idClient value.

  2. The prepare function is called on the $conn object (which represents the database connection) with the SQL query as its argument. This function returns a new statement object.

  3. The bind_param function is called on the statement object. This function binds the idClient value to the placeholder in the SQL query. The "i" argument means that the idClient value should be treated as an integer.

  4. The execute function is then called on the statement object to run the SQL query.

  5. Finally, the get_result function is called on the statement object to retrieve the result of the SQL query.

This way, even if an attacker tries to inject SQL commands into the idClient header, these commands will not be executed because they are treated as a string, not as part of the SQL query.

References​

Last updated on