If MYSQL is case insensitive,How does it works for the Password field in Login?

Question

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

but in case of login function we just do SELECT * FROM tbl WHERE password = aBc will be case sensitive and only consider password with aBc value.

How does this happens?I didnt found anything about this in my searches.

Please care to explain.

Thanx All.

Answer 1

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

case sensitive passwords will work only if you are storing passwords in encrypted format using 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 user where username='$username' AND BINARY password='$password'";