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

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.

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).

1 Like

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?

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

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.