If MYSQL is not case sensitive, how does it work for the Password field in Login?

Question

I was wondering, in the case of a normal select operation, the search is an insenesitive register. So SELECT * FROM tbl WHERE name = aBc also consider the name with the values ​​ABC, abc, abC, etc.

but in the case of the login function, we just do SELECT * FROM tbl WHERE password = aBc will be case sensitive and takes into account only a password with aBc value.

How does this happen? I did not find anything about this in my quest.

Please help explain.

Thanx All.

Answer 1

I think it depends on the sorting of the columns, the sorting of the default database in MySQL utf8_general_ci , where ci at the end means case insensitive.

case sensitive passwords will only work if you store passwords in an encrypted format using the MD5 or PASSWORD function.

 show variables like '%collation%'; +---------------------------+-------------------+ | Variable_name | Value | +---------------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | utf8_general_ci | | collation_server | latin1_swedish_ci | +---------------------------+-------------------+ 

Answer 2

$ sql = "SELECT * FROM of the user, where username = '$ username' AND BINARY password = '$ password'";

Answer 3

I'm not sure if the answer to your exact question is, however, if you store passwords in the database as text, then this is a VERY bad idea. Instead, you should use the hash password during registration and save it in your database in this form. Then, each time a user tries to log in, you redirect the password provided and compare it with the hash stored in the string with the corresponding username. Since the hash code is case sensitive, this solves your problem by adding the required level of security.

Answer 4

In many implementations, passwords or their hashes are compared on the application server, so the problem does not arise.

Answer 5

case sensitive use (BINARY)

SELECT * FROM tbl WHERE BINARY password = aBc