CODEWITHSUNDEEP
phpmysql
Sekai92
Sekai92

Reputation: 72

MySQL Username not case sensitive

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?

Upvotes: 0

Views: 5479

Answers (3)

sathishrtskumar
sathishrtskumar

Reputation: 19

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.

Upvotes: 1

Arun
Arun

Reputation: 760

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'

Upvotes: 2

Jmunoz Dev
Jmunoz Dev

Reputation: 461

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.

Upvotes: 0

Related Questions