Rails 4 Sanitizing User Input

Question

I am currently making an API using Ruby on Rails. I was just wondering in general if there are built in Rails methods or libraries/gems to sanitize Json and SQL or if Rails 4 does this by default? I am most worried about such cases where I have an SQL statement such as

User.where("users.first_name IS NOT NULL") 

or something like

Event.where(:location => params[:location]). 

Essentially, what should I watch out for in my SQL syntax and in incoming JSON requests?

Answer 1

By default, using the following will sanitize the str and make it safe from SQL injections:

User.where(name: str)
User.where('name ILIKE ?', str)

However, the following code (direct string interpolation then given to the where method) make it unsafe from SQL injections:

User.where("name = '#{str}'")

---

In your case, you can use ActiveRecord::Base.sanitize(your_string_from_user_input). It will use your DB adapter to escape/quote the relevant parts, preventing from SQL injections.

In a Model, you can directly access to the sanitize method (since you are in a context already inheriting from ActiveRecord::Base):

class User < ActiveRecord::Base

  def self.search(string)
    terms = string.split
    searchable_columns = [:name, :username, :whatever]
    query = terms.map do |term|
      fields = searchable_columns.map |column|
        " #{self.table_name}.#{column} LIKE '#{sanitize("%#{term}%")}'"
      end
      "(#{fields.join(' OR ')})"
    end.join(' AND ')

    where(query)
  end
end

The above code will produce a SQL WHERE clause like the following:

# str is 'bob doe'
WHERE 
  (users.name LIKE 'bob' OR users.username LIKE 'bob' OR users.whatever LIKE 'bob')
AND
  (users.name LIKE 'doe' OR users.username LIKE 'doe' OR users.whatever LIKE 'doe')