#84 ✓resolved

Request: Please add support for case sensitive login & email

Reported by Si | April 9th, 2009 @ 01:08 PM

It looks as if validates_uniqueness_of for login and email can be configured in Authlogic to support :case_sensitive => false, to force case insensitivity, but the lookups for login don't.

For background, I use PostgreSQL, but it's the same for some other databases, where text fields are case sensitive. The Rails procedure for fields like login and email, is to check lower(login) against login.downcase. Following the same convention is best, rather than using upper(login)/upcase, since users can have just the one functional index, on lower(login).

Comments and changes to this ticket

  • Si

    Si April 9th, 2009 @ 01:20 PM

    OK, I think config's find_by_login_method is good for this, at least as a workaround. Will investigate.

  • Ben Johnson

    Ben Johnson April 9th, 2009 @ 01:29 PM

    • State changed from “new” to “resolved”

    I see what you're saying. I thought, by default, the find_by_* were case insensitive. I may be wrong. Regardless, yes that configuration option will let you specify your own method and do your own thing.

    I am going to update authlogic to be case insensitive though. I'll update in a min and you should be able to just update from the repo.

  • Ben Johnson

    Ben Johnson April 9th, 2009 @ 01:31 PM

    Also, I have confirmed that the find_by_* is case insensitive. I just did a simple test that passed.

  • Si

    Si April 9th, 2009 @ 01:37 PM

    Did it produce SQL containing the lower function? It's an issue when the query hits the DB unfortunately.

  • Ben Johnson

    Ben Johnson April 9th, 2009 @ 01:43 PM

    I see what you're saying. I'll just add in my own method.

  • Ben Johnson

    Ben Johnson April 9th, 2009 @ 01:45 PM

    Actually, this is a query problem. What do you think about something like this:

    User.first(:conditions => ["login like ?", login])

    I'm pretty sure the "like" condition is case insensitive.

  • Si

    Si April 9th, 2009 @ 01:55 PM

    Even there you would have to use ilike or one of the funky operator equivalents. Bit of a pain.

    User.first(:conditions => [ "lower(login) = ?", login.downcase ])

    definitely works for me, but I suppose some people might actually like having case sensitivity and multiple similar logins / emails. Hmmm. Buffoons I say!

  • Ben Johnson

    Ben Johnson April 9th, 2009 @ 01:59 PM

    I don't think so. People don't want this behavior, not for logins. I was under the assumption rails was case insensitive. I can't think of a reason anyone would want this to happen. It would allow people to try to and pose as others. I would be pretty pissed of someone created "BinaryLogic" on github and started sending people messages as me.

    Do you know that the lower() function is a universally support DB function?

  • Ben Johnson

    Ben Johnson April 9th, 2009 @ 02:01 PM

    Also, does this utilize database indexing?

  • Si

    Si April 9th, 2009 @ 02:31 PM

    Yes, lower is a SQL standard function. You can create a functional index on the column:

    create index users_login_idx ON users(lower(login))

    This is why using lower instead of upper is good. Being consistent throughout Rails allows one functional index instead of two.

    I would only use lower() for situations that need it though, via configuration / magic, since other databases might not be able to use their own index when they get a query containing a lower function!

    Perhaps Rails has a neat way of letting you know about this kind of thing via the adapter.

  • Si

    Si April 9th, 2009 @ 02:45 PM

    I can't see anything in Rails ConnectionAdapters to help.

    It would be nice to specify case sensitivity to be false in the session config then have the default find query modified plus :case_sensitive => false pumped into the uniqueness validations by default (one config addition sets up nice defaults).

  • Ben Johnson

    Ben Johnson April 9th, 2009 @ 02:55 PM

    I agree, I'll see what I can do here.

  • Si

    Si April 9th, 2009 @ 03:13 PM

    That would be great, thanks.

    I'm sure the uniqueness validations could defer to the config for a case_sensitive value, but I think using lower functions with the case insensitive collations of something like MySQL might throw it's query optimizer, so it's best to avoid functions in the login finder unless asked for in the config.

  • Ben Johnson

    Ben Johnson April 9th, 2009 @ 03:24 PM

    I agree but I don't think its possible. By default they should be case insensitive, since this is the case so should the login lookup. ActiveRecord doesn't seem to support this. The only thing I can do is skip all of this madness for mysql, and implement the lower() function for other database types.

  • Si

    Si April 9th, 2009 @ 03:41 PM

    Currently there's this for finding the record:

    search_for_record(find_by_login_method, send(login_field))

    Could search_for_record be sent regular find + arguments instead for this lower(login) find? It just appears to do this at the core of it:


    So :find could be the method (instead of find_by#{login_field}) unless otherwise specified, with conditions to match lower() use or otherwise. Specifying a method to use would follow the existing way of invoking the find method.

  • Si

    Si April 9th, 2009 @ 03:53 PM

    self.attempted_record = search_for_record(:find, :first, :conditions => [ "lower(login) = ?", send(login_field).downcase ])

    Appears acceptable to the underlying code in the scope object.

      User Load (2.2ms)   SELECT * FROM "users" WHERE (lower(login) = E'simon') LIMIT 1
      SQL (0.3ms)   BEGIN
      User Update (0.9ms)   UPDATE "users" SET "login_count" = 4, "updated_at" = '2009-04-09 19:52:41.291093', "perishable_token" = E'DYDSL7Gg4wSLtgVKDu6S', "last_login_at" = '2009-04-09 19:50:47.419921', "current_login_at" = '2009-04-09 19:52:41.286792' WHERE "id" = 1
  • Ben Johnson

    Ben Johnson April 9th, 2009 @ 03:54 PM

    Yeah, you could, but I feel specifying a method is a cleaner approach, it also allows people to use the method their self if needed. There really is no difference.

  • Si

    Si April 9th, 2009 @ 04:06 PM

    For people supplying their own method, they can handle case themselves, so it could be:

    config.case_insensitivity = true
    if find_by_login_method supplied
      use it
      case_cond = case_insensitivity ? "lower#{login_field} = ?" : "#{login_field} = ?"
      self.attempted_record = search_for_record(:find, :first, :conditions => [ case_cond, send(login_field).downcase ])

    The case_insensitivity would affect validations regardless of supplying a method.

  • Ben Johnson

    Ben Johnson April 9th, 2009 @ 04:06 PM

    This is done, pull from the repo and let me know if you have any probs.

  • Si

    Si April 9th, 2009 @ 04:18 PM

    Gotcha, I see what you were getting at.

    I'll check it out. It's getting late for me here (Italy). Will let you know tomorrow, and thanks again.

  • Si
  • jeni

    jeni July 1st, 2020 @ 03:07 AM

    This post is really useful and helpful to know more about the things which you have shared. PSE-Cortex

  • ElizabethLKelly

    ElizabethLKelly September 22nd, 2020 @ 09:09 AM

    Thanks for sharing the detail of the Request: Please add support for case sensitive login & email. I like to learn more about it. The advanced writers can tell us what we can do to find the right answer.

  • jeni

    jeni September 30th, 2020 @ 05:51 AM

    Your post is too amazing. I have found with ease what I was looking for. Moreover, the content quality is awesome. Thanks for the nudge!

  • Allenwood

    Allenwood March 4th, 2021 @ 06:06 AM

    The Rails procedure for fields like login and email, is to check lower(login) against login. https://www.solution2pass.com/ADM-201-questions.html

  • 123VEGA

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Object based authentication solution that handles all of the non sense for you. It's as easy as ActiveRecord is with a database.

People watching this ticket