Sysadmins of the North
Don't forget to share this post!

How to compare MD5 and SHA1 hashes in MySQL

Some web scripting languages, such as 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 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 length 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

Compare SHA1 hash in MySQL
With our new password column, we can perform an SHA1 string comparison, using the SHA1 function. Again, MySQL does the SHA1 hashing and calculations.

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)

buy me a coffee
Buy Me A Coffee

About the Author Jan Reilink

My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, WordPress, websites & optimization. Want to support me and donate? Use this link: https://paypal.me/jreilink.

follow me on:

Leave a Comment:

Skip to content EqeyMM Tzo Cc FYP N Uz