Error in (My)SQL Syntax for expire_on_login counter

[grafgraffy]

There is a bug (at least I think so) with the SQL Syntax for the expire_on_login counter. Currently (FR 3.0.7 (raddb/mods-config/sql/counter/mysql/expire_on_login_counter.conf) and 2.2.6 (raddb/modules/sqlcounter_expire_on_login) ) the syntax is:

query = "SELECT TIMESTAMPDIFF(SECOND, acctstarttime, NOW()) \
    FROM radacct \
    WHERE UserName='%{${key}}' \
    ORDER BY acctstarttime \
    LIMIT 1;" 

The problem is when the initial/first "lookup" in the database is done during authorization there is no entry in radacct yet (accounting after authorization) so the result of the query will fail with "no integer found in string ".... because of MySQL giving NULL as result.

I suggest to change the query to:

query = "SELECT IFNULL( MAX(TIME_TO_SEC(TIMEDIFF(NOW(), acctstarttime))),0) \
        FROM radacct \
        WHERE UserName='%{${key}}' \
        ORDER BY acctstarttime \
        LIMIT 1;"

so that if no entry is present (MySQL will reply with NULL Value) the query will then return 0 which is a number and can be used within the radius response instead of NULL.

The additional MAX Function around it makes sure that timechanges at the server will not result in negative results.

I'm not sure if it affects MySQL only, the important thing is that an integer value is responded by the query.