[Dnsmasq-discuss] Dnsmasq MySQL back-end

Michael Rack michael.rack at rsm-freilassing.de
Mon Feb 10 06:36:23 GMT 2014


Hi audiotec69,

i use a combination of DNSMASQ and PHP to store the leases in database.

I've attached a PHP-Script that will do the job. You need to install 
CreoleDB (Database Abstraction Layer).

You have to give the script executable bits (chmod 775 )

In your DNSMASQ-Configuration enable the dhcp-script option and point it 
absolute to the script-path. Restart your DNSMASQ and your leases will 
be stored in database.

BTW: A native MySQL-Backend-Driver will be the best solution an 
recommend. But my skills in programming C are not the best to accomplish 
the goals.

Liebe Grüße aus Freilassing,

Michael Rack
RSM Freilassing
-- 
RSM Freilassing                 Tel.: +49 8654 607110
Nocksteinstr. 13                Fax.: +49 8654 670438
D-83395 Freilassing            www.rsm-freilassing.de

Am 10.02.2014 06:31, schrieb audiotec69:
> So apparently back in 2005 there was a patch created for dnsmasq 
> version 2.20 
> (http://lists.thekelleys.org.uk/pipermail/dnsmasq-discuss/2005q1/000163.html)
> to use a MySQL back-end for leases. Could this still be possible? If 
> so where might I find documentation on such a configuration. If this 
> is not possible, perhaps someone could send me in the right direction. 
> I am working on a project that needs the ability to store leases in a 
> database.
>
> Ideally I would like to be able to capture the time the lease was 
> issued and the time it expired.
>
> Thanks.
>
> _______________________________________________
> Dnsmasq-discuss mailing list
> Dnsmasq-discuss at lists.thekelleys.org.uk
> http://lists.thekelleys.org.uk/mailman/listinfo/dnsmasq-discuss

-------------- next part --------------
#!/usr/bin/php
<?PHP

	$params = array_slice($_SERVER['argv'], 1);
	if( count($params) !== 4 AND count($params) !== 3 AND (count($params) === 1 AND $params[0] === 'init') === false )
	{
		printLine('Usage: store-dhcp-lease action mac-address ip-address hostname');
		exit( 100 );
	}

	// Datenbank laden
	define( 'MYSQL_LOCAL_DSN', 'mysql://username:password@unix('. rawurlencode('/tmp/mysql.sock') .')/database' );
	require_once('creole/Creole.php');

	// Try to get connection
	$trys = 0; $maxTrys = 10;
	while(true)
	{
		try
		{
			$conn = Creole::getConnection( MYSQL_LOCAL_DSN );
			break;
		}
		catch( SQLException $e )
		{
			usleep( 500000 );
			if( ++$trys === $maxTrys )
			{
				printLine($e->getMessage());
				exit( 11 );
			}
		}
	}
	
	$now = date('Y-m-d H:i:s');
	switch( strtolower($params[0]) )
	{
		// Add Lease
		case 'add':
			if( strlen($params[1]) < 17 ) $params[1] = '00:'. $params[1];
			$strQuery = 'INSERT INTO `dhcp_leases` (`mac_address`, `ip_address`, `dhcp_relay`, `hostname`, `lease_end`)'.
			            ' VALUES ('
			                .chr(39). mysql_escape_string($params[1]) .chr(39)
			                .', INET_ATON('.chr(39). mysql_escape_string($params[2]) .chr(39).')'
			                .', '. ( ( array_key_exists('DNSMASQ_RELAY_ADDRESS', $_ENV)
			                           AND empty($_ENV['DNSMASQ_RELAY_ADDRESS']) === false
			                         )
			                         ? 'INET_ATON('. chr(39). mysql_escape_string($_ENV['DNSMASQ_RELAY_ADDRESS']) . chr(39) .')'
			                         : 'NULL'
			                       )
			                .', '. ( ( array_key_exists('DNSMASQ_SUPPLIED_HOSTNAME', $_ENV)
			                           AND $_ENV['DNSMASQ_SUPPLIED_HOSTNAME'] !== ''
			                         )
			                         ? chr(39). mysql_escape_string($_ENV['DNSMASQ_SUPPLIED_HOSTNAME']) . chr(39)
			                         : 'NULL'
			                       )
			                .', '.chr(39). mysql_escape_string(date('Y-m-d H:i:s', (int)$_ENV['DNSMASQ_LEASE_EXPIRES'])) .chr(39)
			            .')';
			write_log( $strQuery );
			if( $conn->ExecuteUpdate( $strQuery ) > 0 )
			{
				printLine( sprintf('OK: Lease for ip-address %s added', $params[2]) );
			}
			else
			{
				printLine( sprintf('ERR: Failed to add lease for %s', $params[2]) );
				exit( 32 );
			}
			exit( 0 );
			break;

		// Renew Lease
		case 'old':
			if( strlen($params[1]) < 17 ) $params[1] = '00:'. $params[1];
			$strQuery = 'UPDATE `dhcp_leases`'.
			            ' SET `lease_end`='.chr(39). mysql_escape_string(date('Y-m-d H:i:s', (int)$_ENV['DNSMASQ_LEASE_EXPIRES'])) .chr(39).
			            ' WHERE `mac_address`='.chr(39). mysql_escape_string($params[1]) .chr(39).
			            ' AND `ip_address`=INET_ATON('.chr(39). mysql_escape_string($params[2]) .chr(39).')'.
			            ' AND `lease_end` >= \''. $now .'\'';
			write_log( $strQuery );
			if( $conn->ExecuteUpdate( $strQuery ) === 0 )
			{
				$strQuery = 'INSERT INTO `dhcp_leases` (`mac_address`, `ip_address`, `dhcp_relay`, `hostname`, `lease_end`)'.
			                ' VALUES ('
			                    .chr(39). mysql_escape_string($params[1]) .chr(39)
			                    .', INET_ATON('.chr(39). mysql_escape_string($params[2]) .chr(39).')'
			                    .', '. ( ( array_key_exists('DNSMASQ_RELAY_ADDRESS', $_ENV)
			                               AND empty($_ENV['DNSMASQ_RELAY_ADDRESS']) === false
			                             )
			                             ? 'INET_ATON('. chr(39). mysql_escape_string($_ENV['DNSMASQ_RELAY_ADDRESS']) . chr(39) .')'
			                             : 'NULL'
			                           )
			                    .', '. ( ( array_key_exists('DNSMASQ_SUPPLIED_HOSTNAME', $_ENV)
			                               AND $_ENV['DNSMASQ_SUPPLIED_HOSTNAME'] !== ''
			                             )
			                             ? chr(39). mysql_escape_string($_ENV['DNSMASQ_SUPPLIED_HOSTNAME']) . chr(39)
			                             : 'NULL'
			                           )
			                    .', '.chr(39). mysql_escape_string(date('Y-m-d H:i:s', (int)$_ENV['DNSMASQ_LEASE_EXPIRES'])) .chr(39)
			                .')';
				write_log( $strQuery );
				if( $conn->ExecuteUpdate( $strQuery ) === 0 )
				{
					printLine( sprintf('ERR: Lease for %s could not be reinserted into database.', $params[2]) );
					exit( 42 );
				}
				else
				{
					printLine( sprintf('OK: Lease for %s reinserted.', $params[2]) );
				}
			}
			else
			{
				printLine( sprintf('OK: Lease for %s updated.', $params[2]) );
			}
			break;

		// Delete a Lease
		case 'del':
			write_log( 'Delete called for Lease: '. $params[2] );
			if( strlen($params[1]) < 17 ) $params[1] = '00:'. $params[1];
			$strQuery = 'UPDATE `dhcp_leases`'.
			            ' SET `lease_end`=\''. $now .'\'' .
			            ' WHERE `mac_address`='.chr(39). mysql_escape_string($params[1]) .chr(39).
			            ' AND `ip_address`=INET_ATON('.chr(39). mysql_escape_string($params[2]) .chr(39).')'.
			            ' AND `lease_end` >= \''. $now .'\'';
			write_log( $strQuery );
			if( $conn->ExecuteUpdate( $strQuery ) > 0 )
			{
				printLine( sprintf('OK: Lease for %s deleted', $params[2]) );
				exit( 0 );
			}
			else
			{
				printLine( sprintf('ERR: Lease for %s was not found', $params[2]) );
				exit( 51 );
			}
			break;

		// Lease-File
		case 'init':
			$strQuery = 'SELECT MAX(UNIX_TIMESTAMP(`lease_end`)) as `expire`, `mac_address`, INET_NTOA(`ip_address`) as `ip_address` FROM `dhcp_leases`'.
			            ' WHERE `lease_end` >= \''. $now .'\''.
			            ' GROUP BY `mac_address`, `ip_address`';
			$rs = $conn->ExecuteQuery( $strQuery );
			while( $rs->next() )
			{
				printLine( $rs->getString('expire') .' '. $rs->getString('mac_address') .' '. $rs->getString('ip_address') .' * *' );
			}
			$rs->close();
			unset( $rs );
			exit( 0 );
			break;

		default:
			printLine('Usage-Options: add, old, del, init');
			exit( 200 );
		
	}

	 /**
		* Gibt an der Konsole eine Zeile aus
		* @param  string    $line       Zeile
		* @return void
		*/
	function printLine( $line )
	{
		echo $line . chr(10);
		echo write_log( $line );
	}

	/**
	 * Update Log-File
	 * @param   string    $line		Info
	 * @return  void
	 */
	function write_log( $line )
	{
			$fp = fopen('/tmp/dnsmasq.log', 'a');
			fwrite($fp, sprintf( '%1$s %2$s', date('Y-m-d H:i:s'), $line) . chr(10));
			fclose($fp);
	}
?>
-------------- next part --------------
-- phpMyAdmin SQL Dump
-- version 4.0.8
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 10. Feb 2014 um 07:34
-- Server Version: 5.5.33a-MariaDB
-- PHP-Version: 5.3.2

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Datenbank: `net-rsm-connect-hotspot`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `dhcp_leases`
--

CREATE TABLE IF NOT EXISTS `dhcp_leases` (
  `mac_address` char(25) NOT NULL,
  `ip_address` int(10) unsigned NOT NULL,
  `dhcp_relay` int(11) unsigned DEFAULT NULL,
  `hostname` char(255) DEFAULT NULL,
  `lease_start` datetime NOT NULL,
  `lease_end` datetime NOT NULL,
  `last_change` datetime NOT NULL,
  PRIMARY KEY (`mac_address`,`ip_address`,`lease_end`),
  UNIQUE KEY `ip_address` (`ip_address`,`lease_end`)
) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 COMMENT='Lease-Tabelle';

--
-- Trigger `dhcp_leases`
--
DROP TRIGGER IF EXISTS `dhcp_leases_before_insert`;
DELIMITER //
CREATE TRIGGER `dhcp_leases_before_insert` BEFORE INSERT ON `dhcp_leases`
 FOR EACH ROW BEGIN
  
  IF  new.`lease_start` IS NULL
      OR new.`lease_start`='0000-00-00 00:00:00'
  THEN
    SET new.`lease_start` = now();
  END IF;
  SET new.`last_change` = now();

END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `dhcp_leases_before_update`;
DELIMITER //
CREATE TRIGGER `dhcp_leases_before_update` BEFORE UPDATE ON `dhcp_leases`
 FOR EACH ROW BEGIN
  
  SET new.`last_change` = now();

END
//
DELIMITER ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


More information about the Dnsmasq-discuss mailing list