0

I am running an ProFTPD Server with a MySQL backend for user authentication.

The passwords for the users are currently in plaintext. And my goal is, that all the users have encrypted passwords stored in the database.

I know that when I want to encrypt one password from one user I can type in the SQL command: 

update users set password= md5('MyPassword') where password="myPassword";

But how can I encrypt all all passwords from all users?

I hope anyone can help me.

Wubi
  • 83
  • 1
  • 9
  • 2
    Use an external script to do that. Don't use MD5. Don't use the builtin password functions. Use a strong hashing algorithm. – Gerald Schneider Jan 23 '17 at 15:53
  • 3
    Reading material: http://security.stackexchange.com/questions/211/how-to-securely-hash-passwords – Gerald Schneider Jan 23 '17 at 15:54
  • Thank you really much for your help. I basically learned that MD5 and hashes without salt are insecure. I think that this MySQL code should be sufficient to generate a secure hash value: `update users set password= sha2(concat('MyPassword',uuid()),256) where password="myPassword"; ` sha2 should generate a secure hash value and concate is for concatenate the password and the random salt. – Wubi Jan 23 '17 at 19:53
  • 1
    @Wubi that's a much better password hash, but still not actually good. sha2 can be computed fast, and therefore an attacker can try lots of guesses fast. In addition to salting the hash, you also should use a hash that's slow/expensive to compute (see [this blog entry](http://security.blogoverflow.com/2013/09/about-secure-password-hashing/)). Basically, that means using PBKDF2, bcrypt, scrypt, or possibly Argon2. Unfortunately, none of these seem to be available natively in MySQL (seriously?). Any chance of getting something like PHP involved? – Gordon Davisson Jan 24 '17 at 06:03
  • @Gordon Thanks for the answer. I read the stackexchange question Gerald sent me and then consult the MySQL manual https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html . SHA2 is seem to be the best hash algorithm implemented in MySQL. Unfortunately there is no bcrypt or PBKDF2 implemented in MySQL.To the question if I could involve PHP. It's an FTP Server, I administrate, but maybe I could make an HMTL page with PHP to generate a secure hash. Considering the php manual, there is a "password_bcrypt" function in php. So what do you think of this idea? – Wubi Jan 24 '17 at 06:44
  • @Gordon I'd see that ProFTPD implemented an PBKDF2 hash generation for passwords http://www.proftpd.org/docs/contrib/mod_sql_passwd.html#SQLPasswordOptions – Wubi Jan 24 '17 at 07:39
  • @Wubi That'd be a much better way to go. Unfortunately, I don't see a trivial way to do the conversion to that format. You'd probably do best to ask in a ProFTPD community. – Gordon Davisson Jan 25 '17 at 02:23
  • @Gordon Thank you very much for your answer. I ask the question in the profptd forum and close this question here. – Wubi Jan 25 '17 at 19:12

1 Answers1

1
update users set password= md5('MyPassword') where password="myPassword";

It is not safe to use MD5 as a hash. MD5 is deprecated

Safe hash algorithms are PBKDF2, bcrypt, scrypt etc. And additionally all hash algorithms have to be used with salt. @Gerald Schneider posted a very good link for this topic: https://security.stackexchange.com/questions/211/how-to-securely-hash-passwords

The problem here is, that none of these save hash algorithm are implemented in mysql. There is an authentication mode for PBKDF2 in ProFTPD in the mod sql module. But there is no way OOTB you can generate a PBKDF2 hashed password, or a password, that is hashed with another safe algorithm, in a mysql database.

A possible solution would be to create an HTML page with PHP. PHP has functions, implemented by default, for generating safe hashed passwords.

I asked in the ProFTPD forum, if anyone there knows another, maybe better answer, to the problem: https://forums.proftpd.org/smf/index.php/topic,12110.0.html

Yes, I know the original question was, how could one hash all the passwords in my database at once with one command. But I think, first, I should look for a safe hash algorithm.

kenlukas
  • 3,101
  • 2
  • 16
  • 26
Wubi
  • 83
  • 1
  • 9
  • If you come up with a procedure to do the conversion, please add it to your answer; others may be able to use it as well. BTW, once the passwords are converted, be sure to securely delete the old password column, and all backup copies of the database that aren't offline and securely stored (preferably encrypted). – Gordon Davisson Jan 25 '17 at 23:00
  • Hey there. In the proftpd forum, they wrote, that there is no method to create to securely hash a password in mysql. To I am writing a script in php wo do that job. – Wubi Feb 03 '17 at 13:41