Quantcast

Possible bug in rlm_sqlcounter examples

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Possible bug in rlm_sqlcounter examples

Tim White-2
Hi All.

I am using the following SQL in sqlcounter for a MySQL database in the
Grase Hotspot project, as part of daily/hourly/monthly counters.

         query = "SELECT SUM(acctsessiontime - \
                  GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
                  FROM radacct WHERE username = '%{%k}' AND \
                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"

This is taken directly out of the examples that come with Freeradius,
and is also in the Wiki.
http://wiki.freeradius.org/Rlm_sqlcounter#Example+Setup

Recently I was having problems where the first login for a day, wasn't
being limited to it's daily limit. However, subsequent logins for they
day were. So for example, if they had a 4 hour limit, and the first
login went over 4 hours, it could keep going as Session-Limit was being
returned by freeradius. However, all subsequent logins would return a
valid Session-Limit (timeout?) or an access denied if they had gone over
the daily limit.
Some poking around showed that if there was no logins for that day, the
above SQL will return NULL, which Freeradius complains about, something
along the lines of there not being an integer in the results (I can't
get the exact error message right now), and so the sqlcounter just
passes through as noop.
To solve the problem, I needed to use an IFNULL (or COALESCE) to return
a 0 instead of NULL and then Freeradius sqlcounter returns the correct
attributes.
         query = "SELECT COALESCE( SUM(acctsessiontime - \
                  GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) ) \
                  FROM radacct WHERE username = '%{%k}' AND \
                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"

This happens on the arm architecture, and so may be architecture
dependent. A quick test on x86 MySQL shows it also returns NULL, however
I've not had the chance to test how Freeradius interprets the NULL, as 0
or NULL. I will get out an x86 test machine shortly and test what
Freeradius is returning.

$ apt-cache policy freeradius
freeradius:
   Installed: 2.1.10+dfsg-2

Debian 6.0.3 Linux Kernel 2.6.32 armv5tel

Has anyone else run into this problem?

Tim
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Possible bug in rlm_sqlcounter examples

Tim White-2
Following on from my previous email, I've checked an x86 machine as
well, and get the same behaviour.

Debug logs follow, the first being the initial login for the day,
showing sqlcounter not finding an integer and hence returning noop. The
second being after an initial login where a correct integer is returned.

Can anyone else confirm that the example sqlcounter queries are at fault
and that we need ether an IFNULL or COALESCE surrounding the SUM? I'll
be updating the Grase Hotspot files, but I'm wondering if a change was
made in rlm_sqlcounter in the last few months (year) that has caused it
to treat NULL as NULL and not as 0, and hence the SQL queries need to be
updated?

Thanks

Tim
--

rlm_sqlcounter: Entering module authorize code
sqlcounter_expand:  'SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)),
0))                  FROM radacct WHERE username = '%{User-Name}'
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
'1329832800''
[dailycounter]     expand: SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)),
0))                  FROM radacct WHERE username = '%{User-Name}'
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
'1329832800' -> SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)),
0))                  FROM radacct WHERE username = 'timtest'
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
'1329832800'
sqlcounter_expand:  '%{sql:SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)),
0))                  FROM radacct WHERE username = 'timtest'
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
'1329832800'}'
[dailycounter] sql_xlat
[dailycounter]     expand: %{User-Name} -> timtest
[dailycounter] sql_set_user escaped user --> 'timtest'
[dailycounter]     expand: SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)),
0))                  FROM radacct WHERE username = 'timtest'
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
'1329832800' -> SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)),
0))                  FROM radacct WHERE username = 'timtest'
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
'1329832800'
rlm_sql (sql): Reserving sql socket id: 3
[dailycounter] row[0] returned NULL
rlm_sql (sql): Released sql socket id: 3
[dailycounter]     expand: %{sql:SELECT SUM(acctsessiontime
-                  GREATEST((1329832800 -
UNIX_TIMESTAMP(acctstarttime)), 0))                  FROM radacct WHERE
username = 'timtest' AND                  UNIX_TIMESTAMP(acctstarttime)
+ acctsessiontime > '1329832800'} ->
rlm_sqlcounter: No integer found in string ""
++[dailycounter] returns noop






--------------


rlm_sqlcounter: Entering module authorize code
sqlcounter_expand:  'SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)),
0))                  FROM radacct WHERE username = '%{User-Name}'
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
'1329832800''
[dailycounter]     expand: SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)),
0))                  FROM radacct WHERE username = '%{User-Name}'
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
'1329832800' -> SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)),
0))                  FROM radacct WHERE username = 'timtest'
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
'1329832800'
sqlcounter_expand:  '%{sql:SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)),
0))                  FROM radacct WHERE username = 'timtest'
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
'1329832800'}'
[dailycounter] sql_xlat
[dailycounter]     expand: %{User-Name} -> timtest
[dailycounter] sql_set_user escaped user --> 'timtest'
[dailycounter]     expand: SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)),
0))                  FROM radacct WHERE username = 'timtest'
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
'1329832800' -> SELECT SUM(acctsessiontime -                  
GREATEST((1329832800 - UNIX_TIMESTAMP(acctstarttime)),
0))                  FROM radacct WHERE username = 'timtest'
AND                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime >
'1329832800'
rlm_sql (sql): Reserving sql socket id: 3
[dailycounter] sql_xlat finished
rlm_sql (sql): Released sql socket id: 3
[dailycounter]     expand: %{sql:SELECT SUM(acctsessiontime
-                  GREATEST((1329832800 -
UNIX_TIMESTAMP(acctstarttime)), 0))                  FROM radacct WHERE
username = 'timtest' AND                  UNIX_TIMESTAMP(acctstarttime)
+ acctsessiontime > '1329832800'} -> 85
rlm_sqlcounter: Check item is greater than query result
rlm_sqlcounter: Authorized user timtest, check_item=600, counter=85
rlm_sqlcounter: Sent Reply-Item for user timtest, Type=Session-Timeout,
value=515
++[dailycounter] returns ok


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Possible bug in rlm_sqlcounter examples

Phil Mayers
On 02/21/2012 11:04 PM, Tim White wrote:
> Following on from my previous email, I've checked an x86 machine as
> well, and get the same behaviour.

I should hope so; SQL is not architecture specific!

Your original solution was correct as far as I could see; if there's any
chance a column might be absent/null, coalesce or nullif are required. I
don't use rlm_sqlcounter so can't say whether absent/null values are
expected or a peculiarity of your setup, but a mix of both is possible.

>
> Debug logs follow, the first being the initial login for the day,
> showing sqlcounter not finding an integer and hence returning noop. The
> second being after an initial login where a correct integer is returned.
>
> Can anyone else confirm that the example sqlcounter queries are at fault
> and that we need ether an IFNULL or COALESCE surrounding the SUM? I'll
> be updating the Grase Hotspot files, but I'm wondering if a change was
> made in rlm_sqlcounter in the last few months (year) that has caused it
> to treat NULL as NULL and not as 0, and hence the SQL queries need to be
> updated?

Try looking through the source code history:

https://github.com/alandekok/freeradius-server
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Loading...