[RADIATOR] Using SQL statements inside a PostAuthHook

[Heikki Vatiainen]

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


-- 
Heikki Vatiainen <hvn at open.com.au>

Radiator: the most portable, flexible and configurable RADIUS server
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS,
TTLS, PEAP, TNC, WiMAX, RSA, Vasco, Yubikey, MOTP, HOTP, TOTP,
DIAMETER etc. Full source on Unix, Windows, MacOSX, Solaris, VMS,
NetWare etc.