MySQL ENCRYPT salt is not random?

Question

I know there's little reason to still use ENCRYPT() nowadays, what with bcrypt being almost ubiquitous and MySQL providing better hashes such as SHA1.

But while dabbling with ENCRYPT() on MySQL 5.6.12 (OpenSuSE 13.1, x64), I noticed an anomaly with its output that I at first attributed to the entropy pool being depleted (it could have been), and then possibly to the salt being leaked between connections.

Upon verification, it turned out not to be the case.

Rather, the salt is derived from the UNIX timestamp. So two calls to ENCRYPT() within the same second will yield identical salts, and the salt repeats itself exactly every one hour, 12 minutes, 16 seconds.

while(true); do \
    echo "SELECT NOW(),ENCRYPT('test');" \
    | mysql test | grep -v ENCRYPT; \
done | uniq -c

 54 2014-05-14 00:13:16     w5kVzeZkJCcqM
148 2014-05-14 00:13:17     x5/h3KfsBkEYk
150 2014-05-14 00:13:18     y5thvRDxwJgM6
145 2014-05-14 00:13:19     z5RL9IZ0..XH6
141 2014-05-14 00:13:20     .6asQOJRqB1i2
130 2014-05-14 00:13:21     /6J1nHNWbi6Ac
124 2014-05-14 00:13:22     06NT9j.EegRJs

Of course I can supply my own salt and get it from a random source - TO_BASE64(CONCAT(CHAR(RAND()*256),CHAR(RAND()*256))) or something, which should get the same character range as the original salt in its first two bytes, so ENCRYPT()'s whipping up a salt of its own needs only be a last-ditch effort.

And the salt isn't expected to be secret, so it being known beforehand is maybe not too great a problem.

Even so, using time() for salting doesn't seem a very good option to me (this answer also confirms this).

Am I missing something obvious? Is maybe this behaviour configurable somehow (apart from recompiling)? Is it a known feature (I wasn't able to find any reference on Google or MySQL KB)?

Answer 1

The MySQL ENCRYPT() function is very poorly named, because it is not encryption at all; it is a wrapper around the OS-provided crypt() function, which is equally badly named. This is a password hashing function which happens to have been derived from the block cipher DES, but "derived" is the important word. The algorithm has been modified, and, in particular, the provided password is used as encryption key, not as data to be encrypted. In particular, the transform is not reversible: there is no decrypt() which would reveal the password.

It so happens that the traditional DES-based crypt() function, that ENCRYPT() builds upon, includes a 12-bit salt. The point of a salt is to repeat itself as rarely as is feasible. However, with a 12-bit salt, there are only 4096 possible salt values, so salt reuse cannot really be avoided. On the other hand, salts don't need to be secret or random or unpredictable. A time-based allocation strategy is as good as any other; random salt selection would not fare much better in that respect. That two calls within the same second imply the same salt is kind of sloppy; it may backfire if you have to use ENCRYPT() in a batch fashion, to hash a lot of passwords in a short time. Apart from that, there is nothing really wrong about using the current time as salt.

The real weakness, here, is using the DES-based crypt() function at all. It has some serious shortcomings:

• Passwords limited to 8 ASCII characters (subsequent characters, and the high bit of each byte, are ignored).
• Very small salt space leading to unavoidable non-negligible salt reuse.
• Way too fast for comfort: the internal number of iteration is fixed and low, and the function can be speeded up a lot in the context of a parallel attack by using bitslicing techniques.

If you have taken the ill-fated decision to use that function, then a time-based salt will not noticeably increase your self-inflicted misery.

Answer 2

Looking at the mysql source (item_strfunc.cc), you appear to be correct:

String *Item_func_encrypt::val_str(String *str) { ... #ifdef HAVE_CRYPT char salt[3],*salt_ptr; if ((null_value=args[0]->null_value)) return 0; if (res->length() == 0) return make_empty_result(); if (arg_count == 1) { // generate random salt time_t timestamp=current_thd->query_start(); salt[0] = bin_to_ascii( (ulong) timestamp & 0x3f); salt[1] = bin_to_ascii(( (ulong) timestamp >> 5) & 0x3f); salt[2] = 0; salt_ptr=salt; }

The random salt is generated directly from the start time of the query:

• timestamp=current_thd->query_start()