Time-based SQL Injection
Need
Prevention of time-based SQL injection attacks
Context
- Usage of Ruby for building dynamic and object-oriented applications
- Usage of ActiveRecord for Object-Relational Mapping (ORM) in database operations
Description
Non compliant code
def search
query = "SELECT * FROM users WHERE name = '#{params[:name]}'"
@results = ActiveRecord::Base.connection.execute(query)
end
The above code is a simple search function in a Ruby on Rails application. It is designed to take a parameter from the user (in this case, a name) and use it to query a database for matching users.
The vulnerability lies in the way the SQL query is constructed. The params[:name] is directly interpolated into the SQL query string. This means that if a user were to provide a value like ' OR '1'='1', the resulting SQL query would be SELECT * FROM users WHERE name = '' OR '1'='1'. This would return all users, effectively bypassing any search restrictions.
Furthermore, if a user were to provide a value like ' OR SLEEP(10)--, the resulting SQL query would be SELECT * FROM users WHERE name = '' OR SLEEP(10)--. This would cause the database to pause for 10 seconds before returning a response, thus creating a time-based SQL injection vulnerability.
This vulnerability can be exploited by an attacker to extract sensitive information from the database, cause denial of service, or even execute arbitrary commands on the database server.
Steps
- Use parameterized queries or prepared statements to prevent SQL injection.
- Instead of directly concatenating user input into the query string, use placeholders and bind the user input to those placeholders.
- In Ruby on Rails, you can use ActiveRecord's query interface or ActiveRecord::Sanitization module to handle parameterized queries.
- Avoid executing raw SQL queries if possible and use ActiveRecord's query methods to build and execute queries.
- Validate and sanitize user input before using it in a query to prevent any malicious input from being executed as part of the query.
Compliant code
def search
@results = User.where("name = ?", params[:name])
end
The original code was vulnerable to Time-based SQL Injection because it was directly concatenating user input into the SQL query string. This could allow an attacker to inject malicious SQL code into the query, causing a delay in the execution of the query or executing a heavy query that generates a time delay. By observing the time response, the attacker could deduce some information and determine if a vulnerability is present to exploit it.
The fixed code uses ActiveRecord's where method to build and execute the query, which automatically handles parameterized queries. Instead of directly inserting params[:name] into the query string, it uses a placeholder (?) and binds params[:name] to that placeholder. This prevents any SQL code in params[:name] from being executed as part of the query, thus preventing SQL injection.
In addition, the fixed code avoids executing raw SQL queries, which is a best practice for preventing SQL injection. Instead, it uses ActiveRecord's query methods, which are designed to safely build and execute SQL queries.
Finally, it's important to note that you should always validate and sanitize user input before using it in a query. This can further prevent any malicious input from being executed as part of the query.