(RADIATOR) Using SQL statemens inside a PostAuthHook

Wed Feb 19 12:28:13 CST 2003

Below is an example for you:

Note the example contains some Oracle specifics (bind variables).

hth

Paul

############################################################

sub {

# OBJECT REF

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

# RETURN VOID

        return unless ($p->code() eq 'Access-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 $nasaddress          =
$p->getAttrByNum($Radius::Radius::NAS_IP_ADDRESS);

        my $nasport             =
$p->getAttrByNum($Radius::Radius::NAS_PORT);

        my $framedipaddress     =
$r->getAttrByNum($Radius::Radius::FRAMED_IP_ADDRESS);
           $framedipaddress     = $r->get_attr('Framed-IP-Address') if
!$framedipaddress;

        my $callingstation      =
$p->getAttrByNum($Radius::Radius::CALLING_STATION_ID);

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

        my $query               = undef;

        my $rt                  = $p->{RecvTime};

        my $lower               = 1000;

        my $upper               = 2000000;

        my $random              = int(rand( $upper-$lower+1 ) ) + $lower;

        my $reallyrandom        = $random.$rt;

       if (defined($framedipaddress) && $framedipaddress ne '' &&
                defined($callingstation) && $callingstation ne '') {

                # prepare
                $query  = "delete from some_db_tbl where " .
                        "IP_ADDR= ? or " .
                        "MSISDN= ? ";
                my $sth = $sess_handle->prepareAndExecute($query,
$framedipaddress, $callingstation);

                # prepare
                $query  = "insert into some_db_tbl " .
                        "(IP_ADDR, MSISDN, NET_TYPE, DATE_CREATED,
SESSION_ID, ACTIVE, NAS_ID) " .
                        "values ( ? , ? , " .
                        "'GSMSMS', SYSDATE, $reallyrandom, 'y',
'$nasaddress')";
                $sth    = $sess_handle->prepareAndExecute($query,
$framedipaddress, $callingstation);

        } elsif (defined($framedipaddress) && $framedipaddress ne '' &&
                !defined($callingstation) || $callingstation eq '' ) {

                # prepare
                $query  = "delete from some_db_tbl where " .
                                "IP_ADDR= ? ";
                my $sth = $sess_handle->prepareAndExecute($query,
$framedipaddress);

        } elsif (defined($callingstation) && $callingstation ne '' &&
                !defined($framedipaddress) || $framedipaddress eq '' ) {

                # prepare
                $query  = "delete from some_db_tbl where " .
                                "MSISDN= ? ";
                my $sth = $sess_handle->prepareAndExecute($query,
$callingstation);
        }

}

############################################################

----- Original Message -----
From: "Bogdan TARU" <bgd at icomag.de>
To: "Hugh Irvine" <hugh at open.com.au>
Cc: <radiator at open.com.au>
Sent: Wednesday, February 19, 2003 10:44 AM
Subject: Re: (RADIATOR) Using SQL statemens inside a PostAuthHook

Hi Hugh,

 What happens if I want to use the database connection from somewhere
'outside' the AuthBy? For example, in a "PostAuthHook"?

 I have tried the following:

--------------------------
PostAuthHook sub { \
  my $p = ${$_[0]}; \
  my $rp = ${$_[1]}; \
  my $op = ${$_[2]}; \
  my $identifier = $p->{Client}->{Identifier}; \
  &main::log($main::LOG_DEBUG, "Using Identifier $identifier"); \
}
--------------------------

 And that didn't work. I have also tried the example in goodies/hooks.txt
file, meaning I have replaced the line:

  my $identifier = $p->{Client}->{Identifier}; \

 with

  my $identifier = Radius::Util::format_special('%{Handler:Identifier}',
$op,
$rp);

 But that didn't work either. I imagine it doesn't work because it's
outside the 'scope' of the AuthBy block.

 Thanks,
 bogdan

----------------------------
iCom Media AG
Kirchweg 36
Koln, 50858
Germany

Phone: +49-(0)221-485-689-16
Fax  : +49-(0)221-485-689-20
Mobile:+49-(0)173-906-46-01

On Wed, 12 Feb 2003, Hugh Irvine wrote:

>
> Hello Bogdan -
>
> Yes you can use all of the Radiator internal routines on an existing
> connection to the database established in an AuthBy SQL clause. You
> just need to get a handle to the AuthBy clause (using find()) and use
> that handle for your calls.
>
> You will find an example of how to get a handle to an AuthBy clause in
> the hook AllocateIPAddressFromProxy in the file goodies/hooks.txt. You
> will also find lots of examples of how to use the SQL calls in the code
> in the Radius directory.
>
> regards
>
> Hugh
>
>
> On Tuesday, Feb 11, 2003, at 21:56 Australia/Melbourne, Bogdan TARU
> wrote:
>
> >
> > Hi everyone,
> >
> >  I have noticed the following phrase in the Radiator documentation:
> >
> >  6.16.14 PostAuthHook:
> >   -skip- PostAuthHook can be an arbitrarily complicated Perl function,
> > that might run external processes, consult databases, change the
> > contents
> > of the current request or many other things. -skip-
> >
> >  My question is how can I consult a database from inside a
> > PostAuthHook? I
> > am tring to use the same SQL database as the one configured in <AuthBy
> > SQL>. So, do I have to connect once again using perl functions, or can
> > I
> > somehow 're-use' the existing SQL connection to make some queries?
> >
> >  Could I get some examples of how to do this? I have already checked
> > the
> > 'hooks.txt' file, but no database examples are there.
> >
> >  Thank you,
> >  bogdan
> >
> >
> > ----------------------------
> > iCom Media AG
> > Kirchweg 36
> > Koln, 50858
> > Germany
> >
> > Phone: +49-(0)221-485-689-16
> > Fax  : +49-(0)221-485-689-20
> > Mobile:+49-(0)173-906-46-01
> >
> > ===
> > Archive at http://www.open.com.au/archives/radiator/
> > Announcements on radiator-announce at open.com.au
> > To unsubscribe, email 'majordomo at open.com.au' with
> > 'unsubscribe radiator' in the body of the message.
> >
>

===
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.