I: rlm_sql

3 Feb 2006

      Ok,

Ive tested the function once again on RADIUS, It was an error in the
function (I used utente instead of user) that caused RADIUS malfunction.

The process logic is as follows:

1                    check if users MAC is present in radcheck table

2                    if not present his MAC will be inserted into radcheck

In sql.conf Postauth_query is changed as detailed 

postauth_query = "INSERT into ${postauth_table} (id, user, pass, reply,
date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}',
'%{replyacket-Type}', NOW()); SELECT mac_check('%{User-Name}',
'%{Calling-Station-Id}')"

it is simply added a call to the function mac_check, defined as follows

CREATE FUNCTION `mac_check`(user VARCHAR(20),mac VARCHAR(30)) RETURNS
varchar(50)

    DETERMINISTIC

BEGIN

IF (SELECT (SELECT MAX((Attribute='Calling-Station-Id')) from radcheck WHERE
UserName=user))

THEN

            RETURN 'record already present';

END IF;

            INSERT INTO radcheck (UserName,Attribute,op,Value) VALUES
(user,'Calling-Station-Id',':=', mac);

            RETURN 'record inserted';

END

Regards,

Carlo

  _____  

Da: Carlo Prestopino [mailto:c.prestopino@waitalia.com] 
Inviato: venerdì 3 febbraio 2006 13.57
A: 'FreeRadius developers mailing list'
Oggetto: R: rlm_sql

Yes, its ok.

Ive simply made a mistake in cut/paste operations.

The corret one is as follows

CREATE FUNCTION `mac_check`(utente VARCHAR(20),mac VARCHAR(30)) RETURNS
varchar(50)

    DETERMINISTIC

BEGIN

IF (SELECT (SELECT MAX((Attribute='Calling-Station-Id')) from radcheck WHERE
UserName=user))

THEN

            #INSERT into radpostauth (id, user, pass, reply, date) values
('', utente, passwd,reply_packet, NOW());

            RETURN 'record already present';

END IF;

            INSERT INTO radcheck (UserName,Attribute,op,Value) VALUES
(utente,'Calling-Station-Id',':=', mac);

            RETURN 'record inserted';

END

The five arguments one is because I tried to change the content of
postauth_query with a function that have to perform two actions:

-          INSERT into radcheck (needed to perform MAC control)

-          INSERT into radpostauth (original behaviour of postauth_query)

Regards,

Carlo