Preventing SQL Injection for Rails Queries.

As a Rails developer, writing secure and efficient model methods that interact with the database is crucial. Recently, I discovered that using the Rails query API can prevent SQL injection and improve the security of your application.

In this article, we will explore the basics of SQL injection and how the Rails query API can be used to secure and prevent SQL injection and improve the efficiency of your model methods. In the end, you will have the knowledge and skills needed to write secure and efficient model methods in Rails.

Let’s observe these two methods where the User model has a method that returns 3 most recent posts

# app/models/user.rb

  def most_recent_posts
   posts.order('created_at DESC').limit(3)
  end

# And

  def most_recent_posts
   posts.order(created_at: :desc).limit(3)
  end

Both methods will return a user's three most recent likes in descending order of creation time. The first method uses a string with the SQL ORDER BY clause, while the second method uses the ActiveRecord method order with a symbol to specify the ordering.

It’s worth noting that the way the SQL is written doesn’t affect the performance as much as the indexes on the table or the database's query planner. So, it’s better to test the performance in your specific case and see which one performs better. However, it’s good practice to use the Rails query API to prevent SQL injection. This will ensure the security of your application and avoid any potential vulnerabilities.

What is SQL Injection?

• SQL injection is a type of security vulnerability that occurs when an attacker is able to insert malicious SQL code into a query, allowing them to gain unauthorized access to the database or manipulate data.

Photo by Magnet.me on Unsplash

What is the Rails Query API and How to use them to prevent SQL Injection?

• The Rails query API is a set of methods provided by ActiveRecord, the ORM (Object-Relational Mapping) framework used in Ruby on Rails, that allows you to interact with the database in a secure and efficient manner. These methods provide a higher-level, more abstract interface for querying the database, and take care of escaping any user-supplied input to prevent SQL injection attacks.
• By using the Rails query API, you can prevent SQL injection by ensuring that any user-supplied input is properly escaped and sanitized before it’s included in a query. For example:

Instead of using raw SQL and string interpolation to build a query, you can use ActiveRecord methods such as where, order, limit etc. These methods will automatically escape any user-supplied input, so you don't have to worry about it. eg:

def most_recent_posts(user_input)
 likes.where("name = ?", user_input).limit(3).order(created_at: :desc)
end

In this example, thewhere method takes two arguments: the SQL fragment and the user input. The ? is a placeholder, and the user_input will automatically be sanitized and escaped before it's included in the query, preventing any SQL injection attack.

Sanitization refers to the process of cleaning or validating user-supplied input to ensure that it doesn’t contain any malicious code. For example, if the user input is a string, ActiveRecord will check for any special characters or SQL keywords that could be used to inject malicious code into the query. If any are found, they will be removed or replaced with safe equivalents.

Escaping refers to the process of adding special characters to user-supplied input to make it safe to include in a query. For example, if the user input is a string, ActiveRecord will add escape characters to any special characters or SQL keywords that could be used to inject malicious code into the query. This ensures that the input is treated as a literal value and not as part of the query itself.

The use of placeholders is also an important part of preventing SQL injection attacks. A placeholder is a special marker, such as “user_name”, that indicates where the user-supplied input should be inserted into the query. The ActiveRecord framework will automatically substitute the user input for the placeholders, and the SQL query is constructed with the safe user input.

=> Here are some examples of using the Rails query API to build queries:

a. Finding all users with a specific email address:

# Using SQL Placeholders
User.where("email = ?", 'example@example.com')

Using SQL placeholders is a secure approach to include dynamic values in SQL queries. Placeholders, represented by ?, are utilized within the query, and values are passed separately.

In this query, the ? acts as a placeholder for the email address value, ensuring proper sanitation and protection against SQL injection.

# Using Named Placeholders:
User.where("email = :email", email: 'example@example.com')
User.where("username LIKE :search OR email LIKE :search", search: "%#{params[:search]}%")

Named placeholders, represented by symbols preceded by a colon (:), offer a secure alternative for incorporating dynamic values in SQL queries. These placeholders are associated with specific values when executing the query. For example:
'example@example.com' passed separately, ensuring proper sanitation and protection against SQL injection.

Additionally, named placeholders can be useful for filtering based on multiple criteria. In this scenario, the :search placeholder is employed to search for users based on usernames or email addresses containing a specified search term, maintaining security against SQL injection.

b. Finding all orders placed after a certain date:

Order.where('created_at > ?', 1.week.ago)

The where method is used to filter the orders by creation date. The method takes a string with the SQL fragment and a value as the second parameter. The value is passed as a placeholder in the query.

c. Ordering all products by price in ascending order:

Product.order(price: :asc)

In this example, the order method is used to sort the products by price. The method takes a symbol with the column name and the sorting order.

=> Here are examples of not using the Rails query API:

a. Finding all users with a specific email address:

User.find_by_sql("SELECT * FROM users WHERE email = '#{params[:email]}'")
# eg Vulnerable to SQL injection
# User.where("username LIKE '%#{params[:search]}%' OR email LIKE '%#{params[:search]}%'")

In this example, the find_by_sql method is used to execute a raw SQL query. The email address is passed as a string, which is vulnerable to SQL injection if the user input is not properly sanitized.

b. Finding all orders placed after a certain date:

Order.find_by_sql("SELECT * FROM orders WHERE created_at > #{1.week.ago}")

The find_by_sql method is used to execute a raw SQL query. The date value is passed as a string, which is vulnerable to SQL injection if the user input is not properly sanitized.

c. Ordering all products by price in ascending order:

Product.find_by_sql("SELECT * FROM products ORDER BY price ASC")

In this example, the find_by_sql the method is used to execute a raw SQL query. The sorting order is passed as a string, which is vulnerable to SQL injection if the user input is not properly sanitized.

Conclusion

• In summary, when you use the Rails query API, any user-supplied input is automatically sanitized, escaped, and passed as placeholders in the query, which prevents any SQL injection attack. This is a crucial feature of ActiveRecord that allows you to write secure, database-agnostic code and protects your application from potential vulnerabilities.

Written by Ben Mukebo.

I am a software developer, familiar with a variety of different web technologies and frameworks, and keen on always finding ways to explain things as simple as possible.

If this article has been helpful, please hit the 👏 button and share it with others! 🚀 to show how much you liked it! 😄