1

I'm building a search for my website and I'm trying to use the search query by splitting it to terms and then search by subcategory or short_description:

whereQuery = ''
declared(params).search.downcase.split(' ').each_with_index do |searchTerm, index|
  if index != 0
    whereQuery += ' and ';
  end
  whereQuery += '(lower(short_description) like "%'+searchTerm+'%" or lower(subcategory) like "%'+searchTerm+'%")'
end
orders.where(whereQuery).order(number_of_purchases: :desc, rating: :desc)

Is there a better/safer way to avoid SQL INJECTION with this query?

itzikos
  • 375
  • 1
  • 5
  • 13
  • not sure about your query but 1 thing in SQL (all types) should let you do is in your where clause say where 1 = 1 (then you can avoid having to check if there is something in the where clause already because you are forcing it to have something there then every new statement just put AND criteria . Saves a lot of extra logic to see if you have something in your where clause already – Brad May 07 '18 at 18:16
  • try `LIKE ?` in the SQL, then `.where(whereQuery, *terms)` in the Ruby code, where `terms` is an array of strings with wildcards: `"%#{searchTerm}%"`. – Phlip May 07 '18 at 18:34
  • Also, it's a very good style rule to always put SQL statements in CAPITALS, such as `AND` and `LIKE`. That helps readability when you have a big mix of languages going on... – Phlip May 07 '18 at 18:35

1 Answers1

3

Use ActiveRecord chaining:

orders = Order

declared(params).search.downcase.split(' ').each do |searchTerm|
  orders = orders.where('(LOWER(short_description) LIKE ? OR LOWER(subcategory) LIKE ?', "%#{searchTerm}%", "%#{searchTerm}%")
end

orders = orders.order(number_of_purchases: :desc, rating: :desc)
NARKOZ
  • 27,203
  • 7
  • 68
  • 90