MySQL string comparison functions for MD5 and SHA1 hashes

MySQL string comparison functions for MD5 and SHA1 hashes; how to calculate MD5 and SHA1 hashes in MySQL and let MySQL do the calculations for you.

Some web scripting languages like classic ASP don’t have native string hashing functions like MD5 or SHA1. This makes it quite difficult to hash or encrypt user supplied input and to perform string comparison to compare hashes.

Let’s make MySQL do the calculations and comparison for us!

MySQL MD5 string comparison – example 1

The examples and code speaks for itself; first we create a table called tblUser, and fill it with an username and password combination. MySQL creates an MD5 hash of the supplied password.

$ mysql -u db-name -p
Enter password: ...
Welcome to the MariaDB monitor.  Commands end with ; or \\g.
Your MariaDB connection id is 9950505
Server version: 5.5.32-MariaDB-log MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

MariaDB [(none)]> use db-name;
Database changed

MariaDB [db-name]> CREATE TABLE `tblUser` (
    -> id SMALLINT unsigned NOT NULL auto_increment,
    -> username VARCHAR(15) NOT NULL UNIQUE,
    -> password VARCHAR(32),
    -> active BOOLEAN NOT NULL DEFAULT 1,
    -> INDEX (id, username, active),
    -> PRIMARY KEY (id)) engine = InnoDB;
Query OK, 0 rows affected (0.04 sec)

MariaDB [db-name]> INSERT `tblUser` (
    -> id,
    -> username,
    -> password,
    -> active)
    -> VALUES (
    -> DEFAULT,
    -> 'admin',
    -> MD5('password123'),
    -> 1);
Query OK, 1 row affected (0.00 sec)

This is how our password123 looks like when it’s hashed

MariaDB [db-name]> select password from tblUser where username = 'admin';
+----------------------------------+
| password                         |
+----------------------------------+
| 482c811da5d5b4bc6d497ffa98491e38 |
+----------------------------------+
1 row in set (0.00 sec)

We compare an MD5 hash of password123 with the hash MySQL calculates using the MD5 function. The IF function returns true or false, which makes the comparison.

MariaDB [db-name]> SELECT IF (
	'482c811da5d5b4bc6d497ffa98491e38' = 
	MD5('password123'), 'true', 'false') 
	AS valid;
+-------+
| valid |
+-------+
| true  |
+-------+
1 row in set (0.00 sec)

MySQL SHA1 hashing and string comparison – example 2

With our altered password column we can perform an SHA1 string comparison using the SHA1 function. Again, MySQL does the calculation.

MariaDB [db-name]> SELECT password
	FROM tblUser
	WHERE username = 'admin';
+------------------------------------------+
| password                                 |
+------------------------------------------+
| cbfdac6008f9cab4083784cbd1874f76618d2a97 |
+------------------------------------------+
1 row in set (0.00 sec)

MariaDB [db-name]> SELECT IF (
	'cbfdac6008f9cab4083784cbd1874f76618d2a97' = 
	SHA1('password123'), 'true', 'false')
	AS valid;
+-------+
| valid |
+-------+
| true  |
+-------+
1 row in set (0.00 sec)

A more dynamic select in MySQL

The previous examples weren’t very dynamic. We just used the MD5 or SHA1 hash of our password. But when creating a login session on a website, we don’t know the hash and therefor we have to select it.

MariaDB [db-name]> SELECT IF (
	(SELECT password FROM tblUser 
	WHERE username = 'admin') = 
	SHA1('password123'), 'true', 'false') 
	AS valid;
+-------+
| valid |
+-------+
| true  |
+-------+
1 row in set (0.00 sec)

MariaDB [db-name]> SELECT IF (
	(SELECT password FROM tblUser WHERE username = 'admin') = 
	SHA1('password1234'), 'true', 'false')
	AS valid;
+-------+
| valid |
+-------+
| false |
+-------+
1 row in set (0.00 sec)

Alter MySQL password column for SHA1

For the above example to compare SHA1 hashes, we need to alter the password column in order to support the length of an SHA1 hash. Here is how to alter the column.

MariaDB [db-name]> ALTER TABLE tblUser MODIFY password VARCHAR(40);
Query OK, 1 row affected (0.00 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [db-name]> UPDATE tblUser
	SET password = SHA1('password123')
	WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0