这容易发生 SQL 注入吗? [英] 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

在模型中:

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

我知道如果您在查询中直接使用参数，则可以进行 SQL 注入.在这里，我通过 Article.search(params[:search]) 将参数直接传递给 where 查询.这容易发生 SQL 注入吗?如果是，我怎样才能使它更安全?我也怀疑我是否正确编写了控制器代码.如果您对重构控制器代码有任何建议，请告诉我，我们将不胜感激.非常感谢！

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!

推荐答案

对于你的查询，你应该尝试使用 ActiveRecord 提供的方法或通过 Arel 本身(不过，您目前的方法很好).这将确保您的 SQL 正确转义.如果你现在不想深入了解 Arel 的细节，你可以使用像 这样的 gemssqueel 或 meta_where(适用于较旧的导轨).

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).

我强烈推荐这些 gem 来满足您的大多数查询构建需求.您可以使用 Arel 直接写出任何更高级的东西.

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

我不记得了，如果你能在没有帮助的情况下，直接用基本的 ActiveRecord.where 语法做 matches (LIKE)宝石，但.但是你绝对可以直接在 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}%"))

此时您可以对文章执行to_a或使用to_sql并将其传递给您的文章使用 find_by_sql 建模.

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 有一个 paginate_by_sql 方法，我假设kaminari 也会有一个(或者至少我认为会有).

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')

您现在使用的方法将抓取所有"[允许的] 记录然后排序，然后返回您的 per_page 限制.在这种情况下，哪种方式完全违背了分页的目的.

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)

只要你传递一个 ActiveRelation，你就可以绑定额外的东西，因为 Rails 延迟加载它的查询到数据库的方式.

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.

这篇关于这容易发生 SQL 注入吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！