ruby-on-rails - Ruby on Rails SQL 注入(inject) - 构建查询

Question

我正在解决系统中的所有 SQL 注入(inject)，但我发现了一些我不知道如何处理的内容。

有人可以帮助我吗？

这是我的方法

def get_structure()
   #build query
   sql = %(
           SELECT pc.id AS "product_id", pc.code AS "code", pc.description AS "description", pc.family AS "family", 
                  p.code AS "father_code", p.description AS "father_description", 
                  p.family AS "father_family"
           FROM products pc
           LEFT JOIN imported_structures imp ON pc.id = imp.product_id
           LEFT JOIN products p ON imp.product_father_id = p.id
           WHERE pc.enable = true AND p.enable = true
   )
   #verify if there is any filter
   if !params[:code].blank?
     sql = sql + " AND UPPER(pc.code) LIKE '%#{params[:code].upcase}%'"
   end
   #many other parameters like the one above
   #execute query
   str = ProductStructure.find_by_sql(sql)
end

谢谢!

Answer 1

您可以使用Arel它将为您转义，并且是 ActiveRecord/Rails 的底层查询构建器。例如。

products = Arel::Table.new("products")
products2 = Arel::Table.new("products", as: 'p')
imported_structs = Arel::Table.new("imported_structures")
query = products.project(
  products[:id].as('product_id'),
  products[:code],
  products[:description], 
  products[:family], 
  products2[:code].as('father_code'),
  products2[:description].as('father_description'),
  products2[:family].as('father_family')).
  join(imported_structs,Arel::Nodes::OuterJoin).
    on(imported_structs[:product_id].eq(products[:id])).
  join(products2,Arel::Nodes::OuterJoin).
    on(products2[:id].eq(imported_structs[:product_father_id])).
  where(products[:enable].eq(true).and(products2[:enable].eq(true)))
if !params[:code].blank?
  query.where(
     Arel::Nodes::NamedFunction.new('UPPER',[products[:code]])
       .matches("%#{params[:code].to_s.upcase}%")
  )
end

SQL 结果:(使用 params[:code] = "' OR 1=1 --test")

SELECT 
  [products].[id] AS product_id, 
  [products].[code], 
  [products].[description], 
  [products].[family], 
  [p].[code] AS father_code, 
  [p].[description] AS father_description, 
  [p].[family] AS father_family 
FROM 
  [products] 
  LEFT OUTER JOIN [imported_structures] ON [imported_structures].[product_id] = [products].[id] 
  LEFT OUTER JOIN [products] [p] ON [p].[id] = [imported_structures].[product_father_id] 
WHERE 
  [products].[enable] = true AND 
  [p].[enable] = true  AND 
  UPPER([products].[code]) LIKE N'%'' OR 1=1 --test%'

使用

ProductStructure.find_by_sql(query.to_sql)

与 String 查询相比，我更喜欢 Arel(如果可用)，因为:

• 支持转义
• 它利用您现有的 sytnax 连接适配器(因此，如果您更改数据库，它是可移植的)
• 它是内置于代码中的，因此语句顺序并不重要
• 它更具动态性和可维护性
• ActiveRecord 本身支持它
• 您可以构建任何您能想象到的复杂查询(包括复杂联接、CTE 等)
• 它仍然非常可读