questions regarding sql xlat
Hello list, I am trying to use some internal database function to calculate the Session-Timeout. If I set the value for Session-Timeout to a integer like 123, its correctly delivered as answer to my request. If I try to use sql xlat I will get the answer: Error: rlm_sql: Failed to create the pair: Unknown value %{sql:SELECT disctime.time FROM `disctime` WHERE disctime.user = %{User-Name}} for attribute Session-Timeout Sun Apr 19 20:11:03 2009 : Error: rlm_sql (sql): Error getting data from database I tried several different things: Use a select statement to query the integer directly. I ends up in the same error. I am using: - freeradius: FreeRADIUS Version 2.0.4, for host i486-pc-linux-gnu, built on Sep 7 2008 at 23:35:34 - Debian GNU/Linux 5.0 (lenny) - postgresql 8.3.7-0lenny1 BR Uwe -- kiste lat: 54.322684, lon: 10.13586 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: questions regarding sql xlat
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Uwe Kastens wrote: > Hello list, > > I am trying to use some internal database function to calculate the > Session-Timeout. > > If I set the value for Session-Timeout to a integer like 123, its > correctly delivered as answer to my request. > > If I try to use sql xlat I will get the answer: > Error: rlm_sql: Failed to create the pair: Unknown value %{sql:SELECT > disctime.time FROM `disctime` WHERE disctime.user = %{User-Name}} for > attribute Session-Timeout > Sun Apr 19 20:11:03 2009 : Error: rlm_sql (sql): Error getting data from > database > > I tried several different things: Use a select statement to query the > integer directly. I ends up in the same error. I am using: > *sigh* single quotation marks around %{User-Name}, it's still a string remember > - freeradius: FreeRADIUS Version 2.0.4, for host i486-pc-linux-gnu, > built on Sep 7 2008 at 23:35:34 > - Debian GNU/Linux 5.0 (lenny) > - postgresql 8.3.7-0lenny1 > > BR > > Uwe > > > -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.9 (Darwin) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAknsLZAACgkQcaklux5oVKJICwCdGQ8po+i46xN3uXUyp0GGDwOr xWIAn22YpKHnqtJse1rCGRFow+KHc6hq =1D2m -END PGP SIGNATURE- - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: questions regarding sql xlat
Arran, >> If I try to use sql xlat I will get the answer: >> Error: rlm_sql: Failed to create the pair: Unknown value %{sql:SELECT >> disctime.time FROM `disctime` WHERE disctime.user = %{User-Name}} for >> attribute Session-Timeout >> Sun Apr 19 20:11:03 2009 : Error: rlm_sql (sql): Error getting data from >> database > >> I tried several different things: Use a select statement to query the >> integer directly. I ends up in the same error. I am using: > > *sigh* single quotation marks around %{User-Name}, it's still a string > remember So this should work? ATM I am nearly sure that this could never work, since the 1st query will deliver the string, that rlm_sql should expand with another query. But it would be great if it will work :-) BR Uwe -- kiste lat: 54.322684, lon: 10.13586 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: questions regarding sql xlat
On 20/4/09 09:34, Uwe Kastens wrote: Arran, If I try to use sql xlat I will get the answer: Error: rlm_sql: Failed to create the pair: Unknown value %{sql:SELECT disctime.time FROM `disctime` WHERE disctime.user = %{User-Name}} for attribute Session-Timeout Sun Apr 19 20:11:03 2009 : Error: rlm_sql (sql): Error getting data from database I tried several different things: Use a select statement to query the integer directly. I ends up in the same error. I am using: *sigh* single quotation marks around %{User-Name}, it's still a string remember So this should work? ATM I am nearly sure that this could never work, since the 1st query will deliver the string, that rlm_sql should expand with another query. But it would be great if it will work :-) rlm_sql does recursive xlat, all xlat expansions in the SQL query are done before the query string is passed off to the SQL libraries. The xlat parser is just looking for a string bounded by %{ }. Your query should be: %{sql:SELECT disctime.time FROM `disctime` WHERE disctime.user = '%{User-Name}'} It's not a problem in FreeRADIUS it's a problem in your SQL syntax :) Arran -- Arran Cudbard-Bell (a.cudbard-b...@sussex.ac.uk), Authentication, Authorisation and Accounting Officer, Infrastructure Services (IT Services), E1-1-08, Engineering 1, University Of Sussex, Brighton, BN1 9QT DDI+FAX: +44 1273 873900 | INT: 3900 GPG: 86FF A285 1AA1 EE40 D228 7C2E 71A9 25BB 1E68 54A2 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
fixed => Re: questions regarding sql xlat
Hi list, >> >> So this should work? ATM I am nearly sure that this could never work, >> since the 1st query will deliver the string, that rlm_sql should expand >> with another query. >> >> But it would be great if it will work :-) > > rlm_sql does recursive xlat, all xlat expansions in the SQL query are > done before the query string is passed off to the SQL libraries. The > xlat parser is just looking for a string bounded by %{ }. > > Your query should be: > %{sql:SELECT disctime.time FROM `disctime` WHERE disctime.user = > '%{User-Name}'} > > It's not a problem in FreeRADIUS it's a problem in your SQL syntax :) Both :-) I missed some backticks: `%{sql: select sessionto('%{User-Name}') }` is working. sessionto is a postgres function which calculates the session-timeout. Thanks BR uwe -- kiste lat: 54.322684, lon: 10.13586 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html