Connecting PHP and MYSQL using SSL

malakiahs

I need to connect php and mysql; however, each reside on different servers, because of this I want to encrypt the data transmitted.
I created and signed my own certificates using openssl, to connect both the server and the client with the key.

I'm requiring the mysql user to use the "require issuer" instead of require ssl, by using the following command:

grant all privileges on table.* to 'user'@'iphere' identified by 'mysupersecretpass' require issuer 'details here';

To do this on php i'm using this function:
mysqli_ssl_set ( mysqli $link , string $key , string $cert , string $ca , string $capath , string $cipher )

I have set the following specifications

mysqli_ssl_set ( mysqli $link , "path to client-key", "path to client-cert" , "path to ca" , NULL , NULL)

This works, the connection is encrypted. The problem is that I'm not sure if it is properly encrypted.
I'm thinking this because it doesn't appear to take the "path to ca", I can put anything in that field such as a random string, NULL, any number, and the database server (MYSQL) still takes it. Whether I put the proper path to ca or not the connection still shows to be encrypted.

Can anyone help or can give an advise why this is happening? Any help is greatly appreciated

bradgrafelman

Can you show us more of the actual code you're using to make the connection to MySQL?

Also, do you have error_reporting set to E_ALL (or better) and display_errors set to On?

malakiahs

bradgrafelman;10998558 wrote:
Can you show us more of the actual code you're using to make the connection to MySQL?

Also, do you have error_reporting set to E_ALL (or better) and display_errors set to On?

Thank you for your time and reply. The code I'm is:


$dbc = mysqli_init(); 

mysqli_ssl_set($dbc,"C:/mysslcerts/my-client-key.pem", "C:/mysslcerts/my-client-cert.pem", "C:/mysslcerts/my-ca.pem", NULL, NULL) ;

mysqli_real_connect($dbc, '#######', 'my_test_user', '**********' , 'test_db');

// Instead of ******* I have the actual password in my script. 
// ####### is the ip that i'm connecting to.


if (!$dbc) {
	trigger_error('Could not connect to MySQL: ' . mysqli_connect_error() ); 	
} else {//Otherwire, set the encoding:
	mysqli_set_charset($dbc, 'utf8'); 
}
echo mysqli_stat($dbc); 
$result = mysqli_query($dbc, 'SHOW STATUS');
while ($row = mysqli_fetch_assoc($result)) {
 echo $row['Variable_name'] . ' = ' . $row['Value'] . "<br />\n";
}

And yes I do have error_reporting on, the problem is that I get no errors because the encryption works. If I change "C:/mysslcerts/my-ca.pem" to this "12345", or NULL, or "1", the encrypted connection still seems to work. I'm I missing something?

I'm worried that I might not be doing this the right way and not encrypting this properly.

bradgrafelman

What are you using/doing to determine whether or not the connection was actually made over an SSL connection?

One thing I notice is that you're not setting the MYSQLI_CLIENT_SSL flag when calling [man]mysqli_real_connect/man; see the manual page for this function to learn more about the 'flags' you can pass to it.

malakiahs

bradgrafelman;10998565 wrote:
What are you using/doing to determine whether or not the connection was actually made over an SSL connection?

One thing I notice is that you're not setting the MYSQLI_CLIENT_SSL flag when calling [man]mysqli_real_connect/man; see the manual page for this function to learn more about the 'flags' you can pass to it.

I have a script at the bottom that shows me the status of the connection. When I don't use an SSL connection it shows (Please note that I removed the "require issuer" option on the MYSQL user, otherwise the connection is denied):

< -I'm truncating the output since the rest is a non-ssl list of variables->
...
Ssl_cipher =
Ssl_cipher_list =
...
<-end of truncated output->

Now, when I use the SSL connection the output changes to:

< -I'm truncating the output again->
...
Ssl_cipher = DHE-RSA-AES256-SHA
Ssl_cipher_list = DHE-RSA-AES256-RMD😃HE-RSA-AES128-RMD😃HE-RSA-DES-CBC3-RMD:RC4-SHA:RC4-MD5😃ES-CBC3-SHA😃ES-CBC-SHA:EDH-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA
...
<-end of truncated output->

I read the man page for mysqli_ssl_set and it automatically sets the MYSQLI_CLIENT_SSL flag on the mysqli_real_connection.

What I would like to know is why instead of putting"C:/mysslcerts/my-ca.pem" on the actual script I provided earlier, I can literally change it to "12345" or NULL and the encrypted connection still holds.

bradgrafelman

My only guess is that when using the 'REQUIRE ISSUER' statement, the MySQL server is disabling the 'SSL_VERIFY_PEER' option in the SSL library, meaning that self-signed certs do not need to supply a valid CA chain for verification. This seems bizarrely incorrect, though, so I'd be surprised if that guess is correct.

Perhaps an SSL expert can jump in and save me from further speculation, but... is it possible that the MySQL already has the same CA cert loaded (meaning that even though you aren't supplying it, MySQL can still verify your self-signed cert since it already has the necessary CA cert)?

malakiahs

bradgrafelman;10998581 wrote:
My only guess is that when using the 'REQUIRE ISSUER' statement, the MySQL server is disabling the 'SSL_VERIFY_PEER' option in the SSL library, meaning that self-signed certs do not need to supply a valid CA chain for verification. This seems bizarrely incorrect, though, so I'd be surprised if that guess is correct.

Perhaps an SSL expert can jump in and save me from further speculation, but... is it possible that the MySQL already has the same CA cert loaded (meaning that even though you aren't supplying it, MySQL can still verify your self-signed cert since it already has the necessary CA cert)?

Well, on the mysql my.ini file, under [mysqld] I included the path to all three ssl-ca, ssl-cert, and ssl-key files (of the server) and then restarted the mysql service for it read the files. I want to believe that this is the reason why the mysql SSL connection is not verifying the CA on the client site. Could this be?

paddysmith

I have successfully setup an SSL enabled install of MySQL on one server on one network and can connect to it using SSL with the Linux command line mysql client on a different server on a different network, however every time I try to connect (using PHP 5.3.3) I keep getting:

<?php

error_reporting(E_ALL);
ini_set("display_errors", "1");

$obj = mysqli_init();

mysqli_options($obj, MYSQLI_OPT_CONNECT_TIMEOUT, 5);

mysqli_ssl_set( $obj,
'/mysql-ssl-certs/server-key.pem',
'/mysql-ssl-certs/server-cert.pem',
'/mysql-ssl-certs/ca-cert.pem',
NULL,
NULL);

$link = mysqli_real_connect($obj, 'localhost', 'ssluser', 'some_password', 'test');

if (!$link)
{
die('<br /><br />Connect Error (' . mysqli_connect_errno() . ') '.mysqli_connect_error());
}

echo 'Success... ' . mysqli_get_host_info($obj) . "\n";

$obj->close();