Tag Archives: mysql

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!

Geiger Counter Visualisation

Hi there!

I build a visualisation (php javascript html5 chart.js mysql perl) for my Arduino Geiger Counter. Here some screenshots.

I’m no professional programmer. But it does what it should. Might someone find this useful.
Here the downloadlink.

https://www.paranoids.at/downloads/geiger-f5b7681.tar.gz

Just import the .sql files, connect the arduino via usb, change data formatting if needed and start logGeiger.pl.

Happy plotting 🙂

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!

rsyslog own mysql table per host with loganalyzer innodb and innodb-compression

Hi

Here my rsyslog config file placed under /etc/rsyslog.d/48-mysql.conf

$ModLoad ommysql
$template dbFormatHost1,"insert into SystemEventsHost1 (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag:R,ERE,1,FIELD:(.+)(\[[0-9]{1,5}\]).*--end%', '%syslogtag:R,ERE,1,BLANK:\[([0-9]{1,5})\]--end%')",SQL
$template dbFormatHost2,"insert into SystemEventsHost2 (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag:R,ERE,1,FIELD:(.+)(\[[0-9]{1,5}\]).*--end%', '%syslogtag:R,ERE,1,BLANK:\[([0-9]{1,5})\]--end%')",SQL
:source , isequal , "host1" :ommysql:localhost,Syslog,rsyslog,mypassword;dbFormatHost1
:source , isequal , "host2" :ommysql:localhost,Syslog,rsyslog,mypassword;dbFormatHost2

Here my database table structure

CREATE TABLE IF NOT EXISTS `SystemEventsHost1` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `CustomerID` bigint(20) DEFAULT NULL,
 `ReceivedAt` datetime DEFAULT NULL,
 `DeviceReportedTime` datetime DEFAULT NULL,
 `Facility` smallint(6) DEFAULT NULL,
 `Priority` smallint(6) DEFAULT NULL,
 `FromHost` varchar(60) DEFAULT NULL,
 `Message` text,
 `NTSeverity` int(11) DEFAULT NULL,
 `Importance` int(11) DEFAULT NULL,
 `EventSource` varchar(60) DEFAULT NULL,
 `EventUser` varchar(60) DEFAULT NULL,
 `EventCategory` int(11) DEFAULT NULL,
 `EventID` int(11) DEFAULT NULL,
 `EventBinaryData` text,
 `MaxAvailable` int(11) DEFAULT NULL,
 `CurrUsage` int(11) DEFAULT NULL,
 `MinUsage` int(11) DEFAULT NULL,
 `MaxUsage` int(11) DEFAULT NULL,
 `InfoUnitID` int(11) DEFAULT NULL,
 `SysLogTag` varchar(60) DEFAULT NULL,
 `EventLogType` varchar(60) DEFAULT NULL,
 `GenericFileName` varchar(60) DEFAULT NULL,
 `SystemID` int(11) DEFAULT NULL,
 `ProcessID` varchar(60) NOT NULL DEFAULT '',
 `checksum` int(11) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`) KEY_BLOCK_SIZE=4,
 KEY `ReceivedAt` (`ReceivedAt`),
 KEY `Facility` (`Facility`),
 KEY `Priority` (`Priority`),
 KEY `FromHost` (`FromHost`),
 KEY `DeviceReportedTime` (`DeviceReportedTime`),
 KEY `SysLogTag` (`SysLogTag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `SystemEventsHost2` (
 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `CustomerID` bigint(20) DEFAULT NULL,
 `ReceivedAt` datetime DEFAULT NULL,
 `DeviceReportedTime` datetime DEFAULT NULL,
 `Facility` smallint(6) DEFAULT NULL,
 `Priority` smallint(6) DEFAULT NULL,
 `FromHost` varchar(60) DEFAULT NULL,
 `Message` text,
 `NTSeverity` int(11) DEFAULT NULL,
 `Importance` int(11) DEFAULT NULL,
 `EventSource` varchar(60) DEFAULT NULL,
 `EventUser` varchar(60) DEFAULT NULL,
 `EventCategory` int(11) DEFAULT NULL,
 `EventID` int(11) DEFAULT NULL,
 `EventBinaryData` text,
 `MaxAvailable` int(11) DEFAULT NULL,
 `CurrUsage` int(11) DEFAULT NULL,
 `MinUsage` int(11) DEFAULT NULL,
 `MaxUsage` int(11) DEFAULT NULL,
 `InfoUnitID` int(11) DEFAULT NULL,
 `SysLogTag` varchar(60) DEFAULT NULL,
 `EventLogType` varchar(60) DEFAULT NULL,
 `GenericFileName` varchar(60) DEFAULT NULL,
 `SystemID` int(11) DEFAULT NULL,
 `ProcessID` varchar(60) NOT NULL DEFAULT '',
 `checksum` int(11) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`ID`) KEY_BLOCK_SIZE=4,
 KEY `ReceivedAt` (`ReceivedAt`),
 KEY `Facility` (`Facility`),
 KEY `Priority` (`Priority`),
 KEY `FromHost` (`FromHost`),
 KEY `DeviceReportedTime` (`DeviceReportedTime`),
 KEY `SysLogTag` (`SysLogTag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 AUTO_INCREMENT=1 ;

Here my loganalyzer source config snippet

$CFG['Sources']['Host1']['ID'] = 'Host1';
$CFG['Sources']['Host1']['Name'] = 'Host1';
$CFG['Sources']['Host1']['ViewID'] = 'SYSLOG';
$CFG['Sources']['Host1']['SourceType'] = SOURCE_DB;
$CFG['Sources']['Host1']['DBTableType'] = 'monitorware';
$CFG['Sources']['Host1']['DBType'] = DB_MYSQL;
$CFG['Sources']['Host1']['DBServer'] = 'localhost';
$CFG['Sources']['Host1']['DBName'] = 'Syslog';
$CFG['Sources']['Host1']['DBUser'] = 'rsyslog';
$CFG['Sources']['Host1']['DBPassword'] = 'mypassword';
$CFG['Sources']['Host1']['DBTableName'] = 'SystemEventsHost1';
$CFG['Sources']['Host1']['DBEnableRowCounting'] = false;
$CFG['Sources']['Host2']['ID'] = 'Host2';
$CFG['Sources']['Host2']['Name'] = 'Host2';
$CFG['Sources']['Host2']['ViewID'] = 'SYSLOG';
$CFG['Sources']['Host2']['SourceType'] = SOURCE_DB;
$CFG['Sources']['Host2']['DBTableType'] = 'monitorware';
$CFG['Sources']['Host2']['DBType'] = DB_MYSQL;
$CFG['Sources']['Host2']['DBServer'] = 'localhost';
$CFG['Sources']['Host2']['DBName'] = 'Syslog';
$CFG['Sources']['Host2']['DBUser'] = 'rsyslog';
$CFG['Sources']['Host2']['DBPassword'] = 'mypassword';
$CFG['Sources']['Host2']['DBTableName'] = 'SystemEventsHost2';
$CFG['Sources']['Host2']['DBEnableRowCounting'] = false;

If you’re paranoid you can modify your ubuntu 14.04 apparmor profile also, just add these lines to /etc/apparmor.d/usr.sbin.rsyslogd
and enforce rsyslog’s apparmor profile

# Add these for mysql support
/etc/mysql/my.cnf r,
/etc/mysql/conf.d/ r,
/etc/mysql/conf.d/** r,
/{,var/}run/mysqld/mysqld.sock rw,
/usr/share/mysql/charsets/Index.xml r,

Have fun!

freeradius anti bruteforce lockout via mysql

hi there

here my version of an user lockout via mysql for freeradius

Create a table like:

CREATE TABLE IF NOT EXISTS `failed` (
 `stationid` text NOT NULL,
 `nasip` text NOT NULL,
 `username` text NOT NULL,
 `authdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
vi /etc/freeradius/policy.conf
lockout_check {
 update control {
 Tmp-Integer-0 := "%{sql:SELECT COUNT(*) FROM failed where username='%{User-Name}' AND authdate>=DATE_SUB(NOW(), INTERVAL 15 MINUTE)}"
 }
 if (control:Tmp-Integer-0 > 5) {
 reject
 }
 }
 lockout_incr {
 update control {
 Tmp-Integer-0 := "%{sql:INSERT INTO failed (stationid,nasip,username,authdate) VALUES ('%{Calling-Station-Id}','%{NAS-IP-Address}','%{User-Name}', NOW())}"
 Tmp-Integer-1 := "%{sql:DELETE FROM failed WHERE authdate<=DATE_SUB(NOW(), INTERVAL 30 MINUTE)}"
 }
 }
vi /etc/freeradius/sites-enabled/default
authorize {
 lockout_check
 .....
 }
post-auth {
 Post-Auth-Type REJECT {
 lockout_incr
 }
 .....
 }

Have fun!