3 # Log failed logins in the sql database
4 # Works with mysql, postgresql and Oracle
5 # It will read the sql parameters from the admin.conf file
8 # log_badlogins <radius.log> [<admin.conf>] [all]
12 # admin.conf: /usr/local/dialup_admin/conf/admin.conf
13 # all: no. Go to the end of the file. Don't read it all.
15 use Date::Manip qw(ParseDate UnixDate);
21 $conf=shift||'/usr/local/dialup_admin/conf/admin.conf';
22 $all_file=shift||'no';
24 # Uncomment to force inserts even if there are sql errors. That can
25 # help in case there is one sql query which stops the whole failed
26 # logins logging system from working
30 # CHANGE THESE TO MATCH YOUR SETUP
32 #$regexp = 'from client localhost port 135|from client blabla ';
33 $tmpdir=tempdir( CLEANUP => 1 );
34 $tmpfile="$tmpdir/sql.input";
40 or die "Could not open configuration file\n";
43 ($key,$val)=(split /:\s*/,$_);
44 # Fixme : recursivly solve %{.*} replacement for $val
45 # Fixme: Conf should be put in an associative array
46 $sql_type = $val if ($key eq 'sql_type');
47 $sql_server = $val if ($key eq 'sql_server');
48 $sql_username = $val if ($key eq 'sql_username');
49 $sql_password = $val if ($key eq 'sql_password');
50 $sql_database = $val if ($key eq 'sql_database');
51 $sql_accounting_table = $val if ($key eq 'sql_accounting_table');
52 $realm_strip = $val if ($key eq 'general_strip_realms');
53 $realm_del = $val if ($key eq 'general_realm_delimiter');
54 $realm_for = $val if ($key eq 'general_realm_format');
55 $domain = $val if ($key eq 'general_domain');
56 $sql_timeout = $val if ($key eq 'sql_connect_timeout');
57 $sql_extra = $val if ($key eq 'sql_extra_servers');
58 $sqlcmd = $val if ($key eq 'sql_command');
59 $clients= $val if ($key eq 'general_clients_conf');
63 open CLIENTS, "<$clients"
64 or die "Could not open $clients file\n";
69 if (!/^\s*$/ && /=/) {
70 ($key,$val)=(split /\s*=\s*/,$_);
71 $client_short = $val if ($key eq 'shortname');
74 s/.*client\s+([^\s]*)\s+\{.*$/\1/;
75 if (/^\d+\.\d+\.\d+\.\d+/) {
78 if (/\./ || /localhost/) {
81 $name = $_.".".$domain;
83 $addr = gethostbyname $name;
84 ($a,$b,$c,$d)=unpack('C4',$addr);
85 $client = "$a.$b.$c.$d";
86 #DEBUG# print $name." = ".$client."\n";
90 $client_array{$client_short} .= $client;
97 $realm_del = '@' if ($realm_del eq '');
98 $realm_for = 'suffix' if ($realm_for eq '');
99 if ($sql_type eq 'mysql'){
100 $pass = (!$sql_password) ? '' : "-p$sql_password";
103 $pass = $sql_password;
105 $pass =~ s/(\W)/\\$1/g;
106 die "SQL server not defined\n" if ($sql_server eq '');
108 die "sql_command directive is not set in admin.conf\n" if ($sqlcmd eq '');
109 die "sql command '$sqlcmd' not found or does not seem to be executable\n" if (! -x $sqlcmd);
112 $opt = "-O connect_timeout=$sql_timeout" if ($sql_timeout);
113 $opt .= " -f" if ($force);
114 @servers = (split /\s+/,$sql_extra) if ($sql_extra ne '');
115 unshift @servers, $sql_server;
118 or die "Could not open file $file\n";
119 if ($verbose > 1) { print STDOUT "DEBUG: Opened $file\n" }
121 seek LOG, 0, 2 if ($all_file eq 'no');
124 if ($verbose > 1) { print STDOUT "DEBUG: Reading $file\n" }
127 next if ($regexp ne '' && !/$regexp/);
129 $user = $nas = $port = $caller = '-';
130 if (/Login incorrect/){
131 if (/Login incorrect \((.+?)\):/){
132 $cause = "Login-Incorrect ($1)";
133 if ($verbose > 1) { print STDOUT "DEBUG: Login-Incorrect ($1)\n" }
135 $cause='Login-Incorrect';
136 if ($verbose > 1) { print STDOUT "DEBUG: Login-Incorrect\n" }
140 elsif (/Invalid user/){
141 if (/Invalid user \((.+?)\):/){
142 $cause = "Invalid-User ($1)";
144 $cause='Invalid-User';
148 elsif (/Multiple logins/){
150 $cause='Multiple-Logins (MPP Attempt)';
152 $cause='Multiple-Logins';
156 elsif (/(Outside allowed timespan \(.+?\)):/){
161 $date = (split / : /,$_)[0];
162 $date2 = ParseDate($date);
164 ($year,$mon,$mday,$hour,$min,$sec)=UnixDate($date2,'%Y','%m','%d','%H','%M','%S');
166 $time = "$year-$mon-$mday $hour:$min:$sec";
167 if (/\[([\w\-\.\!\@\s]+?)\]\s+\(from (.+?)\)/){
169 ($nas,$port) = (split /\s+/,$2)[1,3];
170 if ($2 =~ /cli (.+?)$/){
174 elsif (/\[([\w\-\.\!\@\s]+?)\/.+?\]\s+\(from (.+?)\)/){
176 ($nas,$port) = (split /\s+/,$2)[1,3];
177 if ($2 =~ /cli (.+?)$/){
181 $caller='' if (!defined($caller));
182 $user =~s/[^\w\-\.\d\!\@\s]//g;
183 $nas =~s/[^\w\.\-]//g;
185 $addr = $client_array{$nas};
186 if ($user ne '' && $realm_strip eq 'yes'){
187 ($one,$two) = (split /$realm_del/, $user)[0,1];
189 $user = ($realm_for eq 'suffix') ? $one : $two;
192 foreach $server (@servers){
193 unlink "$tmpfile.$server" if ($delete{$server});
194 open TMP, ">>$tmpfile.$server"
195 or die "Could not open temporary file\n";
196 $ctx = Digest::MD5->new;
201 $ctx->add('badlogin');
202 $uniqueid = $ctx->hexdigest;
203 print TMP "ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';\n" if ($sql_type eq 'oracle');
204 #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";
205 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";
207 $command = "$sqlcmd -h$server $opt -u$sql_username $pass $sql_database <$tmpfile.$server" if ($sql_type eq 'mysql');
208 $command = "$sqlcmd -U $sql_username -f $tmpfile.$server $sql_database" if ($sql_type eq 'pg');
209 $command = "$sqlcmd $sql_username/$pass" . "@" . "$sql_database <$tmpfile.$server" if ($sql_type eq 'oracle');
210 $command = "$sqlcmd '$server' '$sql_port' '' '$sql_username' '$sql_pass' <$tmpfile.$server" if ($sql_type eq 'sqlrelay');
211 if ($verbose > 1) { print STDOUT "DEBUG: Sending datafile $tmpfile.$server to \"$sql_type\" database\n" }
213 if ($verbose > 1) { print STDOUT "DEBUG: Sent data to \"$sql_type\" database\n" }
216 $delete{$server} = ($exit == 0) ? 1 : 0;
217 print STDERR "ERROR: SQL query failed for host $server\n" if ($exit != 0);
222 if ($all_file ne 'once') {