How to use the case-sensitive for MySQL password field?

Question

I have user login but user can login with case-insensitive way Means if your password is 'test' then user able to login with 'TEST' password.
I want to avoid the such type authentication on my password field.

Answer 1

The easiest way is to use the binary keyword in your query use:

SELECT /*fields*/ FROM table WHERE /* where clause */ BINARY password = "userpassword"

OR

use the strcmp in your PHP code:

You can use this also if you store hashed or encrypted password which I recommend.

Answer 2

I guess you are storing passwords in clear. That's not only pretty insecure, it's also unnecessary in most situations. My advice is to store passwords in two columns, e.g.:

password_salt VARCHAR(16)
password_hash VARCHAR(40)

Before storing a new password, take the password provided by the user ($clear_password), create a random string ($salt) and use both to create a hash (sha1sum($salt . $clear_password). Store both the salt and the hash and discard the clear password.

To validate a password, retrieve the stored salt for the given user, generate the hash and see if it matches with the hash in DB.

This technique is called salted passwords.