Newest Downloads

Mar.18

  [zenoss@zenoss2 ~]$ python Zenoss_Template_Manager.py -husage: Examples: python Zenoss_Templa...

How to store encrypted passwords in MySQL
Database HowTo's - MySQL HowTo's
Written by Allen Sanabria   
Sunday, 10 February 2008 00:16

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

    • 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)

     

    • 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)

     

     

    • 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


Add this page to your favorite Social Bookmarking websites
Reddit! Del.icio.us! Mixx! Free and Open Source Software News Google! Live! Facebook! StumbleUpon! Yahoo! Free Joomla PHP extensions, software, information and tutorials.
Comments
Search RSS
Only registered users can write comments!

3.22 Copyright (C) 2007 Alain Georgette / Copyright (C) 2006 Frantisek Hliva. All rights reserved."

Last Updated on Thursday, 08 May 2008 07:01