Re: SQL statement in radcheck

3 Sep 2008

      Alan DeKok напиша:
...
Ljupco Vangelski wrote:
...
I'm trying to allow a user to log in from few Calling-Station-Ids, but
to load them at run-time (read them from mysql table). For that I'm
trying to use sql statement in radcheck, but it doesn't seem to be working.
Here is a simplified example (mysql, table radcheck):
UserName..........Attribute..............op..........Value
(1) test                    Calling-Station-Id  ==        
001122334455                              (is working)
(2) test                    Calling-Station-Id  ==         %{sql:select
"001122334455";}    (is not working)
That isn't valid SQL, for one.
Why?
select "001122334455";
Will return 001122334455 in any MySQL server version (It was just for 
illustration, I have no intention for using a query of that kind).
Anyway, I've tried with more complex examples (select mytable.field from 
mytable where mytable.username='%{User-Name}';) and it is not working.
...
...
Can sql statements be used to compare against the Attribute Value in
CHECK statements, like in the cases (2) above?
Not really.
The question is whether I can use sql in check items at all?

There are two other issues that aren't clear to me and might be 
connected to this one. Let's take two attributes in the radreply table - 
Framed-Id and Reply-Message).

(1) For one attribute (Reply-Message), the runtime variable is converted 
into it's runtime value, for the other one (Filter-Id) it just returns 
the text.
Table radreply:

------------------------------------------------------------------------------------
UserName......Attribute..........op.........Value
test          Framed-Id          =          %{User-Name}
test          Reply-Message      =          %{User-Name}
# radtest test test localhost 523 testing123
Sending Access-Request of id 229 to 127.0.0.1 port 1812
        User-Name = "test"
        User-Password = "test"
        NAS-IP-Address = 255.255.255.255
        NAS-Port = 523
rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=229, length=40
        Filter-Id = "%{User-Name}"
        Reply-Message = "test"
------------------------------------------------------------------------------------
UserName......Attribute..........op.........Value
test          Framed-Id          =          %{check:User-Name}
test          Reply-Message      =          %{check:User-Name}
# radtest test test localhost 523 testing123
Sending Access-Request of id 229 to 127.0.0.1 port 1812
        User-Name = "test"
        User-Password = "test"
        NAS-IP-Address = 255.255.255.255
        NAS-Port = 523
rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=229, length=40
        Filter-Id = "%{check:User-Name}"
        Reply-Message = "test"
--------------------------------------------------------------------------------------
UserName......Attribute..........op.........Value
test          Framed-Id          =          %{config:logdir}
test          Reply-Message      =          %{config:logdir}
# radtest test test localhost 523 testing123
Sending Access-Request of id 229 to 127.0.0.1 port 1812
        User-Name = "test"
        User-Password = "test"
        NAS-IP-Address = 255.255.255.255
        NAS-Port = 523
rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=229, length=40
        Filter-Id = "%{config:logdir}"
        Reply-Message = "/var/log"
--------------------------------------------------------------------------------------

How can I make the Framed-Id attribute to return a runtime variable 
(example %{User-Name}, %{Calling-Station-Id} or else) ?

(2) The second issue is that for the first attribute (Reply-Message), 
the sql returns a value, for the other one (Filter-Id) it just returns 
the whole field as text (doesn't use the sql module to extract the Value).
Table radreply:

--------------------------------------------------------------------------------------
[radreply]
UserName......Attribute..........op.........Value
test          Framed-Id          =          %{sql:SELECT Name FROM userinfo WHERE UserName='test';}
test          Reply-Message      =          %{sql:SELECT Name FROM userinfo WHERE UserName='test';}
[userinfo]
UserName......Name.............................
test          test               ...        ...
# radtest test test localhost 523 testing123
Sending Access-Request of id 245 to 127.0.0.1 port 1812
        User-Name = "test"
        User-Password = "test"
        NAS-IP-Address = 255.255.255.255
        NAS-Port = 523
rad_recv: Access-Accept packet from host 127.0.0.1:1812, id=245, length=83
        Filter-Id = "%{sql:SELECT Name FROM userinfo WHERE UserName='test';}"
        Reply-Message = "test"

How can I make freeradius use the sql module and extract (return) 
information for Filter-Id just like it does for Reply-Message?

Thanks in advance,

-- 
Ljupco