log_badlogins   [plain text]

#!/usr/bin/perl
#
# Log failed logins in the sql database
# Works with mysql, postgresql and Oracle
# It will read the sql parameters from the admin.conf file
#
# Usage:
# log_badlogins <radius.log> [<admin.conf>] [all]
#
# Defaults:
# radius.log: none
# admin.conf: /usr/local/dialup_admin/conf/admin.conf
# all:        no. Go to the end of the file. Don't read it all.

use Date::Manip qw(ParseDate UnixDate);
use Digest::MD5;
use File::Temp;
$|=1;

$file=shift||'none';
$conf=shift||'/usr/local/dialup_admin/conf/admin.conf';
$all_file=shift||'no';
#
# Uncomment to force inserts even if there are sql errors. That can
# help in case there is one sql query which stops the whole failed
# logins logging system from working
#$force=1;
#
#
# CHANGE THESE TO MATCH YOUR SETUP
#
#$regexp = 'from client localhost port 135|from client blabla ';
$tmpdir=tempdir( CLEANUP => 1 );
$tmpfile="$tmpdir/sql.input";
#
$verbose = 0;
#

open CONF, "<$conf"
	or die "Could not open configuration file\n";
while(<CONF>){
	chomp;
	($key,$val)=(split /:\s*/,$_);
	# Fixme : recursivly solve %{.*} replacement for $val
	# Fixme: Conf should be put in an associative array
	$sql_type = $val if ($key eq 'sql_type');
	$sql_server = $val if ($key eq 'sql_server');
	$sql_username = $val if ($key eq 'sql_username');
	$sql_password = $val if ($key eq 'sql_password');
	$sql_database = $val if ($key eq 'sql_database');
	$sql_accounting_table = $val if ($key eq 'sql_accounting_table');
	$realm_strip = $val if ($key eq 'general_strip_realms');
	$realm_del = $val if ($key eq 'general_realm_delimiter');
	$realm_for = $val if ($key eq 'general_realm_format');
	$domain = $val if ($key eq 'general_domain');
	$sql_timeout = $val if ($key eq 'sql_connect_timeout');
	$sql_extra = $val if ($key eq 'sql_extra_servers');
	$sqlcmd = $val if ($key eq 'sql_command');
	$clients= $val if ($key eq 'general_clients_conf');
}
close CONF;

open CLIENTS, "<$clients"
	or die "Could not open $clients file\n";
while(<CLIENTS>){
	chomp;
	s/^\s*//g;
	s/\s*#.*//g;
	if (!/^\s*$/ && /=/) {
		($key,$val)=(split /\s*=\s*/,$_);
		$client_short = $val if ($key eq 'shortname');
	} else {
		if (/\{/) {
			s/.*client\s+([^\s]*)\s+\{.*$/\1/;
			if (/^\d+\.\d+\.\d+\.\d+/) {
				$client = $_;
			} else {
				if (/\./ || /localhost/) {
					$name = $_ ;
				} else {
					$name = $_.".".$domain;
				}
				$addr = gethostbyname $name;
				($a,$b,$c,$d)=unpack('C4',$addr);
				$client = "$a.$b.$c.$d";
#DEBUG#				print $name." = ".$client."\n";
			}
		} else {
			if (/\}/) {
				$client_array{$client_short} .= $client;
			}
		}
	}
}
close CLIENTS;

$realm_del = '@' if ($realm_del eq '');
$realm_for = 'suffix' if ($realm_for eq '');
if ($sql_type eq 'mysql'){
	$pass = (!$sql_password) ? '' : "-p$sql_password";
}
else{
	$pass = $sql_password;
}
$pass =~ s/(\W)/\\$1/g;
die "SQL server not defined\n" if ($sql_server eq '');

die "sql_command directive is not set in admin.conf\n" if ($sqlcmd eq '');
die "sql command '$sqlcmd' not found or does not seem to be executable\n" if (! -x $sqlcmd);

$opt = "";
$opt = "-O connect_timeout=$sql_timeout" if ($sql_timeout);
$opt .= " -f" if ($force);
@servers = (split /\s+/,$sql_extra) if ($sql_extra ne '');
unshift @servers, $sql_server;

open LOG, "<$file"
	or die "Could not open file $file\n";
if ($verbose > 1) { print STDOUT "DEBUG: Opened $file\n" }

seek LOG, 0, 2 if ($all_file eq 'no');
for(;;){
	while(<LOG>){
		if ($verbose > 1) { print STDOUT "DEBUG: Reading $file\n" }
		$do=0;
		chomp;
		next if ($regexp ne '' && !/$regexp/);
		if ($_ ne ''){
			$user = $nas = $port = $caller = '-';
			if (/Login incorrect/){
				if (/Login incorrect \((.+?)\):/){
					$cause = "Login-Incorrect ($1)";
					if ($verbose > 1) { print STDOUT "DEBUG: Login-Incorrect ($1)\n" }
				}else{
					$cause='Login-Incorrect';
					if ($verbose > 1) { print STDOUT "DEBUG: Login-Incorrect\n" }
				}
				$do=1;
			}
			elsif (/Invalid user/){
				if (/Invalid user \((.+?)\):/){
					$cause = "Invalid-User ($1)";
				}else{
					$cause='Invalid-User';
				}
				$do=1;
			}
			elsif (/Multiple logins/){
				if (/MPP attempt/){
					$cause='Multiple-Logins (MPP Attempt)';
				}else{
					$cause='Multiple-Logins';
				}
				$do=1;
			}
			elsif (/(Outside allowed timespan \(.+?\)):/){
				$cause = "$1";
				$do=1;
			}
			if ($do){
				$date = (split / : /,$_)[0];
				$date2 = ParseDate($date);
				if ($date2){
					($year,$mon,$mday,$hour,$min,$sec)=UnixDate($date2,'%Y','%m','%d','%H','%M','%S');
				}
				$time = "$year-$mon-$mday $hour:$min:$sec";
				if (/\[([\w\-\.\!\@\s]+?)\]\s+\(from (.+?)\)/){
					$user = $1;
					($nas,$port) = (split /\s+/,$2)[1,3];
					if ($2 =~ /cli (.+?)$/){
						$caller = $1;
					}
				}
				elsif (/\[([\w\-\.\!\@\s]+?)\/.+?\]\s+\(from (.+?)\)/){
					$user = $1;
					($nas,$port) = (split /\s+/,$2)[1,3];
					if ($2 =~ /cli (.+?)$/){
						$caller = $1;
					}
				}
				$caller='' if (!defined($caller));
				$user =~s/[^\w\-\.\d\!\@\s]//g;
				$nas =~s/[^\w\.\-]//g;
				$port =~s/[^\d]//g;
				$addr = $client_array{$nas};
				if ($user ne '' && $realm_strip eq 'yes'){
					($one,$two) = (split /$realm_del/, $user)[0,1];
					if ($two ne ''){
						$user = ($realm_for eq 'suffix') ? $one : $two;
					}
				}
				foreach $server (@servers){
					unlink "$tmpfile.$server" if ($delete{$server});
					open TMP, ">>$tmpfile.$server"
						or die "Could not open temporary file\n";
					$ctx = Digest::MD5->new;
					$ctx->add($user);
					$ctx->add($addr);
					$ctx->add($port);
					$ctx->add($time);
					$ctx->add('badlogin');
					$uniqueid = $ctx->hexdigest;
					print TMP "ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';\n" if ($sql_type eq 'oracle');
#DEBUG#					print "INSERT INTO $sql_accounting_table (UserName,AcctUniqueId,NASIPAddress,NASPortId,AcctStartTime,AcctStopTime,AcctSessionTime,AcctInputOctets,AcctOutputOctets,CallingStationId,AcctTerminateCause) VALUES ('$user','$uniqueid','$addr','$port','$time','$time','0','0','0','$caller','$cause');\n";
					print TMP "INSERT INTO $sql_accounting_table (UserName,AcctSessionId,AcctUniqueId,NASIPAddress,NASPortId,AcctStartTime,AcctStopTime,AcctSessionTime,AcctInputOctets,AcctOutputOctets,CallingStationId,AcctTerminateCause) VALUES ('$user','$uniqueid','$uniqueid','$addr','$port','$time','$time','0','0','0','$caller','$cause');\n";
					close TMP;
					$command = "$sqlcmd -h$server $opt -u$sql_username $pass $sql_database <$tmpfile.$server" if ($sql_type eq 'mysql');
					$command = "$sqlcmd  -U $sql_username -f $tmpfile.$server $sql_database" if ($sql_type eq 'pg');
					$command = "$sqlcmd  $sql_username/$pass" . "@" . "$sql_database <$tmpfile.$server" if ($sql_type eq 'oracle');
					$command = "$sqlcmd '$server' '$sql_port' '' '$sql_username' '$sql_pass' <$tmpfile.$server" if ($sql_type eq 'sqlrelay');
					if ($verbose > 1) { print STDOUT "DEBUG: Sending datafile $tmpfile.$server to \"$sql_type\" database\n" }
					`$command`;
					if ($verbose > 1) { print STDOUT "DEBUG: Sent data to \"$sql_type\" database\n" }

					$exit = $? >> 8;
					$delete{$server} = ($exit == 0) ? 1 : 0;
					print STDERR "ERROR: SQL query failed for host $server\n" if ($exit != 0);
				}
			}
		}
	}
	if ($all_file ne 'once') {
		sleep 2;
		seek LOG,0,1;
	} else {
		exit(0);
	}
}