MySQL string comparison functions for MD5 and SHA1 hashes; how to calculate MD5 and SHA1 hashes in MySQL and compare strings with MySQL. Some web scripting languages like 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 string comparison and hash calculations 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 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)

Alter MySQL password column for SHA1

For our next example, to compare SHA1 hashes, we need to alter our password column in order to support the lenght of an SHA1 hash:

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

SHA1 hashing and string comparison in MySQL – example 2

With our new password column, we can perform an SHA1 string comparion, using the SHA1 function. Again, MySQL does the SHA1 hashing and calcualtions.

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)

Dynamic SELECT in MySQL to validate a password hash – example 2.2

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 therefore 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)
This may interest you:   PHP 5.6 default_charset change may break HTML output