AdoDB, PHP, MySQL, SSL

(I'm practicing for an "Unreadable blog post title" contest.)

Here are a few hints on how to use SSL certificates when connecting to MySQL from a PHP program using the AdoDB database abstraction layer. (You may want to see my previous post on setting up SSL certificates for MySQL connections.)

The trick is to use a DSN in the NewADOConnection() call (rather than authenticating with a Connect() call) and to use the mysqli driver (looks like the mysql driver won't work for this). The DSN syntax allows you to supply client flags, and there's a mysqli flag for using SSL certificates.

After creating a CA certificate (we'll say it's at /path/to/ca-cert.pem), make sure that the following item is in the [client] stanza of /etc/my.cnf or the connecting user's ~/.my.cnf on the client host:

ssl-ca=/path/to/ca-cert.pem

Then try the following PHP program:

// these are part of the AdoDB library
require '/path/to/adodb-exceptions.inc.php';
require '/path/to/adodb.inc.php';

/*
* I got the '2048' from running
* printf( "%d\n", MYSQLI_CLIENT_SSL )
* in a PHP program (w/ the mysqli extention installed)
*/
$dsn = 'mysqli://ssluser:sslpass@dbhost/test?clientflags=2048';

$dbh = NewADOConnection($dsn);

$sql = "show status like 'ssl_cipher'";
$res =& $dbh->Execute($sql);
print_r( $res->fields );
$res->Close();
$dbh->Close();

This should generate output similar to like this:

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