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!
MD5 string comparison in MySQL
The examples and code speaks for itself; first we create a table called tblUser, and insert 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)
Next we compare an MD5 hash of password123 with the hash MySQL calculates using the MD5 function. The MySQL IF function returns true or false. This is wat 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 in MySQL, 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 comparison in MySQL
Compare SHA1 hashes 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 for us.
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)
As easy as that! :-)
Dynamic SELECT in MySQL: how to validate password hashes 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 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)
Neat, right?