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.
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)
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)
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)
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)
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)
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.
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)
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)
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)
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.).
If you have any questions about storing passwords securely you can post a comment below. Thanks so much for reading! And don't be shy to share this article.
Join our mailing list and stay tuned! Never miss out news about Zino UI, new releases, or even blog post.