为什么Rails使用带范围的where子句哈希语法向查询添加"OR 1 = 0"? [英] Why is Rails is adding `OR 1=0` to queries using the where clause hash syntax with a range?
问题描述
我正在研究的项目是在RDS上使用MySQL(特别是mysql2 gem).
The project that I'm working on is using MySQL on RDS (mysql2 gem specifically).
当我在where语句中使用条件散列(包括范围)时，查询中出现了一些奇怪的变化.
When I use a hash of conditions including a range in a where statement I'm getting a bit of an odd addition to my query.
User.where(id: [1..5])
和
User.where(id: [1...5])
分别导致以下查询:
SELECT `users`.* FROM `users` WHERE ((`users`.`id` BETWEEN 1 AND 5 OR 1=0))
SELECT `users`.* FROM `users` WHERE ((`users`.`id` >= 1 AND `users`.`id` < 5 OR 1=0))
由于OR FALSE实际上是无操作的，因此查询工作正常.我只是想知道为什么Rails或ARel将此片段添加到查询中.
The queries work perfectly fine since OR FALSE is effectively a no-op. I'm just wondering why Rails or ARel is adding this snippet into the query.
It looks like the line that could explain this is line 26 in ActiveRecord::PredicateBuilder. Still no idea how the hash could be empty? at that point but maybe someone else does.
这很有趣.我一直在调查Filip的评论，以了解他为什么这样做，因为这似乎像是在澄清，但他对1..5 != [1..5]的说法是正确的.前者的取值范围是1到5，其中后者是一个数组，其第一个元素是.我尝试将它们放入ARel where调用中以查看生成的SQL，而OR 1=0不存在！
This is intersting. I was looking into Filip's comment to see why he made it since it seems just like a clarification but he is correct that 1..5 != [1..5]. The former is an inclusive range from 1 to 5 where as the latter is an array whose first element is the former. I tried putting these into an ARel where call to see the SQL produced and the OR 1=0 is not there!
User.where(id: 1..5) #=> SELECT "users".* FROM "users" WHERE ("users"."id" BETWEEN 1 AND 5)
User.where(id: 1...5) #=> SELECT "users".* FROM "users" WHERE ("users"."id" >= 1 AND "users"."id" < 5)
尽管我仍然不知道为什么为什么 ARel添加了OR 1=0，但这将始终是错误的，而且似乎是不必要的.可能是由于Array和Range的处理方式不同.
While I still do not know why ARel is adding the OR 1=0 which will always be false and seemingly unnecessary. It may be due to how Arrays and Ranges are handled differently.
推荐答案
基于您发现的事实，[1..5]不是指定范围的正确方法...我发现了为什么1=0 SQL条件:
Building on the fact, which you've discovered, that [1..5] is not the correct way to specify the range... I have discovered why [1..5] behaves as it does. To get there, I first found that an empty array in a hash condition produces the 1=0 SQL condition:
User.where(id: []).to_sql
# => "SELECT \"users\".* FROM \"users\" WHERE 1=0"
And, if you check the ActiveRecord::PredicateBuilder::ArrayHandler code, you'll see that array values are always partitioned into ranges and other values.
ranges, values = values.partition { |v| v.is_a?(Range) }
这说明了为什么在使用非范围值时看不到1=0的原因.也就是说，从数组中获取1=0而不包含范围的唯一方法是提供一个空数组，这会产生1=0条件，如上所述.当所有数组都在一个范围内时，您将获得范围条件(ranges)，并分别执行一个空数组条件(values).我的猜测是没有充分的理由……让它成为可能比避免它更容易(因为结果集在任一方向上都是等效的).如果分区代码更聪明，则不必添加其他空的values数组，并且可以跳过1=0条件.
This explains why you don't see the 1=0 when using non-range values. That is, the only way to get 1=0 from an array without including a range is to supply an empty array, which yields the 1=0 condition, as shown above. And when all the array has in it is a range you're going to get the range conditions (ranges) and, separately, an empty array condition (values) executed. My guess is that there isn't a good reason for this... it just simply is easier to let this be than to avoid it (since the result set is equivalent either way). If the partition code was a bit smarter then it wouldn't have to tack on the additional, empty values array and could skip the 1=0 condition.
首先，关于1=0的来源...我认为这是来自数据库适配器的，但是我找不到确切的位置.但是，我将其称为无法找到记录的尝试.换句话说，WHERE 1=0永远不会返回任何用户，这对替代SQL来说是有意义的，例如WHERE id=null，它将找到id为null的所有用户(意识到这并不是真正的SQL语法).这就是我试图查找其ID在空集中的所有用户时的期望值(即，我们不要求提供nil id或null id或其他任何值).因此，在我看来，将1=0的确切位置留给黑匣子是可以的.至少我们现在可以推断出为什么数组内部的范围导致其显示出来了！
As for where the 1=0 comes from in the first place... I think that comes from the database adapter, but I couldn't find exactly where. However, I would call it an attempt to fail to find a record. In other words, WHERE 1=0 isn't ever going to return any users, which makes sense over alternative SQL like WHERE id=null which will find any users whose id is null (realizing that this isn't really correct SQL syntax). And this is what I'd expect when attempting to find all Users whose id is in the empty set (i.e. we're not asking for nil ids or null ids or whatever). So, in my mind, leaving the bit about exactly where 1=0 comes from as a black box is OK. At least we now can reason about why the range inside of the array is causing it to show up!
我还发现，即使直接使用ARel，您仍然可以获得1=0:
I've also found that, even when using ARel directly, you can still get 1=0:
User.arel_table[:id].in([]).to_sql
# => "1=0"
这篇关于为什么Rails使用带范围的where子句哈希语法向查询添加"OR 1 = 0"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
