Create Certificates and Use SSL with MySQL and the ADOdb Database Abstraction Library for PHP

This post is mainly for my own records and is the combination of 3 tutorials from KrWiki at waterlovinghead, Carl’s Whine Rack and Mad Irish. All 3 are almost 100% what I was looking for.

I needed to set up a secure database connection between our live web server and database server, both running Ubuntu Server. The 2 machines are completely separate and have their own IP address. The procedure for setting up an SSL connection between the two is similar to setting up a standard SSL certificate in Apache. First you create the certificates and sign them, then you configure the client (web server 192.168.0.2) and server (database server 192.168.0.1) to use them, finally you create a special MySQL user that uses SSL and connect using ADOdb. You don’t need to use ADOdb but it makes things a lot easier (see my previous posts).

It’s very important that your MySQL server has SSL enabled (most do by default). You can test this by connecting to your server and typing the SQL command:

SHOW VARIABLES LIKE '%ssl%'

Which should show “have_ssl” as either “DISABLED” or “YES”. If it says “NO” you will need to enable SSL (which might mean recompiling MySQL).

Creating the certificates

You need to create certificates for both the server (our database server) and the client (our web server). There are always a few ways of doing this described in various tutorials but this way is the easiest.

First create a directory somewhere that you will store your certificates in, e.g. “/var/ssl_certificates/” then go into the directory and type the following:

openssl genrsa 2048 > ca-key.pem

Which will generate a 2048 bit RSA private key for the Certificate Authority (CA). Now you need to create a Certificate Authority (CA) certificate, which will be used later to sign our server and client certificates. It is possible to use an external CA but in this case we are self signing, which makes things easier.

openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

You will be asked a few details: country name, state or province name, locality name, organisation name, organisational unit name, common name and email address. Set the common name to the name of your server e.g. 192.168.0.1. The ca-cert.pem file created is our CA certificate.

Now you can create the server certificate request:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

This will ask for the same details as the CA certificate but will also ask you to put in a challenge password and optional company name. In our case, we are not going to be using the challenge password as we would have to enter it every time the SQL server started up.

Now sign the request using our CA certificate and key and generate the server certificate:

openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Now we need to create a client certificate request. We need to create one for each client, but in this case we only need to create one for the web server:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem

You will be asked the same questions as for the server certificate request. The difference this time is that you need to put the client hostname in e.g. 192.168.0.2. Now sign this request and generate the client certificate:

openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

So now we should have several files in our directory. These are:

  • ca-key.pem – The certificate authority (CA) key
  • ca-cert.pem – The certificate authority (CA) certificate
  • server-key.pem – The server key
  • server-req.pem – The server certificate request
  • server-cert.pem – The server certificate
  • client-key.pem – The client key
  • client-req.pem – The client certificate request
  • client-cert.pem – The client certificate

So now we have the required certificates for both the server and the client we can set up both.

Setting up the MySQL server and client to allow SSL

All you need to do to set up the MySQL server to accept SSL requests is edit the MySQL my.cnf configuration file (usually in either /etc/my.cnf or /etc/mysql/my.cnf). Add the following lines in the [mysqld] section to point MySQL to the required files (in our case in /var/ssl_certificates/):

[mysqld]
ssl-ca=/var/ssl_certificates/ca-cert.pem
ssl-cert=/var/ssl_certificates/server-cert.pem
ssl-key=/var/ssl_certificates/server-key.pem

Now restart the MySQL service to check it works by typing:

/etc/rc.d/init.d/mysqld restart

If everything is working fine it should just start up like normal.

Now to set up the client (our web server) we need to copy the 3 required client files ca-cert.pem, client-cert.pem & client-key.pem to a directory on the client (e.g. /var/ssl_certificates/ again). Now you edit the my.cnf on the client (usually in either /etc/my.cnf or /etc/mysql/my.cnf) and add the following lines in the [client] section, which you may need to add yourself:

[client]
ssl-ca=/var/ssl_certificates/ca-cert.pem
ssl-cert=/var/ssl_certificates/client-cert.pem
ssl-key=/var/ssl_certificates/client-key.pem

So now the client and server are set up to use the SSL certificates you created for all SSL connections between the two. Now we set up a user for the database who is forced to use SSL.

Setting up a user to use SSL only

It’s easiest to set up the user on the command line using the “mysql” command on the database server. So log in as your root user (or equivalent) on the database server and create a test database “test_ssl”:

create database test_ssl;

Now create a test user “test_user” with password “testpass” with the correct client hostname “192.168.0.2″ and force them to use SSL when logging in:

grant all privileges on test_ssl.* to 'test_user'@'192.168.0.2' identified by 'testpass' require ssl;
flush privileges;

Now we can create a test table “test” and insert some data so we can query it to test everything is working from PHP:

create table test (test_id int auto_increment primary key, test_data varchar(255));
insert into test set test_data='foo';

Now we go back to ADOdb and PHP to test this all works.

Connect to the database over SSL using ADOdb

Simply create a .php file on your web server with the following code:

<?php
include('adodb5/adodb.inc.php'); // database libraries (must point to where your ADOdb libraries are kept)

$dsn = 'mysqli://test_user:testpass@192.168.0.1/test_ssl?clientflags=2048';
$dbh = NewADOConnection($dsn);

$arr = $dbh->GetAll("SELECT * from test");
print_r($arr);
?>

This should connect to our database using the test_ssl user and using SSL and print out the following on your web browser:

Array ( [0] => Array ( [0] => 1 [test_id] => 1 [1] => foo [test_data] => foo ) )

Now the great thing about this is that by using ADOdb we don’t need to worry about any kind of special connection string. The only thing that is different between an SSL connection and a standard connection is that SSL ues the “mysqli” command in the data source name rather than “mysql”, the rest of the code stays the same.

NOTE: It’s worth double checking that everything is working by executing "show status like 'ssl_cipher'" as a SQL query in the PHP above:

<?php
include('adodb5/adodb.inc.php'); // database libraries (must point to where your ADOdb libraries are kept)

$dsn = 'mysqli://test_user:testpass@192.168.0.1/test_ssl?clientflags=2048';
$dbh = NewADOConnection($dsn);

$arr = $dbh->GetAll("show status like 'ssl_cipher'");
print_r($arr->fields);
?>

which gives:

Array ( [0] => Ssl_cipher [Variable_name] => Ssl_cipher [1] => DHE-RSA-AES256-SHA [Value] => DHE-RSA-AES256-SHA )

Using ADODB to easily connect to MySQL and Microsoft SQL Server from PHP

In previous posts I’ve talked about connecting to Microsoft SQL Server from PHP using ODBC and using mssql_connect to connect directly. The most important thing I’ve realised is not to re-invent the adodb wheel, which has shown to be a really great cross-platform way of connecting to databases.

The best thing about adodb is that just by changing the connection string I can talk to MySQL and Microsoft SQL Server without changing any of my code. I’ve used this for performance and migration testing and it’s been perfect, with none of the strange hangs I noticed with a standard ODBC connection. Currently I am using Ubuntu Server 10.10 to host all the PHP, which has overall been a pretty good experience, apart from these strange hangs.

If you are in Ubuntu Server you may need to install some ODBC packages in order to get adodb to work. Some of these packages are not totally necessary but I would recommend them for testing ODBC connections etc:

sudo apt-get install php5-odbc unixodbc unixodbc-dev freetds-dev sqsh tdsodbc

Now you need to create a SQL user in either your MySQL or SQL Server database. Make sure you map the SQL Server user as a database owner on the DB you want to connect to and give them the same default schema. The reason for this is I couldn’t get the connection in adodb to work with the actual DB specified (unlike in MySQL).

Download adodb5 and place the directory in the root of your webserver and add the following to the top of your PHP file:

include(‘adodb5/adodb.inc.php’);

Now open up your PHP file and insert the following for SQL Server with IP “the.ser.ver.ip”:

$dbcstring = “mssql://username:password@the.ser.ver.ip”;

Or the following for MySQL:

$dbcstring = “mysql://username:password@the.ser.ver.ip/databasename?persist”;

Create a connection to the database:

$DB = NewADOConnection($dbcstring);

Now you can use the built in commands in adodb to easily pull data from the database and display it on screen. It doesn’t matter which database you are connecting to, the commands are now the same. This is the main advantage of adodb in my opinion, along with the ease of use:

$arr = $DB->GetAll(“SELECT column FROM table”);
for($i=0;$i<count($arr);$i++)
{
echo $arr[$i][0]  . “<br/>”;
}

It’s worth reading the guide on adodb for the commands you can use, such as $DB->GetRow(), $DB->GetOne() and $DB->Execute(). The great thing is that adodb makes everything so simple you can treat your data as arrays or you can go further and make use of some of the more advanced functionality. Either way, by using adodb you massively cut down on code rewrites when you migrate to a new database.

Connecting to Microsoft SQL Server using ODBC from Ubuntu Server

Previously I showed how to connect to Microsoft SQL Server in PHP using mssql_connect(). This works but is not as neat as using ODBC.

Unfortunately when writing this guide I got stuck at the last hurdle when trying to use ODBC and adodb in PHP. I could connect to the database using:

include(‘adodb5/adodb.inc.php’);
$connection=& ADONewConnection(‘odbc_mssql’);
$connection->Connect(‘datasourcename’,’username’,’password’);

Or using odbc_connect:

$connection= odbc_connect(‘datasourcename’, ‘username’, ‘password’);

But selecting data using “$connection->GetAll()” or “odbc_exec()” just resulted in a page that never loaded. Fortunately the ODBC part worked, there was just something I haven’t figured out yet with ODBC in PHP.

This guide will show you how to set up ODBC in Ubuntu using FreeTDS to talk to Microsoft SQL Server. I am trying to work out why the server connects fine but won’t execute queries in PHP. When I find out why I will update this post. To create this guide I went off a post on Ubuntu forums with some additions. There are a lot of steps, but at least some of the programs you use along the way allow you to check things are working as expected before you come to test in PHP.

First you need to install php5-odbc in Ubuntu:

sudo apt-get install php5-odbc

Now you need to add freetds to create a connection and talk to your server, plus a few other handy programs:

sudo apt-get install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc

Edit the example server details or create a new one in /etc/freetds/freetds.conf to point to your server (in my case I called it “sqlserver”):

[sqlserver]
host = ip.add.ress
port = 1433
tds version = 7.0

You can check the connection is set up using sqsh:

sqsh -S sqlserver -U username -P password

Alternatively, you can connect directly to the ip.add.ress:

sqsh -S ip.add.ress -U username -P password

Now in sqsh you can select from a table in your SQL Server database and display it on screen by entering your query and “go” on the next line:

1> select * from databasename.dbo.table
2> go

Hopefully you should see your data on screen. The next step is to configure ODBC so check where the config files are located using:

odbcinst -j

Which should say “DRIVERS: /etc/odbcinst.ini” and “SYSTEM DATA SOURCES: /etc/odbc.ini” which are the files we will be editing.

Now we set up the FreeTDS driver so it can be used by ODBC so open “/etc/odbcinst.ini” (was a blank file in my case) and add:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

Open “/etc/odbc.ini” and create the system data source “datasourcename” that we will use everywhere (including in PHP):

[datasourcename]
Driver = FreeTDS
Description  = ODBC connection via FreeTDS
Trace = No
Servername = sqlserver
Database = databasename

It’s important to note that “Servername” here refers to the server details in /etc/freetds/freetds.conf NOT to an IP address.

You can test the connection works using isql:

isql -v datasourcename username password

Now you can use this data source “datasourcename” in PHP, which is the goal! To test the connection works I tried odbc_connect():

$connection = odbc_connect(‘datasourcename’, ‘username’, ‘password’);
if (!$connection) die(“Connection failed”);
else echo “works”;

Which does work. Now I just need to work out why queries such as the following cause PHP to hang:

$query = odbc_exec($connection, ‘SELECT column FROM table’) or die (odbc_errormsg());