这是容易SQL注入吗？ [英] Is this prone to SQL injection?

问题描述

在我的控制器动作中，我有以下：

  def index 
 @articles = 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]）每（25）
 
 respond_to do | 
 format.html 
 format.json {render json：@articles} 
 end 
 end 
  

在模型中：

  def self.search如果search.present？ 
 where（category LIKE？OR article_type LIKE？，％＃{search}％，％＃{search}％）
 else 
 find（：all）
 end 
 end 
  

我理解如果直接使用params，SQL注入是可能的在查询中。这里，我通过 Article.search（params [：search]）将params直接传递给where查询。这是容易SQL注入吗？如果是，我如何使它更安全？我也有我的怀疑，如果我已经编写控制器代码正确。如果你有重构控制器代码的建议，请让我知道，他们将非常感谢。非常感谢！

解决方案

对于你的查询，你应该尝试使用 ActiveRecord 或者通过 Arel 本身（你当前的方法是好的）。这将确保您的SQL正确转义。如果你现在不想详细了解 Arel ，可以使用 squeel 或 meta_where （适用于旧版路轨）。

我强烈推荐这些gems大部分的查询建筑需求。任何更高级的，你可以直接使用 Arel 写出来。

我不记得，如果你能做直接在 ActiveRecord.where 语法中匹配（ LIKE ，没有宝石的帮助，但。但你可以直接在 Arel 中直接执行此操作。

  .arel_table 
 articles = articles [：category] ​​.matches（％＃{search}％）。 
或（articles [：article_type] .matches（％＃{search}％））
  

此时，您可以在文章上使用 to_a 或使用 to_sql 并使用 find_by_sql 将此传递到您的文章模型。

  Article.find_by_sql articles.to_sql 
  

will_paginate 有一个 paginate_by_sql 方法，我会假设 kaminari 也会有一个（或至少我会认为它会）。

你的控制器代码，我会传递任何类型的排序选项到数据库（这也是为了你的分页），如果你可能的话。

  articles.sort（'`articles`Created_at DESC'）
  pre> 
 
 
您现在使用的方法将抓取ALL的[允许的]记录然后排序，然后回馈您的 per_page  limit。 
 
 
 
至少应尝试将当前设置重构为：
  @articles =（params [：mine] ==true）？ current_user.articles：Article.search（params [：search]）
 @articles = @ articles.sort（'`articles`.created_at DESC'）。page（params [：page]）。 b $ b  
只要传递 ActiveRelation 可以绑定额外的东西，因为Rails lazyloads它的查询到数据库的方式。
 
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!
解决方案
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.

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