Rails 3. Validating email uniqueness and case sensitive fails

Question

I am developing an app in Rails 3 and upon signup I need the user to enter their email address and I need it to be unique and case sensitive. I.e. no one should be able to sign up with myEmail@yahoo.com when MyEmail@yahoo.com already exists in the database.

This is my code and it crashes the app:

validates :email, :presence => true, :uniqueness => true, :case_sensitive => true,
                      :format => {:with => /^([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})$/i}

What is wrong with it?

score 44 · Accepted Answer · edited May 15 '16 at 16:16

44

Please dont use case sensitive there!!!. It will fetch all the users! So if you have 100.000 users. first it will fetch them all with LOWER(email). This can be VERY slow and it wont use your index on email.

Here an article that i found just now about this topic: http://techblog.floorplanner.com/post/20528527222/case-insensitive-validates-uniqueness-of-slowness

My suggesting is: Run a query to make all the emails downcased and make a before validation filter to downcase the email attribute so you dont have any uppercased characters in that column.

User.update_all('email = LOWER(email)')

before filter:

before_validation :downcase_email

private

def downcase_email
  self.email = email.downcase if email.present?
end

edited May 15 '16 at 16:16

Darme

6,984
5
37
52

answered Jun 21 '11 at 09:00

Michael Koper

9,586
7
45
59

Great idea! Thank you so much, it did not know this and I can see the problem it would cause. – Jonathan Clark Jun 21 '11 at 20:09
2

This is an old approach. Rails 3 allows to use hash validates :email, :uniqueness => { :case_sensitive => false }, as Geoff Evason wrote. – AntonAL Mar 20 '13 at 09:11
1

the link of the post listed in this answer seem to be broken, this is a worked one : http://techblog.floorplanner.com/post/20528527222/case-insensitive-validates-uniqueness-of-slowness – medBouzid Apr 11 '14 at 08:31
not even sure if this issue still true with Rails 4, i'm interested to know if someone have the answer :) – medBouzid Apr 11 '14 at 08:34
What if the table has fewer entries? – Anwar Oct 17 '15 at 12:34
You should also strip emails, because a lot of users somehow add white spaces at the end or beginning... – BvuRVKyUVlViVIc7 May 20 '16 at 10:30
Good answer! I like this one! – kdweber89 Jul 28 '16 at 15:35

score 33 · Answer 2 · answered Aug 08 '11 at 19:28

33

For the Rails 3 type of validation you need to nest the casse insensitive block like so

validates :email, :uniqueness => { :case_sensitive => false }

answered Aug 08 '11 at 19:28

Geoff Evason

461
3
5

Won't this have the same effect as not using the email index, as noted by @Michael Koper? – Christian Fazzini Feb 11 '13 at 17:41
A postgres database allows to build indices on functions, so not in general. – radiospiel Jul 30 '13 at 09:36

score 3 · Answer 3 · edited Mar 16 '21 at 10:33

I don't have the reputation to comment on the accepted answer, but @medBo asked about how this behaves in Rails 4. For reference, when using Rails 4.2 + MySQL, if I specify

validates :username, uniqueness: { case_sensitive: true }

ActiveRecord performs this query:

SELECT  1 AS one FROM `users` WHERE `users`.`username` = 'TEST_USER' LIMIT 1

In this case the search is not case sensitive. But when I set:

validates :username, uniqueness: { case_sensitive: false }

it performs:

SELECT  1 AS one FROM `users` WHERE `users`.`username` = BINARY 'TEST_USER'

The BINARY operator ensures the search is case sensitive without fetching all users, meaning for my setup at least, the case_sensitive flag doesn't suffer from the performance issue that @Michael Koper notes for earlier versions of Rails. I can't comment on how ActiveRecord performs for other database setups.

If I'm not mistaken, both of the cases in your example are the same. `validates :username, uniqueness: { case_sensitive: false }` — TCannadySF, Jul 12 '17 at 18:24

score 1 · Answer 4 · answered Jun 21 '11 at 08:50

I'm not sure if it is possible to do case insensitive validations using that syntax (at least, I haven't found any documentation for it).

You should be able to validate case insensitive uniqueness like this though:

validates_uniqueness_of :email, :case_sensitive => false

Without more detail on the crash you get, I can't help more than that.

score 0 · Answer 5 · answered May 15 '21 at 23:11

You can use a callback in your model like "before_validation" on email attribute to make it lowercased like this:

  before_validation { self.email = email.downcase }

This will make the email input lowercased, after that try uniqueness validation without case sensitive:

validates :email, uniqueness: true

For more info about callbacks: here is ruby guide https://guides.rubyonrails.org/active_record_callbacks.html

Rails 3. Validating email uniqueness and case sensitive fails

5 Answers5

Linked