Skip to main content

SQL injection - Code

Need​

Implementation of proper input validation and the use of parameterized statements or stored procedures to prevent SQL injection attacks.

Context​

  • Usage of Ruby for building web applications and scripting
  • Usage of ActiveRecord for Object-Relational Mapping (ORM) in database operations

Description​

Non compliant code​

def search_user(username)
  User.find_by_sql("SELECT * FROM users WHERE username = '#{username}'")
end

In the provided code snippet, we have a method search_user which takes a username as an argument and uses it directly in a SQL query to search for a user in the database.

The issue here is that the username is directly interpolated into the SQL query string without any form of validation or sanitization. This leaves the code vulnerable to SQL Injection attacks. An attacker could provide a username such as 'admin'; DROP TABLE users;-- which would result in the entire users table being dropped from the database.

This is a serious security vulnerability as it allows an attacker to execute arbitrary SQL commands on the database. This can lead to data loss, data corruption, unauthorized access to data, and other serious consequences.

Steps​

  • Use parameterized statements or prepared statements to handle user input in SQL queries.
  • Avoid concatenating user input directly into SQL queries.
  • Validate and sanitize user input before using it in SQL queries.
  • Implement input validation to ensure that only expected data types and formats are accepted.
  • Implement proper error handling and logging to detect and respond to potential SQL injection attacks.
  • Consider using an ORM (Object-Relational Mapping) framework, such as ActiveRecord in Ruby on Rails, which automatically handles parameterization and sanitization of SQL queries.

Compliant code​

def search_user(username)
  User.where("username = ?", username)
end

The original code was vulnerable to SQL injection attacks because it was directly interpolating user input into a SQL query. This is a dangerous practice as it allows an attacker to manipulate the SQL query by providing specially crafted input.

The fixed code uses the where method provided by ActiveRecord, which is the default ORM (Object-Relational Mapping) framework in Ruby on Rails. The where method automatically escapes the input parameters to prevent SQL injection attacks.

In the where method, the question mark (?) is a placeholder that gets replaced by the sanitized username parameter. This way, the username parameter is properly escaped, and the SQL query is safe from injection attacks.

This is a parameterized query, which means that the SQL and the data are sent separately and the data is inserted safely into the query by the database engine. This makes it impossible for an attacker to inject malicious SQL.

In addition to using parameterized queries, it's also important to validate and sanitize user input before using it in SQL queries, implement proper error handling and logging to detect and respond to potential SQL injection attacks, and consider using an ORM framework, which automatically handles parameterization and sanitization of SQL queries.

References​

Last updated on