MySQL Encryption and Decryption functions

Encryption and Decryption in MySQL

MD5 generates a 128-bit hash value. You can use CHAR(32) or BINARY(16)
SHA-1 generates a 160-bit hash value. You can use CHAR(40) or BINARY(20)
SHA2-224 generates a 224-bit hash value. You can use CHAR(56) or BINARY(28)
SHA2-256 generates a 256-bit hash value. You can use CHAR(64) or BINARY(32)
SHA2-384 generates a 384-bit hash value. You can use CHAR(96) or BINARY(48)
SHA2-512 generates a 512-bit hash value. You can use CHAR(128) or BINARY(64)

Create Table

• I create the table and define the password column as TEXT.
• It is totally depending on the encryption function you are using,
• and you can set the length or you can use VARCHAR(n) or CHAR(n),
• or BINARY (n) accordingly.

CREATE TABLE userpassword
(
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(200) NOT NULL,
    password TEXT NOT NULL,
    PRIMARY KEY(id)
);

Insert using password()

mysql> INSERT INTO userpassword(username,password)
    ->  VALUES('Bob',password('mypassword1'));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM userpassword;
+----+----------+-------------------------------------------+
| id | username | password                                  |
+----+----------+-------------------------------------------+
|  2 | Bob      | *B9C1006CDC136604FA2B47681FC3643D7DCFD438 | 
+----+----------+-------------------------------------------+
1 row in set (0.00 sec)

Select the password table

mysql> SELECT id FROM userpassword 
    -> WHERE password=password('mypassword1');
+----+
| id |
+----+
|  2 | 
+----+
1 row in set (0.00 sec)

empty the table

You need to empty the table for the next tutorial.

mysql> truncate userpassword;

MD5 encryption

mysql> SELECT MD5('mypassword');
+----------------------------------+
| MD5('mypassword')                |
+----------------------------------+
| 34819d7beeabb9260a5c854bc85b3e44 | 
+----------------------------------+
1 row in set (0.00 sec)

Insert into table using md5 encryption

mysql> INSERT INTO userpassword (username,password) 
    ->         VALUES('David',MD5('mypassword1')
    -> );
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM userpassword;
+----+----------+----------------------------------+
| id | username | password                         |
+----+----------+----------------------------------+
|  1 | David    | 0d28e4080dc8f64fc9603639bb7aa1b9 | 
+----+----------+----------------------------------+
1 row in set (0.00 sec)

before you process this example, you need to empty the table

mysql> truncate userpassword;

mysql> SELECT * FROM userpassword 
    -> WHERE password=md5('mypassword1');
+----+----------+----------------------------------+
| id | username | password                         |
+----+----------+----------------------------------+
|  1 | David    | 0d28e4080dc8f64fc9603639bb7aa1b9 | 
+----+----------+----------------------------------+
1 row in set (0.00 sec)

using AES_ENCRYPT and AES_DECRYPT function

• You are required to have the key, and the key have to be the same when everytime you use to insert or retrieve record

mysql> SELECT AES_ENCRYPT('password1','secret');
+-----------------------------------+
| AES_ENCRYPT('password1','secret') |
+-----------------------------------+
| � �������q��"�C�                     | 
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT AES_DECRYPT( AES_ENCRYPT('password1','secret'),'secret');
+----------------------------------------------------------+
| AES_DECRYPT( AES_ENCRYPT('password1','secret'),'secret') |
+----------------------------------------------------------+
| password1                                                | 
+----------------------------------------------------------+
1 

using AES_ENCRYPT to insert into the table for password encryption

mysql> INSERT INTO userpassword(username,password)
    ->  VALUES('David',AES_ENCRYPT('password1','key:w3cyberlearning')),
    ->        ('Paul', AES_ENCRYPT('password2','key:w3cyberlearning')),
    ->        ('Jimmy',AES_ENCRYPT('password3','key:w3cyberlearning'));
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

using AES_DECRYPT to get the password

mysql> SELECT id,username,AES_DECRYPT(password,'key:w3cyberlearning') as password
    ->  FROM userpassword;
+----+----------+-----------+
| id | username | password  |
+----+----------+-----------+
|  1 | David    | password1 | 
|  2 | Paul     | password2 | 
|  3 | Jimmy    | password3 | 
+----+----------+-----------+
3 rows in set (0.00 sec)

Select the password

mysql> SELECT id,username FROM userpassword 
    -> WHERE AES_DECRYPT(password,'key:w3cyberlearning')='password1';
+----+----------+
| id | username |
+----+----------+
|  1 | David    | 
+----+----------+
1 row in set (0.00 sec)

Using SHA1 encryption

mysql> SELECT SHA1('mysuppersecret');
+------------------------------------------+
| SHA1('mysuppersecret')                   |
+------------------------------------------+
| 5f98d3338cc611003cbb97072357fa120880eba6 | 
+------------------------------------------+
1 row in set (0.00 sec)

Insert SHA1 password

mysql> INSERT INTO userpassword (username,password)
    -> VALUES('David',SHA1('password1')),
    ->    ('Christ',SHA1('password2')),
    ->    ('Jimmy',SHA1('password3'));
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM userpassword;
+----+----------+------------------------------------------+
| id | username | password                                 |
+----+----------+------------------------------------------+
|  1 | David    | e38ad214943daad1d64c102faec29de4afe9da3d | 
|  2 | Christ   | 2aa60a8ff7fcd473d321e0146afd9e26df395147 | 
|  3 | Jimmy    | 1119cfd37ee247357e034a08d844eea25f6fd20f | 
+----+----------+------------------------------------------+
3 rows in set (0.00 sec)

Get user id by password and username

mysql> SELECT id, username FROM userpassword WHERE
    ->  username='David' AND password=SHA1('password1');
+----+----------+
| id | username |
+----+----------+
|  1 | David    | 
+----+----------+
1 row in set (0.00 sec)

Using SHA2

• In the SHA2, the default digest algorithm uses is SHA-256.
• SHA2('mssage',0) uses the default digest algorithm (SHA-256)
• The second argument is 224, 246, 384, or 512. ie. SHA2('mssag',224), or SHA2('mssag',384)

mysql> SELECT SHA2('message',0);
+------------------------------------------------------------------+
| SHA2('message',0)                                                |
+------------------------------------------------------------------+
| ab530a13e45914982b79f9b7e3fba994cfd1f3fb22f71cea1afbf02b460c6d1d |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SHA2('message',256);
+------------------------------------------------------------------+
| SHA2('message',256)                                              |
+------------------------------------------------------------------+
| ab530a13e45914982b79f9b7e3fba994cfd1f3fb22f71cea1afbf02b460c6d1d |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Insert into userpassword table using SHA2 digest encryption

• You need to use MySQL truncate to empty the table before you process this tutorial.

mysql> INSERT INTO userpassword(id, username,password)
    -> VALUES(null,'Lili',SHA2('mypassword1',256));
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM userpassword\G
*************************** 1. row ***************************
      id: 1
username: Lili
password: 09343625c6c123d3434932fe1ce08bae5ac00a8f95bd746e10491b0bafdd1817
1 row in set (0.00 sec)

Query or Select using SHA2

mysql> SELECT id,username FROM userpassword WHERE
    -> password=SHA2('mypassword1',256);
+----+----------+
| id | username |
+----+----------+
|  1 | Lili     |
+----+----------+
1 row in set (0.03 sec)

DES_ENCRYPT and DES_DECRYPT

• Encrypts the given string using Triple-DES algorithm

Using DES_ENCRYPT to encrypt

mysql> SELECT DES_ENCRYPT('mypassword1','key:w3cyberlearning');
+--------------------------------------------------+
| DES_ENCRYPT('mypassword1','key:w3cyberlearning') |
+--------------------------------------------------+
|   ╞
ö}Φ╤≡╥≥1±pv┤
                                |
+--------------------------------------------------+
1 row in set (0.05 sec)

Using DES_DECRYPT to decrypt

mysql> SELECT DES_DECRYPT(DES_ENCRYPT('mypassword1','key:w3cyberlearning'),'key:
w3cyberlearning') AS decrypt;
+-------------+
| decrypt     |
+-------------+
| mypassword1 |
+-------------+
1 row in set (0.00 sec)

mysql>