[RADIATOR] Using SQL statements inside a PostAuthHook

[Thomas Kurian]

Hello Heikki,
The script is working now ,traces users who exceeded quota and calls web 
link with user details. Thank you very much for your help and advice. 
Following is the clean & complete version of my script.

#! /usr/bin/perl -w
use strict;
use warnings;
use diagnostics;
use URI::Escape ('uri_escape');
require LWP::UserAgent;
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->{thomas};

         &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;


         &main::log($main::LOG_DEBUG, "Running PostAuthHook sql query 
check for :
$username");

                 $query  = "select username from quotasubscribers where 
switched = 0 and type = 'Q' and monthlycounter >= maxquota ";
                 my $sth = $sess_handle->prepareAndExecute($query);
                 my @row = $sess_handle->getOneRow($sth);
                 $sth->finish;
                 my $db_user_name = $row[0];


my $ua = LWP::UserAgent->new;
if ( $db_user_name eq  $username )
{my $response = $ua->get('http://94.187.187.8:180/changespeed.aspx?uname=' .
   uri_escape($username) .
   '&pwd=pssword'
);
         if ($response->is_success)
         {
          print $response->content; # or whatever
          &main::log($main::LOG_DEBUG, "The user $db_user_name has 
exceeded allocated quota and is been limited to speed2");
         }
         else
         {
          die $response->status_line;
         }
}else {
         &main::log($main::LOG_DEBUG, "The user $username either has not 
yet exceeded allocated quota or isnt a quota based user");
       }

}

Best Regards,

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

On 3/30/2013 11:37 AM, Heikki Vatiainen wrote:
> On 03/29/2013 07:29 PM, Thomas Kurian wrote:
>
>> I appended the additional config lines you sent me in your reply. But i
>> still did not get the required result. I have included the error logged
>> in the log file. Please advice me on how to resolve this error based on
>> the updated script.
> Here $self is undefined. See the line where you have $self=undef;
>   my @row = $self->getOneRow($sth);
>
> you could try this:
>   my @row = $sess_handle->getOneRow($sth);
>
>> I also commented out some config lines  in the script file, can you
>> please check if these are just unwanted statements which is not required
>> for my purpose with the hook.
> I see a couple of lines related to generating random numbers. All I can
> say is they seem not to be used by your script.
>
>> I just want to check if the user crossed his allocated quota , if yes
>> then i need to call the web link containing username.
>>
>> Also i could not find any prepared statements or quote() to get
>> User-Name from a SQL query in the sqlDb.pm file . Can you please point
>> out these prepared statements from the file.
> Instead of doing this:
>
>   my $username = "whatever";
>   my $query = "select something from users where username='$username'";
>   prepareAndExecute($query);
>
> You can keep the $query constant and call it with the variables like this:
>
>   my $username = "whatever";
>   my @bind_variables;
>   push @bind_variables, $username;
>   my $query = "select something from users where username=?";
>   prepareAndExecute($query, @bind_variables);
>
> In this way the query can be processed only once by the SQL layer and
> executed multiple times with the different contents of bind_variables.
>
> See section "5.4 Bind Variables" in the reference manual for more.
>
>
>> Also tell me the syntax for adding additional log() calls._
> You already call main::log once. Just add more of these to keep track
> what your hook is doing. That will help with development.
>
> Thanks,
> Heikki
>
>
>> error log_
>> Thu Mar 28 09:36:40 2013: DEBUG: AuthBy SQL result: ACCEPT,
>> Thu Mar 28 09:36:40 2013: DEBUG: Running PostAuthHook: Using Identifier
>>
>> Thu Mar 28 09:36:40 2013: DEBUG: Query to 'dbi:ODBC:IRONMAN': 'select
>> username from quotasubscribers where switched = 0 and type = 'Q' and
>> monthlycounter >= maxquota '$
>> Thu Mar 28 09:36:40 2013: ERR: Error in PostAuthHook(): Can't call
>> method "getOneRow" on an undefined value at (eval 53) line 50.
>> _
>> __Updated Script_
>> #! /usr/bin/perl -w
>> use strict;
>> use warnings;
>> use diagnostics;
>> use URI::Escape ('uri_escape');
>> require LWP::UserAgent;
>> 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->{thomas};
>>
>>          &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;
>>          my $self                = undef;
>>
>>          #my $rt                  = $p->{RecvTime};
>>
>>         # my $lower               = 1000;
>>
>>        #  my $upper               = 2000000;
>>
>>         # my $random              = int(rand( $upper-$lower+1 ) ) + $lower;
>>
>>          #my $reallyrandom        = $random.$rt;
>>
>>
>>                  $query  = "select username from quotasubscribers where
>> switched = 0 and type = 'Q' and monthlycounter >= maxquota ";
>>                  my $sth = $sess_handle->prepareAndExecute($query);
>>                  my @row = $self->getOneRow($sth);
>>                  $sth->finish;
>>                  my $db_user_name = $row[0];
>>
>> my $ua = LWP::UserAgent->new;
>> if ( $db_user_name eq  $username )
>> {my $response = $ua->get('http://94.187.187.8:880/changespeed.aspx?uname=' .
>>    uri_escape($username) .
>>    '&pwd=peter'
>> );
>>          if ($response->is_success)
>>          {
>>           print $response->content; # or whatever
>>          }
>>          else
>>          {
>>           die $response->status_line;
>>          }
>> }
>>
>> }
>>
>>
>> -- 
>> Requesting your kind help & cooperation ,
>>
>> 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/20130331/27011e8f/attachment.html