Could someone please please please help me get remote MySQLi over SSL on PHP working?

[wh33t]

I have confirmed that I have SSL remote connections working (tested from the command line mysql-client remotely).

I can’t for the life of me figure out how to make this work from PHP land though. I am novice when it comes to encryption.

This is my connection script:

# Init SQL Link
$con = mysqli_init();
if(!$con) 
{
  die('Init Failed');
}

mysqli_ssl_set($con,null,null,null,null,null);
mysqli_options($con,MYSQLI_OPT_SSL_VERIFY_SERVER_CERT,false);

if(!mysqli_real_connect(
  $con,
  'myserverip',
  'myusername',
  'mypassword',
  'mydatabase',
  3306,null,MYSQLI_CLIENT_SSL
))
{
  die('No SQL');
}

The error reported to me is:

Warning: mysqli_real_connect(): (HY000/1044): Access denied for user 'myusername'@'myservername' to database 'mydatabase' in /var/www/html/public_html/test.php on line 39

I have the following SSL files (which I believe were generated via command line utility mysql_ssl_rsa_setup)

ca-key.pem
ca.pem
client-key.pem
client-cert.pem
private_key.pem
server-cert.pem
public_key.pem
server-key.pem

Would anyone happen to know which of these and in which order I use them in mysql_ssl_set()?

Please and thank you so much. No amount of Googling has lead me to the answer and I suspect it’s due to me not understanding how the Encryption works OR not understanding how my webhost has configured their mysql-client.

[rpkamp]

From looking at the docs I’m guessing it should be

mysqli_ssl_set(
    $con,
    $dir . '/client-key.pem', // client key
    $dir . '/client-cert.pem', // client certificate,
    $dir . '/ca.pem', // ca_certificate,
    null, // ca_path - not needed, ca_certificate already supplied
    null, // cypher algos - no preference, let the server figure it out
);

Where $dir is the absolute path to the directory all these files are in.

Also, you set MYSQLI_OPT_SSL_VERIFY_SERVER_CERT to true instead of false. Otherwise PHP will connect even if the server certificate isn’t trusted (based on ca.pem).

[wh33t]

Thanks so much. I have that option to true now. And have put those values into mysqli_ssl_set() and now I get this error:

Warning : mysqli_real_connect(): Peer certificate CN=MySQL_Server_8.0.23_Auto_Generated_Server_Certificate' did not match expected CN=myserverip’ in ** /var/www/html/public_html/test.php>** on line 24

What does this mean?

[rpkamp]

How was SSL installed on the server, did you use the other files in the directory you showed earlier?