How to check for case sensitive record in mySQL

Some times you will come across a situation where you need to restrict a user based on the case sensitive nature of the data in the database. To better understand this problem, suppose we have a mySQL table which houses the username and passwords of the users and some user has entered his username with one or two characters in upper case and the rest in lower case. We will take for example that the user has entered “Admin” as his preferred username. If the username field has been created with varchar type then the user will be able to enter into the system (provided he uses the correct password as well) using “admin”, “ADMIN” or “aDMIN” which would be a grave security error. Read on to see this issue in action and how we can counter it

Firstly run the following SQL statement in your mySQL database.

DROP TABLE IF EXISTS `test_admin`;

CREATE TABLE `test_admin` (
  `id` int(11) DEFAULT NULL,
  `userid` varbinary(20) DEFAULT NULL,
  `userids` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pwd` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert into `tadmin` (`id`,`userid`,`userids`,`pwd`) values (1,'Admin','Admin','admin');

The above SQL will create a table “test_admin” in your database and will also insert one record.

Now run the following SQL statements

// This SQL statement will return no records
select * from test_admin where userid = 'admin' and pwd = 'admin';

// This SQL statement will return 1 record
select * from test_admin where userids = 'admin' and pwd = 'admin';

You will see that the first SQL did not return any record where as the second one returned one record.

Now run the next set of SQL statements

// This SQL statement will now return 1 record
select * from test_admin where userid = 'Admin' and pwd = 'admin';

// This SQL statement will return 1 record as before
select * from test_admin where userids = 'Admin' and pwd = 'admin';

This time both SQL statements returned 1 record.

What was the difference?

If you see the SQL statement which created the table in mySQL we created 2 columns. The “userid” column had varbinary data type whereas the “userids” column had the varchar data type.

The varbinary data type forced mySQL to compare the string provided in the SELECT SQL statement at the binary string level with string saved in the table whereas the varchar data type checked the string at the character level and therefore it did not see any difference in “admin” and “Admin”.

Use the mySQL varbinary data type when ever you need to make sure that the case of the string input by the user matches with that in the database.

Please follow and like us:
error

Be the first to comment

Leave a Reply

Your email address will not be published.