MySQL setup script does not work with Mysql 8.0 and above

[rimas-kudelis]

MySQL 5.7 was the last MySQL version to support the ENCRYPT() function. We currently call this function in two places:

• vexim2/setup/migrations/vexim_2.2_to_2.3_mysql.sql

  Lines 40 to 41 in 664b08d

  	SET `crypt` = COALESCE(ENCRYPT(`clear`, CONCAT(@NEW_PW_PREFIX, MD5(RAND()))), `crypt`)
  	WHERE `crypt` = ENCRYPT(`clear`, `crypt`) AND @NEW_PW_PREFIX IS NOT NULL;

• vexim2/setup/mysql.sql

  Line 218 in 664b08d

  INSERT INTO `users` VALUES (1,1,'siteadmin','siteadmin',ENCRYPT(@SITE_ADMIN_PW,CONCAT(@PW_PREFIX,CONVERT(MD5(RAND()) USING utf8))),65535,65535,'','','site',1,0,0,0,0,0,0,0,1,NULL,NULL,0,0,0,'SiteAdmin',0,0,NULL,NULL);

The former is an old migration script. I hope nobody uses pre-2.3 VExim anymore, but if they do, well, I guess it's fair to ask that they run that migration script using MySQL 5.7. A note of caution in the migration script wouldn't hurt though.

Meanwhile the latter is our setup script, and it sucks that it fails. I guess the best way to fix it is to fall back to a predefined password again.

[runout-at]

I have no mySQL deployment anymore. Only MariaDB and it seems they didn't remove it:

Server version: 11.8.6-MariaDB-0+deb13u1 from Debian-log -- Please help get to 10k stars at https://github.com/MariaDB/Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT ENCRYPT('encrypt me');
+-----------------------+
| ENCRYPT('encrypt me') |
+-----------------------+
| 72R6E4vUcQvmE         |
+-----------------------+

[rimas-kudelis]

That's good to know. It's sort of an easy fix though, so I we could still aim for broader compatibility and not use ENCRYPT, especially since we only do so during set up or upgrade from 2.2.

[rimas-kudelis]

also relevant: https://unix.stackexchange.com/questions/52108/how-to-create-sha512-password-hashes-on-command-line

[runout-at]

In veximpy I use

from passlib.hash import pbkdf2_sha256, pbkdf2_sha512
...
        if settings['PWD_CRYPT_METHOD'] == 'pbkdf2_sha256':
            self.crypt = pbkdf2_sha256.hash(password)
        else:
            self.crypt = pbkdf2_sha512.hash(password)
...

In bash i'd go for

echo -n "${password}" | openssl dgst -sha256

For generating random passwords on commandline something like

pwgen 20 1