sql - 进行安全的 sql 查询

Question

下面的代码来自 Sinatra 应用程序(使用 DataMappe)，我正在尝试将其转换为 Rails 3 应用程序。它是 Visit 类中的一个类方法。

def self.count_by_date_with(identifier,num_of_days)
    visits = repository(:default).adapter.query("SELECT date(created_at) as date, count(*) as count FROM visits where link_identifier = '#{identifier}' and created_at between CURRENT_DATE-#{num_of_days} and CURRENT_DATE+1 group by date(created_at)")
    dates = (Date.today-num_of_days..Date.today)
    results = {}
    dates.each { |date|
      visits.each { |visit| results[date] = visit.count if visit.date == date }
      results[date] = 0 unless results[date]
    }
    results.sort.reverse    
  end

我的问题是这部分

 visits = repository(:default).adapter.query("SELECT date(created_at) as date, count(*) as count FROM visits where link_identifier = '#{identifier}' and created_at between CURRENT_DATE-#{num_of_days} and CURRENT_DATE+1 group by date(created_at)")

Rails(据我所知)没有这个存储库方法，我希望对某种对象调用查询，例如 Visit.find
谁能给我一个提示，如何最好地为 Rails 应用程序编写它？

我应该做

Visit.find_by_sql("SELECT date(created_at) as date, count(*) as count FROM visits where link_identifier = '#{identifier}' and created_at between CURRENT_DATE-#{num_of_days} and CURRENT_DATE+1 group by date(created_at)")

Answer 1

我知道您已经接受了一个答案，但是您要求最好的方法来完成您在 Rails 中提出的要求。我提供这个答案是因为 Rails 不推荐 building conditions作为纯查询字符串。

Building your own conditions as pure strings can leave you vulnerable to SQL injection exploits. For example, Client.where("first_name LIKE '%#{params[:first_name]}%'") is not safe.

幸运的是，Active Record 非常强大，可以构建非常复杂的查询。例如，您的查询可以通过四个方法调用重新创建，同时仍然易于阅读和安全。

# will return a hash with the structure
# {"__DATE__" => __COUNT__, ...}
def self.count_by_date_with(identifier, num_of_days)
  where("link_identifier = ?", identifier)
  .where(:created_at => (num_of_days.to_i.days.ago)..(1.day.from_now))
  .group('date(created_at)')
  .count
end

Active Record 旨在将 Ruby 对象转换为有效的 SQL 选择器和运算符。让它如此酷的是 Rails 可以变成 Ruby Range成 BETWEEN operator或 Array成 IN expression .

有关 Active Record 的更多信息，请查看 the guide .它解释了 Active Record 的功能以及如何使用它。