Tag Archives: dns

Simple PHP Mysql Bind dynamic DNS Service

Hi

Need a simple dyndns service.
Here my crappy 10 minutes approach to get this done:

curl -> webserver -> mysql -> cronjob -> namedZoneFile

Here my curl client cronjob every minute:

/usr/bin/curl --silent --user username:password http://dyn.xxx.xx

Webserver PHP implementation:

.htaccess

<IfModule mod_rewrite.c>
RewriteEngine on
RewriteRule .* - [E=HTTP_AUTHORIZATION:%{HTTP:Authorization},L]
</IfModule>

index.php

<?php

$ipAddress = $_SERVER['REMOTE_ADDR'];

if (!isset($_SERVER['PHP_AUTH_USER'])) {
  header('WWW-Authenticate: Basic realm="paranoids.at DynDns"');
  header('HTTP/1.0 401 Unauthorized');
  echo 'No Auth, Try again';
} 
else {
  $isAuth = getUserPass($_SERVER['PHP_AUTH_USER'],$_SERVER['PHP_AUTH_PW']);
  if (!empty($isAuth)) {
    if (empty(ifHostExists($isAuth['id']))) {
      insertHost($isAuth['id'], $ipAddress);
    }
    else {
      $host = ifHostExists($isAuth['id']);
      if (strcmp($host['ip'], $ipAddress) !== 0) {
        updateHost($host['userid'], $ipAddress);
      } 
    }
  }
  else {
    echo 'Wrong Auth, Try again';
  }
}

connect_db_dyn()->close();

function ifHostExists($userid) {
  $result = connect_db_dyn()->query("SELECT * FROM host WHERE userid='$userid';");
  return $result->fetch_assoc();
}

function getUserPass($user,$pass) {
  $result = connect_db_dyn()->query("SELECT * FROM user WHERE username='$user' AND password='$pass' LIMIT 1;");
  return $result->fetch_assoc();
}

function insertHost($userid,$newip) {
  connect_db_dyn()->query("INSERT INTO host (userid, ip, changed) VALUES ('$userid', '$newip', '1');");
}

function updateHost($userid,$ip) {
  connect_db_dyn()->query("UPDATE host SET ip='$ip', changed=1, timestamp=CURRENT_TIMESTAMP WHERE userid='$userid';");
}

function connect_db_dyn() {
  $dbh = new mysqli('localhost', 'username', 'password', 'database');
  return $dbh;
}

?>

Database dump:

-- phpMyAdmin SQL Dump
-- version 4.6.4
-- https://www.phpmyadmin.net/
--
-- Host: localhost

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

--
-- Database: `database`
--

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

--
-- Table structure for table `host`
--

CREATE TABLE `host` (
 `id` int(11) NOT NULL,
 `userid` int(11) NOT NULL,
 `ip` varchar(255) NOT NULL,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `changed` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `host`
--

INSERT INTO `host` (`id`, `userid`, `ip`, `timestamp`, `changed`) VALUES
(14, 1, '1.1.1.1', '2016-11-30 04:38:03', 0);

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

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `username` varchar(255) NOT NULL,
 `password` varchar(255) NOT NULL,
 `comment` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `username`, `password`, `comment`) VALUES
(1, 'username1', 'password1', 'comment1');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `host`
--
ALTER TABLE `host`
 ADD PRIMARY KEY (`id`);

--
-- Indexes for table `user`
--
ALTER TABLE `user`
 ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `host`
--
ALTER TABLE `host`
 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

dnsserverside cronjob every minute:

<?php

if (!empty(getChange())) {
 deleteHosts();
 updateHosts();
 writeBind();
}

connect_Db_Dyn()->close();

function getChange() {
 $result = connect_Db_Dyn()->query("SELECT * FROM host WHERE changed = 1");
 return $result->fetch_assoc();
}

function getHosts() {
 $result = connect_Db_Dyn()->query("SELECT * FROM host");
 return $result->fetch_all($resulttype = MYSQLI_ASSOC);
}

function deleteHosts() {
 connect_Db_Dyn()->query("DELETE FROM host WHERE timestamp < date_add(current_date, interval -14 day) LIMIT 1000;");
}

function updateHosts() {
 connect_Db_Dyn()->query("UPDATE host SET changed=0;");
}

function getUsernameById($userid) {
 $result = connect_Db_Dyn()->query("SELECT username FROM user WHERE id='$userid'");
 $row = $result->fetch_assoc();
 return $row['username'];
}

function connect_Db_Dyn() {
 $dbh = new mysqli('localhost', 'username', 'password', 'database');
 return $dbh;
}

function writeBind() {
$date = new DateTime();
$texthead = '$TTL 60
@ IN SOA ns1.xxxx.xx. hostmaster.xxxx.xxx. (
 sedSerial ; serial, unix timestamp  #
 7200 ; refresh, seconds
 540 ; retry, seconds
 604800 ; expire, seconds
 3600 ) ; minimum, seconds
;'."\n\n";

$texthead = preg_replace("/sedSerial/",$date->getTimestamp(),$texthead);

$textbody = "@"."\t"."A"."\t"."1.1.1.1"."\n";

foreach (getHosts() as $host) {
 $textbody .= getUsernameById($host['userid'])."\t"."A"."\t".$host['ip']."\n";
}

$textfooter = '
@ NS ns2.xxx.xx.
@ NS ns1.xxx.xx.
';
$file = $texthead.$textbody.$textfooter;

file_put_contents("/etc/bind/dns.xxx.xx", $file);

system('/usr/sbin/rndc -q reload');

}

?>

Bind named.conf:

zone "dns.xxx.xx" {
 type master;
 allow-transfer {1.1.1.1;2:1:1::2;};
 file "/etc/bind/dns.xxx.xx";
};

Have fun!

ispconfig tlsa patch for dane using postfix

Hi There

I’ve added TLSA DNS RR support to my ispconfig server. This howto relies on my previous post which adds dnssec support to ispconfig. http://www.paranoids.at/bind9-ispconfig-dnssec-inline-signing-ubuntu-1204/

Actually I’m using ubuntu 14.04 with most recent version of ispconfig 3. With ubuntu 14.04 you don’t need the bind ppa cause bind version in 14.04 supports auto keyrollover for dnssec singed zones.

Simply copy the files as following:

cd /usr/local/ispconfig/interface/web/dns
cp -av dns_srv_edit.php  dns_tlsa_edit.php
cp -av form/dns_srv.tform.php form/dns_tlsa.tform.php
cp -av templates/dns_srv_edit.htm templates/dns_tlsa_edit.htm
cp -av lib/lang/de_dns_srv.lng templates/dns_tlsa_edit.htm

Then run the patches agains every file mentioned in the patch.

Here the patch for the interface:
http://www.paranoids.at/downloads/ispconfig-tlsa-interface-patch.txt
Here the patch for the server:
http://www.paranoids.at/downloads/ispconfig-tlsa-server-patch.txt

You also have to alter the table structure of dns_rr in dbispconfig. You only have to edit type as following:

`type` enum('A','AAAA','ALIAS','CNAME','HINFO','MX','NAPTR','NS','PTR','RP','SRV','TLSA','TXT') DEFAULT NULL

Here some nice Firefox tool to verify your dnssec and tlsa records: https://www.dnssec-validator.cz/

Here the config snippets from postfix’s main.cf:

smtp_dns_support_level = dnssec
smtp_tls_security_level = dane

Have fun!