26
.
06
.
2024
25
.
06
.
2024
Backend
Ruby on Rails
Software

The Deadly Sins in RoR security - SQL Injection

Urszula Sołogub
Ruby Developer
Ula Sołogub - SQL Injection in Ruby on Rails

Ruby on Rails provides tools and security measures that are foolproof in many use cases. A regular developer writing code does not have to worry about whether a form field is vulnerable or someone could bypass authentication by typing a simple semicolon on the login screen. However, as the application becomes more complex and requirements become more complicated, the demand for custom solution rises. Foolproof mechanisms may not always align with the business logic, so developers sometimes need to get creative. And that is exactly where the trouble begins…

The Sin

Software development is not merely about creating and maintaining applications. A significant aspect of a developer's role involves working with data - extracting, presenting, and safeguarding it. Nowadays, people entrust their most valuable information to various applications - online shops, banks or offices. Any leakage of personal data can result in multiple individual tragedies and financial losses. Hence, developers must prioritize security as paramount.

One of the most common vulnerabilities is known as the SQL injection. This exploit specifically targets the database to read or modify the information it contains. Consequently, sensitive information can be stolen, and valuable data may be irreversibly damaged, causing serious harm. To execute an injection one sends data containing code in SQL language as input. If the server lacks adequate security measures, the code will be executed.

Sloth

Using Ruby on Rails’ ORM for standard use cases is very secure. However, when there is a need to extract complex data with intricate associations, developers quickly realize that deep knowledge of the framework can never completely replace plain SQL writing skills. Often, it is easier and faster to just write a query. The issue is the conveniences provided by RoR have made us developers quite lazy. We tend to forget that when implementing custom solutions, the built-in RoR mechanisms are no longer our guardian angels. It then becomes our responsibility to ensure protection against vulnerabilities.

User.find_by_sql(%Q(
  SELECT *
    FROM users
    WHERE email = '#{params[:email]}'
      AND password = '#{params[:password]}'))
))

A big no-no is using string interpolation or concatenation in SQL queries when the values inserted into the query are provided by users. Assuming that the example above is used for authentication, anyone can access any account. For example, if we provide parameters containing an existing e-mail and, instead of a password, enter a value that is always true: params[:password] = "password' OR '1' = '1". As a result, the following query is being executed in the database:

SELECT *
  FROM users
  WHERE email='user@example.com
    AND password='password' OR '1'='1' 

The database will search for a user with the provided e-mail. If it exists, no matter what the password was, the AND password='password' OR '1' = '1' part will be translated to AND false OR true, which will always result in true value. Poof, the authentication is no more.

pry(main)> email = "'; DROP DATABASE application"
pry(main)> User.find_by_sql(%Q(SELECT * FROM users WHERE email = '#{email}'))

User Load (3.4ms)  SELECT * FROM users WHERE email = ''; DROP DATABASE application;'

ActiveRecord::StatementInvalid: PG::SyntaxError:
ERROR:  cannot insert multiple commands into a prepared statement

Thankfully, database engines usually do not allow us to commit ye good ol’ semicolon trick to provide another command and destroy all the records. For example, PostgreSQL will end an attempt with PG::SyntaxError. But it does not mean that all the databases have such behavior implemented, so do not let your guard down.

Conscience examination

The example with authentication and providing credentials is quite extreme. Rails projects typically use Devise to handle it anyway, but this example clearly shows the potential impact. I hope no one commits such obvious sins in commercial projects. However, let us examine our conscience and check for any less obvious mistakes that we all did at some point.

Record.delete_all("id = #{params[:id]}")

Product.joins(%Q(
  INNER JOIN categories
    ON categories.id = products.category_id
    AND categories.name = '#{params[:category]}'
))

Using user parameters anywhere in the database query is always risky. Depending on the original intention of the query, different kinds of damage can occur - from serious data leaks to actual records deletion.

class ProductsController < ApplicationController
  def index
    filters = []
    filters << "created_at >= '#{params[:start_date]}'" if params[:start_date]
    filters << "created_at <= '#{params[:end_date]}'" if params[:end_date]
    filters << "status = '#{params[:status]}'" if params[:status]

    @products = Product.where(filters.join(' AND '))
  end
end

Complex dynamic queries are also something developers usually forget to stay alert about, especially when they involve filtering based on user input, such as date ranges. Even if the frontend uses selectors that do not allow any custom values, they still cannot be fully trusted, as user can manually modify HTTP arguments or the entire payload and send them directly to the backend. Furthermore, even if the application is designed only for trained professionals for internal company use, users still cannot be fully trusted. You never know if they might become victims of phishing or if someone steals their identity to gain access to the data you protect.

Repentance

It is impossible to develop a fully functional application that completely avoids using user-provided parameters. Concatenating values directly from the outside world with our own code carries inherent risks. However, there are different syntax constructs that allow us to use any user-provided parameters without leading to SQL injection vulnerability. Simply, as long as we do not overcomplicate the tools that Ruby on Rails provides, nothing bad will happen.

@users = User.where("email = ?", params[:email])
@users = User.where(email: params[:email])

@user = User.find_by("email = ?", params[:email])
@user = User.find_by(email: params[:email])
@user = User.find_by_email(params[:email])

Conclusion

The conclusion about SQL Injection in Ruby on Rails is quite simple. Whenever we develop custom features, we need to stay vigilant. Even very experienced developers can forget about the basics, such as avoiding the use of raw user parameters in queries. That is why teamwork and code reviews are crucial - if one person overlooks a rule, someone else can catch it.

Urszula Sołogub
Ruby Developer

Check my Twitter

Check my Linkedin

Did you like it? 

Sign up To VIsuality newsletter

READ ALSO

Recap of Friendly.rb 2024 conference

Insights and Inspiration from Friendly.rb: A Ruby Conference Recap

02
.
10
.
2024
Kaja Witek
Conferences
Ruby on Rails

Covering indexes - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Ruby on Rails
Postgresql
Backend
Ula Sołogub - SQL Injection in Ruby on Rails

The Deadly Sins in RoR security - SQL Injection

14
.
11
.
2023
Urszula Sołogub
Backend
Ruby on Rails
Software
Michal - Highlights from Ruby Unconf 2024

Highlights from Ruby Unconf 2024

14
.
11
.
2023
Michał Łęcicki
Conferences
Visuality
Cezary Kłos - Optimizing Cloud Infrastructure by $40 000 Annually

Optimizing Cloud Infrastructure by $40 000 Annually

14
.
11
.
2023
Cezary Kłos
Backend
Ruby on Rails

Smooth Concurrent Updates with Hotwire Stimulus

14
.
11
.
2023
Michał Łęcicki
Hotwire
Ruby on Rails
Software
Tutorial

Freelancers vs Software house

02
.
10
.
2024
Michał Krochecki
Visuality
Business

Table partitioning in Rails, part 2 - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Backend
Postgresql
Ruby on Rails

N+1 in Ruby on Rails

14
.
11
.
2023
Katarzyna Melon-Markowska
Ruby on Rails
Ruby
Backend

Turbo Streams and current user

29
.
11
.
2023
Mateusz Bilski
Hotwire
Ruby on Rails
Backend
Frontend

Showing progress of background jobs with Turbo

14
.
11
.
2023
Michał Łęcicki
Ruby on Rails
Ruby
Hotwire
Frontend
Backend

Table partitioning in Rails, part 1 - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Postgresql
Backend
Ruby on Rails

Table partitioning types - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Postgresql
Backend

Indexing partitioned table - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Backend
Postgresql
SQL Views in Ruby on Rails

SQL views in Ruby on Rails

14
.
11
.
2023
Jan Grela
Backend
Ruby
Ruby on Rails
Postgresql
Design your bathroom in React

Design your bathroom in React

14
.
11
.
2023
Bartosz Bazański
Frontend
React
Lazy Attributes in Ruby - Krzysztof Wawer

Lazy attributes in Ruby

14
.
11
.
2023
Krzysztof Wawer
Ruby
Software

Exporting CSV files using COPY - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Postgresql
Ruby
Ruby on Rails
Michał Łęcicki - From Celluloid to Concurrent Ruby

From Celluloid to Concurrent Ruby: Practical Examples Of Multithreading Calls

14
.
11
.
2023
Michał Łęcicki
Backend
Ruby
Ruby on Rails
Software

Super Slide Me - Game Written in React

14
.
11
.
2023
Antoni Smoliński
Frontend
React
Jarek Kowalewski - ILIKE vs LIKE/LOWER - Postgres Stories

ILIKE vs LIKE/LOWER - Postgres Stories

14
.
11
.
2023
Jarosław Kowalewski
Ruby
Ruby on Rails
Postgresql

A look back at Friendly.rb 2023

14
.
11
.
2023
Cezary Kłos
Conferences
Ruby

Debugging Rails - Ruby Junior Chronicles

14
.
11
.
2023
Piotr Witek
Ruby on Rails
Backend
Tutorial