MySQL Username not case sensitive

Question

sir i have problem on php mysql login

On my db:

username = admin 
password = addbcea06efdd20f934b35e3b2111e55

1st i test This the query on mysql

SELECT COUNT(`user_id`) FROM `users` WHERE `username` = 'admin' AND `password` = 'addbcea06efdd20f934b35e3b2111e55'

the 1st result is 1

but when i my second test

SELECT COUNT(`user_id`) FROM `users` WHERE `username` = 'Admin' AND `password` = 'addbcea06efdd20f934b35e3b2111e55'

why the result keep 1? This should be 0. I only change the username to capital letter. What should i do?

http://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql — J Young, Feb 25 '15 at 12:05

score 2 · Accepted Answer · answered Feb 25 '15 at 12:20

MySQL columns can be made IN-case-sensitive by creating them with the binary keyword. I suspect this is your problem. You can modify the column to be binary or change your query to

SELECT COUNT(`user_id`) FROM `users` WHERE BINARY `username` = 'Admin' AND `password` = 'addbcea06efdd20f934b35e3b2111e55'

score 1 · Answer 2 · edited Feb 25 '15 at 18:42

1

MySQL compares data in a case-insensitive manner except for database, table and column names. That is why your second query didn't return 0.

edited Feb 25 '15 at 18:42

Chris Forrence

10,042
11
48
64

answered Feb 25 '15 at 12:16

sathishrtskumar

19
6

Not always true, depends on the server configuration for the charset. – Tensibai Feb 25 '15 at 13:08

score 0 · Answer 3 · answered Feb 25 '15 at 12:16

The best way to avoid case-sensitive problems is always to use the php function strtolower() in the two parts of data manipulation: insert and query, in this case in the registration and the login verification.

You can add this line before the registration insert in your code:

$username = strtolower ( $username );

This will make always the username is stored in lowercase, then you can do the same in the login part of your program. Since you always will be comparing lowercases strings there is no chance of a case-sensitive error.

MySQL Username not case sensitive

3 Answers3