Skip to main content

SQL Injection - Headers

Need​

Prevention of SQL injection attacks in the idClient header and application fields

Context​

  • Usage of Scala for building scalable and high-performance applications
  • Usage of play.api.mvc for handling HTTP requests and responses in Play Framework
  • Usage of Anorm for type-safe SQL queries in Scala

Description​

Non compliant code​

import play.api.mvc._
import anorm._

def getClientDetails = Action { request =>
  val idClient = request.headers.get("idClient").getOrElse("")

  val sql = SQL(s"SELECT * FROM clients WHERE id = $idClient")
  val result = db.executeQuery(sql)

  Ok(result)
}

The above code is vulnerable to SQL Injection via the idClient header.

The request.headers.get("idClient") is used to fetch the idClient from the request headers. This value is directly used in the SQL query without any validation or sanitization. This means that if an attacker sets the idClient header to a malicious SQL statement, it will be executed directly on the database.

For example, an attacker could set idClient to 1; DROP TABLE clients; --, which would result in the following SQL being executed: SELECT * FROM clients WHERE id = 1; DROP TABLE clients; --. This would delete the entire clients table from the database.

This is a serious security vulnerability as it allows an attacker to execute arbitrary SQL statements on the database, leading to data loss, data corruption, or unauthorized access to data.

Steps​

  • Use parameterized queries or prepared statements to prevent SQL injection.
  • Sanitize and validate user input before using it in SQL queries.
  • Implement proper input validation and filtering to ensure only valid data is used in the query.
  • Use an ORM (Object-Relational Mapping) framework or a query builder to handle SQL queries safely.
  • Avoid concatenating user input directly into SQL queries.
  • Use framework-specific features or libraries that provide built-in protection against SQL injection.
  • Regularly update and patch the application and its dependencies to ensure any security vulnerabilities are addressed.
  • Educate developers about secure coding practices and the risks associated with SQL injection.
  • Perform regular security audits and penetration testing to identify and fix any vulnerabilities in the application.

Compliant code​

import play.api.mvc._
import anorm._

def getClientDetails = Action { request =>
  val idClient = request.headers.get("idClient").getOrElse("")

  val sql = SQL("SELECT * FROM clients WHERE id = {id}").on("id" -> idClient)
  val result = db.executeQuery(sql)

  Ok(result)
}

The original code was vulnerable to SQL Injection through the idClient header. The idClient value was directly interpolated into the SQL query string, which means that an attacker could potentially manipulate the SQL query by sending a specially crafted idClient value.

The fixed code uses parameterized queries to prevent SQL Injection. Instead of directly interpolating the idClient value into the SQL query string, it uses a placeholder {id} and then binds the idClient value to this placeholder using the .on("id" -> idClient) method. This ensures that the idClient value is properly escaped and treated as a literal value, not part of the SQL syntax, thus preventing SQL Injection.

In addition, it's important to validate and sanitize the idClient value before using it in the SQL query. This could involve checking that it's a valid client ID format, removing any potentially harmful characters, etc. This wasn't included in the code fix because it depends on the specific requirements and context of your application.

Remember to always avoid using user input directly in SQL queries, use parameterized queries or prepared statements instead. Also, consider using an ORM (Object-Relational Mapping) framework or a query builder, which can provide additional layers of protection against SQL Injection. Regularly update and patch your application and its dependencies, educate your developers about secure coding practices, and perform regular security audits and penetration testing.

References​

Last updated on