White snow agains the black ice ...

In my previous post I explained how to use your Belgian eID to login on your openVPN server.
I did use a simple hello-world script to check the authorization. The username (rijksregisternummer/numero du registre national) was hardcoded in a file.

This evening I finally quickly enhanced the script do perform database authorization:

if ($x509 =~ /\/serialNumber=([^\/]+)/) {
    # Accept the connection if the X509 common name
    # string matches the passed cn argument.
    my $dbh = DBI->connect('DBI:mysql:sslvpn', 'sslvpn', 'sslvpn')
            or die "Couldn't connect to database: " . DBI->errstr;
    my $sth = $dbh->prepare("SELECT `id`, `name`, `firstname` FROM `users` WHERE id=$1")
            or die "Couldn't prepare statement: " . $dbh->errstr;
    my @data;
    $sth->execute() 
            or die "Couldn't execute statement: " . $sth->errstr;

    # Read the matching records and print them out          
    while (@data = $sth->fetchrow_array()) {
        $id = $data[0];
        $name = $data[1];
        $firstname = $data[2];
        #print "Database result: \t$id: $firstname $name\n";
    }

    # Authentication failed -- Either we could not parse
    # the X509 subject string, or the common name in the
    # subject string didn't match the passed cn argument.
    if ($sth->rows == 0) {
        print "TLS-VERIFY: EE - Unknown user: $x509\n";
        $result=1;
    } 
    # Authentication is OK
    else {
        print "TLS-VERIFY: OK - $id - '$firstname $name' logged in\n";
        $result=0;
    } 
    $sth->finish;
    $dbh->disconnect;
}

The database:

CREATE TABLE `users` (
  `id` varchar(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `firstname` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `id` (`id`),
  KEY `id_2` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `users` (`id`, `name`, `firstname`) VALUES 
('83021811535', 'Vandeplas', 'Christophe');