Биллинговая система Nodeny

DROP FUNCTION IF EXISTS `get_ip_by_num`;
DELIMITER $$
CREATE FUNCTION `get_ip_by_num` ( user_id INTEGER UNSIGNED, num INTEGER)  
    RETURNS VARCHAR(15) NO SQL
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip VARCHAR(15);

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND type='static' ORDER BY ip LIMIT num, 1;
    IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND type='static' ORDER BY ip LIMIT 1;
    IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;

    SELECT 1 INTO real_ip FROM users_services WHERE uid = user_id AND tags LIKE '%,realip,%';
    UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 300 
        WHERE (uid = 0 OR uid = user_id)
            AND type = 'dynamic'
            AND realip = IF(real_ip>0,1,0)
        ORDER BY uid DESC, id ASC LIMIT 1;

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id LIMIT 1;
    RETURN user_ip;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  DECLARE real_login VARCHAR(64) DEFAULT NULL;
  SELECT SUBSTRING_INDEX(login, '+', 1) INTO real_login;
  SELECT id, name, 'Password' AS Attribute, AES_DECRYPT(passwd,'hardpass') AS Value,'=='
    FROM users WHERE name=real_login;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  DECLARE ip_index INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  DECLARE real_login VARCHAR(64) DEFAULT NULL;

  SELECT SUBSTRING_INDEX(login, '+', 1) INTO real_login;
  SELECT CAST(REPLACE(login, real_login, '') AS UNSIGNED) INTO ip_index;

  SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
  SELECT get_ip_by_num(usr_id, ip_index) INTO usr_ip;

  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;

call test_radreply('ivanov+1');

[sql] 	expand: %{User-Name} -> sov213+1
[sql] sql_set_user escaped user --> 'sov213+1'
rlm_sql (sql): Reserving sql socket id: 9
[sql] 	expand: call radcheck('%{SQL-User-Name}') -> call radcheck('sov213=2B1')
rlm_sql_mysql: query:  call radcheck('sov213=2B1')
rlm_sql (sql): Released sql socket id: 9
[sql] User sov213+1 not found

SELECT SUBSTRING_INDEX(login, '+', 1) INTO real_login;
SELECT CAST(REPLACE(login, real_login, '') AS UNSIGNED) INTO ip_index;

SELECT SUBSTRING_INDEX(login, '=', 1) INTO real_login;
SELECT CAST(REPLACE(REPLACE(login, real_login, ''),'=2B','') AS UNSIGNED) INTO ip_index;

call test_radreply('ivanov+1');

call test_radreply('ivanov=2B1');

DROP FUNCTION IF EXISTS `get_ip_by_num`;
DELIMITER $$
CREATE FUNCTION `get_ip_by_num` ( user_id INTEGER UNSIGNED, num INTEGER)  
    RETURNS VARCHAR(15) NO SQL
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip VARCHAR(15);
    
    IF( num IS NULL ) THEN SET num=0; END IF;
    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND type='static' ORDER BY ip LIMIT num, 1;
    IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND type='static' ORDER BY ip LIMIT 1;
    IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;

    SELECT 1 INTO real_ip FROM users_services WHERE uid = user_id AND tags LIKE '%,realip,%';
    UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 300 
        WHERE (uid = 0 OR uid = user_id)
            AND type = 'dynamic'
            AND realip = IF(real_ip>0,1,0)
        ORDER BY uid DESC, id ASC LIMIT 1;

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id LIMIT 1;
    RETURN user_ip;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  DECLARE real_login VARCHAR(64) DEFAULT NULL;
  SELECT SUBSTRING_INDEX(login, '=2B', 1) INTO real_login;
  SELECT id, name, 'Password' AS Attribute, AES_DECRYPT(passwd,'hardpass') AS Value,'=='
  FROM users WHERE name=real_login;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  DECLARE ip_index INT ;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  DECLARE real_login VARCHAR(64) DEFAULT NULL;

  SELECT SUBSTRING_INDEX(login, '=2B', 1) INTO real_login;
  
  IF login REGEXP '[=2B][0-9]+$' THEN
  	SELECT CAST(REPLACE(REPLACE(login, real_login, ''),'=2B','') AS UNSIGNED) INTO ip_index;
  ELSE SET ip_index=0;END IF;
   
  SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
  SELECT get_ip_by_num(usr_id, ip_index) INTO usr_ip;

  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radupdate`;
DELIMITER $$
CREATE PROCEDURE `radupdate`(IN `login` VARCHAR(64), IN `ip` VARCHAR(16), IN `properties` VARCHAR(255))
BEGIN
  DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  DECLARE real_login VARCHAR(64) DEFAULT NULL;
  DECLARE ip_index INT ;
  
  SELECT SUBSTRING_INDEX(login, '=2B', 1) INTO real_login;
  IF login REGEXP '[=2B][0-9]+$' THEN
  	SELECT CAST(REPLACE(REPLACE(login, real_login, ''),'=2B','') AS UNSIGNED) INTO ip_index;
  ELSE SET ip_index=0;END IF;
 
  SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
  SELECT get_ip_by_num(usr_id, ip_index) INTO usr_ip;
  CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radstop`;
DELIMITER $$
CREATE PROCEDURE `radstop`(IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  DECLARE ip_index INT ;
  DECLARE real_login VARCHAR(64) DEFAULT NULL;

  SELECT SUBSTRING_INDEX(login, '=2B', 1) INTO real_login;
  
  IF login REGEXP '[=2B][0-9]+$' THEN
  	SELECT CAST(REPLACE(REPLACE(login, real_login, ''),'=2B','') AS UNSIGNED) INTO ip_index;
  ELSE SET ip_index=0;END IF;
 
  SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
  DELETE FROM auth_now WHERE ip = get_ip_by_num(usr_id, ip_index) LIMIT 1;
END$$
DELIMITER ;

Found Auth-Type = PAP
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!    Replacing User-Password in config items with Cleartext-Password.     !!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!! Please update your configuration so that the "known good"               !!!
!!! clear text password is in Cleartext-Password, and not in User-Password. !!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 SELECT id, name, 'Password' AS Attribute,

 SELECT id, name, 'Cleartext-Password' AS Attribute,

/* For RADIUS 1.X */
DROP PROCEDURE IF EXISTS `radcheck`;

DELIMITER $$
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'XXXXXX') AS Value,'=='
  FROM users WHERE name=login;
  END$$
DELIMITER ;

/* For RADIUS 2.X */
DROP PROCEDURE IF EXISTS `radcheck2`;

DELIMITER $$
CREATE PROCEDURE `radcheck2` (IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Cleartext-Password' AS Attribute,AES_DECRYPT(passwd,'XXXXXX') AS Value,':='
  FROM users WHERE name=login;
  END$$
DELIMITER ;