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.

If you save passwords as hashes it won't matter. And that is how you should do it. — juergen d, Jul 30 '12 at 06:38
No @quarry not that I was just wondering how does that works? — techie_28, Jul 30 '12 at 06:40
`password` column probably has a different `COLLATE` sequence/setting. See the related question http://stackoverflow.com/questions/5629111/mysql-case-sensitive-string-comparison. But you shouldn't save passwords as plain text. — Andrew Leach, Jul 30 '12 at 06:41
@techie_28 case-sensitivity depends on type of column and collation. — Zaffy, Jul 30 '12 at 06:41
@juergend thats right I save them as a hash but this is from when I was just a newbie and didnt knew much of hashes. — techie_28, Jul 30 '12 at 06:42
@quarry I dont remember defining any collation on the password coloumn ever.I always had made them like all the others. — techie_28, Jul 30 '12 at 06:46
this is because encryption functions which you are using are case sensitive. — Omesh, Jul 30 '12 at 07:20
@Omesh I am not using any encryption functions.My question is that it works as case insensitive in the first case and as a case sensitive when I use where password ='abC'.Collation of all columns is latin1_swedish_ci. — techie_28, Jul 30 '12 at 08:55
that`s weird. try changing column name from password to name or something else. — Omesh, Jul 30 '12 at 09:12

score 5 · Accepted Answer · answered Jul 30 '12 at 06:43

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

but still it behaves as Case Sensitive even if I havent encrypted passwords to hashes. — techie_28, Jul 30 '12 at 08:50
Thanx @Omesh I think that explains it very well and suits my test cases. — techie_28, Sep 06 '12 at 10:40

score 4 · Answer 2 · answered Jun 09 '16 at 06:56

4

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

answered Jun 09 '16 at 06:56

Pankaj Upadhyay

2,001
16
22

score 3 · Answer 3 · answered Aug 10 '12 at 14:27

3

for case-sensitive use (BINARY)

SELECT * FROM tbl WHERE BINARY password = aBc

answered Aug 10 '12 at 14:27

KSA dev

51
2

score 1 · Answer 4 · answered Jul 30 '12 at 06:50

I'm not sure what the answer to your exact question is, however if you're storing passwords in a database as text, then that is a VERY bad idea. What you should do instead is hash the password upon registration and store it in your database in that form. Then each time a user attempts to login, you rehash the submitted password and compare it to the hash stored in the row with the matching username. Since the hash IS case-sensitive, this solves your problem while adding a much needed level of security.

score 1 · Answer 5 · answered Jul 30 '12 at 07:42

1

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

answered Jul 30 '12 at 07:42

Dojo

4,729
4
35
65

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

5 Answers5

Linked