Using unlang / SQL with single return value; couple of questions.
(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,
On Apr 26, 2018, at 10:31 AM, Dom Latter <freeradius-users@latter.org> wrote:
First thanks to Alan for pointing me in the right direction.
You're welcome.
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?
Use %%. That gets converted to a bare % when passed to SQL.
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?
Not really. "!" is a special character for SQL, and will get escaped by FreeRADIUS. Alan DeKok.
On 26/04/18 20:27, Alan DeKok wrote:
Use %%. That gets converted to a bare % when passed to SQL.
Thanks! Perhaps worth noting for the benefit of the archives that compared to the "old" PHP script method the throughput as measured by a simple radperf test has gone up by a factor of 32.
On Apr 27, 2018, at 11:53 AM, Dom Latter <freeradius-users@latter.org> wrote:
On 26/04/18 20:27, Alan DeKok wrote:
Use %%. That gets converted to a bare % when passed to SQL.
Thanks!
Perhaps worth noting for the benefit of the archives that compared to the "old" PHP script method the throughput as measured by a simple radperf test has gone up by a factor of 32.
That's why we don't recommend forking scripts, much less PHP. :) Alan DeKok.
participants (2)
-
Alan DeKok -
Dom Latter