Is Activerecord's "Order" Method Vulnerable to SQL Injection

Is ActiveRecord's order method vulnerable to SQL injection?

Yes, ActiveRecord's “order” method is vulnerable to SQL injection.

No, it is not safe to use interpolated strings when calling .order.

The above answers to my question have been confirmed by Aaron Patterson, who pointed me to http://rails-sqli.org/#order . From that page:

Taking advantage of SQL injection in ORDER BY clauses is tricky, but a
CASE statement can be used to test other fields, switching the sort
column for true or false. While it can take many queries, an attacker
can determine the value of the field.

Therefore it's important to manually check anything going to order is safe; perhaps by using methods similar to @dmcnally's suggestions.

Thanks all.

Is ActiveRecord's “order” method passed with hash vulnerable to SQL injection?

I think this is at least still open for a Denail of Service attack.
http://brakemanscanner.org/docs/warning_types/denial_of_service/index.html

The reference is from a nice gem called brakeman which finds vunerable things in a rails application.

In general I would advise you to use @dmcnally's approach from the other issue you posted.

Here an example of what I did in my own projects:

SORT = {  newest: { created_at: :desc },
          cheapest: { price: :asc },
          most_expensive: { price: :desc }
}.stringify_keys

And then use SORT[param[:sort]] to get the sort order. You can also do this by using two seperate hashes for direction and column like you supposed. If you use brakeman you will be able to have a little but of safety since it finds most things like that.

Is the following code snippet vulnerable to SQL injection in Rails 5?

Found the answer to my question here https://medium.com/@mitsun.chieh/activerecord-relation-with-raw-sql-argument-returns-a-warning-exception-raising-8999f1b9898a.

Rails - SQL injection using .order to filter an index

Passing key/value pairs to order(created_at: params[:sort]) is safe. Rails validates the direction. If you give it an invalid direction it will raise ArgumentError: Direction "..." is invalid. It's been this way since the syntax was introduced in Rails 4.

Passing a string to order as in order("created_at #{params[:sort]}") could be exploited in Rails 5 and earlier. See Rails SQL Injection for details. Rails 6 now sanitizes order arguments and will raise an exception if it detects funny business.

Rails 6, in general, is more robust against SQL injection. But it's up to you to sanitize your inputs before passing them to anything which accepts raw SQL.

---

Your view is not turning the bookings into a drop down menu. Instead, it's just a bunch of text. As lurker suggested, use a function like collection_select to generate the select and option tags for you.

<%= form_for @user do |f| %>
  <%= f.collection_select :booking_id, @bookings, :id, proc { |b| "#{b.address} #{b.created_at}" , prompt: true %>
  <%= f.submit %>
<% end %>

To tidy that up a bit, you can add a method to Booking to produce the label you want and replace the proc.

class Booking
  def dropdown_value
    "#{address} #{created_at}"
  end
end

<%= form_for @user do |f| %>
  <%= f.collection_select :booking_id, @bookings, :id, :dropdown_value, prompt: true %>
  <%= f.submit %>
<% end %>

SQL Injection and ActiveRecord

All of ActiveRecord's query-building methods, like where, group, order, and so on, are safe against SQL injection AS LONG AS you do not pass them raw SQL strings. This is vulnerable to SQL injection:

 Model.where("event_id = #{params[:id]}")

When you pass a string to a query-building method like that, the string will be inserted directly into the generated SQL query. This is useful sometimes, but it does raise the danger of an injection vulnerability. On the other hand, when you pass a hash of values, like this:

 Model.where(event_id: params[:id])

...then AR automatically quotes the values for you, protecting you against SQL injection.

rails 3 activerecord order - what is the proper sql injection work around?

Ryan Bates' method:

in your controller:

def index
  @users = User.order(sort_by + " " + direction)
end

private
  def sort_by
    %w{email name}.include?(params[:sort_by]) ? params[:sort_by] : 'name'
  end

  def direction
    %w{asc desc}.include?(params[:direction]) ? params[:direction] : 'asc'
  end

Essentially you're making a whitelist, but it's easy to do and insusceptible to injection.

Rails: Still confused about SQL Injection

ActiveRecord will prevent any SQL injection attacks, AS LONG AS you are using the parameterized form. As a rule of thumb, ALL information coming from the user should be a parameter.

In your example you mention converting the user query into:

where(["name LIKE ? AND address.town NOT LIKE ?", "hello", "villa"])

In this case ActiveRecord will protect hello and villa from SQL injection, but it will NOT protect name LIKE ? AND address.town NOT LIKE ?. ActiveRecord assumes that name LIKE ? AND address.town NOT LIKE ? is being generated either by the developer (hard coded) or by the application, either way it assumes it's safe to execute.

So if any part of name LIKE ? AND address.town NOT LIKE ? is coming from the user your app could be vulnerable to SQL injection attacks.

The proper way to do it would be to use a language parser to completely decompose the user query and then re-generate it as a safe query. Using Regex to match and replace could be a naive approach unless you are a master in Regex and security.

---

Related Topics