Using unlang / SQL with single return value; couple of questions.

26 Apr 2018

      (This is partly a continuation of the thread "Perl vs. python vs. Lua?"
started late February) [1].

First thanks to Alan for pointing me in the right direction.

I thought I would describe the process for the benefit of others.

Basically we need to assign wifi users a VLAN based on their location
and username.  If they are rejected we want to put a reason in the
Reply-Message (and this *should* in due course end up in the line log
and the postauth table).

We have been using a PHP script.

To replace it I have put the bulk of the logic in to the SELECT clause
of the SQL query.  If there's a "normal" VLAN available it's in the
range 1 to 4096.  Return codes 5000 to 5005 indicate "accepted" but
with special conditions.  Return codes 5010 to 5014 indicate "reject".
Return codes above 10000 indicate a VLAN applied a different way,
and so on.

So in in our default server in the post-auth block we have (for
example):

update control {
   CustomVal := "%{sql:SELECT \
     CASE \
	WHEN (cs_suspended.value = '1') THEN 5010 \
	WHEN (u.deleted != '0') THEN 5011 \
// etc

Then the unlang code "decodes" the return value:
if ( &control:XL-Vlan < 5000 ) {
   update reply {
     Filter-Id := &control:CustomVal
     Reply-Message += "Accept: MAC found."
   }
}
elsif (....

So I have managed quite okay without the map functionality of version 4;
in fact, as the query should cache quite nicely it's possible that it's
much better to put as much logic as possible into the query...

I hope the above is reasonably clear - I have done my best with the time
available!

1st question: I am currently using a REGEXP in the query to perform a
partial match and would prefer to use a LIKE '%foo%' instead... but the
% wildcard seems to conflict with freeradius's own substitution.
Anybody know a workaround?

2nd question: we use a "magic" MAC address for non-wifi authentication;
which contains for no good reason, an exclamation mark.  The problem
is that in the query the '%{request:Calling-Station-Id} gets expanded
such that the '!' becomes '=21'.  So my WHEN clause in the SELECT has
to match on '=21'.  Any workarounds so that the SQL query would see
the "real" value?

[1] I only do this gig part-time and only part of *that* is spent on
freeradius development,

Using unlang / SQL with single return value; couple of questions.

Dom Latter