Storing passwords securely

Posted on: January 27, 2017 by Dimitar Ivanov

Storing a sensitive data in plain text format could turn into a nightmare if the access to your database has been compromised. To minimize losses in such an cases MySQL provides functions for encrypt and hash of data.

Hashing

The hash functions are intended to map data of arbitrary size to data of fixed size. Hashing is a one-way process, i.e. the original data cannot be retrieved. Instead of storing the password a common practice is to store only its checksum.

mysql> CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  `pswd` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

md5

The MD5(str) function calculates the 128-bit checksum for a given string, returned as a string of 32 hex digits.

mysql> INSERT INTO `users` (`email`, `pswd`) VALUES ('user1@example.com', MD5('pass123'));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT `pswd` FROM `users` WHERE `email` = 'user1@example.com';
+----------------------------------+
| pswd                             |
+----------------------------------+
| 32250170a0dca92d53ec9624f336ca24 |
+----------------------------------+
1 row in set (0.00 sec)

sha1

The SHA1(str) function calculates the 160-bit checksum for a given string, returned as a string of 40 hex digits. The function is a successor of the less secure MD5().

mysql> INSERT INTO `users` (`email`, `pswd`) VALUES ('user2@example.com', SHA1('pass123'));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT `pswd` FROM `users` WHERE `email` = 'user2@example.com';
+------------------------------------------+
| pswd                                     |
+------------------------------------------+
| aafdc23870ecbcd3d557b6423a8982134e17927e |
+------------------------------------------+
1 row in set (0.00 sec)

password

The PASSWORD(str) function is used by the MySQL itself to store passwords of its users, but its not recommended to use it in your own programs.

mysql> INSERT INTO `users` (`email`, `pswd`) VALUES ('user3@example.com', PASSWORD('pass123'));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT `pswd` FROM `users` WHERE `email` = 'user3@example.com';
+-------------------------------------------+
| pswd                                      |
+-------------------------------------------+
| *FB6E1F205D675BC29B052DB14CCEFE7759C5FF7E |
+-------------------------------------------+
1 row in set (0.00 sec)

encrypt

The ENCRYPT function uses the UNIX native crypt() program, so do not expect to work on Windows.

mysql> INSERT INTO `users` (`email`, `pswd`) VALUES ('user4@example.com', ENCRYPT('pass123'));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT `pswd` FROM `users` WHERE `email` = 'user4@example.com';
+---------------+
| pswd          |
+---------------+
| 3kdKWkKx9T4Uo |
+---------------+
1 row in set (0.01 sec)

Encryption

The encryption is a process of converting a string into hard to read binary data. Once being encrypted the data can be decrypted later. The MySQL encryption functions allow us to encrypt and decrypt data values. If you plan to store a data values encrypted with these functions always use a BLOB column type.

mysql> CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  `pswd` blob,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

The MySQL functions used for an encryption can be divided into 3 sets according to the used algorithm.

encode

To encrypt a password use the ENCODE(str,pass_str) function:

mysql> INSERT INTO `users` (`email`, `pswd`) VALUES ('user5@example.com', ENCODE('pass123', 'secret'));
Query OK, 1 row affected (0.00 sec)

To decrypt a password previously encypted with the ENCODE function use the DECODE(crypt_str,pass_str) function:

mysql> SELECT DECODE(`pswd`, 'secret') AS `pswd` FROM `users` WHERE `email` = 'user5@example.com';
+-----------+
| pswd      |
+-----------+
| pass123   |
+-----------+
1 row in set (0.00 sec)

aes - these functions use the official AES algorithm (also known as "Rijndael") that provides encoding with a 128-bit key.

To encrypt a password use the AES_ENCRYPT(str,key_str) function:

mysql> INSERT INTO `users` (`email`, `pswd`) VALUES ('user6@example.com', AES_ENCRYPT('pass123', 'secret'));
Query OK, 1 row affected (0.01 sec)

To decrypt a password previously encypted with the AES algorithm use the AES_DECRYPT(crypt_str,key_str) function:

mysql> SELECT AES_DECRYPT(`pswd`, 'secret') AS `pswd` FROM `users` WHERE `email` = 'user6@example.com';
+-----------+
| pswd      |
+-----------+
| pass123   |
+-----------+
1 row in set (0.00 sec)

des - these functions use the Triple-DES algorithm. Note that they work only if MySQL has been configured with SSL support.

To encrypt a password use the DES_ENCRYPT(str[,(key_num|key_str)]) function:

mysql> INSERT INTO `users` (`email`, `pswd`) VALUES ('user7@example.com', DES_ENCRYPT('pass123', 'secret'));
Query OK, 1 row affected (0.00 sec)

To decrypt a password previously encypted with the DES algorithm use the DES_DECRYPT(crypt_str[,key_str]) function:

mysql> SELECT DES_DECRYPT(`pswd`, 'secret') AS `pswd` FROM `users` WHERE `email` = 'user7@example.com';
+-----------+
| pswd      |
+-----------+
| pass123   |
+-----------+
1 row in set (0.00 sec)

Conclusion

The AES standard provides the best defense in terms of security than the rest of algorithms described in this article. The AES_ENCRYPT and AES_DECRYPT functions are the best choices to store a sensitive data (e.g. passwords, credit card numbers, etc.).