techie_28 techie_28 - 5 months ago 10
SQL Question

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

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

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 |
+---------------------------+-------------------+