The Advisory Boar

By Abhijit Menon-Sen <>

Spreadsheet::XLSX and humongous spreadsheets

I've been using Spreadsheet::Read backed by Spreadsheet::ParseExcel and Spreadsheet::XLSX to process Excel spreadsheets, and the combination works reasonably well (despite various frustrating problems in retrieving dates).

Today, I wrote code with lots of diagnostics to parse a large spreadsheet I'd never seen before. The code ran properly for a while, and then complained of various inconsistencies in the data. After much digging, I realised that Spreadsheet::XLSX wasn't parsing columns beyond ZZ. LibreOffice showed me data that my Perl code wasn't seeing; but it, too, has an arbitrary 1024 column limit. I had to unzip the XLSX file and read the XML source to find the real number of columns: 1059.

Until today, I'd never imagined a spreadsheet that big either.

The fault lies with the following code in Spreadsheet/, which assumes that the column name is two letters followed by a number:

foreach ($member_sheet -> contents =~ /(\<.*?\/?\>|.*?(?=\<))/g) {
    if (/^\<c r=\"([A-Z])([A-Z]?)(\d+)\"/) {
        $col = ord ($1) - 65;
        if ($2) {
            $col *= 26;
            $col += (ord ($2) - 65);

Fortunately, it's not hard to extend the code to handle longer names:

foreach ($member_sheet -> contents =~ /(\<.*?\/?\>|.*?(?=\<))/g) {
    if (/^\<c r=\"([A-Z])([A-Z]*)(\d+)\"/) {
    $col = ord ($1) - 65;

    my $rest = $2;
    while (my $l = substr ($rest, 0, 1, '')) {
        $col *= 26;
        $col += (ord ($l) - 65);

I sent the patch to Dmitry Ovsyenko, author of Spreadsheet::XLSX, but received no response.

IPv6 support for authbind

A few days ago, my friend Aaron was trying to add IPv6 support to authbind, a program by Ian Jackson which allows unprivileged processes to bind reserved ports through LD_PRELOAD-interception of bind(2) and a setuid-root helper program.

Yesterday, after returning from a long train journey, I took a few hours to decompress and hack the necessary changes together. It turned out to be quite simple. Here's the patch.

The changes have received only light testing, but everything seemed to work in the test cases I contrived. I'll send the patch upstream after a couple of other people confirm that I didn't overlook anything.

Testing and feedback are very welcome.

Update: a week later, at least one site runs the patched authbind in production, and I have sent the patch to the author (with no response yet). The patch is also now cited in a bug report filed against the Debian package.

Update: a year later, Ian Jackson responded to the bug report and said the patch was unacceptable, because it changed the internal calling convention for a helper program. I wanted to redo and resubmit the patch, but couldn't drum up the motivation to actually do so.

Update (2012-06-02): a year and a half after I wrote the patch, Ian Jackson has released authbind 2.0.0 with IPv6 support. A quick glance suggests that he didn't use any of my code.

Net::XMPP virtual hosting support

I use a Git post-receive hook that sends commit notifications to a Jabber server using the Net::XMPP Perl module.

For the first year or so of its operation, the hook would connect to the Jabber server (first jabberd 1.4, later Ejabberd) that I ran on, and send the commit message as, and all was well. Recently, we reorganised our servers. The git repository stayed in the same place, but we wanted it to connect to the Jabber server on and send notifications as

I run Ejabberd on too, and it was the work of a moment to add to its list of hostnames and create an account for But when I tried to make my hook connect to the new server, I discovered that Net::XMPP does not allow me to specify the name of the server independently of the hostname in the Jabber ID:

my $client = new Net::Jabber::Client ();
    hostname => '',
) or die "Can't connect: $!\n";

my @r = $client->AuthSend(
    username => 'git',
    password => '...',
    resource => 'aox.git'
die "Cannot authenticate (@r)\n" if $r[0] ne "ok";

This code would connect to and try to authenticate as Changing hostname to "" didn't work (even though we had SRV records pointing to the right server). Changing the username to "" didn't work either. I spent some time looking through the code and a tcpdump of the session to understand the problem. It turns out that an XMPP session begins with a <stream> element like this:

<stream:stream version='1.0'

What is needed is to connect to and send to='' in the stream, because Ejabberd rejects connections to a hostname not listed in its configuration.

Net::XMPP and XML::Stream (the underlying module) use the hostname passed to Connect() to decide where to connect and what to send in the <stream>. Furthermore, XML::Stream did support SRV lookups, but it would replace the hostname with the result of the lookup and use that name to compose the stream; and Net::XMPP had no way to enable the SRV lookups anyway.

I whipped up a patch to solve these problems. It adds "servername" and "srv" parameters to the Connect() method, which are passed down to XML::Stream. If the servername is specified, it is used to decide where to connect (and if not, the old behaviour remains unchanged). The srv parameter enables SRV lookups, and the results are used to set (only) the servername; but the hostname is used to compose the stream in every case. Thus the two are separated enough for my hook to work again.

I sent my patch to the module maintainers, but did not receive a reply. The patch is archived here. It applies to Net::XMPP 1.02 and XML::Stream 1.23, but may need minor tweaking to apply cleanly to other versions.

HTTP::Parser 0.04 bugfix

In writing Inspector34, I decided to use the HTTP::Parser module and the corresponding POE::Filter to parse HTTP requests and responses.

I discovered that HTTP::Parser stores the protocol version in an X-HTTP-Version pseudo-header field, which causes LWP to treat the resulting HTTP::Request object as an HTTP/0.9 request (as it is required to do, since no version is explicitly specified). That was certainly not what I wanted.

Since HTTP::Message provides a protocol() method, it seemed sensible to use that instead of X-HTTP-Version. I whipped up a simple patch to do this (and correct a few documentation errors to boot).

I sent this patch to the author, but received no response. It is preserved here for the record.