PHP5 + MySQLi + SSL (3rd party hosted database)

software development
#1

My company currently has a group of MySQL master/slave servers to support our installation of RADIUS, and a number of other services and web applications.
We would like to migrate one of our main websites to our Dreamhost account, but the website needs to access some of the information in our RADIUS database.
Since migrating RADIUS or the RADIUS database is not currently an option, I would like to use MySQLi to create a secure connection to our MySQL servers.

I am using a new MySQL user that only requires SSL (not X509).
The following test code seems to work fine when running on our production web server (CentOS 4 server, Apache 2.0.52, PHP 5.1.6, MySQLi 5.0.27):

<?php $DB_SETTINGS['HOST'] = 'HOST'; $DB_SETTINGS['DB_NAME'] = 'DB'; $DB_SETTINGS['USERNAME'] = 'USERNAME'; $DB_SETTINGS['PASSWORD'] = 'PASSWORD'; echo "Trying to connect using:
"; echo $DB_SETTINGS['HOST']."
"; echo $DB_SETTINGS['USERNAME']."
"; echo $DB_SETTINGS['DB_NAME']."
"; echo $DB_SETTINGS['PASSWORD']."
"; $objDB = mysqli_init(); echo "MySQLi Initialized
"; $objDB->ssl_set(NULL,NULL,NULL,NULL,NULL); echo "SSL Set
"; $objDB->real_connect($DB_SETTINGS['HOST'],$DB_SETTINGS['USERNAME'],$DB_SETTINGS['PASSWORD'],$DB_SETTINGS['DB_NAME'],3306,'TCP',MYSQLI_CLIENT_SSL); echo "Connected

"; echo "Testing...
"; $query="SELECT id FROM table WHERE username='TESTUSER'"; echo "Query: $query
"; $result=$objDB->query($query); while($row = $result->fetch_assoc()) { echo "Result: ".$row['id']."
"; } ?>

But, after coping the MySQL user information and placing the script on the Dreamhost server, the test page does not load (it eventually times out with a 500 error).
The packets appear to be hitting the MySQL server(s), since I see the packet counters go up for the rules that allow MySQL connections from the Dreamhost servers.

Has anyone used MySQLi + SSL on the Dreamhosts systems? Or have any ideas on what to try next (I have already opened the firewalls wide, and I tried to test with the command line MySQL client, but the command line client available via shell does not have SSL support built into it)?

Thank you,
Devon Mackay

#2

Heavy.

Anyway, if I understand this correctly (…which is unlikely), you’re trying to connect to a mySQL server that isn’t hosted at DH; it’s difficult to see whether that’s the case because actual hostname being connected to was (wisely) not supplied.

It is possible, but the remote (non-DH) mySQL server has to be prepared to receive the connections; this may or may not involve:

  1. opening firewall ports on the network that’s hosting the mySQL servers/databases.
  2. updating user permissions within the aforementioned mySQL servers/databases to allow that user to connect via the DH hostname/IP address.

It’s a tricky thing, but it does work and DH will almost certainly not give ya support. The only real caveat in that case is to bear in mind that you’re using a shared hosting service which may at any time (however unlikely) change the IP your domain is hosted on.

#3

You are correct. I am connecting my company’s MySQL servers on a different network.

As far as I can tell, the issue does appear to be SSL specific…
A test page similar to the one above, but connecting with a username that allows non-SSL connections appears to work just fine. It can connect to the database, run a query, and return a result.

I believe I have opened all the ports required (and at one point, I just allows all traffic from that Dreamhost server period), and I created the new MySQL user by modifying the information displayed by “SHOW GRANTS” for the user that works fine from our production web server (also, I would think I would get some form of “Access Denied” message should it be an authorization problem, but since I do not know what the issue is, I cannot say for sure).

This the first time I have needed to use SSL to make a MySQL connection, since we used to host everything ourselves… but the fact that the same code works fine from one of my own linux servers has left me a bit unsure as to my next troubleshooting step.

#4

Mea culpa; the SSL part would be a whole new animal to me as well. If there’s an actual certificate issuer involved, perhaps they could shed some light on the situation - all I know is that regard is that I’m under the impression that DH uses OpenSSL (…I think it’s loaded as some sort of Apache module but that level of tech is beyond me) which I understand does have some interoperability issues.