Is this prone to SQL injection?

问题:

In my controller action, I have the following:

def index
    @articles = (params[:mine] == "true") ? current_user.articles : Article.search(params[:search])
    @articles = @articles.sort! { |a,b| b.created_at <=> a.created_at }
    @articles = Kaminari.paginate_array(@articles).page(params[:page]).per(25)

    respond_to do |format|
      format.html
      format.json { render json: @articles }
    end
end

And in the model:

  def self.search(search)
    if search.present?
      where("category LIKE ? OR article_type LIKE ?", "%#{search}%","%#{search}%")
    else
      find(:all)
    end
  end

I understand that SQL injection would be possible if you use params directly in a query. Here, I'm passing params directly to the where query through Article.search(params[:search]). Is this prone to SQL injection? If it is, how can I make it more secure? I also have my doubts if I've written the controller code properly. If you have suggestions in refactoring the controller code, please do let me know and they'll be very much appreciated. Thanks a lot!

回答1:

For your query, you should try to use the methods provided by ActiveRecord or go through Arel itself (your current method is fine though). This will ensure your SQL is properly escaped. If you don't want to go into details of Arel right now, you can use gems like squeel or meta_where (for older rails).

I highly recommend these gems for most of your query building needs. Anything more advanced you can write out directly using Arel.

I can't recall, if you can do matches (LIKE) directly out in basic ActiveRecord.where syntax, without the help of gem, yet. But you can definitely do this directly in Arel.

articles = Article.arel_table
articles = articles[:category].matches("%#{search}%").
  or(articles[:article_type].matches("%#{search}%"))

At this point you can do a to_a on articles or use to_sql and pass this to your Article model using find_by_sql.

Article.find_by_sql articles.to_sql

will_paginate has a paginate_by_sql method, and I would assume kaminari would have one as well (or at least I would think it would).

As for your controller code, I would pass any type of sorting option off to the database (this goes for your pagination as well), if you possibly can.

articles.sort('`articles`.created_at DESC')

The method your using now will grab "ALL" the [allowed] records then sort, then give back your per_page limit. Which kind of defeats the purpose of paginating at all, in this case.

At a minimum, try to refactor your current setup as:

@articles = (params[:mine] == "true") ? current_user.articles : Article.search(params[:search])
@articles = @articles.sort('`articles`.created_at DESC').page(params[:page]).per(25)

As long as your passing around an ActiveRelation you can bind additional stuff to this due to the way Rails lazyloads it's queries to the database.