MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / SQL Statements / Question No: 228

How to validate user login in case sensitive manner?

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?

Answer No: 228

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';

Related MySQL FAQs to the Above FAQ

What-does-alias-means-in-MySQL What does alias means in MySQL?

What-does-SQL-Expressions-mean-in-MySQL What does SQL Expressions mean in MySQL?

How-to-use-numeric-expressions-in-MySQL How to use numeric expressions in MySQL?

How-to-use-string-expressions-in-MySQL How to use string expressions in MySQL?

How-to-manage-case-sensitivity-in-string-comparisons How to manage case sensitivity in string comparisons?

How-to-use-LIKE-pattern-matching-operator How to use LIKE pattern-matching operator?

How-to-use-date-or-temporal-expressions How to use date or temporal expressions?

What-does-NULL-value-mean-in-MySQL What does NULL value mean in MySQL?

How-to-use-comments-in-SQL-statements How to use comments in SQL statements?

What-is-difference-between-delete-and-truncate-statements-of-MySQL What is difference between delete and truncate statements of MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.