Would anyone mind sharing some light on how to get PHP-Mysqli to work remotely using SSL?

[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:

Quote

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.

[gizmola]

Is the server "remote"?  When you did your "command line mysql" verification, did you do that on the server or from your workstation? 

A "decode" of the files generated by the mysql_ssl_rsa_setup:

• ca.pem  - the "certificate authority" public key for the self-signed certs that were generated.  (You will want this file on your client)
• client-key.pem - the "client" private key they generated.  (You will want this file on your client)
• client-cert.pem - the "client" certificate that was generated which goes along with the client-key.

These are the files you would need available to your client.  Ideally you want to make copies of those files in a directory (not under the web root) of the server running your php application, with read only permissions, but still readable by the user that the php process is running as.  You need to pass an actual or relative path to the files, when you make mysqli_ssl_set initialization.

 

mysqli_ssl_set($con,"/path/to/client-key.pem","/path/to/client-cert.pem","/path/to/ca.pem",NULL, NULL); 

 

 

[requinix]

Is authentication supposed by handled by a client certificate or by a standard username and password? That error message suggests the connection is fine and the credentials are wrong - after all, the client must be able to connect to the server if it's able to report to you information like "the credentials are wrong".

[wh33t]

23 minutes ago, gizmola said:

Is the server "remote"?  When you did your "command line mysql" verification, did you do that on the server or from your workstation? 

A "decode" of the files generated by the mysql_ssl_rsa_setup:

• ca.pem  - the "certificate authority" public key for the self-signed certs that were generated.  (You will want this file on your client)
• client-key.pem - the "client" private key they generated.  (You will want this file on your client)
• client-cert.pem - the "client" certificate that was generated which goes along with the client-key.

These are the files you would need available to your client.  Ideally you want to make copies of those files in a directory (not under the web root) of the server running your php application, with read only permissions, but still readable by the user that the php process is running as.  You need to pass an actual or relative path to the files, when you make mysqli_ssl_set initialization.

 

mysqli_ssl_set($con,"/path/to/client-key.pem","/path/to/client-cert.pem","/path/to/ca.pem",NULL, NULL); 

 

 

OOOH I'm getting closer. THANK YOU SO MUCH.

Here are the error messages now. Maybe I should regenerate it?
 

Quote

 

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/ on line 24

Warning: mysqli_real_connect(): Cannot connect to MySQL by using SSL in /var/www/html/public_html/ on line 24

Warning: mysqli_real_connect(): [2002] (trying to connect via (null)) in /var/www/html/public_html/ on line 24

Warning: mysqli_real_connect(): (HY000/2002): in /var/www/html/public_html/ on line 24

 

 

[wh33t]

13 minutes ago, requinix said:

Is authentication supposed by handled by a client certificate or by a standard username and password? That error message suggests the connection is fine and the credentials are wrong - after all, the client must be able to connect to the server if it's able to report to you information like "the credentials are wrong".

I would love for it to only require a user/pass, when I connect from the command line utility all I do is give user/pass and it connects just fine. It's just PHP that is making this weird.

[wh33t]

44 minutes ago, gizmola said:

Is the server "remote"?  When you did your "command line mysql" verification, did you do that on the server or from your workstation? 

To answer your question, I connected over the command line mysql-client from an entirely different machine using mysql --host=myserverip, it worked. I even tested it by adding the option "--ssl-mode=DISABLED" and I was refused when forcing the client to connect without SSL. So I'm confident it's working as expected, just not in PHP.

[gizmola]

20 hours ago, wh33t said:

OOOH I'm getting closer. THANK YOU SO MUCH.

Here are the error messages now. Maybe I should regenerate it?
 

 

Yes, the CN name needs to match the hostname.  You will have to add an entry on the PHP server that resolves to the IP.  So make the CN something like mysqlserver1.dummydomain.internal, where dummydomain is whatever you want it to be.

On the server running php make an /etc/hosts entry for that domain that resolves to the mysqlserver IP.  Then when you connect, use the mysqlserver1.dummydomain.internal hostname. 

If you are using a version of php >= 7.1,  an alternative solution that might work is to pass MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT in the flags parameter for mysqli_real_connect.  Theoretically that gets you around the problem, and may be what is happening with the mysql CLI on whatever other machine you are testing with.