Skip to main content

SQL Injection - Headers

Need​

Prevention of SQL injection attacks in the application headers

Context​

  • Usage of Ruby 2.0+ for developing Ruby applications
  • Usage of Rails framework for building web applications
  • Usage of ActiveRecord for Object-Relational Mapping (ORM) in database operations

Description​

Non compliant code​

# Controller
class ClientsController < ApplicationController
  def show
    idClient = request.headers['idClient']
    @client = Client.find_by_sql("SELECT * FROM clients WHERE id = #{idClient}")
  end
end

In this code snippet, we have a Ruby on Rails controller named ClientsController which handles operations related to Client objects. One of the operations is show which is used to retrieve a specific client's details.

The show method retrieves the idClient from the headers of the HTTP request. This idClient is then directly used in a SQL query to fetch the client's details from the database. The SQL query is constructed as a string with the idClient value embedded in it.

The problem with this approach is that it leaves the application vulnerable to SQL Injection attacks. If an attacker sends a specially crafted idClient value in the request headers, they could manipulate the SQL query to retrieve unauthorized data or perform other malicious operations. This is because the idClient value is directly interpolated into the SQL query string without any sanitization or escaping.

For example, if an attacker sends 1 OR 1=1 as the idClient, the SQL query becomes SELECT * FROM clients WHERE id = 1 OR 1=1 which would return all clients instead of just one, thus causing a data leak.

Steps​

  • Use parameterized queries or prepared statements to handle user input in SQL queries.
  • Sanitize and validate user input before using it in SQL queries.
  • Implement input validation to ensure that only valid input is accepted.
  • Use ORM (Object-Relational Mapping) techniques provided by the framework to interact with the database instead of writing raw SQL queries.
  • Avoid concatenating user input directly into SQL queries.
  • Implement proper access control mechanisms to restrict unauthorized access to sensitive data.
  • Regularly update and patch the framework and its dependencies to ensure the latest security fixes are applied.

Compliant code​

# Controller
class ClientsController < ApplicationController
  def show
    idClient = request.headers['idClient']
    @client = Client.where("id = ?", idClient).first
  end
end

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

The fixed code uses the ActiveRecord where method to query the database. This method automatically escapes the input parameters, preventing SQL Injection attacks. The where method takes two arguments: the SQL fragment and the value to substitute into the SQL fragment. The '?' in the SQL fragment is a placeholder that gets replaced with the sanitized 'idClient' value.

The first method is used to return the first record that matches the condition. If no record matches the condition, nil is returned. This is a safer alternative to find_by_sql, which executes a raw SQL query and is therefore more susceptible to SQL Injection attacks.

This fix ensures that the application is no longer vulnerable to SQL Injection attacks through the 'idClient' header. However, it's important to apply the same principles to all parts of the application that interact with the database. Always use parameterized queries or the ORM's methods to interact with the database, and never concatenate user input directly into SQL queries.

References​

Last updated on