RAILS: Follow the ActiveRecord recommendations in order to be protected against SQL injection

07 Jun RAILS: Follow the ActiveRecord recommendations in order to be protected against SQL injection

Working recently on a feature for searching I found out that sometimes when I’m creating complex queries we forget to take advantages of ActiveRecord and as a result our SQL queries could be vulnerable to SQL injection, for example, let’s say we have a dynamic search where we search for different fields depending on the users input, for example, I came up with a query that searches for name and first name:

User.where("name ILIKE '%#{text_value}%' or first_name ILIKE '%#{text_value}%'")

But in case that the users input is something like this:

text_value = "' OR 1=1--"

We will get an error exception because the malformed SQL query and if you are using SQLite you can get into terrible issues, for example, if they enter in the search input something like this:

text_value = "'; drop table your_table_name;--"

Boom bye bye table.

So how do you protect against that with the help of ActiveRecord?

As simple as:

User.where('name ilike ? or first_name ilike ?', "%#{text_value}%", "%#{text_value}%")
#or if you are building your sql manually for example:
User.where('name ilike :query or first_name ilike :query', query: "%#{text_value}%")

The second way is pretty helpful when you have a long sql query such as:

  def search_by_full_name_sql_string
    %{
      CONCAT(users.first_name, ' ', users.last_name) ILIKE :query_string
      OR
      CONCAT(users.last_name, ' ', users.first_name) ILIKE :query_string
      OR
      CONCAT(users.first_name, ' ', users.middle_name, ' ', users.last_name) ILIKE :query_string
      OR
      CONCAT(users.last_name, ' ', users.middle_name, ' ', users.first_name) ILIKE :query_string
      OR
      users.name ILIKE :query_string
    }.gsub(/\s+/, " ").strip
  end

Then you can execute that query with:

User.where(search_by_full_name_sql_string, query_string: "%#{text_value}%")

There you go now you will be fine – keep reading, coding and relax

H.