CODEWITHSUNDEEP
mysqllinuxunixhashcrypt
fabrik
fabrik

Reputation: 14365

Why does MySQL's ENCRYPT return different results on each call?

I have an ugly server issue, and i'm trying not to overlook any details on this.

My virtual email users' passwords are stored with MySQL's ENCRYPT function. My basic idea was I'll dump my virtual users' table from the old machine, then import it in the new one.

Just for double-check I tried to store a string with ENCRYPT then again, and the stored data was different. Does this mean I can't export/import my users simply as I thought?

Upvotes: 3

Views: 2773

Answers (4)

Gabe L
Gabe L

Reputation: 11

I understand this is an old post but if you have a similar problem you don't need to rebuild all the encrypted passwords. The salt is the first two characters.

Upvotes: 1

James C
James C

Reputation: 14149

What Datajam has already described is correct. Here's some further explanation.

If you don't supply a salt to the ENCRYPT() function then a random one will be generated and used to encrypt the string. The salt is just two bytes/characters.

First I'll demonstrate that if I run ENCRYPT() twice with the same string it'll give different values (because the random salt differs)

mysql> SELECT ENCRYPT('hello');
+------------------+
| ENCRYPT('hello') |
+------------------+
| 5Q5CiJWj4GItY    | 
+------------------+
1 row in set (0.02 sec)

mysql> SELECT ENCRYPT('hello');
+------------------+
| ENCRYPT('hello') |
+------------------+
| 7QHPY3iSLVdas    | 
+------------------+
1 row in set (0.00 sec)

Now if I use the last entry and attempt to ENCRYPT() again using the value we have already as the salt we'll get the same result back:

mysql> SELECT ENCRYPT('hello', '7QHPY3iSLVdas');
+-----------------------------------+
| ENCRYPT('hello', '7QHPY3iSLVdas') |
+-----------------------------------+
| 7QHPY3iSLVdas                     | 
+-----------------------------------+
1 row in set (0.00 sec)

Just to prove that if we get the string (password) wrong with the same salt we'll get a different value. Note that in this example the two first characters (which are just the salt) remain the same.

mysql> SELECT ENCRYPT('helloX', '7QHPY3iSLVdas');
+------------------------------------+
| ENCRYPT('helloX', '7QHPY3iSLVdas') |
+------------------------------------+
| 7QKDSis4DZnCU                      | 
+------------------------------------+
1 row in set (0.01 sec)

Using this information you should try to run the ENCRYPT() function both of the MySQL servers specifying the same salt with both you should get the same result back. If not then the implementation of crypt() likely varies between the two.

Upvotes: 5

Datajam
Datajam

Reputation: 4231

MySQL's ENCRYPT() function has an optional second argument to define the salt used by the hashing algorithm. If you do not provide a salt then the result will be different, even for the same input string.

If you are migrating a database and want to retain the same hashes, just make sure you also use the same salt value. ENCRYPT() should give the same result with the same input string and salt value.

Upvotes: 0

Graeme Perrow
Graeme Perrow

Reputation: 57248

It's likely that the ENCRYPT function salts the input with a random value for just that reason - you want encrypting the same data twice to give different ciphertext.

Upvotes: 0

Related Questions