Securing Password in MySQL
Storing plaint text password in the database is never a good idea. Fortunately MySQL provides you with some options to protect passwords.
1. SHA1: Secure Hash Algorithm version 1.0
2. SHA functions
3. MD5: Message Digest Algorithm 5
4. RFC 1321: The Message Digest Algorithm
5. AES: Advanced Encryption Standard
I will disscuss on AES over here,
To implement AES (Advanced Encryption Standard) encryption in your database, you will need to specify the password field as BLOB data type.
Syntax: mysql> INSERT INTO TableName(field1,field2) VALUES ('fieldValue1',AES_ENCRYPT('fieldValue2','my_keyword_to_encrypt') );
Note: - fieldValue2 is password to be stored
To create a new record, we have to call AES function and in addition to providing the password to be protected, we will also provide the keyword/String to encrypt.
For example:mysql> INSERT INTO tbluser (useName, Password) VALUES ('Jagadish', AES_ENCRYPT ('Jagadish123','J007’) );
mysql>select * from tbluser;
| userName | Password |
| jagadish | ]MÚ¤î¤hÁÉ[*,» |
Now to authenticate a user, we have to decrypt the password which is stored in the database and compare with the password entered by the user to login with the same keyword/String as we used before. Also we can encrypt the password entered by the user and then compare it with the password stored in the database. And also we have to provide the keyword/String used before.
For example:
mysql> select userName, AES_DECRYPT (password,'J007') from tbluser where userName = 'Testing'
| userName | Password |
| jagadish | Jagadish123 |
(The author Jagdish.M is a Software Engineer at Binary Spectrum.)
Comments
Nice Artice, can you give me more examples?
Posted by: ashok | March 10, 2009 05:46 PM