CanCan adds " AND (1=0)" to Sql Query

[swistaczek]

Hi,
I found out that CanCan adds " AND (1=0)" to SqlQuery and break results, please look at this example:

class Partner::UsersController < Partner::BaseController
  load_and_authorize_resource class: PartnerUser
...

SELECT `users`.* FROM `users` INNER JOIN `partner_users` ON `users`.`id` = `partner_users`.`user_id` WHERE `partner_users`.`partner_id` = 1 AND (1=0)"

If you have any suggestions I would be glad,
Thanks.

[thedelchop]

Please attach your ability file and the PartnerUser model.

[zealot128]

I had the same issue with

cancan 2.0.0.alpha

downgrading to latest stable 1.6.8 fixed that issue.
I had a almost blank ability:

class Ability
  include CanCan::Ability  
  def initialize(user)
    user ||= User.new
    can :read, Empfehlungscode
  end       
end      

# 2.0.0.alpha
>> Empfehlungscode.accessible_by(Ability.new(User.first)).to_sql
=> "SELECT `empfehlungscodes`.* FROM `empfehlungscodes`  WHERE (1=0)"

# 1.6.8
=> ""SELECT `empfehlungscodes`.* FROM `empfehlungscodes` "

[vixlima]

Friends,

I have the same problem, any suggestion to resolve it?

Thanks

Andrew

[viniciusnz]

Hi guys,

I was facing the same issue and solved it.

When the user is not allowed to :index and you try something like Model.accessible_by(current_ability).all it will add the WHERE (1=0) so as not to allow the user to list any item from the model.

Solution add

can [:index], [Model]

And it will no longer add the WHERE (1=0).

I hope that helps... All the best!

[jmaniv]

I have also faced same issue.

I have installed two versions(1.6.7 and 1.6.8) and configured cancan 1.6.7 in GemFile as gem "cancan", "1.6.7"

If user is allowed only :show action and cancan will append WHERE (1=0) when we try Model.accessible_by(current_ability, :show).

There is no problem only on first request after restarting server.

[callumlocke]

I'm really confused by the behaviour of the :index ability. Where is it documented? I can't find it mentioned in the wiki.

[twessler]

@callumlocke - By default, cancan adds some functionality based on the CRUD routes. Defining the :index ability has no behavior by itself, but :read is aliased to [:index, :show].

https://github.com/ryanb/cancan/blob/master/lib/cancan/ability.rb#L303-309

Whenever you call Model#accessible_by(ability) without defining a permission explicitly, it defaults to :read. Defining :index will allow you to have more fine grained control.

For instance, you may want to allow certain users to view individual phone numbers that aren't deleted, but never be able to list (or index) all phone numbers. You could do that like this:

ability.can :show,
  PhoneNumber,
  deleted => false

ability.cannot :index,
  PhoneNumber

# Somewhere in controllers
phone = PhoneNumber.new
ability.can? :show, phone #=> true

PhoneNumber.accessible_by(ability, :index) #=> []

[the-harry]

Does anyone already solved this issue?
I'm having quite a similar bug here that I really believe that cancan is causing it. I have a few scopes to filter queries, something like:

User.scoped_doc_like(result, key).pluck(:user_doc).uniq

This query works 98% of the time, but sometimes, randomly it adds 1=0 at the end of the query.

SELECT users.* FROM users WHERE 1=0

None of the replies above solved my issue, I tried to add read and index to all users in cancan, with no success, though I'm having this problem logged as admin, and admin can :manage, :all. So I'm kinda lost about what is happening!

I also thought that this was an empty hash being passed to the lambda scope, but even with emptiness validation it fails.