在Rails模型范围内修复SQL注入

我一直试图重构一个模型范围，Brakeman抱怨它，所以我认为修复它是一个好主意，因为我们被寻找我们站点漏洞的机器人扫描了。

scope :cash_deal_aggregated,-> (filter = '') {
select("deals.*")
.from([Arel.sql(
"(SELECT DISTINCT ON (COALESCE(cash_deal_details.cash_deal_id,0.1*deals.id)) deals.*
FROM deals
INNER JOIN portfolios ON portfolios.id = deals.portfolio_id
LEFT JOIN cash_deal_details ON deals.cash_deal_detail_id = cash_deal_details.id
#{filter}) deals"
)]
)
}


上面的范围是这样使用的：

filter = "WHERE portfolios.client_id = #{client_id}"
deal_records = deal_records = Deal.cash_deal_aggregated(filter)


它也像这样使用：

deal_records = Deal.cash_deal_aggregated


最初，我试图通过在查询中直接添加filter来解决此问题，但随后出现多个错误。

感谢您对此重构的建议。

用ActiveRecord包装connection.quote()，用这种方法包装client_id，例如，在您的情况下，请尝试

"WHERE portfolios.client_id = #{connection.quote(client_id)}"


我也早些时候从布雷克曼那里得到了这些错误，这解决了。

这是重构。归功于Rajdeep Singh

scope :cash_deal_aggregated,-> (client_id = nil) {
filter = "WHERE portfolios.client_id = #{connection.quote(client_id)}" if client_id
select("deals.*")
.from([Arel.sql(
"(SELECT DISTINCT ON (COALESCE(cash_deal_details.cash_deal_id,0.1*deals.id)) deals.*
FROM deals
INNER JOIN portfolios ON portfolios.id = deals.portfolio_id
LEFT JOIN cash_deal_details ON deals.cash_deal_detail_id = cash_deal_details.id
#{filter}) deals"
)]
)
}