Home / SQL Statements / Question No: 228

I need to validate user login in case sensitive manner through MySQL query.  I have a table named user.   The sample table data is as below:

mysql> SELECT * FROM user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | Adnan    | pw12345  |
|  2 | adnan    | pw98765  |
+----+----------+----------+
2 rows in set (0.02 sec)

There are two users having username like Adnan and adnan with different ids like 1 and 2 and passwords pw12345 and pw98765 respectively.  Now at my login page, if user Adnan comes and wants to login with his valid password i.e. pw12345, he gets 'invalid password' message instead of successful login. My select query is like below:

SELECT * FROM user WHERE username = 'Adnan' AND password = 'pw12345';

I believe that the problem is caused by same usernames.  Therefore, the username column should be compared in case sensitive manner. Is there any solution to compare column value in case sensitive manner in MySQL?

MySQL provides more than one mothed to do so:

• You can set your concerned column's COLLATION to latin_general_cs.
• If you don't like above, you can force MySQL to check for an exact case. For this you can use the BINARY keyword in your query. Simply place BINARY before the column you want to compare. Take an example:

SELECT * FROM user WHERE BINARY username = 'Adnan' AND password = 'pw12345';