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

$do_not_use_shape = 0;          # 1 - если не нужно шейпить клиентов

$test_whithout_connect = 0;     # 1 - тестирование без соединения с микротиком

$pause_after_x_bytes = 10000;   # пауза каждые x байт (если тупит микротик)

	Framed-IP-Address = 10.1.0.10
	Session-Timeout = 600
	Acct-Interim-Interval = 60

/ip address
add address=10.1.0.11/16 interface=ether1 network=10.1.0.0
/ip dhcp-server
add disabled=no interface=ether1 name=dhcp1 use-radius=yes
/ip dhcp-server network
add dns-server=8.8.4.4 gateway=10.1.0.11 netmask=16
/radius
add address=10.1.0.1 secret=hardpass5 service=dhcp

shell> mysql -u root -p
Enter password: hardpass

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| nodeny   |
+----------+

mysql> USE nodeny

........ дальше по инструкции.......

--- Модуль dhcp ---

sql error. Run with -v at /usr/local/nodeny/modules/dhcp/run.pl line 27.

 
        authorize_check_query = "call radcheck('%{User-Name}')"                        
        authorize_reply_query = "call radreply('%{User-Name}', '%{Called-Station-Id}')"
        postauth_query = "call radupdate('%{User-Name}','%{reply:Framed-IP-Address}',\
                '%{Called-Station-Id}', 'nas=%{NAS-IP-Address}')"
        accounting_update_query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',\
                '%{Called-Station-Id}', 'nas=%{NAS-IP-Address}')"}

alter table mac_uid change mac `mac` varchar(16) DEFAULT NULL;

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

    SELECT net * 256 INTO net;

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

    UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 300
    WHERE id = (SELECT id FROM (
        (SELECT id, uid FROM ip_pool 
        WHERE uid = 0
            AND type = 'dynamic'
            AND realip = IF(real_ip>0,1,0)
            AND ip >= (INET_ATON('10.0.0.0') + net)
            AND ip <= (INET_ATON('10.0.0.255') + net)
        LIMIT 1)
            UNION
        (SELECT id, uid FROM ip_pool
        WHERE uid = user_id
            AND type = 'dynamic'
            AND realip = IF(real_ip>0,1,0)
            AND ip >= (INET_ATON('10.0.0.0') + net)
            AND ip <= (INET_ATON('10.0.0.255') + net)
        LIMIT 1)
    ) AS tbl ORDER BY uid DESC LIMIT 1);

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id
        AND ip >= (INET_ATON('10.0.0.0') + net)
        AND ip <= (INET_ATON('10.0.0.255') + net)
        LIMIT 1;
    RETURN user_ip;
END$$
DELIMITER ;

DROP FUNCTION strSplit;
CREATE FUNCTION strSplit(x MEDIUMTEXT, delim MEDIUMTEXT, pos int)
    RETURNS MEDIUMTEXT
RETURN 
    TRIM(BOTH '\r' FROM TRIM(
        REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), 
                LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1), delim, '')
    ));


DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64), IN net VARCHAR(64))
BEGIN
    DECLARE usr_mac VARCHAR(16);
    DECLARE usr_ip VARCHAR(15);
    DECLARE usr_id INT;
    DECLARE usr_state VARCHAR(10);
    DECLARE add_attr MEDIUMTEXT;
    DECLARE line MEDIUMTEXT;
    DECLARE i INT DEFAULT 1;

    SELECT REPLACE(net, 'dhcp_', '') INTO net;
    SELECT REPLACE(login, ':', '') INTO usr_mac;
    SELECT CONCAT(net, usr_mac) INTO usr_mac;
    SELECT uid INTO usr_id FROM mac_uid WHERE mac=usr_mac;
    IF usr_id IS NOT NULL AND usr_id>0 THEN
        SELECT get_ip_w_net(usr_id, net) INTO usr_ip;
        UPDATE mac_uid SET ip=0 WHERE ip=INET_ATON(usr_ip) AND uid<>usr_id;
        UPDATE mac_uid SET ip=INET_ATON(usr_ip), time=UNIX_TIMESTAMP() WHERE uid=usr_id;
    ELSE
        UPDATE mac_uid SET ip=0 WHERE time < (UNIX_TIMESTAMP()-3600);
        START TRANSACTION;
        SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool p WHERE uid=0 AND type='dynamic'
            AND NOT EXISTS (SELECT ip FROM mac_uid WHERE ip=p.ip)
            AND ip >= (INET_ATON('10.0.0.0') + net*256)
            AND ip <= (INET_ATON('10.0.0.255') + net*256)
            ORDER BY RAND() LIMIT 1 FOR UPDATE;
        INSERT INTO mac_uid VALUES(
            NULL, usr_mac, INET_ATON(usr_ip), 0, UNIX_TIMESTAMP(), 0, 0, 0)
        ON DUPLICATE KEY
            UPDATE ip=INET_ATON(usr_ip), time=UNIX_TIMESTAMP();
        COMMIT;
        SELECT INET_NTOA(ip) INTO usr_ip FROM mac_uid WHERE mac=usr_mac;
    END IF;

    SELECT radius_attr INTO add_attr FROM users_services
        WHERE uid=usr_id AND tags LIKE '%,inet,%' LIMIT 1;

    SELECT NULL, login, 'Framed-IP-Address', usr_ip, '=';
    SELECT NULL, login, 'Session-Timeout', '900', '=';

    attr_loop: WHILE TRUE DO
        SELECT strSplit(add_attr, '\n', i) INTO line;
        IF LENGTH(line) = 0 OR i > 20 THEN LEAVE attr_loop; END IF;
        IF line LIKE '%+=%' THEN
            SELECT NULL,login,strSplit(line, '+=', 1),strSplit(line, '+=', 2),'+=';
        ELSEIF line LIKE '%=%' THEN
            SELECT NULL,login,strSplit(line, '=', 1),strSplit(line, '=', 2),'=';
        END IF;
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;


DROP PROCEDURE IF EXISTS `radupdate`;
DELIMITER $$
CREATE PROCEDURE `radupdate`(
    IN login VARCHAR(64), IN ipa VARCHAR(16), IN net VARCHAR(64), IN properties VARCHAR(255))
BEGIN
    DECLARE usr_mac VARCHAR(16);
    SELECT REPLACE(login, ':', '') INTO usr_mac;
    SELECT REPLACE(net, 'dhcp_', '') INTO net;
    CALL set_auth(ipa, CONCAT('mod=dhcp;vlan=', net, ';user=', usr_mac, ';', REPLACE(properties,':','')));
    UPDATE mac_uid SET time=UNIX_TIMESTAMP() WHERE ip=INET_ATON(ipa) LIMIT 1;
END$$
DELIMITER ;

CALL radreply('00:11:22:33:44:55', 'dhcp_1');

grep ip_tags cfg/noserver.cfg.pm

/ip hotspot profile add hotspot-address=10.254.111.1 html-directory=hotspotmacwithoutpass login-by=mac mac-auth-mode=mac-as-username-and-password name=IPOE nas-port-type=ethernet radius-interim-update=5m use-radius=yes
/ip hotspot add address-pool=v111 addresses-per-mac=1 disabled=no interface=bridge name=IPOE-hotspot profile=IPOE
/ip hotspot user add name=admin
/radius add address=172.16.0.1 secret=hardpass service=hotspot,dhcp timeout=2s
/radius incoming set accept=yes

/ip firewall filter
add action=accept chain=forward dst-port=80 out-interface=ether1-gw protocol=tcp src-address-list=!goodboys
add action=drop chain=forward out-interface=ether1-gw src-address-list=!goodboys
/ip firewall nat
add action=dst-nat chain=dstnat dst-port=80 protocol=tcp src-address-list=!goodboys to-addresses=10.20.30.40 to-ports=80

SELECT REPLACE(login, ':', '') INTO usr_mac;

SELECT LOWER(REPLACE(login, ':', '')) INTO usr_mac;

        SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool p WHERE uid=0 AND type='dynamic'
            AND NOT EXISTS (SELECT ip FROM mac_uid WHERE ip=p.ip)
            ORDER BY RAND() LIMIT 1 FOR UPDATE;

/ip dhcp-server network add dns-server=10.0.1.1 gateway=10.0.1.1 netmask=24

mysql>  SELECT *, INET_NTOA(ip) FROM mac_uid;
+----+--------------+-----------+-----+------------+------------+-------------+------------+---------------+
| id | mac          | ip        | uid | time       | device_mac | device_port | oneconnect | INET_NTOA(ip) |
+----+--------------+-----------+-----+------------+------------+-------------+------------+---------------+
|  1 | 005533007766 | 167776437 |   0 | 1484492413 | 0          |           0 |          0 | 10.0.16.181   |
+----+--------------+-----------+-----+------------+------------+-------------+------------+---------------+
1 row in set (0,00 sec)

mysql>  SELECT *, INET_NTOA(ip) FROM mac_uid;
+----+--------------+-----------+-----+------------+------------+-------------+------------+---------------+
| id | mac          | ip        | uid | time       | device_mac | device_port | oneconnect | INET_NTOA(ip) |
+----+--------------+-----------+-----+------------+------------+-------------+------------+---------------+
|  2 | 005533007766 | 167772163 |   2 | 1484492608 |            |           0 |          0 | 10.0.0.3      |
+----+--------------+-----------+-----+------------+------------+-------------+------------+---------------+
1 row in set (0,00 sec)

BEGIN
  SELECT u.id, u.name, 'Password' AS Attribute, AES_DECRYPT(passwd,'hardpass') AS Value,'==' AS Op FROM users u WHERE u.name=login ;
IF ((SELECT m.uid FROM users u JOIN mac_uid m WHERE uid=u.id ORDER BY m.uid DESC LIMIT 1) <> '') 
THEN
  SELECT users.id,users.name,'Calling-Station-Id' AS Attribute,mac_uid.mac AS Value,'==' AS Op FROM users,mac_uid WHERE mac_uid.uid=users.id AND users.name=login ORDER BY mac_uid.uid DESC LIMIT 1;
END IF;   
END

y# iptables -n -L -v --line-numbers
Chain INPUT (policy ACCEPT 0 packets, 0 bytes)
num   pkts bytes target     prot opt in     out     source               destination
1     1638  110K ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:8080
2      186  9412 ACCEPT     all  --  lo     *       0.0.0.0/0            0.0.0.0/0
3      314  226K ACCEPT     all  --  enp2s0 *       0.0.0.0/0            0.0.0.0/0

Chain FORWARD (policy ACCEPT 0 packets, 0 bytes)
num   pkts bytes target     prot opt in     out     source               destination
1        0     0 ACCEPT     tcp  --  *      *       217.55.66.250        0.0.0.0/0            tcp dpt:8080
2        0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            217.66.99.250        tcp dpt:8080
3        0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            217.66.99.250        tcp spt:8080
4        0     0 ACCEPT     tcp  --  *      *       217.55.66.250        0.0.0.0/0            tcp dpt:8080

Chain OUTPUT (policy ACCEPT 309 packets, 37457 bytes)
num   pkts bytes target     prot opt in     out     source               destination
1     3414  211K ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp spt:8080
2        0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:1812
3        0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp spt:1812
4        0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp dpt:1813
5        0     0 ACCEPT     tcp  --  *      *       0.0.0.0/0            0.0.0.0/0            tcp spt:1813

${f} add 350 allow tcp from any to any 22,80,443,8080 in
${f} add 450 allow udp from 1.1.1.1 to any 1812,1813 in
${f} add 600 fwd 1.1.1.1 tcp from any 8080 to any

/ip firewall nat
add action=dst-nat chain=dstnat dst-address=!1.1.1.2 src-address=!1.1.1.2 dst-port=80 fragment=no protocol=tcp src-address-list=!goodboys to-addresses=1.1.1.2 to-ports=8080

route add -net 10.0.1.0/24 gw 217.55.66.250 dev enp2s0

/sbin/route add 10.0.1.0/24 217.55.66.250

iptables -A INPUT -p tcp --dport 8080 -j ACCEPT
iptables -A INPUT -p tcp --sport 8080 -j ACCEPT
iptables -A OUTPUT -p tcp --dport 8080 -j ACCEPT
iptables -A OUTPUT -p tcp --sport 8080 -j ACCEPT

iptables -A INPUT -p tcp --dport 1813 -d 217.55.66.250/32 -j ACCEPT
iptables -A INPUT -p tcp --sport 1813 -s 217.55.66.250/32 -j ACCEPT
iptables -A OUTPUT -p tcp --dport 1813 -d 217.55.66.250/32 -j ACCEPT
iptables -A OUTPUT -p tcp --sport 1813 -s 217.55.66.250/32 -j ACCEPT

iptables -A FORWARD -p tcp --dport 8080 -d 217.55.66.250/32 -j ACCEPT
iptables -A FORWARD -p tcp --dport 8080 -s 217.55.66.250/32 -j ACCEPT

iptables -A INPUT -i enp2s0 -j ACCEPT
iptables -A OUTPUT -i enp2s0 -j ACCEPT

iptables -n -L -v --line-numbers

tcpdump -i enp2s0 -n port 8080

route add -net 10.0.1.0/24 gw 217.66.55.250 dev enp2s0

add action=accept chain=forward out-interface=ether1 protocol=icmp src-address=217.66.55.252
add action=accept chain=forward dst-address=217.66.55.252 out-interface=ether1 protocol=icmp

/ip firewall nat
add action=accept chain=srcnat disabled=yes protocol=icmp
add action=accept chain=dstnat disabled=yes protocol=icmp

add action=accept chain=forward src-address=217.66.55.252 in-interface=ether1 protocol=icmp
add action=accept chain=forward dst-address=217.66.55.252 out-interface=ether1 protocol=icmp

route add -net 10.0.1.0/24 gw 217.10.30.2 dev enp2s0

/sbin/route add 10.0.1.0/24 217.10.30.2

my $db = Db->sql("SELECT INET_NTOA(ip) AS ipa FROM mac_uid WHERE uid>0 AND ip>0 AND time>(UNIX_TIMESTAMP()-?)", [b]14405[/b]*60);

    SELECT NULL, login, 'Session-Timeout', '600', '=';

tool netwatch add host=200.200.200.200 
up-script=#Pri aktivnoy svyazi s bilingo, udalyaem vse staticheskie adresa
/ip dhcp-server lease remove [/ip dhcp-server lease find]
down-script=#Vse aktivnye adresaperevodim v statiku,chtoby u klienta rabotal internet v sluchae otsutstviya svyazi s bilingom
/ip dhcp-server lease make-static [/ip dhcp-server lease find]

DROP PROCEDURE IF EXISTS `radupdate`;
.........
CALL set_auth(usr_ip, CONCAT('mod=[b]pppoe[/b];', REPLACE(properties,':','')));

:log warning "UP - ISP1(Ubi.nets): IP poyavilsya v internete";

/tool e-mail send to=Priem@email.net from="MikroTik: Pobedy24<robot@mysite.ru>" \
subject="UP - ISP1(Ubi.nets): IP poyavilsya v internete" \
body="UP - ISP1(Ubi.nets): IP poyavilsya v internete\nData: $[/system clock get date]\nTime: $[/system clock get time]\nSostoyanie: ISP1: $[/interface get number=ISP1 last-link-up-time]\nSostoyanie: ISP2: $[/interface get number=ISP2 last-link-up-time]\nSostoyanie: ISP3: $[/interface get number=ISP3 last-link-up-time]";

#Telegram
/tool fetch url="h_ttps://api.telegram.org/bot777777777:AAF1IywqEMRsNObEXUwtiNL2Y3ygJtMVQrU/sendMessage\?chat_id=333344444&text=UP - MikroTik: Pobedy24 - ISP1(Ubi.nets): IP poyavilsya v internete"

:log warning "DOWN - ISP1(Ubi.nets): IP propal v internete";

/tool e-mail send to=Priem@email.net from="MikroTik: Pobedy24<robot@mysite.ru>" \
subject="DOWN - ISP1(Ubi.nets): IP propal v internete" \
body="DOWN - ISP1(Ubi.nets): IP propal v internete\nData: $[/system clock get date]\nTime: $[/system clock get time]\nSostoyanie: ISP1: $[/interface get number=ISP1 last-link-up-time]\nSostoyanie: ISP2: $[/interface get number=ISP2 last-link-up-time]\nSostoyanie: ISP3: $[/interface get number=ISP3 last-link-up-time]";

#Telegram
/tool fetch url="h_ttps://api.telegram.org/bot777777777:AAF1IywqEMRsNObEXUwtiNL2Y3ygJtMVQrU/sendMessage\?chat_id=333344444&text=DOWN - MikroTik: Pobedy24 - ISP1(Ubi.nets): IP propal v internete"

$do_not_use_shape = 0;          # 1 - если не нужно шейпить клиентов

ee /usr/local/nodeny/modules/mikrotik/create.cfg.mikrotik.cfg.pm 

$do_not_use_shape = 0;          # 1 - если не нужно шейпить клиентов

$do_not_use_shape = 1;          # 1 - если не нужно шейпить клиентов

root@nodeny:/usr/local/nodeny # perl noserver.pl -g=_mikrotik1.cfg.pm -v
Experimental keys on scalar is now forbidden at noserver.pl line 257.
Type of arg 1 to keys must be hash or array (not hash element) at noserver.pl line 257, near "}) "
Execution of noserver.pl aborted due to compilation errors.

06:37:02 system,info address list entry removed by bill 
06:37:02 system,info address list entry removed by bill 
06:37:02 system,info address list entry removed by bill 
06:37:03 system,info address list entry removed by bill 
06:37:03 system,info address list entry removed by bill 
06:37:03 system,info address list entry removed by bill 
06:37:03 system,info address list entry removed by bill 
06:37:03 system,info,account user bill logged out from 10.1.0.5 via api 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:04 system,info address list entry removed by bill 
06:37:06 system,info address list entry removed by bill 
06:37:06 system,info address list entry removed by bill 
06:37:06 system,info address list entry removed by bill 
06:40:44 system,info,account user bill logged in from 10.1.0.5 via api 
06:41:01 system,info address list entry removed by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 
06:41:06 system,info address list entry added by bill 

    my $reply = $m->hw_dialog('/login') or return 0;
    exists $reply->{ret} or return 0;
    my $md5 = new Digest::MD5;
    $md5->add(chr(0));
    $md5->add($m->{pass});
    $md5->add(pack('H*',$reply->{ret}));
    $reply = $m->hw_dialog('/login','=name='.$m->{user},'=response=00'.$md5->hexdigest) or return 0;
    return $reply->{'!done'};

    my $reply = $m->hw_dialog('/login','=name='.$m->{user},'=password='.$m->{pass}) or return 0;
    return $reply->{'!done'};

Ready to process requests
(7) Received Access-Request Id 43 from 10.55.97.2:45083 to 10.55.97.1:1812 length 113
(7)   User-Name = "3C:97:0E:AD:F2:5C"
(7)   NAS-Port-Type = Ethernet
(7)   NAS-Port = 2209350395
(7)   Service-Type = Framed-User
(7)   Calling-Station-Id = "1:3c:97:e:ad:f2:5c"
(7)   Called-Station-Id = "v109"
(7)   User-Password = ""
(7)   NAS-Identifier = "SAT1"
(7)   NAS-IP-Address = 10.55.97.2
(7) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/nodeny
(7)   authorize {
rlm_sql (sql): Reserved connection (5)
(7) sql: EXPAND call radcheck('%{User-Name}')
(7) sql:    --> call radcheck('3C:97:0E:AD:F2:5C')
(7) sql: Executing select query: call radcheck('3C:97:0E:AD:F2:5C')
(7) sql: User found in radcheck table
(7) sql: Conditional check items matched, merging assignment check items
(7) sql:   Cleartext-Password := ""
(7) sql: EXPAND call radreply('%{User-Name}','%{Calling-Station-Id}')
(7) sql:    --> call radreply('3C:97:0E:AD:F2:5C','1:3c:97:e:ad:f2:5c')
(7) sql: Executing select query: call radreply('3C:97:0E:AD:F2:5C','1:3c:97:e:ad:f2:5c')
[b](7) sql: ERROR: rlm_sql_mysql: ERROR 1321 (FUNCTION get_ip_by_tag ended without RETURN): 2F005
(7) sql: ERROR: SQL query error getting reply attributes[/b]
rlm_sql (sql): Released connection (5)
(7)     [sql] = fail
(7)   } # authorize = fail
(7) Invalid user (sql: rlm_sql_mysql: ERROR 1321 (FUNCTION get_ip_by_tag ended without RETURN): 2F005): [3C:97:0E:AD:F2:5C] (from client sat1 port 2209350395 cli 1:3c:97:e:ad:f2:5c)
(7) Using Post-Auth-Type Reject
(7) Post-Auth-Type sub-section not found.  Ignoring.
(7) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(7) Delaying response for 1.000000 seconds
Waking up in 0.3 seconds.
Waking up in 0.6 seconds.
(7) Sending delayed response
(7) Sent Access-Reject Id 43 from 10.55.97.1:1812 to 10.55.97.2:45083 length 20
Waking up in 3.9 seconds.

mysql> SHOW CREATE FUNCTION get_ip_by_tag;
+---------------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function      | sql_mode                                   | Create Function                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | character_set_client | collation_connection | Database Collation |
+---------------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| get_ip_by_tag | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `get_ip_by_tag`( user_id INTEGER UNSIGNED, tag VARCHAR(64) ) RETURNS varchar(15) CHARSET utf8
    NO SQL
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip VARCHAR(15) DEFAULT 0;
    DECLARE row_cnt INTEGER;
    DECLARE ip_id INTEGER;
    DECLARE tries INTEGER DEFAULT 30;
    DECLARE id_min INTEGER;
    DECLARE id_max INTEGER;

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND type='static' 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,%';

    SELECT id, INET_NTOA(ip) INTO ip_id, user_ip FROM ip_pool
        WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
            AND tags LIKE CONCAT('%,', tag, ',%')
        LIMIT 1;

    IF( ip_id IS NOT NULL) THEN
        UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE id = ip_id AND uid = user_id;
        SELECT ROW_COUNT() INTO row_cnt;
        IF( row_cnt > 0 ) THEN RETURN user_ip; 
END IF;
    END IF;

    SELECT MAX(id), MIN(id) INTO id_max, id_min
        FROM ip_pool
        WHERE type = 'dynamic' AND realip = IF(real_ip>0,1,0)
            AND tags LIKE CONCAT('%,', tag, ',%');

    sel_ip: WHILE tries > 0 DO
        SELECT id, INET_NTOA(ip) INTO ip_id, user_ip
            FROM ip_pool
            WHERE uid = 0
                AND id >= (CEIL(RAND() * (id_max - id_min)) + id_min)
                AND id <= id_max
                LIMIT 1;
        IF( user_ip IS NOT NULL) THEN
            UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 3600
                WHERE id = ip_id AND uid = 0;
            SELECT ROW_COUNT() INTO row_cnt;
            IF( row_cnt > 0 ) THEN RETURN user_ip;
END IF;
            SET tries = tries - 5;
        END IF;
        SET tries = tries - 1;
    END WHILE;

END | latin1               | latin1_swedish_ci    | utf8_general_ci    |
+---------------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

call radreply('3C:97:0E:AD:F2:5C','1:3c:97:e:ad:f2:5c')

call radreply('%{User-Name}','%{Calling-Station-Id}')

# -*- text -*-
##
## sql.conf -- SQL modules
##
##      $Id$

sql {
        database = "mysql"
        driver = "rlm_sql_mysql"
        server = "localhost"
        port = 3306
        login = "nodeny"
        password = "hardpass"
        radius_db = "nodeny"

        deletestalesessions = yes
                                                                                      
        sqltrace = no                                                                 
        sqltracefile = ${logdir}/sqltrace.sql                                         
                                                                                      
        num_sql_socks = 5                                                             
                                                                                      
        connect_failure_retry_delay = 60                                              
                                                                                      
        lifetime = 0                                                                  
                                                                                      
        max_queries = 0                                                               
        authorize_check_query = "call radcheck('%{User-Name}')"
        authorize_reply_query = "call radreply('%{User-Name}', '%{Called-Station-Id}')"
        postauth_query = "call radupdate('%{User-Name}','%{reply:Framed-IP-Address}','nas=%{NAS-IP-Address}', '%{Called-Station-Id}')"
        accounting_update_query = "call radupdate('%{User-Name}','%{Framed-IP-Address}','nas=%{NAS-IP-Address}', '%{Called-Station-Id}')"                                                                                      
}