PHP & MariaDB: Establish a secure mySQL-Connection

Question

Folks,

The Internet is full of hits on this topic, but none really helps or the information is very outdated.

I want to establish a TLS-secured connection to a mySQL database server using PHP 7.4. I want to avoid client certificates - if possible - because they are "over engineered" for my use case.

Unfortunately, I only manage to establish an unsecured connection. As soon I enforce only secure connections (require-secure-transport = on), the external Webserver is not able to connect to the DB.

With this configuration, the authentication will fail. I thought, filling $mysqli->ssl_set with NULL will initiate a TLS-Connection.

Any idea how to solve this?

mySQL-Server Configuration:

Creating Certs

mkdir /etc/mysql/certs
sudo openssl genrsa -out /etc/mysql/certs/ca-key.pem 4096
sudo openssl req -new -x509 -nodes -days 1825 -key /etc/mysql/certs/ca-key.pem -out 
/etc/mysql/certs/ca-cert.pem
chown -R mysql:mysql /etc/mysql/certs/

DB-Setup /etc/mysql/mariadb.conf.d/50-server.cnf

[mysql]
general_log_file           = /var/log/mysql/mysql.log
general_log                = 1
log_error                  = /var/log/mysql/error.log
ssl_cert                   = /etc/mysql/certs/ca-cert.pem
ssl_key                    = /etc/mysql/certs/ca-key.pem
tls_version                = TLSv1.2,TLSv1.3
require-secure-transport   = on
bind-address               = 0.0.0.0 # will allow Connections from everywhere

PHP

/var/www/html/example.php

$servername    = "foo.bar";
$username      = "user";
$password      = "secret";
$dbname        = "myDb";

$mysqli = mysqli_init();
$mysqli->ssl_set(NULL, NULL, NULL, NULL, NULL);
$mysqli->real_connect($servername, $username, $password, $dbname);

This question has been answered pretty well already: https://stackoverflow.com/questions/3657765/php-to-mysql-ssl-connections Accepted answer includes debugging steps to take with the mysql cli — gview, Nov 23 '21 at 17:34
Yes, I have found this article, but as I said, I don't like to use the Cert-Files. — Gill-Bates, Nov 23 '21 at 17:37
I don't understand your reply. You need to set the server cert in the client ssl_set. With that said, since your server cert is self signed, you will need to add the flag MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT. — gview, Nov 23 '21 at 17:54

score 2 · Answer 1 · answered Nov 24 '21 at 11:44

Finally, it tooks me 2 days to find the correct configuration. Hopefully it wil help others:

I am starting from scratch with Server-Side Setup (Debian Bullseye mariaDB)

1. Creating Key and Certificate on Server-side

mkdir /etc/mysql/certs
sudo openssl genrsa -out /etc/mysql/certs/ca-key.pem 4096
sudo openssl req -new -x509 -nodes -days 1825 -key /etc/mysql/certs/ca-key.pem -out /etc/mysql/certs/ca-clientcert.pem
chown -R mysql:mysql /etc/mysql/certs/

2. Change Config on Server

nano etc/mysql/mariadb.conf.d/50-server.cnf  

[client]
ssl-cipher  = DHE-RSA-AES256-SHA

[mysql]
log_error                  = /var/log/mysql/error.log
ssl_cert                   = /etc/mysql/certs/ca-clientcert.pem
ssl_key                    = /etc/mysql/certs/ca-key.pem
tls_version                = TLSv1.2,TLSv1.3
require-secure-transport   = on
bind-address               = 0.0.0.0 # will allow Connections from everywhere

Restart the server: systemctl restart mariadb.service

3. Now on the Client-Side, following PHP will be used:

Don't forget to transfert the file ca-clientcert.pem to your Webserver!

<?php

// SETUP ENVIRONMENT
session_start();
header('Content-Type: text/html; charset=utf-8');
error_reporting(E_ALL);
ini_set("display_errors", "1");

// LOADING SENSITIVE DATA FROM NON-PUBLIC LOCATION
$certfile = "/home/files/ca-clientcert.pem";
$pwfile  = "/home/files/mysecret.txt";
$fh = fopen($pwfile, 'r')
or die("Can't initialize Database connection!");;
$loadedpw = fgets($fh);

// INITILIZE DB CONNECTION
$host         = "foo.bar";
$port         = 3306;
$username     = "user";
$password     = $loadedpw;
$dbname       = "databasename";

$con = mysqli_init();
mysqli_ssl_set($con, NULL, NULL, $certfile, NULL, NULL);
mysqli_options($con, MYSQLI_OPT_CONNECT_TIMEOUT, 10);
mysqli_options($con, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, false);
mysqli_real_connect($con, $host, $username, $password, $dbname, $port);
if (!$con)
{
    die('<br /><br />Connect Error (' . mysqli_connect_errno() . ') '.mysqli_connect_error());
}

?>

PHP & MariaDB: Establish a secure mySQL-Connection

mySQL-Server Configuration:

PHP

1 Answers1