search

multiOTP / multiotp

multiOTP open source strong two factor authentication PHP library, OATH certified, with TOTP, HOTP, Mobile-OTP, YubiKey, SMS, QRcode provisioning, etc.
http://www.multiOTP.net/
GNU Lesser General Public License v3.0
346 stars 74 forks source link

Cannot connect to MySQL database #194

Closed Armaggedon closed 2 weeks ago

Armaggedon commented 1 month ago

I'm trying to set a MySQL database as a backend. I've configured multiotp for such scenario:

multiotp -config backend-type=mysql
multiotp -config sql-server=<dbserver>:<port>
multiotp -config sql-database=motp_users
multiotp -config sql-username=admin
multiotp -config sql-password=xxxxxxx

multiotp config file is updated accordingly. When executing multiotp -initialize-backend nothing happens and the database is empty, and on -ldap-users-sync it fails:

multiotp 5.9.7.1
Your script is running from C:\MultiOTP\windows\.\
2024-08-13 16:48:30 debug       LDAP    Debug: *AD/LDAP synchronization started at 16:48:30 / Memory used: 14.5MB / Peak: 27.1MB    0   RDSMOTP03
2024-08-13 16:48:30 info        LDAP    Info: AD/LDAP synchronization started   0   RDSMOTP03
2024-08-13 16:48:30 debug       System  Debug: *LDAP cache folder value: C:\Users\xxxxxx\AppData\Local\Temp\2\.ldap_cache/  0   RDSMOTP03
2024-08-13 16:49:39 debug       LDAP    Debug: *AD/LDAP synchronization started at 16:48:30, LDAP account #1 at 16:49:39 / Memory used: 14.6MB / Peak: 27.1MB   0   RDSMOTP03
2024-08-13 16:49:39 info        LDAP    Info: No update for the 0 LDAP synced users, based on 6 LDAP entries (processed in 00:01:09)    0   RDSMOTP03
2024-08-13 16:49:39 debug       Debug   Debug: *99 ERROR: Authentication failed (and other possible unknown errors) 0   RDSMOTP03
2024-08-13 16:49:39 debug       Debug   Debug: *Attributes sent to the RADIUS server: Reply-Message := "ERROR: Authentication failed (and other possible unknown errors)"   0   RDSMOTP03

However when I check the databaes or the multiOTP web console, there's nothing.

Some useful information:

Any ideas?

Edited a few times to better narrow down the issue.

Armaggedon commented 4 weeks ago

Today I've reconfigured it again and now the problem is different:

C:\MultiOTP\windows>multiotp -initialize-backend
PHP Fatal error:  Uncaught mysqli_sql_exception: Table 'motp_users.multiotp_config' doesn't exist in C:\MultiOTP\windows\php\multiotp.windows.php:2406
Stack trace:
#0 C:\MultiOTP\windows\php\multiotp.windows.php(2406): mysqli->query('SELECT * FROM `...')
#1 C:\MultiOTP\windows\php\multiotp.windows.php(5877): Multiotp->WriteData(Array)
#2 C:\MultiOTP\windows\php\multiotp.windows.php(5508): Multiotp->WriteConfigData()
#3 C:\MultiOTP\windows\php\multiotp.windows.php(1269): Multiotp->ReadConfigData()
#4 C:\MultiOTP\windows\php\multiotp.windows.php(75101): Multiotp->__construct('DefaultCliEncry...', true, 'C:\\MultiOTP\\win...', '')
#5 {main}
  thrown in C:\MultiOTP\windows\php\multiotp.windows.php on line 2406

Of course that 'motp_users.multiotp_config' doesn't exist, but that's what initialize-backend is supposed to do, right? Create the tables.

sansam commented 4 weeks ago

The same issue happened to me. While I find a workaround bellow.

  1. Get the source code from tag:5.8.8.4 and build&run a docker with it.
  2. Run "apt install php-mysql -y" inside the docker instance.
  3. Insert "extension=mysqli.so" to php configuration file "/etc/php/7.4/fpm/php.ini".
  4. Run "nginx -s reload" to active mysqli extension.
  5. Run "multiotp -config ***" and "multiotp -initialize-backend", you'll see all tables are created.
  6. Destory docker instance above.
  7. Restart the latest multiotp instance and repeate step 2~5, you'll seed all tables are updated.
Armaggedon commented 4 weeks ago

Hm, it actually works, many thanks! So I guess there's some bug in 5.9.x that doesn't create the database tables for MySQL (nor PostgreSQL, I've also tried).

Hopefully the devs will find the bug to avoid this workaround :)

Armaggedon commented 3 weeks ago

After having provisioned the database with 5.8.8.4, every ldap-users-sync throw these warnings per user:

LOG 2024-08-19 16:35:25 debug System Warning: *The key delayed_account is not in the users table schema
LOG 2024-08-19 16:35:25 debug System Warning: *The key delayed_time is not in the users table schema
LOG 2024-08-19 16:35:25 debug System Warning: *The key delayed_finished is not in the users table schema

But the docs say the tables and schema will be auto-updated, I guess by the -initialize-backend. Perhaps not happening? Is it important?

multiOTP commented 3 weeks ago

Hello,

We are not able to reproduce your issue. We have used a clean Windows device, installed the last VC redist (https://aka.ms/vs/17/release/vc_redist.x64.exe and https://aka.ms/vs/17/release/vc_redist.x86.exe), set-up the

multiotp -config backend-type=mysql
multiotp -config sql-server=<dbserver>:<port>
multiotp -config sql-database=motp_users
multiotp -config sql-username=admin
multiotp -config sql-password=xxxxxxx

And launched the initialize-backend: multiotp -initialize-backend -display-log -debug

Regards,

LittleTux75 commented 3 weeks ago

Hello,

You can reproduce the same issue in a test enviroment based on linux+xampp (test VM with Ubuntu 22.04 & xampp 8.2.12 (this example doesn't care about security)

This is what I did and the commands I used (xampp is running)

Structure of the multiotp directory in htdocs

user@linux:/opt/lampp/htdocs$ tree multiotp
multiotp
├── check.multiotp.class.php
├── COPYING
├── COPYING.LESSER
├── multiotp.class.php
├── multiotp.php
├── multiotp.server.php
├── oath
│   ├── pskc-hotp-aes.txt
│   ├── pskc-hotp-pbe.txt
│   ├── pskc-totp-aes.txt
│   ├── pskc-totp-pbe.txt
│   ├── tokens_hotp_aes.pskc
│   ├── tokens_hotp_pbe.pskc
│   ├── tokens_ocra_aes.pskc
│   ├── tokens_ocra_pbe.pskc
│   ├── tokens_totp_aes.pskc
│   └── tokens_totp_pbe.pskc
├── readme_5.9.7.1.txt
├── scripts
│   ├── multiotp.pl
│   └── multiotp-service.sh
├── templates
│   ├── scratchtemplate.html
│   └── template.html
└── test-tokens.csv

3 directories, 22 files

Grant write to everyone to the multiotp directories (and create the directory in /etc)

user@linux:/opt/lampp/htdocs$ sudo chown -R daemon multiotp
user@linux:/opt/lampp/htdocs$ sudo chgrp -R daemon multiotp
user@linux:/opt/lampp/htdocs$ sudo chmod 0777 multiotp
user@linux:/opt/lampp/htdocs$ sudo mkdir /etc/multiotp
user@linux:/opt/lampp/htdocs$ sudo chown -R daemon /etc/multiotp
user@linux:/opt/lampp/htdocs$ sudo chgrp -R daemon /etc/multiotp
user@linux:/opt/lampp/htdocs$ sudo chmod 0777 /etc/multiotp/

Set parameters about mysql 

user@linux:/opt/lampp/htdocs$ cd multiotp/
user@linux:/opt/lampp/htdocs/multiotp$ ../../bin/php multiotp.php -display-log -debug -config backend-type=mysql sql-server=localhost:3306 sql-database=multiotp sql-username=multiotp sql-password=password

LOG 2024-08-23 01:16:30 debug Debug Debug: **New configuration value for actual_version: '5.9.7.1' (was '' before)

LOG 2024-08-23 01:16:30 debug Debug Debug: **New configuration value for backend_type: 'mysql' (was 'files' before)

LOG 2024-08-23 01:16:30 debug Debug Debug: **New configuration value for sql_server: 'localhost:3306' (was '' before)

LOG 2024-08-23 01:16:30 debug Debug Debug: **New configuration value for sql_username: 'multiotp' (was '' before)

LOG 2024-08-23 01:16:30 debug Debug Debug: **New configuration value for sql_password: 'password' (was '' before)

LOG 2024-08-23 01:16:30 debug Debug Debug: **New configuration value for sql_database: 'multiotp' (was '' before)

LOG 2024-08-23 01:16:30 debug Debug Debug: **Writing configuration data needed
PHP Fatal error:  Uncaught mysqli_sql_exception: Table 'multiotp.multiotp_config' doesn't exist in /opt/lampp/htdocs/multiotp/multiotp.php:2408
Stack trace:
#0 /opt/lampp/htdocs/multiotp/multiotp.php(2408): mysqli->query('SELECT * FROM `...')
#1 /opt/lampp/htdocs/multiotp/multiotp.php(5879): Multiotp->WriteData(Array)
#2 /opt/lampp/htdocs/multiotp/multiotp.php(76176): Multiotp->WriteConfigData(Array, true)
#3 {main}
  thrown in /opt/lampp/htdocs/multiotp/multiotp.php on line 2408

configuration in multiotp.ini in /etc/multiotp/config after settings the parameters

user@linux:/opt/lampp/htdocs/multiotp$ cat /etc/multiotp/config/multiotp.ini
multiotp-database-format-v3
; If backend is set to something different than files,
; and backend_type_validated is set to 1,
; only the specific information needed for the backend
; is used from this config file.

encryption_hash=99CCFC0D033729754B6BB4832FE786A1
actual_version=5.9.7.1
admin_password_hash:=
anonymous_stat=1
anonymous_stat_last_update=0
anonymous_stat_random_id=
attributes_to_encrypt=
auto_resync=1
backend_encoding=UTF-8
backend_type=mysql
backend_type_validated=0
cache_data=0
cache_ldap_hash=1
case_sensitive_users=0
challenge_response_enabled=0
clear_otp_attribute=
console_authentication=0
create_host=linux
create_time=1724318155
debug=0
default_algorithm=totp
default_dialin_ip_mask=
default_pin_digits=4
default_user_group=
default_request_ldap_pwd=1
default_request_prefix_pin=1
demo_mode=0
developer_mode=0
display_log=0
domain_name=
email_admin_address=
email_code_allowed=0
email_code_timeout=600
email_digits=6
encode_file_id=0
encryption_key_full_path=
failure_delayed_time=300
group_attribute=Filter-Id
hash_salt_full_path=
issuer=multiOTP
language=en
last_failed_white_delay=60
last_sync_update=0
last_sync_update_host=
last_update=1724318155
last_update_host=linux
ldap_expired_password_valid=1
ldap_account_suffix=
ldap_activated=0
ldap_base_dn=
ldap_bind_dn=
ldap_cache_folder=
ldap_cache_on=1
ldap_cn_identifier=
ldap_default_algorithm=totp
ldap_domain_controllers=
ldap_filter=
ldap_group_attribute=
ldap_group_cn_identifier=
ldap_users_dn=
ldap_hash_cache_time=604800
ldap_in_group=
ldap_language_attribute=preferredLanguage
ldap_network_timeout=10
ldap_port=389
ldap_recursive_cache_only=0
ldap_recursive_groups=1
ldap_server_password:=
ldap_server_type=1
ldap_ssl=0
ldap_synced_user_attribute=
ldap_time_limit=30
ldap_without2fa_in_group=
ldaptls_reqcert=
ldaptls_cipher_suite=
log=0
max_block_failures=6
max_delayed_failures=3
max_event_resync_window=10000
max_event_window=100
max_time_resync_window=90000
max_time_window=600
multiple_groups=0
ntp_server=pool.ntp.org
overwrite_request_ldap_pwd=1
radius_error_reply_message=1
radius_reply_attributor= +=
radius_reply_separator_hex=2c
radius_tag_prefix=
scratch_passwords_digits=6
scratch_passwords_amount=10
self_registration=1
server_cache_level=1
server_cache_lifetime=15552000
server_secret:=dGx9cn5qeFV7fFJ5RGR/cGd4
server_timeout=5
server_type=
server_url=
sms_api_id:=
sms_basic_auth=0
sms_code_allowed=1
sms_content_encoding=
sms_content_success=
sms_digits=6
sms_encoding=
sms_header=
sms_international_format=0
sms_ip=
sms_message_prefix=
sms_method=
sms_no_double_zero=0
sms_originator=multiOTP
sms_password:=
sms_port=
sms_provider=
sms_send_template=
sms_status_success=
sms_timeout=180
sms_url=
sms_userkey:=
smtp_auth=0
smtp_password:=
smtp_port=25
smtp_sender=
smtp_sender_name=
smtp_server=
smtp_ssl=0
smtp_username=
sql_server=localhost
sql_username=multiotp
sql_password:=R3V5TXJidVQ=
sql_database=multiotp
sql_schema=
sql_config_table=multiotp_config
sql_cache_table=multiotp_cache
sql_ddns_table=multiotp_ddns
sql_devices_table=multiotp_devices
sql_groups_table=multiotp_groups
sql_log_table=multiotp_log
sql_stat_table=multiotp_stat
sql_tokens_table=multiotp_tokens
sql_users_table=multiotp_users
sync_delete_retention_days=30
syslog_facility=7
syslog_level=5
syslog_port=514
syslog_server=
tel_default_country_code=
timezone=Europe/Zurich
token_serial_number_length=12
token_otp_list_of_length=6
verbose_log_prefix=
sms_challenge_enabled=0
text_sms_challenge=
text_token_challenge=

Directories structure after settings the parameters

user@linux:/opt/lampp/htdocs/multiotp$ tree
.
├── check.multiotp.class.php
├── COPYING
├── COPYING.LESSER
├── log
├── multiotp.class.php
├── multiotp.php
├── multiotp.server.php
├── oath
│   ├── pskc-hotp-aes.txt
│   ├── pskc-hotp-pbe.txt
│   ├── pskc-totp-aes.txt
│   ├── pskc-totp-pbe.txt
│   ├── tokens_hotp_aes.pskc
│   ├── tokens_hotp_pbe.pskc
│   ├── tokens_ocra_aes.pskc
│   ├── tokens_ocra_pbe.pskc
│   ├── tokens_totp_aes.pskc
│   └── tokens_totp_pbe.pskc
├── readme_5.9.7.1.txt
├── scripts
│   ├── multiotp.pl
│   └── multiotp-service.sh
├── templates
│   ├── scratchtemplate.html
│   └── template.html
└── test-tokens.csv

4 directories, 22 files
user@linux:/opt/lampp/htdocs/multiotp$ tree /etc/multiotp/
/etc/multiotp/
├── config
│   └── multiotp.ini
├── ddns
├── devices
├── groups
├── tokens
└── users

Running the initialize command

user@linux:/opt/lampp/htdocs/multiotp$ ../../bin/php multiotp.php -initialize-backend -display-log -debug
PHP Fatal error:  Uncaught mysqli_sql_exception: Table 'multiotp.multiotp_config' doesn't exist in /opt/lampp/htdocs/multiotp/multiotp.php:2408
Stack trace:
#0 /opt/lampp/htdocs/multiotp/multiotp.php(2408): mysqli->query('SELECT * FROM `...')
#1 /opt/lampp/htdocs/multiotp/multiotp.php(5879): Multiotp->WriteData(Array)
#2 /opt/lampp/htdocs/multiotp/multiotp.php(5510): Multiotp->WriteConfigData()
#3 /opt/lampp/htdocs/multiotp/multiotp.php(1271): Multiotp->ReadConfigData()
#4 /opt/lampp/htdocs/multiotp/multiotp.php(75103): Multiotp->__construct('DefaultCliEncry...', true, '/opt/lampp/htdo...', '/etc/multiotp/c...')
#5 {main}
  thrown in /opt/lampp/htdocs/multiotp/multiotp.php on line 2408

Test mysql functionalities

user@linux:/opt/lampp/htdocs/multiotp$ ../../bin/mysql -u multiotp -ppassword -h localhost multiotp
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.4.32-MariaDB Source distribution

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 [multiotp]> CREATE TABLE Test (
    ->     ID int,
    ->     LastName varchar(32),
    ->     FirstName varchar(32),
    ->     Address varchar(128),
    ->     City varchar(32)
    -> );
Query OK, 0 rows affected (0.165 sec)

MariaDB [multiotp]> SHOW TABLES;
+--------------------+
| Tables_in_multiotp |
+--------------------+
| Test               |
+--------------------+
1 row in set (0.001 sec)

Check the multiotp requirements

...
...
OK! ALL 67 TESTS HAVE PASSED SUCCESSFULLY !

Time spent for the whole check.multiotp.class.php: 2 second(s)

Test with single configuration paramenter commands (after reinitializing the /etc/multiotp directory)

user@linux:/opt/lampp/htdocs/multiotp$ ../../bin/php multiotp.php -display-log -debug -config sql-password=password

LOG 2024-08-23 00:53:18 debug Debug Debug: **Writing configuration data not needed (no change)

LOG 2024-08-23 00:53:18 debug Debug Debug: **New stat value for encryption_hash: '99CCFC0D033729754B6BB4832FE786A1' (was '' before)

LOG 2024-08-23 00:53:18 debug Debug Debug: **Writing stat data needed
19 *INFO: Requested operation successfully done
user@linux:/opt/lampp/htdocs/multiotp$ sudo mc

user@linux:/opt/lampp/htdocs/multiotp$ ../../bin/php multiotp.php -display-log -debug -config backend-type=mysql

LOG 2024-08-23 00:54:17 debug Debug Debug: **New configuration value for actual_version: '5.9.7.1' (was '' before)

LOG 2024-08-23 00:54:17 debug Debug Debug: **New configuration value for backend_type: 'mysql' (was 'files' before)

LOG 2024-08-23 00:54:17 debug Debug Debug: **Writing configuration data needed

LOG 2024-08-23 00:54:17 debug Debug Debug: **Writing stat data not needed (no change)
19 *INFO: Requested operation successfully done
user@linux:/opt/lampp/htdocs/multiotp$ ../../bin/php multiotp.php -display-log -debug -config sql-server=localhost:3306

LOG 2024-08-23 00:54:26 debug Debug Debug: **New configuration value for sql_server: 'localhost:3306' (was '' before)

LOG 2024-08-23 00:54:26 debug Debug Debug: **Writing configuration data needed

LOG 2024-08-23 00:54:26 debug Debug Debug: **New stat value for encryption_hash: '99CCFC0D033729754B6BB4832FE786A1' (was '' before)

LOG 2024-08-23 00:54:26 debug Debug Debug: **Writing stat data needed
19 *INFO: Requested operation successfully done
user@linux:/opt/lampp/htdocs/multiotp$ ../../bin/php multiotp.php -display-log -debug -config sql-database=multiotp

LOG 2024-08-23 00:54:33 debug Debug Debug: **New configuration value for sql_database: 'multiotp' (was '' before)

LOG 2024-08-23 00:54:33 debug Debug Debug: **Writing configuration data needed

LOG 2024-08-23 00:54:33 debug Debug Debug: **New stat value for encryption_hash: '99CCFC0D033729754B6BB4832FE786A1' (was '' before)

LOG 2024-08-23 00:54:33 debug Debug Debug: **Writing stat data needed
19 *INFO: Requested operation successfully done
user@linux:/opt/lampp/htdocs/multiotp$ ../../bin/php multiotp.php -display-log -debug -config sql-username=multiotp

LOG 2024-08-23 00:54:45 debug Debug Debug: **New configuration value for sql_username: 'multiotp' (was '' before)

LOG 2024-08-23 00:54:45 debug Debug Debug: **Writing configuration data needed

LOG 2024-08-23 00:54:45 debug Debug Debug: **New stat value for encryption_hash: '99CCFC0D033729754B6BB4832FE786A1' (was '' before)

LOG 2024-08-23 00:54:45 debug Debug Debug: **Writing stat data needed
19 *INFO: Requested operation successfully done
user@linux:/opt/lampp/htdocs/multiotp$ ../../bin/php multiotp.php -display-log -debug -config sql-password=password

LOG 2024-08-23 00:55:02 debug Debug Debug: **New configuration value for sql_password: 'password' (was '' before)

LOG 2024-08-23 00:55:02 debug Debug Debug: **Writing configuration data needed
PHP Fatal error:  Uncaught mysqli_sql_exception: Table 'multiotp.multiotp_config' doesn't exist in /opt/lampp/htdocs/multiotp/multiotp.php:2408
Stack trace:
#0 /opt/lampp/htdocs/multiotp/multiotp.php(2408): mysqli->query('SELECT * FROM `...')
#1 /opt/lampp/htdocs/multiotp/multiotp.php(5879): Multiotp->WriteData(Array)
#2 /opt/lampp/htdocs/multiotp/multiotp.php(76176): Multiotp->WriteConfigData(Array, true)
#3 {main}
  thrown in /opt/lampp/htdocs/multiotp/multiotp.php on line 2408
user@linux:/opt/lampp/htdocs/multiotp$
multiOTP commented 2 weeks ago

Hello, We are able to reproduce the issue on a Linux server. It seems that with previous PHP versions, it makes just a notice, and now, it's an error that stop the process. A new version 5.9.8.x will be available soon. Regards

multiOTP commented 2 weeks ago

Hello, The new version 5.9.8.0 has been released. Please check it and close your ticket if everything is working as expected. ... and if you are happy with this solution, don't hesitate to support our development: https://6co.ch/donate-multiotp/ ;-) Regards,

Armaggedon commented 2 weeks ago

Many thanks, 5.9.8.0 fixes the issue, and all goes smooth :) And I'm finding out about how my Organisation could donate. Cheers!

multiOTP commented 2 weeks ago

Wonderful@Armaggedon, what a great day ;-) Thanks for the feedback, and thanks in advance for your support