An SQL injection is a type of web vulnerability that occurs when an attacker injects malicious SQL code into a web application's input field. This can allow the attacker to gain unauthorized access to sensitive information, modify or delete data, and even take control of the entire system.
Let's suppose we have a website that requires a user to enter a username. The website uses the following SQL query to check whether the entered username matches an existing user in the database:
SELECT * FROM users WHERE username = '<username>'
Now, let's suppose a malicious user enters ' OR 1=1 -— as their username, and the query becomes:
SELECT * FROM users WHERE username = '' OR 1=1 --'
The original query has two single quotes where it would place the username in the middle. The first single quote in the malicious text is necessary so that it can close the starting single quote.
1=1 ensures the condition is always true for all rows.
-- is necessary so that it can comment out the final single quote in the original query.
This means that the query will return all rows from the users table leading to the misuse of the data.
Here are some ways to avoid SQL injection in Ruby on Rails:
One way to avoid SQL injection is to use parameterized queries. This involves using placeholders in SQL statements and binding the user input to those placeholders. Ruby on Rails provides the ActiveRecord query interface, automatically using parameterized queries.
user = User.where("username = #{params[:username]}")
In the example above, the value of params[:username] is directly interpolated into the SQL statement. This can be dangerous because an attacker can inject malicious SQL code into the parameter value, leading to a possible SQL injection vulnerability.
Let's see a better way of typing the query above.
user = User.where("username = ?", params[:username])
In the example above, the ? serves as a placeholder for the value of params[:username]. When the query is executed, the database engine replaces the placeholder with the actual value. In this way, we can prevent the attackers from including executable codes or malicious data since the parameter is treated as a literal value and it will have a type and length, and rules regarding the values that can be assigned.
This technique involves using ActiveRecord's built-in query methods, which automatically sanitize the input and prevent SQL injection.
user = User.find_by_sql("SELECT * FROM usersWHERE username = '#{params[:username]}'").first
In the example above, the find_by_sql method is used to execute a raw SQL query and params[:username] value is directly interpolated into the query, which can be dangerous because an attacker can inject malicious SQL code into the parameter value.
Let's see another way of doing the same.
user = User.where(username: params[:username]).first
In the example above, the where method is used to filter records based on the username column and the params[:username] value is passed in as an argument to the method, which automatically sanitizes the input.
This technique involves using ActiveRecord's sanitize_sql method to sanitize the input before interpolating it into a raw SQL query.
users = User.find_by_sql("SELECT * FROM usersWHERE username = '#{params[:username]}'")
In the example above, the params[:username] value is directly interpolated into the SQL query, which can be dangerous because an attacker can inject malicious SQL code into the parameter value.
We can write the query above in a better way.
username = ActiveRecord::Base.connection.quote(params[:username])users = User.find_by_sql("SELECT * FROM usersWHERE username = #{username}")
In the example above, the ActiveRecord::Base.connection.quote method is used to sanitize the params[:username] value before interpolating it into the SQL query. This technique ensures that any special characters in the input are properly escaped.
It's important to use parameterized queries, ActiveRecord query interface, or raw SQL with sanitization to prevent SQL injection vulnerabilities in Ruby on Rails. ActiveRecord provides a wide range of built-in query methods, such as find_by, where, order, limit, and many more. By using these methods instead of writing raw SQL, we can avoid SQL injection attacks.
To prevent data leaks or loss of database information from malicious SQL injections, it is essential to follow the golden rule of never directly using user input as an argument for Active Record methods. This simple change is a first good step.
It is important to thoroughly understand the working of every method and how it can be vulnerable to attack. By being mindful of these vulnerabilities and understanding how they can be exploited, we can write more secure code and protect our database.
Free Resources