xampp

At times I've wanted to try doing some development work on my eeepc, but the default distribution doesn't come with a LAMP stack (and so far I've been too chicken to try installing something else).

Today I tried installing xampp, and that seems to work pretty well. So far my only complaint is that it doesn't seem to come with any version control tools (like svn), and I don't see an easy way to add/compile them (the eeepc doesn't have gcc).

LAMP pconnect

I had a bad experience a few years ago using pconnect() in a PHP program running in/on/under Apache on Linux to talk to a MySQL database. My memory is somewhat vague, but we had to restart the MySQL service (and switch to non-persistent connections). Ever since then, I've had an irrational fear of pconnect() and have never used it.

Looks like I'm not the only one who doesn't trust pconnect().

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
)

SSL in MySQL connections

Last week I wanted to figure out how to use SSL certificates in MySQL connections. This is well-documented on the MySQL Web site, but here are a few wrinkles I experienced while figuring out how to get this working (this was with MySQL5 on CentOS5 and RHEL5).

After creating the certificate authority (CA) certificate/keyfile pair, you can specify them in the mysqld section of /etc/my.cnf:

ssl-ca=/etc/pki/CA/ca-cert.pem
ssl-cert=/etc/pki/tls/certs/mysql-server-cert.pem
ssl-key=/etc/pki/tls/private/mysql-server-key.pem

When making certificates for a client connecting locally (e.g., ssluser@localhost), it's important to supply localhost as the "common name" when prompted by openssl. (Yes, it's probably pretty silly to use SSL for a connection over the loopback interface, but you might be in this situation if you were testing.)

If you want to specify the CA (whose signature must appear in client certificates) when setting up a MySQL user (as you might when using the require x509 syntax), the fields should be separated by backslashes ('/'): grant usage on *.* to ssluser@localhost require issuer '/C=GB/ST=Berkshire/L=Newbury/O=My Company Ltd/CN=www.example.com/emailAddress=webmaster@example.com';
The following command will more-or-less correctly format the issuer for the grant statement:

openssl x509 -text -in /path/to/ca-cert.pem | grep Issuer \
| cut -d':' -f2 | sed -e 's/, /\//g'

Using issuer and subject items imply x509, and it's an error to try using x509 and issuer.

Depending on the require clause in the grant statement, you can use one or more of the following to connect to the SSL-enabled server:

1. mysql -u ssluser -p


2. mysql -u ssluser --ssl-ca=ca-cert.pem -p


3. mysql -u ssluser --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -p

If you use require none or omit the require clause, you can use any of the three connection commands. If you use require ssl, you can use #2 or #3. And if you use require x509, you have to use #3 (note that #3 includes the --ssl-ca option). After connecting, type status (or just \s) and make sure that the SSL item says something encryptiony (mine says Cipher in use is DHE-RSA-AES256-SHA).

Unless client certificates are really necessary (extra client-level authentication), it's probably adequate just to use require ssl and to have the client provide the CA certificate (this appears to provide as high a level of encryption as the client certificate does). But note that you still need to generate the server certificate and key, even if you're not using client certificates.

MySQL query optimization from Jay Pipes

Jay Pipes has posted slides from a recent presentation in which he discussed query optimization in MySQL. Pretty good pointers, worth a look.