Multiple Calling of SQL Statements

27 Jul 2021

      I want FreeRADIUS 3.0.22 (I can't update now, sorry.) to reject 
anonymous logins *a**nd* log every successful login to the database. To 
that end, I wrote three MySQL stored procedures.

1. is_login_allowed -> Checks if the user in the database and has not 
exceeded his quota etc.

2. log_login -> Logs a successful login to the database.

3. log_mac -> Logs the MAC address of the user's device.

Of course, I thought, the most reasonable place to achieve my goal was 
to place my sproc calls in the inner tunnel, since we always get the 
real user name there, at least that is my thinking. Here is the site 
script for my inner tunnel:

server inner-tunnel-ttls {

listen {
     ipaddr = 127.0.0.1
     port = 18127
     type = auth
}

authorize {

*    # Custom Script**
**    if ("%{sql: CALL is_login_allowed('%{User-Name}', 
'%{Calling-Station-Id}', '%{Called-Station-Id}')}" == "0" ) {**
**        reject**
**    }**
**    else {**
**        "%{sql: CALL log_login('%{User-Name}', 
'%{Calling-Station-Id}', '%{NAS-IP-Address}')}"**
**        "%{sql: CALL log_mac('%{Calling-Station-Id}', '%{User-Name}')}"**
**    }*

     eap {
         ok = return
     }

     sql

     expiration
     logintime
     pap
}

authenticate {

     Auth-Type PAP {
         pap
     }

     Auth-Type MS-CHAP {
         mschap
     }

     eap
}

session {
     sql
}

post-auth {
     update outer.reply {
         User-Name += &request:User-Name
         Class += &reply:Class
         Idle-Timeout += &reply:Idle-Timeout
         Acct-Interim-Interval += &reply:Acct-Interim-Interval
     }

     if (1) {
         update reply {
             User-Name !* ANY
             Message-Authenticator !* ANY
             EAP-Message !* ANY
             Proxy-State !* ANY
             MS-MPPE-Encryption-Types !* ANY
             MS-MPPE-Encryption-Policy !* ANY
             MS-MPPE-Send-Key !* ANY
             MS-MPPE-Recv-Key !* ANY
         }
         update {
             &outer.session-state: += &reply:
         }
     }

     Post-Auth-Type REJECT {
         -sql
         attr_filter.access_reject
         update outer.session-state {
             &Module-Failure-Message := &request:Module-Failure-Message
         }
     }
}

pre-proxy {

}

post-proxy {

     eap
}

}

It is simple, isn't it? And it should work, right? It *does* work. The 
problem is that for each successful login, I see 4 entries in the 
database, which suggests that the sproc is called 4 times simultaneously 
or in quick succession. Can anyone explain to me why this is happening 
and is there a way I can fix this?

Thank you in advance...

SCilek

-- 
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Multiple Calling of SQL Statements

Selahattin CILEK