在条件可能为NULL的Rails中清理SQL [英] Sanitizing SQL in Rails where conditions may be NULL

问题描述

我正在努力清理其中WHERE条件可能具有值或为NULL的原始SQL查询。我希望使用Active Record的内置消毒剂...

I'm struggling to sanitize a raw SQL query in which the WHERE conditions may either have a value or be NULL. I was hoping to use Active Record's built-in sanitizers...

（注意：，我将使用 simplified 用于演示目的的查询-我们真正​​的目标是跨不同模型类型的复杂UNION，使用AR查询界面很难做到）

(NOTE: I'll be using a simplified query for demo purposes- our real one is a complex UNION across different model types that would be hard to do with the AR query interface)

尝试一下1：

raw_query = "SELECT * FROM folders WHERE user_id = ? AND parent_id = ?"
sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, [raw_query, current_user.id, params[:parent_id]])
results = ActiveRecord::Base.connection.execute(sanitized_query);

但是如果 params [：parent_id] 是零，然后 sanitized_query 最终为
SELECT * FROM文件夹，其中user_id = 1 AND parent_id = NULL
无效，因为 parent_id = NULL 应该为 parent_id IS NULL

But if params[:parent_id] is nil, then sanitized_query ends up as SELECT * FROM folders WHERE user_id = 1 AND parent_id = NULL Which isn't valid, as parent_id = NULL should be parent_id IS NULL

尝试2：

然后我找到了 sanitize_sql_hash 方法，该方法似乎很适合建立条件：

I then found the sanitize_sql_hash method, which seemed perfect for building the condition:

sanitized_conditions = ActiveRecord::Base.send(:sanitize_sql_hash, {user_id: current_user.id, parent_id: params[:parent_id]})
sanitized_query = "SELECT * FROM folders WHERE #{sanitized_conditions}"
results = ActiveRecord::Base.connection.execute(sanitized_query);

但第一行失败：

NoMethodError：对象：类的未定义方法'abstract_class？'

NoMethodError: undefined method `abstract_class?' for Object:Class

该方法也已被弃用并将在Rails 5中删除，但这正是我想要的。有没有另一种方法可以从值的散列中生成安全的WHERE条件？

The method is also listed as deprecated and will be removed in Rails 5, but it's exactly what I'm looking for. Is there another way to generate a safe WHERE condition from a hash of values?

推荐答案

似乎正在抛出错误reset_table_name 方法rel = nofollow> https://github.com/rails/rails/blob/7bb620869725ad6de603f6a5393ee17df13aa96c/activerecord/lib/active_record/model_schema.rb#L160 ，所以此方法可能不适用于ActiveRecord： ：基类。

It looks like the error is being thrown in the reset_table_name method at https://github.com/rails/rails/blob/7bb620869725ad6de603f6a5393ee17df13aa96c/activerecord/lib/active_record/model_schema.rb#L160 so maybe this method is not designed to work on the ActiveRecord::Base class.

假设您有一个Folder的模型类，这应该可以工作：

Assuming you have got a model class for Folder this should work:

Folder.send(:sanitize_sql_hash, {user_id: current_user.id, parent_id: params[:parent_id]})

在快速测试中，我得到了类似的东西：

In a quick test I got something like:

'"folders"."user_id" = 123 AND "folders"."parent_id" IS NULL'

这篇关于在条件可能为NULL的Rails中清理SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！