Re: freeradius postgresql sql query glitch

----- "Josip Rodin" <joy(at)entuzijast(dot)net> wrote:
> Hi,
>
> I've observed an SQL logging problem with FreeRADIUS (2.x) and
> PostgreSQL
> (8.1), on several different installations I occasionally get these
> errors:
>
> Mon Dec 7 13:19:01 2009 : Error: [ourlittle_sql] Couldn't update SQL
> accounting STOP record - ERROR: invalid input syntax for integer: ""
>
>
> sql trace log indicates that this is the offending query:
>
> UPDATE radacct
> SET AcctStopTime = ('2009-12-07 13:19:01'::timestamp -
> '6'::interval),
> AcctSessionTime = CASE WHEN '' = '' THEN
> (EXTRACT(EPOCH FROM ('2009-12-07 13:19:01'::TIMESTAMP WITH TIME
> ZONE - AcctStartTime::TIMESTAMP WITH TIME ZONE
> - '6'::INTERVAL)))::BIGINT ELSE '' END,
> AcctInputOctets = (('0'::bigint << 32) + '0'::bigint),
> AcctOutputOctets = (('0'::bigint << 32) + '0'::bigint),
> AcctTerminateCause = 'User-Request',
> AcctStopDelay = 0,
> FramedIPAddress = NULLIF('4.3.2.1', '')::inet,
> ConnectInfo_stop = ''
> WHERE AcctSessionId = '57fc9e4821466d86'
> AND UserName = 'our(at)user(dot)name'
> AND NASIPAddress = '1.2.3.4'
> AND AcctStopTime IS NULL;
>
> I'm using the default unchanged sql/postgresql/dialup.conf setting:
>
> accounting_stop_query = "UPDATE ${acct_table2} \
> SET AcctStopTime = ('%S'::timestamp -
> '%{%{Acct-Delay-Time}:-0}'::interval), \
> AcctSessionTime = CASE WHEN '%{Acct-Session-Time}' = '' THEN \
>
> (EXTRACT(EPOCH FROM ('%S'::TIMESTAMP WITH TIME ZONE -
> AcctStartTime::TIMESTAMP WITH TIME ZONE \
> - '%{%{Acct-Delay-Time}:-0}'::INTERVAL)))::BIGINT ELSE
> '%{Acct-Session-Time}' END, \
> AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) +
> '%{%{Acct-Input-Octets}:-0}'::bigint), \
> AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32)
> + '%{%{Acct-Output-Octets}:-0}'::bigint), \
> AcctTerminateCause = '%{Acct-Terminate-Cause}', \
> AcctStopDelay = 0, \
> FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
> ConnectInfo_stop = '%{Connect-Info}' \
> WHERE AcctSessionId = '%{Acct-Session-Id}' \
> AND UserName = '%{SQL-User-Name}' \
> AND NASIPAddress = '%{NAS-IP-Address}' \
> AND AcctStopTime IS NULL"
>
> Looks like the code wants to use CASE to check whether
> %{Acct-Session-Time}
> exists among the internal FreeRADIUS variables, while the return value
> of
> the whole SQL CASE construct is supposed to be a bigint.
>
> This is a reduced failing case:
>
> radiustmobile=# select CASE WHEN '' = '' THEN (EXTRACT(EPOCH FROM
> ('2009-12-07 13:19:01'::TIMESTAMP WITH TIME ZONE -
> AcctStartTime::TIMESTAMP WITH TIME ZONE - '6'::INTERVAL)))::BIGINT
> ELSE '' END from radacct where AcctSessionId = '57fc9e4821466d86';
> ERROR: invalid input syntax for integer: ""
>
> In the else case, this fallback return value comes into PostgreSQL as
> just
> an empty string, which causes it to trip over - it sees that there's
> a
> possibility to write an empty string into a bigint field, which
> provokes
> the syntax error, even if the problem won't actually happen with this
> particular setup of input data.
>
> I'm not sure what to do... can the query be rewritten in a manner that
> would
> allow for both use cases?

If I understand correctly the below may work. If %{Acct-Session-Time} is an empty string it will return NULL otherwise it will return %{Acct-Session-Time}.

ELSE
NULLIF('%{Acct-Session-Time}','') END,

See here details:
http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html#AEN12697

Adrian Klaver
aklaver(at)comcast(dot)net