A Powerful, Hybrid Design - Apache

I will now propose a hybrid solution, which unites the simplicity of SQL logging with the power of Perl and encryption. The idea is to write a custom logging program, as I mentioned in the previous section. However, rather than implementing a transport protocol from scratch, the logging program will use Perl’s powerful DBD/DBI libraries (Database Driver/Database Interface) in order to access any SQL server and store logging information on a SQL database in the network. I will also use one of Perl’s libraries to encrypt the logging information before sending it to the database server. In this case, symmetric encryption is acceptable, because I will have access to both the encryption and the decryption script (hence, the key won’t have to travel anywhere).

I will use the following components to implement this solution:

• MySQL as my database server. You can use any database supported by Perl’s DBI drivers, however. For example, many system administrators prefer PostgreSQL.

• A database table, able to store the logging information.

• Perl’s DBD drivers for MySQL.

• Perl’s Crypt::CBC.

• Perl’s Crypt-Blowfish-2.09.

• A custom script that encrypts the stored information.

• A custom script that can read the database and decrypt the information contained in it.

You will first need to install MySQL on your system (you can actually follow these instructions with any other database server by marginally changing the Perl code). Then, you can create the database with a mysqladmin command:

This command creates a database called apache. You now need to create a table to store the logging information:

[root@merc root]# mysql apache
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41 to server version: 3.23.54

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

As you can see, the table is extremely simple: it only contains sequence, a column with a sequence number (automatically generated by MySQL), and log_line, the column that will contain the actual log line.

Your database server is now ready to go. I suggest you leave this MySQL session open so that you can see if information actually does get added to your table while testing the script. Note that you should set login and password in order to access your MySQL server.

You now need to install all the Perl components that will be needed by the script in order to work. They are Crypt-CBC-2.08, Crypt-Blowfish-2.09, and DBD-mysql-2.9002. They can all be found on CPAN (http://www.cpan.org, Comprehensive Perl Archive Network: a site that contains every single third-party Perl module), and they can all be installed with the usual perl Makefile.PL. Here is the installation log for Crypt-CBC:

[root@merc root]# tar xvzf Crypt-CBC-2.08.tar.gz
Crypt-CBC-2.08/
Crypt-CBC-2.08/t/
[...] Crypt-CBC-2.08/MANIFEST
[root@merc root]# cd Crypt-CBC-2.0 8
[root@merc Crypt-CBC-2.08]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for Crypt::CBC
[root@merc Crypt-CBC-2.08]# make
cp CBC.pm blib/lib/Crypt/CBC.pm
Manifying blib/man3/Crypt::CBC.3pm
[root@merc Crypt-CBC-2.08]# make install
Writing /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/
auto/Crypt/CBC/.packlist
Appending installation info to /usr/lib/perl5/5.8.0/i386-linux-thread-multi/
perllocal.pod
[root@merc Crypt-CBC-2.08]#

The same installation instructions apply to Crypt-Blowfish and DBD-mysql-2.9002.

You now need to configure Apache so that it pipes the logging information to a program. Here is what your httpd.conf should look like:

CustomLog "|/usr/local/bin/custom_logging_program" common

This is what custom_logging_program should contain:

#!/usr/bin/perl

# Libraries...

#
use strict; # Be strict with coding
use DBI(); # Use DBI drivers
use Crypt::CBC; # Use encryption

# Variables...
#
my($str); # Variable declaration
my($cipher); # Another variable declaration

# Create the cipher object
#
$cipher = Crypt::CBC->new( {'key'       => 'my secret key',
                           'cipher'     => 'Blowfish',
                           'iv'         => 'DU#E*UF',
                           'regenerate_key'  => 0,
                           'padding'    => 'space',
                           'prepend_iv' => 0 
                                     });

# Connect to the database
# my $dbh = DBI->connec("DBI:mysql:database=apache;
                         host=localhost", "root", "");

# Each log line is fetched and stored into $_...
#
while(<STDIN>){
           $str= $cipher->encrypt($_); # The read line is encrypted...

           # ...and stored onto the database
           $dbh->do("INSERT INTO access_log VALUES ('0',".$dbh->quote("$str").")");
}

$dbh->disconnect(); # Disconnect from the database

exit(0); # End of the program

The code is well commented. The program first creates a Crypt::CBC object using the Crypt::CBC->new() command (refer to Crypt::CBC’s official documentation for more cipher options, perldoc Crypt::CBC). The program reads its standard input (while(<STDIN>)), encrypts the log line ($str-$cipher->encrypt($));) and stores the encrypted information in the database ($dbh->do("INSERT INTO access_log VALUES (0,".$dbh->quote("$str").")");).

In order to test it, you should do the following:

1. Delete any records from the database with the following command:
   
   mysql> delete from access_log;
   Query OK, 0 rows affected (0.00 sec)
   mysql>
   
2. Stop and restart Apache:
   
   [root@merc root]# /usr/local/apache2/bin/apachectl stop
   /usr/local/apache2/bin/apachectl stop: httpd stopped [root@merc root]# /usr/local/apache2/bin/apachectl start
   /usr/local/apache2/bin/apachectl start: httpd started
   
3. Connect to your Apache server requesting some pages using your browser.

4. Check if any new entries have been created in your database:
   
   mysql> select * from access_log ;
   +--------+-------------------------------------------+
   |sequence| log_line                                  |
   +--------+-------------------------------------------+
   |   1    |??;F??b92B}?C\x{0748}?`?N^?\x{018E}?G?"    | |   2    |??\x{992B}?C?9[lv?w5I?Tp??Q?\x{02EC}c????b?|
   +--------+-------------------------------------------+
   2 rows in set (0.00 sec)
   mysql>

The information stored in the database is not readable, which means it cannot be modified in any meaningful way. To read your logs, you will need to decrypt them using the same algorithm. Here is the program that will fetch and decrypt the log entries:

#!/usr/bin/perl

# Libraries...
#
use strict; # Be strict with coding
use DBI(); # Use DBI drivers
use Crypt::CBC; # Use encryption

# Variables...
#
my($str);     # Variable declaration
my($cipher); # Another variable declaration

# Create the cipher object
#
$cipher = Crypt::CBC->new( {'key'       => 'my secret key',
                            'cipher'    => 'Blowfish',
                            'iv'        =>  'DU#E*UF',
                            'regenerate_key' => 0,
                            'padding'   => 'space',
                            'prepend_iv'=> 0
                           });

# Connect to the database
#
my $dbh = DBI->connec ("DBI:mysql:database=apache;
                        host=localhost", "root", "");

# Prepare the SQL query
#
my $sth = $dbh->prepare("SELECT * FROM access_log");
$sth->execute();

# Main cycle to read the information
#
while (my $ref = $sth->fetchrow_hashref()) {
        $str= $cipher->decrypt($ref->{'log_line'});
        print($str);
}
$sth->finish();     # End of row-fetching
$dbh->disconnect(); # Disconnect from the database

exit(0); # End of the program

This program is very similar to the previous one. The difference is that the information is fetched from the database (while (my $ref = $sth->fetchrow_hashref()) {), and that it is decrypted ($str= $cipher->decrypt($ref->{'log_line'});).

Once your log information has been fetched by this script, you can feed it to “classic” web analyzing tools and store it in a secure location.