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?
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')
Rails will do it automatically if you format your queries properly
From the guides - don't do things like:
Project.where("name = '#{params[:name]}'")
Instead do
Project.where("name = ?", params[:name])
