View source for SSL/TLS connection to the Mysql/MariaDB

== Requirements ==

* sensor 25.9 or higher
* gui 24.4 or higher

== Mysql/MariaDB ==

Add adjusted options to the mysql config (a key/cert generation is out of scope).

 [mysqld]
 ssl-ca = /etc/mysql/ssl/ca-cert.pem
 ssl-cert = /etc/mysql/ssl/server-cert.pem
 ssl-key = /etc/mysql/ssl/server-key.pem

Usefull additional tls settings
 tls-version=TLSv1,TLSv1.1,TLSv1.2

In the latest versions (2023) only
 tls-version=TLSv1.1,TLSv1.2,TLSv1.3

Some useful console commands:

 SHOW GLOBAL VARIABLES LIKE '%ssl%';
 SHOW GLOBAL VARIABLES LIKE '%tls%';
 set global require_secure_transport = on;
 grant all on *.* to USERNAME require ssl;
 grant all on *.* to USERNAME require none;
 show grants for USERNAME;
 flush privileges;

== Sensor ==

it's simple. You will set/adjust the needed options in the cfg

 # SSL/TLS setting for the mysql connection. You can use key + cert + cacert. Or you can use the cacert only (in the Azure environment). Etc.
 # file with key
 #mysqlsslkey = /etc/ssl/client-key.pem
 # file with certificate
 #mysqlsslcert = /etc/ssl/client-cert.pem
 # file with ca certificate
 #mysqlsslcacert = /etc/ssl/ca-cert.pem
 # directory with certs
 #mysqlsslcapath = /etc/ssl/capath
 # list of allowed ciphers
 #mysqlsslciphers =

 ## for backup db
 # SSL/TLS settings
 #database_backup_from_mysqlsslkey =
 #database_backup_from_mysqlsslcert =
 #database_backup_from_mysqlsslcacert =
 #database_backup_from_mysqlsslcapath =
 #database_backup_from_mysqlsslciphers =

* then restart the sensor and the sensor should connect.

== GUI ==

1) manually add/adjust these options into config/configuration.php file:

 // define("MYSQL_KEY", "/var/www/sslsniff/client-key.pem");
 // define("MYSQL_CERT", "/var/www/sslsniff/client-cert.pem");
 // define("MYSQL_CACERT", "/var/www/sslsniff/ca-cert.pem");
 // define("MYSQL_CAPATH", "");
 // define("MYSQL_CIPHERS", "");

2) Or use GUI->Settings->System configuration->[database] menu

3) in new installation you can enter these options on the setting page

== Troubleshooting ==

There exist some combinations of php and mysql which doesn't work together.

E.g. php versions 5.6,7.0,7.1 don't work with the mysql/mariadb with enabled protocol TLSv1.2 or higher because there is a bug in the php (https://bugs.php.net/bug.php?id=74445)
The problem is that the php don't try more protocols in the connection (it tries TLSv1 or TLSv1.1 only). Fixed in the latest 7.2
But php 5.3 in the Centos6 works without problem.


Here is a part of the code for testing purposes which must work:

 <?php
 
 $db = mysqli_init();
 mysqli_options($db, MYSQLI_OPT_CONNECT_TIMEOUT, 5);
 mysqli_ssl_set($db, '/var/www/sslsniff/client-key.pem', '/var/www/sslsniff/client-cert.pem', '/var/www/sslsniff/ca-cert.pem', NULL, NULL);
 $client_flags = 0;
 if (defined('MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT')) {
         $client_flags |= MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT;
 }
 $link = mysqli_real_connect ($db, 'HOST', 'USER', 'PASS', 'DBNAME', 3306, NULL, $client_flags);
 if (!$link) {
         die ('My Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");
 } else {
         mysqli_close($db);
         die('Success... '. "\n");
 }
 
 ?>

You can see:

 # php7.1 testssl.php 
 PHP Warning:  mysqli_real_connect(): SSL operation failed with code 1. OpenSSL Error messages:
 error:1409442E:SSL routines:ssl3_read_bytes:tlsv1 alert protocol version in /var/www/git-web/testssl.php on line 14
 PHP Warning:  mysqli_real_connect(): Cannot connect to MySQL by using SSL in /var/www/git-web/testssl.php on line 14
 PHP Warning:  mysqli_real_connect(): [2002]  (trying to connect via (null)) in /var/www/git-web/testssl.php on line 14
 PHP Warning:  mysqli_real_connect(): (HY000/2002):  in /var/www/git-web/testssl.php on line 14
 My Connect error (2002): 
 
 # php7.3 testssl.php 
 Success... 


'''So our recommendation is the php7.3 compiled with the openssl library 1.1 or higher. '''

== PHP report 2023 ==

Test environment: Debian 11, Mariadb server 10.5, php packages from 'deb [trusted=yes] https://packages.sury.org/php bullseye main' + original php 7.4 from debian

*TLSv1.1 not compiled in and can't be enabled. 
*TLSv1.2 functional PHP: 7.2, 7.3, 7.4 (both), 8.0, 8.1, 8.2
*TLSv1.3 functional PHP: 7.4 (both), 8.0, 8.1, 8.2

== Sensor report 31.7.x and older (till 2023) ==
* static build from Voipmonitor site: TLSv1, TLSv1.1, TLSv1.2
* own dynamic build: depends on the system's libssl/libmysql/libmariadb libraries. TLSv1, TLSv1.1, TLSv1.2, TLSv1.3 is tested and works (with debian 11, libmariadb.so.3 library)

== Sensor report 31.8 and newer (from 2023) ==
* static build from Voipmonitor site: TLSv1, TLSv1.1, TLSv1.2, TLSv1.3
* own dynamic build: depends on the system's libssl/libmysql/libmariadb libraries. TLSv1, TLSv1.1, TLSv1.2, TLSv1.3 is tested and works (with debian 11, libmariadb.so.3 library)