top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

How to encrypt passwords in MYSQL? What command can be use to encrypt passwords.

+2 votes
777 views
How to encrypt passwords in MYSQL? What command can be use to encrypt passwords.
posted Jan 25, 2016 by Ritika Sharma

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Using the Default Password Encryption

MySQL stores passwords in the User table, along with all things user:

mysql> SELECT host,user,password FROM mysql.user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *D4FA16B3275E6619F3029FDDBA9A90EBA0DDFBEA |
+-----------+------+-------------------------------------------+
Smartly, MySQL doesn't store passwords as plaintext, but rather, as a hashed value that is calculated by the Password() function.

A hash is a special one-way encryption algorithm that produces an encrypted value for a given string.

When you log into MySQL, it runs the supplied password through the same encryption algorithm and compares the result to the stored value in the User table. Being a one-way algorithm makes it harder to crack because even MySQL can't derive the plaintext string from the hashed value! (Note to self: don't forget password.) Before MySQL 4.1, these were 16 bytes long.
Since then, the Password() function has been modified to produce a longer – and more secure - 41-byte hash value.

When we create a new user account using the CREATE USER command, MySQL takes the IDENTIFIED BY value and runs it through the Password() function behind the scenes:

mysql> CREATE USER 'robg'@'localhost' IDENTIFIED BY 'test1234';
0 rows affected, 0 rows found. Duration for 1 query: 0.328 sec.

In fact, you can't set the password directly using an INSERT or UPDATES statement:

mysql> UPDATE 'user' SET Password = 'test1234' WHERE User = 'RobG';
/* SQL Error (1364): Field 'ssl_cipher' doesn't have a default value */
Using the Password() function to encrypt passwords is a whole lot better than nothing, but you can use stronger encryption if your data requires it. The MySQL docs explicitly state that Password() should only be used to manage passwords for MySQL accounts and that you should not use it in your own applications. For that purpose, they recommend going with something a little more potent like MD5 or SHA1.

MD5 creates a hash string of 32 hex digits. SHA1 produces a 160-bit checksum for the string, according to the RFC 3174 (Secure Hash Algorithm) spec, resulting in a string of 40 hex digits. Of the two, SHA1 is considered to be more secure than MD5.

MySQL already has built-in MD5() and SHA1() functions. All you have to do is call them, but use a regular INSERT or UPDATE statement for those.

For instance, this won't work:

SET PASSWORD FOR 'robg'@'localhost' = MD5('newpassword');
However, this will:

UPDATE User SET Password = MD5('newpassword') WHERE user = 'robg';
To store passwords encrypted with SHA1, you'll need to be able to store 40 characters. When in doubt, the CHARACTER_LENGTH() function will tell you how large the password field has to be:

mysql> SELECT CHARACTER_LENGTH(MD5('newpassword'));
+-----------------------------------------+
| CHARACTER_LENGTH(MD5('newpassword')) |
+-----------------------------------------+
| 32 |
+-----------------------------------------+
Exploits have been documented for both the SHA and MD5 ciphers, so if you want to get ahead of the curve, you might consider going to SHA-2, using the SHA2() function. It calculates the SHA-2 family of hash functions, which include SHA-224, SHA-256, SHA-384, and SHA-512, using two arguments: the cleartext string to be hashed and the desired bit length of the result. The latter must have a value of 224, 256, 384, 512, or 0 (which defaults to 256). The function returns a hash value containing the desired number of bits:

mysql> SELECT SHA2('newpassword', 224);
+-----------------------------------------------------------+
| SHA2('newpassword', 224) |
+-----------------------------------------------------------+
| 4a574b42e32e03846eda8fc71b667a527c3840614896f0376bfca92b |
+-----------------------------------------------------------+

mysql> SELECT SHA2(
'newpassword', 384);
+-----------------------------------------------------------------------------------------------+

| SHA2('newpassword', 384) |
+-----------------------------------------------------------------------------------------------+

|0f60c17a9c7df029682066d18836e4213803b62f766b1555efaf14e8b0cf61b81b838deb56ef3397c07e7b7bb8e96df|
+-----------------------------------------------------------------------------------------------+
I think we're gonna need a bigger field!

Using Two-way Encryption

Using two-way encryption on passwords is somewhat controversial because it opens the door – at least in theory – for obtaining the original password string from the encrypted version. Two-way ciphers also require an extra crypt_str argument, so be prepared to take on some additional key management overhead.

One such encryption scheme is DES. It uses the Triple-DES algorithm to encrypt a string with the supplied key. The syntax for the encrypting and corresponding decrypting function is:

DES_DECRYPT(crypt_str[,key_str])
DES_ENCRYPT(str[,{key_num|key_str}])
As the following example demonstrates, two-way ciphers produce binary encoding:

mysql> SELECT DES_ENCRYPT('text','newpassword');
+--------------------------------------+
| DES_ENCRYPT('text','newpassword'); |
+--------------------------------------+
| sN" |
+--------------------------------------+
Likewise, the AES_ENCRYPT() and AES_DECRYPT() functions were added in MySQL 4.0.2 to enable encryption and decryption of data using the Advanced Encryption Standard (AES) algorithm, also previously known as "Rijndael." Encoding is effectuated using a 128-bit key length because it is much faster and it is secure enough for most purposes, but you can extend it up to 256 bits by modifying the source.

Here's an SQL statement to encrypt a password using AES encoding:

mysql> SELECT AES_ENCRYPT('text','newpassword');
+--------------------------------------+
| AES_ENCRYPT('text','newpassword'); |
+--------------------------------------+
| `[1]P]s |
+--------------------------------------+
Again, binary output is produced.

AES_ENCRYPT() and AES_DECRYPT() are considered to be the most cryptographically secure encryption functions currently available in MySQL.

answer Jan 27, 2016 by Manikandan J
Similar Questions
+2 votes

In my web application I need to encrypt the database password and and that should be stored in the properties file?

0 votes

I am having trouble to transfer email user account which is saved in MySQL to another server. Here is the detail:

I have an old email server which using MySQL to store user account information. The password field uses MySQL ENCRYPT function to save the users password. So if I want change the user's password I can do:
UPDATE mail.users SET password = ENCRYPT( '12345' ) WHERE CONVERT( users.email USING utf8 ) = 'g@veecall.com' LIMIT 1 ;

Then the new password "12345" saved in the table as string of "2I6JOeg.JukJ."

Now I build a new server using iRedMail. When I try to transfer user account I have trouble to transfer the password field. Because the iRadMail/dovecot is using MD5-CRAM to encrypt the password then save it
in the MySQL. All the password string is started with "$1$".

So, is there a way to make the MySQL encrypted password string "2I6JOeg.JukJ." convert to MD5 hash "$1$................."?

+1 vote

I have some problem
I have store the data in the database without encrypt but i am view the data encrypt method after 1 day and doesn't know the details.

I am using php and mysql, Could the experts please comment on this, and offer some advice?

+1 vote

So I have been hearing that md5 has been compromised, how much of a security threat does this impose? if it is something that poses a large concern is it possible to change the encryption method from md5 to crypt? or another method that is not compromised?

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...