Implementing Safe ActiveRecord Like Queries for Rails
source link: https://fuzzyblog.io/blog/rails/2019/11/11/implementing-safe-activerecord-like-queries.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Implementing Safe ActiveRecord Like Queries for Rails
Nov 11, 2019
In any SQL based database, a like query is generally an SQL injection attack waiting to happen because the underlying sql statement looks like this:
SELECT id FROM posts WHERE name LIKE '%foo%'
Note: A 30 year old thank you goes out to InfoWorld and Joe Celko who beat into his reader's brains the concept of capitalizing SQL statements for better legibility. Thank you Joe.
A seemingly solid StackOverflow post gives this recommendation:
title = Model.arel_table[:title]
Model.where(title.matches("%#{query}%"))
Please note that Model needs to be replaced with the name of your table. Let's say that our table was named Metric and we have a normal simple_form object for Metric coming into our Rails app with a parameter named q and we have a real world Rails app with a limit clause and pagination.
Here's how this would look:
@q = params[:metric][:q]
note = Metric.arel_table[:note]
@metrics = current_user.metrics.where(note.matches("%#{@q}%")).order("date_created_at desc").limit(@limit).page(params[:page])
- @q represents the incoming query
- note represents the field in our Metrics table that we want to search agains
- @metrics is the collection of data returned by the search and the where clause is "note.matches("%#{@q}%")" to find any instances of the term @q within the note field
Posted In: #rails #activerecord
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK