Case Sensitivity Issue Between MySQL / Postgres

Question

I am doing ajax validation when a user signs up for my application to see if the username already exists. The request is made whenever you unfocus/blur the username field. Locally I am using MySQL and on Heroku it's Postgres, so I'm assuming the issue lies there somehow because this functions perfectly locally on my dev machine.

The username registered is Matt for example...

Development:

Matt = taken is true
matt = taken is true
MATT = taken is true
MaTt = taken is true, etc...

Production:

Matt = taken is true
matt = taken is false
MATT = taken is false
MaTt = taken is false

And here is the method (you can even see I went a step further to try and force it to downcase, again works locally how it should, but not on production)...

def checkname

  scrubb = ActionController::Base.helpers.sanitize(params[:username], :tags => '')
  user = User.find_by_username(scrubb, :conditions => [ "lower(username) = ?", scrubb.downcase ])

  if user.blank?
    render :json => { :isAvailable => true }
  else
    render :json => { :isAvailable => false }
  end

  return

end

**EDIT**

This is the output of the generated MySQL query:

SELECT `users`.* FROM `users` WHERE `users`.`username` = 'MATT' AND (lower(username) = 'matt') LIMIT 1

So, it looks like the real issue is that AND statement Rails is generating. How would I remove that?

Not sure why forcing to lowercase isn't working, but I would recommend against using a different DBMS on dev and production - however much the framework claims to make everything work the same, there will always be differences, and you don't want to run into them for the first time on a live environment. — IMSoP
– IMSoP, Commented Aug 24, 2013 at 23:13

mu is too short · Accepted Answer · 2013-08-24 23:46:29Z

You're using the wrong method to check the username. This:

User.find_by_username(scrubb)

Is just a way to produce this SQL:

select * from users where username = ...

Then you add some :conditions:

:conditions => [ "lower(username) = ?", scrubb.downcase ]

and find_by_username just tacks those onto the WHERE clause that it would normally use. The result is that:

User.find_by_username(scrubb, :conditions => ...)

means:

find the User whose username is scrubb and where :conditions are true.

In MySQL, the string comparison:

`users`.`username` = 'MATT'

is apparently case insensitive in your configuration (and I believe the default configuration) but it will be case sensitive in PostgreSQL; if the username is 'Matt', then that condition will fail in PostgreSQL and you won't find anything other than a case sensitive match.

Don't use find_by_username for this (or anything else if you want username to be case insensitive), use where and count:

n = User.where('lower(username) = ?', scrubb.downcase).count

or better, where and exists?:

taken = User.where('lower(username) = ?', scrubb.downcase).exists?

Adding a scope to User would also seem like a good idea:

class User < ActiveRecord::Base
  def self.with_username(username)
    where('lower(username) = ?', username)
  end    
end

and then you can say:

how_many = User.with_username(scrubb).count
taken    = User.with_username(scrubb).exists?

and you won't have to worry about this case insensitive problem anywhere else.

And please, use the same stack in development and production, there are all sorts of other little differences that will cause you grief.

Community · Accepted Answer · 2017-05-23 11:49:42Z

I actually got around to solving this through some more research on my own that made more sense for me. Figured it might help somebody else.

username = params[:username]
username.downcase
@user = User.where(User.arel_table[:username].matches("%#{username}%")).first

Using Arel and matches will use the ILIKE operator for Postgres, and LIKE for everything else (source). Then, call first so you receive a single object of a user instead of a collection (source).

Works exactly how it should in both MySQL and Postgres. I understand that this is bad practice to have a different production and development environment...moral of the story. I'll get around to this soon.

Note: While on the topic of case insensitivity I also came across a very helpful gem called route_downcaser that you might be interested in.

Collectives™ on Stack Overflow

Case Sensitivity Issue Between MySQL / Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related