3

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?

Community
  • 1
  • 1
fabrik
  • 14,094
  • 8
  • 55
  • 71
  • `Encrypts str using the Unix crypt() system call and returns a binary string.`- your question should probably be whether `crypt()`'s output varies from system to system, or from distribution to distribution. – Pekka Apr 14 '11 at 10:55
  • @Pekka i just read what the manual says but when i tried twice the same string at the same machine it gave me different results. Sorry for my poor knowledge but email is the most important service on the box so i should be very circumspect. – fabrik Apr 14 '11 at 10:59
  • ah! I know little to nothing about `crypt()` myself so I can't answer that. Maybe add some more tags to attract the right people? `Linux` `Unix` and `crypt` come to mind. – Pekka Apr 14 '11 at 11:03
  • yeah. You could consider asking on http://unix.stackexchange.com/ if nothing comes up here – Pekka Apr 14 '11 at 11:07

4 Answers4

5

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.

James C
  • 14,047
  • 1
  • 34
  • 43
  • @James C Unfortunately i know nothing about what is `hello` and that's my problem. I don't want to decrypt these encrypted strings but i'd like to be sure about my newly imported database will accept original passwords. – fabrik Apr 14 '11 at 12:38
  • If you run `ENCRYPT('foo','bar')` on both servers and receive the same value back on both of them then the old passwords will work on the new machine. – James C Apr 14 '11 at 12:43
  • @James C salt wasn't added originally so encrypted strings are random. Looks like nothing left but build the cube then try if it works. Thank you, James. – fabrik Apr 14 '11 at 12:50
  • Please read the post carefully. "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.". The salt is shown as the first two characters of the encrypted string that's stored in your database. – James C Apr 14 '11 at 12:55
  • @James C this piece is mostly clear now. But because i know nothing about my users' passwords i can't import them into the new database. Or am i misunderstanding something? – fabrik Apr 14 '11 at 13:03
  • Please run `SELECT ENCRYPT('foo','bar');` in MySQL on both servers. If it outputs the same result then you're safe to copy the enrypted password data from the table on one database to the other and the users' passwords will continue to work. – James C Apr 14 '11 at 13:05
  • @James C will do it when the new server is alive, thank you again. – fabrik Apr 14 '11 at 13:08
1

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.

Gabe L
  • 11
  • 1
0

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.

Graeme Perrow
  • 56,086
  • 21
  • 82
  • 121
0

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.

Datajam
  • 4,141
  • 2
  • 23
  • 25