SQL SELECT statement

classic Classic list List threaded Threaded
8 messages Options
| Threaded
Open this post in threaded view
|

SQL SELECT statement

Charly
Hello,

I would like to update Cisco-AVPair attribute using a SQL request :

                 update reply {
                         Cisco-AVPair += "%{sql:SELECT value FROM
radreply WHERE username = '%{User-Name}' AND attribute = 'Cisco-AVPair'
ORDER BY id}"
                 }

But only the first row is returned.

Charlye

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

Re: SQL SELECT statement

Alan DeKok-2
On May 18, 2020, at 4:42 AM, Charly <[hidden email]> wrote:

>
> Hello,
>
> I would like to update Cisco-AVPair attribute using a SQL request :
>
>                 update reply {
>                         Cisco-AVPair += "%{sql:SELECT value FROM radreply WHERE username = '%{User-Name}' AND attribute = 'Cisco-AVPair' ORDER BY id}"
>                 }
>
> But only the first row is returned.

  Yes.  The internal APIs have no way of returning multiple values from an SQL SELECT statement.

  This will be fixed in v4.

  Alan DeKok.


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

Re: SQL SELECT statement

Charly
Thank you Alan. Do you know if i can use another way to do it ?

Le 18/05/2020 à 15:00, Alan DeKok a écrit :

> On May 18, 2020, at 4:42 AM, Charly <[hidden email]> wrote:
>> Hello,
>>
>> I would like to update Cisco-AVPair attribute using a SQL request :
>>
>>                  update reply {
>>                          Cisco-AVPair += "%{sql:SELECT value FROM radreply WHERE username = '%{User-Name}' AND attribute = 'Cisco-AVPair' ORDER BY id}"
>>                  }
>>
>> But only the first row is returned.
>    Yes.  The internal APIs have no way of returning multiple values from an SQL SELECT statement.
>
>    This will be fixed in v4.
>
>    Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

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

Re: SQL SELECT statement

Alan DeKok-2


> On May 18, 2020, at 11:23 AM, Charly <[hidden email]> wrote:
>
> Thank you Alan. Do you know if i can use another way to do it ?

  If you're using the standard schema, why not just use the standard SQL qeuries?  i.e. use the SQL module the way it's designed.

  Alan DeKok.


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

Re: SQL SELECT statement

Charly
For user authentication, i use SQL and LDAP modules but Cisco-AVPair
values are only stored in SQL.  My config is :

         sql

         update control {
                 SQL-Test = "%{%{sql:SELECT id FROM radcheck WHERE
username = '%{User-Name}'}:-notfound}"
         }

         if (&control:SQL-Test == "notfound") {
                 update reply {
                         Cisco-AVPair += "%{sql:SELECT value FROM
radreply WHERE username = '%{User-Name}' AND attribute = 'Cisco-AVPair'
ORDER BY id}"
                 }

                 #
                 #  The ldap module reads passwords from the LDAP database.
                 LDAP

                 if ((ok || updated) && User-Password) {
                         update control {
                                 Auth-Type := LDAP
                         }
                 }
         }

Maybe it exists another way to do it ?


Le 18/05/2020 à 17:26, Alan DeKok a écrit :

>
>> On May 18, 2020, at 11:23 AM, Charly <[hidden email]> wrote:
>>
>> Thank you Alan. Do you know if i can use another way to do it ?
>    If you're using the standard schema, why not just use the standard SQL qeuries?  i.e. use the SQL module the way it's designed.
>
>    Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

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

Re: SQL SELECT statement

Alan DeKok-2


> On May 18, 2020, at 11:50 AM, Charly <[hidden email]> wrote:
>
> For user authentication, i use SQL and LDAP modules but Cisco-AVPair values are only stored in SQL.  My config is :
>
>         sql

  Which runs the normal sql queries.

>         update control {
>                 SQL-Test = "%{%{sql:SELECT id FROM radcheck WHERE username = '%{User-Name}'}:-notfound}"
>         }

  Why?  The SQL module automatically processes the radcheck table.

>         if (&control:SQL-Test == "notfound") {
>                 update reply {
>                         Cisco-AVPair += "%{sql:SELECT value FROM radreply WHERE username = '%{User-Name}' AND attribute = 'Cisco-AVPair' ORDER BY id}"
>                 }

  Again... why?  The SQL module automatically processes the radreply table.

> Maybe it exists another way to do it ?

  To do *what*?  The SQL module automatically processes the radcheck and radreply tables.

  You're trying to do something special here, but I don't know what.  And you're not explaining anything.  Just "I configured a bunch of stuff, but it doesn't do what I want'.

  Well, what do you want it to do?  Use words.  Explain.  I can't read your mind.  And because you're mis-using the configuration, I don't know what you expect it to do, either.

  Alan DeKok.


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

Re: SQL SELECT statement

Charly
- i have local users stored in SQL database
- i have LDAP users
- in SQL database, i store Cisco-AVPair values for both of them

The SQL module doesn't process the radreply when user is not found in
the radcheck table (=> LDAP users)


Le 18/05/2020 à 18:04, Alan DeKok a écrit :

>
>> On May 18, 2020, at 11:50 AM, Charly <[hidden email]> wrote:
>>
>> For user authentication, i use SQL and LDAP modules but Cisco-AVPair values are only stored in SQL.  My config is :
>>
>>          sql
>    Which runs the normal sql queries.
>
>>          update control {
>>                  SQL-Test = "%{%{sql:SELECT id FROM radcheck WHERE username = '%{User-Name}'}:-notfound}"
>>          }
>    Why?  The SQL module automatically processes the radcheck table.
>
>>          if (&control:SQL-Test == "notfound") {
>>                  update reply {
>>                          Cisco-AVPair += "%{sql:SELECT value FROM radreply WHERE username = '%{User-Name}' AND attribute = 'Cisco-AVPair' ORDER BY id}"
>>                  }
>    Again... why?  The SQL module automatically processes the radreply table.
>
>> Maybe it exists another way to do it ?
>    To do *what*?  The SQL module automatically processes the radcheck and radreply tables.
>
>    You're trying to do something special here, but I don't know what.  And you're not explaining anything.  Just "I configured a bunch of stuff, but it doesn't do what I want'.
>
>    Well, what do you want it to do?  Use words.  Explain.  I can't read your mind.  And because you're mis-using the configuration, I don't know what you expect it to do, either.
>
>    Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

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

Re: SQL SELECT statement

Alan DeKok-2
On May 19, 2020, at 3:38 AM, Charly <[hidden email]> wrote:
>
> - i have local users stored in SQL database
> - i have LDAP users
> - in SQL database, i store Cisco-AVPair values for both of them

  That's fine.

> The SQL module doesn't process the radreply when user is not found in the radcheck table (=> LDAP users)

  That's how the SQL module is defined to work... see https://wiki.freeradius.org/modules/Rlm_sql

  You can't just put random SQL queries into the server and expect them to re-implement the entire SQL module.

  What you need to do is to re-phrase your problem:

        if user is in ldap
                then look user up in SQL with a fixed name
        else
                look user up in SQL with their user name

  This is relatively simple.  If you're not using realms / 802.1X, you can do:

authorize {
        ...
        ldap
        if (ok) {
                update request {
                        Stripped-User-Name := "LDAP"
                }
        }
        sql
        ...

  And then edit the file mods-config/sql/main/mysql/queries.conf, to set:

sql_user_name = "%{Stripped-User-Name}:-%{%{User-Name}}"

  This will cause the SQL module to use Stripped-User-Name if it exists, otherwise it uses User-Name.

  And then the LDAP users can have a default entry in SQL, with user name "LDAP".  You can, of course, change that to anything else you want.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html