在连接中使用占位符 [英] using a placeholder with joins

问题描述

我正在尝试通过在连接上替换我的参数来避免任何SQL注入漏洞.

I'm attempting to avoid any SQL injection vulnerabilities by substituting with my params on a join.

Category.joins("LEFT OUTER JOIN incomes ON incomes.category_id = categories.id AND incomes.dept_id = ?", params[:Dept])

这将尝试执行带有问号的查询，而不是将其替换为参数.正确的方法是什么?

This attempts to execute the query with a question mark in it, instead of substituting it for the param. What is the proper way to do this?

查询需要返回以下内容:

Query needs to return this:

SELECT categories.* 
FROM "categories" 
LEFT OUTER JOIN incomes 
ON incomes.category_id = categories.id AND incomes.dept_id = 86

不是

SELECT categories.* 
FROM "categories" 
LEFT OUTER JOIN incomes 
ON incomes.category_id = categories.id 
WHERE incomes.dept_id = 86

结果截然不同！

推荐答案

一种选择是使用

One option is to use the sanitize_sql_array method. It is, however, a protected method so on your Category model you could do:

class Category < ActiveRecord::Base
  def self.income_for_dept(dept)
    Category.joins(sanitize_sql_array(["LEFT OUTER JOIN incomes ON incomes.category_id = categories.id AND incomes.dept_id = ?", dept]))
  end
end

然后您将其命名为:

Category.income_for_dept(params[:Dept])

如果需要，Ruby提供了一些其他方法，而无需在Category中创建类方法.

Ruby provides some other methods, if need be, to get at that method without making a class method in Category.

这篇关于在连接中使用占位符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！