rlm_sql: Bug in stripping output of dynamic strings {sql:...}
Hi all, - latest CVS head - mysql Ver 14.7 Distrib 4.1.8, for pc-linux-gnu (i386) - FreeRADIUS Version 2.0.0-pre0, for host i386-pc-linux-gnu, built on Apr 13 2007 at 10:11:51 I'm using dynamic variables like {sql:<sql statement>} throughout my configuration to fetch data from the DB. For example acct_users: DEFAULT Acct-Status-Type == Start session_count := "%{sql:select count(*) from radacct where username='%{SQL-User-Name}'" or radgroupcheck table: +----+-----------+---------------+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | GroupName | Attribute | op | Value | +----+-----------+---------------+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 44 | config | session_count | := | `%{sql:select count(*) from radacct where username='%{SQL-User-Name}'}` | | 47 | config | product_code | := | `%{sql:<retun_some_string>}` | +----+-----------+---------------+----+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ or even in sqlcounter: sqlcounter noresetcounterflat { counter-name = Max-All-Session-Time-Flat check-name = Max-All-Session-Flat sqlmod-inst = sql key = User-Name reset = never query = "SELECT UNIX_TIMESTAMP()-UNIX_TIMESTAMP(AcctStartTime) FROM radacct WHERE username='%{%k}' ORDER BY radacctid limit 1" } Unfortunately I'm getting the output stripped by last character(byte): instead of getting 37 for session_count I get 3, instead of getting 1563 for noresetcounterflat I get 156, instead of getting "S3H" for product_code I get "S3". When the query returns 1 character I get empty output.
BEGIN DEBUG radius_xlat: Running registered xlat function of module sql for string 'select count(*) from radacct where username='%{SQL-User-Name}'' rlm_sql (sql): - sql_xlat radius_xlat: 'fkafvt' rlm_sql (sql): sql_set_user escaped user --> 'fkafvt' radius_xlat: 'select count(*) from radacct where username='fkafvt'' rlm_sql (sql): Reserving sql socket id: 2 rlm_sql (sql): - sql_xlat finished rlm_sql (sql): Released sql socket id: 2 radius_xlat: '3' END DEBUG
DB query clearly shows: mysql> select count(*) from radacct where username = 'fkafvt'; +----------+ | count(*) | +----------+ | 37 | +----------+ 1 row in set (0.00 sec) This behaviour is the same no matter in which part of config the dynamic string appears(radgroupcheck, acct_users, sqlcounter configuration). My workaround for this was to output some additional dummy character from within mysql by surrounding the queries by mysql concat function: %{sql:select concat((select count(*) from radacct where %username='%{SQL-User-Name}'),'\n')} ie: %{sql: select concat((<your query>), '\n')} I hope this could help someone until the code is fixed... Milan Holub holub (at) thenet (dot) ch -------------------------------------- TheNet-Internet Services AG, im Bernertechnopark, Morgenstr. 129 CH-3018, Bern, Switzerland 031 998 4333, Fax 031 998 4330 http://www.thenet.ch http://wlan.thenet.ch --------------------------------------
Milan Holub wrote:
Unfortunately I'm getting the output stripped by last character(byte): instead of getting 37 for session_count I get 3, instead of getting 1563 for noresetcounterflat I get 156, instead of getting "S3H" for product_code I get "S3". When the query returns 1 character I get empty output.
Ah. That looks like an issue with "strlcpy". Try a "cvs update", I've fixed a line in sql_xlat. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
Hi, Trying to use Client-Ip-Address is huntgroups and hints doesn't seem to work, if this because the Client-Ip-Address is written to the request packet at the end of pre-process and not the beginning ? Or is there more strangeness afoot ? Also my understanding of huntgroups is that when you check if 'Huntgroup-Name' matches a value , free radius will go away and look at the huntgroups and determine if the one your trying to match against is valid for the request. Following this logic, you should be able to declare multiple Huntgroups with NAS-Ip-Address = * ANY and different criteria. But it seems you can't .... as when you do ... something weird happens in pre-process and the user is rejected. Also with hints, I can see them being very useful .... but there's no fall-through ??! Arg... So the only way to use multiple hunt group entries, is with multiple instances of pre-process, which isn't a particularly neat solution.... Sorry making one desperate last ditch attempt to get everything working without perl .... perl seems like cheating, plus nasty performance hit. --- Arran
So the only way to use multiple hunt group entries, is with multiple instances of pre-process, which isn't a particularly neat solution....
Sorry that should be So the only way to use multiple hints file entries, is with multiple instances of pre-process, which isn't a particularly neat solution....
Arran Cudbard-Bell wrote:
Hi,
Trying to use Client-Ip-Address is huntgroups and hints doesn't seem to work, if this because the Client-Ip-Address is written to the request packet at the end of pre-process and not the beginning ? Or is there more strangeness afoot ?
In the CVS head, use Packet-Src-IP-Address. It should work everywhere.
Also my understanding of huntgroups is that when you check if 'Huntgroup-Name' matches a value , free radius will go away and look at the huntgroups and determine if the one your trying to match against is valid for the request.
I think there are bugs filed against the huntgroup code. See bugs.freeradius.org. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
Alan DeKok wrote:
Arran Cudbard-Bell wrote:
Hi,
Trying to use Client-Ip-Address is huntgroups and hints doesn't seem to work, if this because the Client-Ip-Address is written to the request packet at the end of pre-process and not the beginning ? Or is there more strangeness afoot ?
In the CVS head, use Packet-Src-IP-Address. It should work everywhere.
Also my understanding of huntgroups is that when you check if 'Huntgroup-Name' matches a value , free radius will go away and look at the huntgroups and determine if the one your trying to match against is valid for the request.
I think there are bugs filed against the huntgroup code. See bugs.freeradius.org.
Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Nope, Client-IP-Address / Packet-Src-IP-Address don't work as check items in huntgroups or hints . --- Arran
Hi,
Trying to use Client-Ip-Address is huntgroups and hints doesn't seem to work, if this because the Client-Ip-Address is written to the request packet at the end of pre-process and not the beginning ? Or is there more strangeness afoot ?
are you sure you want Client-IP-Address and not NAS-IP-Address ? utilizing the NAS-IP-Address allows you to define authorization etc based on the access point that the user has connected via. alan
A.L.M.Buxey@lboro.ac.uk wrote:
Hi,
Trying to use Client-Ip-Address is huntgroups and hints doesn't seem to work, if this because the Client-Ip-Address is written to the request packet at the end of pre-process and not the beginning ? Or is there more strangeness afoot ?
are you sure you want Client-IP-Address and not NAS-IP-Address ?
utilizing the NAS-IP-Address allows you to define authorization etc based on the access point that the user has connected via.
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
NAS's can lie :) . I'm still trying to do this without perl....and this is the last thing ! The very last thing I need to make it all work. nas_hints #/* Authentication Mediums */ # '802.1', # 802.1 (Wired LAN) # '802.11', # 802.11 (Wireless LAN) # 'IPSEC', # IPSEC (VPN) # 'SSH', # Secure Shell/Nas Prompt Login # 'HTTPS', # Captive Portal/Nas Web Interface # 'PROXY', # Client Isn't a NAS it's an offsite Proxy # 'unused', # For future use # 'unused', # For future use #/* Extended Features */ # 'RADACCT',# NAS Can do RADIUS Accounting # 'D802.Q', # NAS Can do Dynamic Vlan Assignment # 'MULTIBESSID'); # NAS Can have multiple SSIDs / BSSIDs ############################################################# # Debug entry for home testing. DEFAULT Packet-Src-IP-Address = '81.6.252.244' NAS-Feature-Set = '00000100000' ############################################################# # Set the 'PROXY' flag in the feature set for the JRS proxies DEFAULT Packet-Src-IP-Address == roaming0.ja.net NAS-Feature-Set = '00000100000' DEFAULT Packet-Src-IP-Address == roaming1.ja.net NAS-Feature-Set = '00000100000' DEFAULT Packet-Src-IP-Address == roaming2.ja.net NAS-Feature-Set = '00000100000' ############################################################# # Retrieve the feature set for all none recognised clients # from the NetReg3 Database DEFAULT NAS-Feature-Set =* ANY NAS-Feature-Set = "%{sql_clients:SELECT EXPORT_SET(master.nas_flags,'1','0','',20) FROM `master` WHERE CONCAT(ip1,'.',ip2,'.',ip3,'.',ip4) = '%{Packet-Src-IP-Address}'}" Need to be able to set static NAS profiles for the few weird clients that can't be included in the NetReg clients database. *sigh* Don't suppose you know how to match multiple values in a request attribute without regexp ? as in could be a,b or c ? Always assumed you couldn't , but may as well ask :) Thanks, Arran
Arran Cudbard-Bell wrote:
A.L.M.Buxey@lboro.ac.uk wrote:
Hi,
Trying to use Client-Ip-Address is huntgroups and hints doesn't seem to work, if this because the Client-Ip-Address is written to the request packet at the end of pre-process and not the beginning ? Or is there more strangeness afoot ?
are you sure you want Client-IP-Address and not NAS-IP-Address ?
utilizing the NAS-IP-Address allows you to define authorization etc based on the access point that the user has connected via.
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
NAS's can lie :) .
I'm still trying to do this without perl....and this is the last thing ! The very last thing I need to make it all work.
nas_hints #/* Authentication Mediums */ # '802.1', # 802.1 (Wired LAN) # '802.11', # 802.11 (Wireless LAN) # 'IPSEC', # IPSEC (VPN) # 'SSH', # Secure Shell/Nas Prompt Login # 'HTTPS', # Captive Portal/Nas Web Interface # 'PROXY', # Client Isn't a NAS it's an offsite Proxy # 'unused', # For future use # 'unused', # For future use #/* Extended Features */ # 'RADACCT',# NAS Can do RADIUS Accounting # 'D802.Q', # NAS Can do Dynamic Vlan Assignment # 'MULTIBESSID'); # NAS Can have multiple SSIDs / BSSIDs
############################################################# # Debug entry for home testing. DEFAULT Packet-Src-IP-Address = '81.6.252.244' NAS-Feature-Set = '00000100000'
############################################################# # Set the 'PROXY' flag in the feature set for the JRS proxies DEFAULT Packet-Src-IP-Address == roaming0.ja.net NAS-Feature-Set = '00000100000'
DEFAULT Packet-Src-IP-Address == roaming1.ja.net NAS-Feature-Set = '00000100000'
DEFAULT Packet-Src-IP-Address == roaming2.ja.net NAS-Feature-Set = '00000100000'
############################################################# # Retrieve the feature set for all none recognised clients # from the NetReg3 Database DEFAULT NAS-Feature-Set =* ANY NAS-Feature-Set = "%{sql_clients:SELECT EXPORT_SET(master.nas_flags,'1','0','',20) FROM `master` WHERE CONCAT(ip1,'.',ip2,'.',ip3,'.',ip4) = '%{Packet-Src-IP-Address}'}"
Need to be able to set static NAS profiles for the few weird clients that can't be included in the NetReg clients database.
*sigh*
Don't suppose you know how to match multiple values in a request attribute without regexp ? as in could be a,b or c ? Always assumed you couldn't , but may as well ask :)
Thanks, Arran
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Yep can confirm latest CVS commit fixed this, Thanks Alan :D Unfortunately Huntgroups are very broken ATM, If the user is in the first huntgroup all is fine, Even if that huntgroup has multiple entries. If they're in the second huntgroup however, it doesn't match. This is huntgroups in the order that they appear in the huntgroups file. This isn't a major issue for me yet. But I know other people rely on them and would like them unbroken :) -- Arran Cudbard-Bell (ac221@sussex.ac.uk) Authentication Authorisation & Accounting Officer Infrastructure Services | ENG1 FF08 EXT:3900
Arran Cudbard-Bell wrote:
A.L.M.Buxey@lboro.ac.uk wrote:
Hi,
Trying to use Client-Ip-Address is huntgroups and hints doesn't seem to work, if this because the Client-Ip-Address is written to the request packet at the end of pre-process and not the beginning ? Or is there more strangeness afoot ?
are you sure you want Client-IP-Address and not NAS-IP-Address ?
utilizing the NAS-IP-Address allows you to define authorization etc based on the access point that the user has connected via.
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
NAS's can lie :) .
I'm still trying to do this without perl....and this is the last thing ! The very last thing I need to make it all work.
nas_hints #/* Authentication Mediums */ # '802.1', # 802.1 (Wired LAN) # '802.11', # 802.11 (Wireless LAN) # 'IPSEC', # IPSEC (VPN) # 'SSH', # Secure Shell/Nas Prompt Login # 'HTTPS', # Captive Portal/Nas Web Interface # 'PROXY', # Client Isn't a NAS it's an offsite Proxy # 'unused', # For future use # 'unused', # For future use #/* Extended Features */ # 'RADACCT',# NAS Can do RADIUS Accounting # 'D802.Q', # NAS Can do Dynamic Vlan Assignment # 'MULTIBESSID'); # NAS Can have multiple SSIDs / BSSIDs
############################################################# # Debug entry for home testing. DEFAULT Packet-Src-IP-Address = '81.6.252.244' NAS-Feature-Set = '00000100000'
############################################################# # Set the 'PROXY' flag in the feature set for the JRS proxies DEFAULT Packet-Src-IP-Address == roaming0.ja.net NAS-Feature-Set = '00000100000'
DEFAULT Packet-Src-IP-Address == roaming1.ja.net NAS-Feature-Set = '00000100000'
DEFAULT Packet-Src-IP-Address == roaming2.ja.net NAS-Feature-Set = '00000100000'
############################################################# # Retrieve the feature set for all none recognised clients # from the NetReg3 Database DEFAULT NAS-Feature-Set =* ANY NAS-Feature-Set = "%{sql_clients:SELECT EXPORT_SET(master.nas_flags,'1','0','',20) FROM `master` WHERE CONCAT(ip1,'.',ip2,'.',ip3,'.',ip4) = '%{Packet-Src-IP-Address}'}"
Need to be able to set static NAS profiles for the few weird clients that can't be included in the NetReg clients database.
*sigh*
Don't suppose you know how to match multiple values in a request attribute without regexp ? as in could be a,b or c ? Always assumed you couldn't , but may as well ask :)
Thanks, Arran
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hmm ignore the last message, the issue is that now Packet-Src-Ip-Address Always matches ! Everywhere..... Which exaplains the weirdness in hints and huntgroups. -- Arran Cudbard-Bell (ac221@sussex.ac.uk) Authentication Authorisation & Accounting Officer Infrastructure Services | ENG1 FF08 EXT:3900
Arran Cudbard-Bell wrote:
the issue is that now Packet-Src-Ip-Address Always matches ! Everywhere.....
OK. I think I see why. I'll have a patch tomorrow. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
the issue is that now Packet-Src-Ip-Address Always matches ! Everywhere.....
Should be fixed. Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog
Alan DeKok wrote:
the issue is that now Packet-Src-Ip-Address Always matches ! Everywhere.....
Should be fixed.
Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Thanks :) BTW I can't seem to reproduce Milans client issues running cvs head on Darwin. -- Arran Cudbard-Bell (ac221@sussex.ac.uk) Authentication Authorisation & Accounting Officer Infrastructure Services | ENG1 FF08 EXT:3900
Hi Alan, On Sat, Apr 14, 2007 at 03:26:11AM +0200, Alan DeKok wrote:
Milan Holub wrote:
Unfortunately I'm getting the output stripped by last character(byte): instead of getting 37 for session_count I get 3, instead of getting 1563 for noresetcounterflat I get 156, instead of getting "S3H" for product_code I get "S3". When the query returns 1 character I get empty output.
Ah. That looks like an issue with "strlcpy". Try a "cvs update", I've fixed a line in sql_xlat.
==> thanks for the fix. I've tested and it seems working! Milan Holub holub (at) thenet (dot) ch -------------------------------------- TheNet-Internet Services AG, im Bernertechnopark, Morgenstr. 129 CH-3018, Bern, Switzerland 031 998 4333, Fax 031 998 4330 http://www.thenet.ch http://wlan.thenet.ch --------------------------------------
participants (4)
-
A.L.M.Buxey＠lboro.ac.uk -
Alan DeKok -
Arran Cudbard-Bell -
Milan Holub