Skip to main content

SQL injection

Need​

Implementation of secure coding practices to prevent SQL injection attacks

Context​

  • Usage of Ruby version 2.0+ for developing Ruby applications
  • Usage of Rails framework for building web applications
  • Usage of ActiveRecord for Object-Relational Mapping (ORM) in a Node.js project

Description​

Non compliant code​

def show
  @user = User.find_by_sql(["SELECT * FROM users WHERE id = ?", params[:id]])
end

In the above code, it is trying to fetch a user from the database using the user's ID. The ID is taken directly from the request parameters and is used in the SQL query without any form of sanitization or validation. This is a typical example of an SQL Injection vulnerability because an attacker can manipulate the 'id' parameter in the request to execute arbitrary SQL commands.

For example, an attacker could provide an 'id' parameter of 1; DELETE FROM users; which would delete all users from the database. The application would construct the following SQL command: SELECT * FROM users WHERE id = 1; DELETE FROM users; which is clearly not the intended behavior.

This vulnerability exists because the code does not use parameterized queries or prepared statements, which automatically handle the necessary escaping of special characters to prevent SQL injection attacks. In addition, there is no validation of the 'id' parameter to ensure it is a valid integer before it is used in the SQL query.

Steps​

  • Use parameterized statements or prepared statements to execute SQL queries.
  • Avoid building dynamic SQL queries by concatenating user input directly into the query string.
  • Validate and sanitize user input before using it in SQL queries.
  • Use ORM (Object-Relational Mapping) techniques provided by the framework to interact with the database.
  • Avoid using raw SQL queries unless absolutely necessary.
  • Implement input validation and data sanitization techniques to prevent SQL injection attacks.
  • Educate developers about the risks of SQL injection and provide training on secure coding practices.
  • Regularly update and patch the Ruby on Rails framework to benefit from security fixes and improvements.

Compliant code​

def show
  @user = User.find(params[:id])
end

The original code was vulnerable to SQL injection because it used raw SQL queries with user input directly inserted into the query. This could allow an attacker to manipulate the SQL query by providing specially crafted input.

The fixed code uses the find method provided by the Ruby on Rails framework. This method automatically escapes the input and prevents SQL injection attacks. The find method is part of the ActiveRecord ORM (Object-Relational Mapping) provided by Rails, which provides a safer and more convenient way to interact with the database.

The find method takes an id and returns the user with that id. If no user with that id exists, it raises an ActiveRecord::RecordNotFound exception. This is a standard behavior in Rails and it's usually handled at the controller level by rendering a 404 page.

By using the find method, we avoid the need to write raw SQL, making our code safer and more maintainable. We also take full advantage of the Rails framework, which is designed to help developers write secure and efficient code.

References​

Last updated on