LinuxDynasty

Mysql storing encrypted passwords howto, All these commands were tested on MySQL5

1. This example we will create a MD5 encrypted password
   • We need to create a table called user_md5, (The value is returned as a binary string of 32 hex digits)
     “CREATE TABLE user_md5 (user_name VARCHAR(16), password VARCHAR(32));”
     Query OK, 0 rows affected (0.10 sec)
   • Now we need to insert a user into the table
     “INSERT INTO user_md5 VALUES (‘test’,MD5(‘test’) );”
     Query OK, 1 row affected (0.00 sec)
   • Now lets see if we query the user with the unencrypted password will it work
     “SELECT * FROM user_md5 where user_name = ‘test’ AND password=MD5(‘test’);”

   
   +-----------+----------------------------------+
   | user_name | password                         |
   +-----------+----------------------------------+
   | test      | 098f6bcd4621d373cade4e832627b4f6 | 
   +-----------+----------------------------------+
   1 row in set (0.00 sec)
   

    

2. This example we will create a SHA1 encrypted password
   • We need to create a table called user_sha1 ((Secure Hash Algorithm). The value is returned as a binary string of 40 hex digits)
     “CREATE TABLE user_sha1 (user_name VARCHAR(16), password VARCHAR(40));”
     Query OK, 0 rows affected (0.10 sec)
   • Now we need to insert a user into the table
     “INSERT INTO user_sha1 VALUES (‘test’,SHA1(‘test’) );”
     Query OK, 1 row affected (0.00 sec)
   • Now lets see if we query the user with the unencrypted password will it work
     “SELECT * FROM user_sha1 where user_name = ‘test’ AND password=SHA1(‘test’);”

   
   +-----------+------------------------------------------+
   | user_name | password                                 |
   +-----------+------------------------------------------+
   | test      | a94a8fe5ccb19ba61c4c0873d391e987982fbbd3 | 
   +-----------+------------------------------------------+
   1 row in set (0.00 sec)
   

    

    

3. This example we will create a AES encrypted password

    

   • We need to create a table called user_aes, ((Advanced Encryption Standard) encryption, you will need to specify the password field to be of type BLOB)
     “CREATE TABLE user_aes (user_name VARCHAR(16), password BLOB);”
     Query OK, 0 rows affected (0.10 sec)
   • Now we need to insert a user into the table
     “INSERT INTO user_aes VALUES(‘test’, AES_ENCRYPT(‘password’, ‘key’) );”
     Query OK, 1 row affected (0.00 sec)
   • Now lets see the results
     “SELECT * FROM user_aes WHERE user_name=’test’ AND password=AES_ENCRYPT(‘password’,'key’);”

   
   +-----------+------------------+
   | user_name | password         |
   +-----------+------------------+
   | test      | ��}��� ���n�
                             <-� | 
   +-----------+------------------+
   1 row in set (0.00 sec)
   

    

   • AES provides reversible encryption (provided you have the key), we can obtain the password in plain text format./
   • 
     
   • Now lets see the unecrypted password
     “SELECT AES_DECRYPT(password,’key’) FROM user_aes;”

   
   +-----------------------------+
   | AES_DECRYPT(password,'key') |
   +-----------------------------+
   | password                    | 
   +-----------------------------+
   1 row in set (0.00 sec)
   

For further info on encryption in MySQL