[RADIATOR] Using SQL statements inside a PostAuthHook

[Thomas Kurian]

Hello Friends,
With reference to this link 
,http://www.mail-archive.com/radiator@open.com.au/msg13497.html , i have 
developed my own version as seen below,
its purpose is to get the user name from  accounting requests and to 
compare it with the corresponding username in the ODBC SQL database 
table . This username is checked for some conditions like if is quota 
limit is exceeded or not. If these conditions are matched then a custom 
website is called and the username value is passed to this website.

Can you please check this hook file configuration with respect to my 
radiator configuration file (after the hook file). I have doubts like if 
the perl hook has all the necessary configuration lines to access my 
odbc database (sql table name: quotasubscribers). I am using 
sessiondatabase but i dont have any thing to modify in the sql table , 
but just to make sure that the hook might be able to access the 
database. I checked this hook and found no syntax errors but i want to 
make sure that my above mentioned purpose would be met by the hook. 
Kindly help me out as i am new to perl.
Please advice me if there is anything to be added/modified in my 
radiator config file or hook file.
--------------------
Hook file : rocky.pl
#!/usr/bin/perl
use LWP::Simple qw(get);
use URI;
use URI::Escape ('uri_escape');
sub {

# OBJECT REF

         my $p = ${$_[0]};
         my $r = ${$_[1]};

# RETURN VOID

         return unless ($p->code() eq 'Accounting-Request')
                                 && (${$_[2]} == $main::ACCEPT);

         my $handler             = $p->{Handler};

         my $identifier          = $handler->{SessionDatabase};

         &main::log($main::LOG_DEBUG, "Running PostAuthHook: Using 
Identifier
$identifier");

         my $username            =
$p->getAttrByNum($Radius::Radius::User-Name);


         my $sess_handle         = Radius::SessGeneric::find($identifier);

         my $query               = undef;




                 $query  = "select username from quotasubscribers where 
switched = 0 and type = 'Q' and monthlycounter >= maxquota ";
                 my $sth = $sess_handle->prepareAndExecute($query);
if ( $sth eq $username )
{
my $content = get(
     URI->new('http://94.187.187.8:8123/changespeed.aspx?uname=' .
   uri_escape($username) .
   '&password=XXXXX')
);
}

}

-----------------

My radiator config file
AcctPort 1813
AuthPort 1812

BindAddress 0.0.0.0

LogDir          /var/log/radius
DbDir           /etc/radiator
# Use a low trace level in production systems. Increase
# it to 4 or 5 for debugging, or use the -trace flag to radiusd
Trace           4

# You will probably want to add other Clients to suit your work site,

<Client DEFAULT>
         Secret  XXXXXX
         DupInterval 0
</Client>


<Client 10.50.1.4>
         Secret XXXXXX
         DupInterval 0
         NasType Cisco
         IgnoreAcctSignature
</Client>

<SessionDatabase SQL>


         Identifier      tamesql
         DBSource        dbi:ODBC:IRONMAN
         DBUsername      xxxxxxxxxxx
         DBAuth          xxxxx

</SessionDatabase>


# Accept processing of other accounting requests of the genre stop


<Handler Request-Type = Accounting-Request>
PreProcessingHook file:"/etc/radiator/createavpairs.pl"
         <AuthBy SQL>
                 Identifier thomas
                 DBSource dbi:ODBC:IRONMAN
                 DBUsername xxxx
                 DBAuth xxxxxxxxx


                 #AccountingStopsOnly
                 AccountingTable ACCOUNTING
                 AcctColumnDef   USERNAME, User-Name
                 AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
                 AcctColumnDef FRAMEDIPADDRESS,Framed-IP-Address
                 AcctColumnDef ACCTINPUTOCTETS,Acct-Input-Octets,integer
                 AcctColumnDef ACCTOUTPUTOCTETS,Acct-Output-Octets,integer
                 AcctColumnDef TIME_STAMP,Event-Timestamp,integer-date
                 AcctColumnDef ACCTSESSIONTIME,Acct-Session-Time,integer
                 AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer
                 AcctColumnDef   ACCTSESSIONID,Acct-Session-Id
                 AcctColumnDef ACCTTERMINATECAUSE,Acct-Terminate-Cause
                 AcctColumnDef   NASIDENTIFIER,NAS-Identifier
                 AcctColumnDef   NASPORT,NAS-Port,integer
                 AcctColumnDef PARENTSESSIONID,parent-session-id

                 AcctSQLStatement update quotasubscribers set 
monthlycounter = monthlycounter + 0%{Acct-Output-Octets}, totalcounter = 
totalcounter + 0%{Acct-Output-Octets}, timestamp = %{Event-Timestamp}  \
                                 where username='%n' \
                                 And Type = 'Q'



         </AuthBy>
#PostAuthHook file:"/etc/radiator/rocky.pl"
                 #Log accounting to a detail file
                 AcctLogFileName %L/detail


</Handler>




<Handler Request-Type=Disconnect-Request>
         <AuthBy RADIUS>

                 <Host 10.50.1.4>
                     Secret xxxxxxxxxx
                 </Host>
         </AuthBy>
</Handler>
-------------------------

-- 
Welcoming your kind reply,

Thomas Kurian
IT Security Engineer (B.Tech. -- Electrical)
Kuwaiti Canadian Consulting Group (www.kccg.com)
T: +965 22435566
F: +965 22415149
E:thomas at kccg.com



-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.open.com.au/pipermail/radiator/attachments/20130317/49c3291b/attachment.html