Dump1090 MySQL Database Script with Alert and Filter

writes Dump1090 data to database and e-mail alerts on special events. you can set how often the script writes to database and looks for alert condition. you can specify the area (lat/lon/alt) to be observed and filter for special hex and/or flight numbers.

please do not abuse my script to generate and publish the flightaware-mlat-data anywhere - this data is only meant for your personal use. you find additional information here: FlightAware MLAT Network Announcement

git install:

git clone --depth=1 http://github.com/TomMuc1/Dump1090-MySQL-Alert-Filter.git

the script to do your own daily aircraft-report directly on the raspberry pi without database you find here: Daily Log/E-Mail Aircraft Report directly on your RaspberryPi

edit 2017_10_21 17:50 UTC: added distance and receiver-message-rate column

do the needed settings at top of radar.php - then place the script in /home/pi/ and follow below instructions …

setup script system service:

sudo chmod 755 /home/pi/radar.php
sudo nano /etc/systemd/system/radar.service

→ in nano insert the following lines

[Unit]
Description=radar.php

[Service]
ExecStart=/home/pi/radar.php
Restart=always
RestartSec=10
StandardOutput=null
StandardError=null

[Install]
WantedBy=multi-user.target

save and exit nano ctrl+x → ctrl+y → enter

sudo chmod 644 /etc/systemd/system/radar.service
sudo systemctl enable radar.service
sudo systemctl start radar.service
sudo systemctl status radar.service
4 Likes

5 Minute Express Install HowTo

given raspbian jessie install with dump1090 with lighttpd


sudo apt-get update
sudo apt-get install sendmail
sudo apt-get install php5-common php5-cgi php5-mysql php5-sqlite php5-curl php5
sudo lighty-enable-mod fastcgi
sudo lighty-enable-mod fastcgi-php
sudo service lighttpd force-reload
sudo apt-get install mysql-server mysql-client

sudo shutdown -r now

mysql -u root -p

CREATE DATABASE adsb;

USE adsb;

CREATE TABLE `aircrafts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message_date` varchar(100) DEFAULT NULL,
  `now` varchar(100) DEFAULT NULL,
  `hex` varchar(100) DEFAULT NULL,
  `flight` varchar(100) DEFAULT NULL,
  `distance` varchar(100) DEFAULT NULL,
  `altitude` varchar(100) DEFAULT NULL,
  `lat` varchar(100) DEFAULT NULL,
  `lon` varchar(100) DEFAULT NULL,
  `track` varchar(100) DEFAULT NULL,
  `speed` varchar(100) DEFAULT NULL,
  `vert_rate` varchar(100) DEFAULT NULL,
  `seen_pos` varchar(100) DEFAULT NULL,
  `seen` varchar(100) DEFAULT NULL,
  `rssi` varchar(100) DEFAULT NULL,
  `messages` varchar(100) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `squawk` varchar(100) DEFAULT NULL,
  `nucp` varchar(100) DEFAULT NULL,
  `mlat` varchar(100) DEFAULT NULL,
  `tisb` varchar(100) DEFAULT NULL,
  `rec_msg_sec` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

SHOW COLUMNS IN aircrafts;

exit

php radar.php

run the script for some seconds/minutes until it says xxx inserted then stop script with ctrl + z

mysql -u root -p

USE adsb;

SELECT * FROM aircrafts;

exit

et voila :)

p.s. for raspbian stretch the php-install line is:
sudo apt-get install php7.0-common php7.0-cgi php7.0-mysql php7.0-sqlite php7.0-curl php7.0

p.p.s. the new mariadb (aka mysql) that comes with stretch is somewhat stupid with the root password.
these steps help to get back the old behavior:

sudo mysql -u root -p (leave password empty)
update mysql.user set password=password('YOUR_DB_PASSWORD') where user='root';
update mysql.user set plugin='' where user='root';
flush privileges;
2 Likes

radar.php

#!/usr/bin/php
<?php

#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);

// below a sample create statement for database table
// CREATE TABLE aircrafts (id INT NOT NULL AUTO_INCREMENT, message_date VARCHAR(100), now VARCHAR(100), hex VARCHAR(100), flight VARCHAR(100), distance VARCHAR(100), altitude VARCHAR(100), lat VARCHAR(100), lon VARCHAR(100), track VARCHAR(100), speed VARCHAR(100), vert_rate VARCHAR(100), seen_pos VARCHAR(100), seen VARCHAR(100), rssi VARCHAR(100), messages VARCHAR(100), category VARCHAR(100), squawk VARCHAR(100), nucp VARCHAR(100), mlat VARCHAR(100), tisb VARCHAR(100), rec_msg_sec VARCHAR(100), PRIMARY KEY (id))

// set the rectangle and altitude to store aircraft-data in database - if your lon is negative be aware to use the right values for max and min
$user_set_array['max_lat'] = 50.000000;    $user_set_array['min_lat'] = 46.000000;    $user_set_array['max_alt'] = 10000;
$user_set_array['max_lon'] = 14.000000;    $user_set_array['min_lon'] = 10.000000;

// set the rectangle and altitude to send alert message - if your lon is negative be aware to use the right values for max and min
$user_set_array['alert_max_lat'] = 49.000000;    $user_set_array['alert_min_lat'] = 47.000000;    $user_set_array['alert_max_alt'] = 5000;
$user_set_array['alert_max_lon'] = 13.000000;    $user_set_array['alert_min_lon'] = 11.000000;

// set lookup-interval default is 1 (must be integer between 1 - 900) this is the frequency the script runs and writes to database or looks for alerts
$user_set_array['sleep'] = 1;

// set your google email-address for alert-messages - if you want to use mailer.php instead gmail set method 'gmail' to 'webmail' or 'pushover' and a key according to mailer.php
$user_set_array['alert_method'] = 'gmail'; $user_set_array['email_address'] = 'YOUR_EMAIL@gmail.com'; $user_set_array['secret_email_key'] = 'YOUR_USER_KEY';

// set parameters for database connection
$user_set_array['db_name'] = 'adsb'; $user_set_array['db_host'] = '127.0.0.1'; $user_set_array['db_user'] = 'USERNAME'; $user_set_array['db_pass'] = 'PASSWORD';

// set path to aircraft.json file
$user_set_array['url_json'] = 'http://127.0.0.1/dump1090/data/';

// set path to your mailer.php file
$user_set_array['url_mailer'] = 'http://YOUR_WEBSPACE.COM/mailer.php';

// set the absolute limit of alert-messages (default is 1000) this script is allowed to send over its whole runtime
$user_set_array['mailer_limit'] = 1000;

// set aircraft suspend time (default is 900) - change only if needed - time in seconds an aicraft is suspended from alert-messages after sending an alert-message for this aircraft
$user_set_array['aircraft_suspend_time'] = 900;

// set this to true if you want alerts and/or database writes from those aircrafts matching your hex_code_array.txt or flight_code_array.txt files within limited area or whole site-range, with or without wildcards
$user_set_array['filter_mode_alert'] = false;    $user_set_array['filter_mode_alert_limited'] = false;
$user_set_array['filter_mode_database'] = false;     $user_set_array['filter_mode_database_limited'] = false;
$user_set_array['filter_mode_wildcard'] = false;

// set path to your hex_code_array.txt and flight_code_array.txt files
$user_set_array['hex_file_path'] = '/home/pi/hex_code_array.txt';
$user_set_array['flight_file_path'] = '/home/pi/flight_code_array.txt';

// set your timezone see http://php.net/manual/en/timezones.php
$user_set_array['time_zone'] = 'America/Chicago';



// wildcard search function for external filter data
function func_wildcard_search($code, $user_code_array, $wildcard_mode) {
	$match = false;
	$code = strtoupper($code);
	if ($wildcard_mode) {
		foreach ($user_code_array as $pattern) {
			if (preg_match('/^' . trim($pattern) . '$/', $code)) $match = true;
		}
	} else {
		$user_code_array = array_map('trim', $user_code_array);
		if (in_array($code, $user_code_array)) $match = true;
	}
	return $match;
}

// function to compute distance between receiver and aircraft
function func_haversine($lat_from, $lon_from, $lat_to, $lon_to, $earth_radius = 3440) {
	$delta_lat = deg2rad($lat_to - $lat_from);
	$delta_lon = deg2rad($lon_to - $lon_from);
	$a = sin($delta_lat / 2) * sin($delta_lat / 2) + cos(deg2rad($lat_from)) * cos(deg2rad($lat_to)) * sin($delta_lon / 2) * sin($delta_lon / 2);
	$c = 2 * atan2(sqrt($a), sqrt(1-$a));
	return $earth_radius * $c;
}

$i = 0;
$alert_message = '';
$sent_alert_messages = 0;
$alert_trigger_array = array();
$start_time = time();
date_default_timezone_set($user_set_array['time_zone']);

// fetch receiver.json and read receiver latitude and longitude
$json_receiver_location = json_decode(file_get_contents($user_set_array['url_json'] . 'receiver.json'), true);
isset($json_receiver_location['lat']) ? $rec_lat = $json_receiver_location['lat'] : $rec_lat = 0;
isset($json_receiver_location['lon']) ? $rec_lon = $json_receiver_location['lon'] : $rec_lon = 0;

while (true) {

	$x = 0;
	$sql = '';
	$start_loop_microtime = microtime(true);

	// fetch aircraft.json and read timestamp and overall message number
	$json_data_array = json_decode(file_get_contents($user_set_array['url_json'] . 'aircraft.json'), true);
	isset($json_data_array['now']) ? $ac_now = $json_data_array['now'] : $ac_now = '';
	isset($json_data_array['messages']) ? $ac_messages_total = $json_data_array['messages'] : $ac_messages_total = '';

	// fetch and read external filter data files
	if ($user_set_array['filter_mode_alert'] || $user_set_array['filter_mode_database']) {
		$hex_code_array = explode(',', str_replace('%', '.', strtoupper(file_get_contents($user_set_array['hex_file_path']))));
		$flight_code_array = explode(',', str_replace('%', '.', strtoupper(file_get_contents($user_set_array['flight_file_path']))));
	}

	// compute receiver message rate averaged over 30 seconds
	$message_rate_array[] = array('messages' => $ac_messages_total, 'time' => $ac_now);
	if (time() - $message_rate_array[0]['time'] > 30) array_shift($message_rate_array);
	$delta_message_number = $message_rate_array[count($message_rate_array) - 1]['messages'] - $message_rate_array[0]['messages'];
	$delta_message_time = $message_rate_array[count($message_rate_array) - 1]['time'] - $message_rate_array[0]['time'];
	$delta_message_time > 0 ? $message_rate = round($delta_message_number / $delta_message_time, '1') : $message_rate = 0;

	// loop through aircraft section of aircraft.json file
	foreach ($json_data_array['aircraft'] as $row) {
		isset($row['hex']) ? $ac_hex = $row['hex'] : $ac_hex = '';
		isset($row['flight']) ? $ac_flight = trim($row['flight']) : $ac_flight = '';
		isset($row['altitude']) ? $ac_altitude = $row['altitude'] : $ac_altitude = '';
		isset($row['lat']) ? $ac_lat = $row['lat'] : $ac_lat = '';
		isset($row['lon']) ? $ac_lon = $row['lon'] : $ac_lon = '';
		isset($row['track']) ? $ac_track = $row['track'] : $ac_track = '';
		isset($row['speed']) ? $ac_speed = $row['speed'] : $ac_speed = '';
		isset($row['vert_rate']) ? $ac_vert_rate = $row['vert_rate'] : $ac_vert_rate = '';
		isset($row['seen_pos']) ? $ac_seen_pos = $row['seen_pos'] : $ac_seen_pos = '';
		isset($row['seen']) ? $ac_seen = $row['seen'] : $ac_seen = '';
		isset($row['rssi']) ? $ac_rssi = $row['rssi'] : $ac_rssi = '';
		isset($row['messages']) ? $ac_messages = $row['messages'] : $ac_messages = '';
		isset($row['category']) ? $ac_category = $row['category'] : $ac_category = '';
		isset($row['squawk']) ? $ac_squawk = $row['squawk'] : $ac_squawk = '';
		isset($row['nucp']) ? $ac_nucp = $row['nucp'] : $ac_nucp = '';
		isset($row['mlat']) ? $ac_mlat = implode(' ', $row['mlat']) : $ac_mlat = '';
		isset($row['tisb']) ? $ac_tisb = implode(' ', $row['tisb']) : $ac_tisb = '';
		$ac_lat && $ac_lon ? $ac_dist = round(func_haversine($rec_lat, $rec_lon, $ac_lat, $ac_lon), 1) : $ac_dist = '';

		// generate sql insert statement per aircraft in range of user set altitude/latitude/longitude and optionally according only to hex or flight numbers in hex_code_array.txt and flight_code_array.txt
		#var_dump($hex_code_array); var_dump($flight_code_array); // show arrays for debug
		if ($user_set_array['filter_mode_database'] && $user_set_array['filter_mode_database_limited']) {
			if (($ac_altitude != '' && $ac_altitude < $user_set_array['max_alt'] && $ac_lat < $user_set_array['max_lat'] && $ac_lat > $user_set_array['min_lat'] && $ac_lon < $user_set_array['max_lon'] && $ac_lon > $user_set_array['min_lon']) && (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
				$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', '$ac_dist', ";
				$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
				$sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb', '$message_rate');";
				$sql .= PHP_EOL;
				$x++;
			}
		} else if ($user_set_array['filter_mode_database']) {
			if (($ac_altitude != '' && $ac_altitude < $user_set_array['max_alt'] && $ac_lat < $user_set_array['max_lat'] && $ac_lat > $user_set_array['min_lat'] && $ac_lon < $user_set_array['max_lon'] && $ac_lon > $user_set_array['min_lon']) || (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
				$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', '$ac_dist', ";
				$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
				$sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb', '$message_rate');";
				$sql .= PHP_EOL;
				$x++;
			}
		} else {
			if ($ac_altitude != '' && $ac_altitude < $user_set_array['max_alt'] && $ac_lat < $user_set_array['max_lat'] && $ac_lat > $user_set_array['min_lat'] && $ac_lon < $user_set_array['max_lon'] && $ac_lon > $user_set_array['min_lon']) {
				$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', '$ac_dist', ";
				$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
				$sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb', '$message_rate');";
				$sql .= PHP_EOL;
				$x++;
			}
		}

		// set and modify alert-trigger-array and build alert-message optionally according only to hex or flight numbers in hex_code_array.txt and flight_code_array.txt
		if ($user_set_array['filter_mode_alert'] && $user_set_array['filter_mode_alert_limited']) {
			if (($ac_altitude != '' && $ac_altitude < $user_set_array['alert_max_alt'] && $ac_lat < $user_set_array['alert_max_lat'] && $ac_lat > $user_set_array['alert_min_lat'] && $ac_lon < $user_set_array['alert_max_lon'] && $ac_lon > $user_set_array['alert_min_lon']) && (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
				if (!array_key_exists($ac_hex, $alert_trigger_array)) {
					$alert_message_subject = urlencode('### STRAFER-ALERT ### ' . $ac_flight . ' ' . $ac_hex . ' : ' . $ac_dist . 'Nm ' . $ac_lat . ' ' . $ac_lon . ' : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message_body = urlencode($ac_flight  . ' ' . $ac_hex . ' : ' . $ac_dist . 'Nm <a href="http://www.google.com/maps/place/' . $ac_lat . ',' . $ac_lon . '/@' . $ac_lat . ',' . $ac_lon . ',12z">' . $ac_lat . ' ' . $ac_lon . '</a> : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message = 'key=' . $user_set_array['secret_email_key'] . '&subject=' . $alert_message_subject . '&body=' . $alert_message_body;
					if ($ac_hex) {
						$alert_trigger_array[$ac_hex] = time();
						#var_dump($alert_trigger_array); // show array for debug
					}
				}
			}
		} else if ($user_set_array['filter_mode_alert']) {
			if (($ac_altitude != '' && $ac_altitude < $user_set_array['alert_max_alt'] && $ac_lat < $user_set_array['alert_max_lat'] && $ac_lat > $user_set_array['alert_min_lat'] && $ac_lon < $user_set_array['alert_max_lon'] && $ac_lon > $user_set_array['alert_min_lon']) || (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
				if (!array_key_exists($ac_hex, $alert_trigger_array)) {
					$alert_message_subject = urlencode('### STRAFER-ALERT ### ' . $ac_flight . ' ' . $ac_hex . ' : ' . $ac_dist . 'Nm ' . $ac_lat . ' ' . $ac_lon . ' : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message_body = urlencode($ac_flight  . ' ' . $ac_hex . ' : ' . $ac_dist . 'Nm <a href="http://www.google.com/maps/place/' . $ac_lat . ',' . $ac_lon . '/@' . $ac_lat . ',' . $ac_lon . ',12z">' . $ac_lat . ' ' . $ac_lon . '</a> : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message = 'key=' . $user_set_array['secret_email_key'] . '&subject=' . $alert_message_subject . '&body=' . $alert_message_body;
					if ($ac_hex) {
						$alert_trigger_array[$ac_hex] = time();
						#var_dump($alert_trigger_array); // show array for debug
					}
				}
			}
		} else {
			if ($ac_altitude != '' && $ac_altitude < $user_set_array['alert_max_alt'] && $ac_lat < $user_set_array['alert_max_lat'] && $ac_lat > $user_set_array['alert_min_lat'] && $ac_lon < $user_set_array['alert_max_lon'] && $ac_lon > $user_set_array['alert_min_lon']) {
				if (!array_key_exists($ac_hex, $alert_trigger_array)) {
					$alert_message_subject = urlencode('### STRAFER-ALERT ### ' . $ac_flight . ' ' . $ac_hex . ' : ' . $ac_dist . 'Nm ' . $ac_lat . ' ' . $ac_lon . ' : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message_body = urlencode($ac_flight  . ' ' . $ac_hex . ' : ' . $ac_dist . 'Nm <a href="http://www.google.com/maps/place/' . $ac_lat . ',' . $ac_lon . '/@' . $ac_lat . ',' . $ac_lon . ',12z">' . $ac_lat . ' ' . $ac_lon . '</a> : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message = 'key=' . $user_set_array['secret_email_key'] . '&subject=' . $alert_message_subject . '&body=' . $alert_message_body;
					if ($ac_hex) {
						$alert_trigger_array[$ac_hex] = time();
						#var_dump($alert_trigger_array); // show array for debug
					}
				}
			}
		}

		// delete aircraft after user set seconds from already-message-sent-trigger
		$outdated_entry = time() - $user_set_array['aircraft_suspend_time'];
		foreach ($alert_trigger_array as $key => $value) {
			if ($value < $outdated_entry) {
				unset($alert_trigger_array[$key]);
			}
		}

		// send alert-message according to absolute limit for maximum number of messages and reset alert-message
		if ($alert_message != '' && $sent_alert_messages < $user_set_array['mailer_limit']) {
			if ($user_set_array['alert_method'] == 'gmail') {
				$email = $user_set_array['email_address'];
				$header  = 'MIME-Version: 1.0' . PHP_EOL;
				$header .= 'Content-type: text/html; charset=iso-8859-1' . PHP_EOL;
				$header .= 'From: ' . $user_set_array['email_address'] . PHP_EOL;
				$header .= 'Reply-To: ' . $user_set_array['email_address'] . PHP_EOL;
				$header .= 'X-Mailer: PHP ' . phpversion();
				mail($user_set_array['email_address'], urldecode($alert_message_subject), urldecode($alert_message_body), $header);
			} else if ($user_set_array['alert_method'] == 'pushover') {
				file_get_contents($user_set_array['url_mailer'] . '?mode=pushover&' . $alert_message);
			} else if ($user_set_array['alert_method'] == 'webmail') {
				file_get_contents($user_set_array['url_mailer'] . '?mode=webmail&' . $alert_message);
			}
			$sent_alert_messages++;
			$alert_message = '';
		}

	}

    // if db connection is ok write selected aircraft data to database
    try {
    	$db = new PDO('mysql:host=' . $user_set_array['db_host'] . ';dbname=' . $user_set_array['db_name'] . '', $user_set_array['db_user'], $user_set_array['db_pass']); $db_insert = '';
    	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    	if ($sql) { $db->exec($sql); $db_insert = 'inserted'; }
    	$db = null;
    } catch (PDOException $db_error) {
    	$db_insert = 'db-error' . PHP_EOL . $db_error->getMessage();
    }
    
    // generate terminal output and set sleep timer to get minimum a full second until next aircraft.json is ready to get fetched
    $runtime = (time() - $start_time);
    $runtime_formatted = sprintf('%d days %02d:%02d:%02d', $runtime/60/60/24,($runtime/60/60)%24,($runtime/60)%60,$runtime%60);
    ($runtime > 0) ? $loop_clock = number_format(round(($i / $runtime),6),6) : $loop_clock = number_format(1, 6);
    $process_microtime = (round(1000000 * (microtime(true) - $start_loop_microtime)));
    print('upt(us): ' . sprintf('%07d', $process_microtime) . ' - ' . $loop_clock . ' loops/s avg - since ' . $runtime_formatted . ' - run ' . $i . ' @ ' . number_format($message_rate, '1', ',', '.') . ' msg/s -> ' . sprintf('%03d', $x) . ' dataset(s) => ' . $db_insert . PHP_EOL);
    sleep($user_set_array['sleep']);
    $i++;

}

?>

sample hex_code_array.txt

hex1, hex2, hex3

sample flight_code_array.txt

flight1, flight2, flight3

mailer.php

<?php

#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);

// set key (letters/numbers only) according to the key you set in radar.php
$user_key = 'YOUR_USER_KEY';

// set your email address if you want to use email alert
$user_email = 'YOUR_EMAIL@EMAIL.COM';

// set your pushover token and key if you want to use pushover alert
$pushover_api_token = 'YOUR_API_TOKEN'; $pushover_user_key = 'YOUR_USER_KEY';

isset($_GET['key']) ? $key = $_GET['key'] : $key = '';
isset($_GET['mode']) ? $mode = $_GET['mode'] : $mode = '';
isset($_GET['subject']) ? $subject = urldecode($_GET['subject']) : $subject = '';
isset($_GET['body']) ? $body = urldecode($_GET['body']) : $body = '';

if ($key == $user_key && $mode == 'webmail') {
	$header  = 'MIME-Version: 1.0' . PHP_EOL;
	$header .= 'Content-type: text/html; charset=iso-8859-1' . PHP_EOL;
	$header .= 'From: ADS-B RADAR' . PHP_EOL;
	$header .= 'Reply-To: ' . $user_email . PHP_EOL;
	$header .= 'X-Mailer: PHP '. phpversion();
	mail($user_email, $subject, $body, $header);
} else if ($key == $user_key && $mode == 'pushover') {
	curl_setopt_array($ch = curl_init(), array(
		CURLOPT_URL => 'https://api.pushover.net/1/messages.json',
		CURLOPT_POSTFIELDS => array('token' => $pushover_api_token, 'user' => $pushover_user_key, 'message' => $body, 'html' => '1'),
		CURLOPT_SAFE_UPLOAD => true,
		CURLOPT_RETURNTRANSFER => true,
	));
	curl_exec($ch);
	curl_close($ch);
}

?>
1 Like

1 Like

haha :slight_smile: yes the original thread has got too long and confusing like the one with flightairmap - so i thought it were easier for new visitors to see what is the actual version and what is the thread about …

1 Like

I wouldn’t put that mailer.php script in a publicly accessible server. It is wide open to spam abuse.

what are you talking about ci?

My apologies. I misread the code with the $user_key check.

That’s what I get for reading code late at night.

1 Like

ok :slight_smile: but it is not only the key - all possible attacks that i’m aware of are against parameters 1, 4 or 5 of mail function. parameter 5 isn’t set at all and 1 and 4 are hardcoded in the script. so even if there were no key i do not see how to inject different mail addresses or additional headers via linebreaks.

added distance and receiver-overall-message-rate column to database script …

just a simple sample how to replay a single flight from database in skyview/mutability map

replay.php

<?php

// set hex and date you want to replay and set $i to timelapse-factor e.g. 10 is 10-times faster than reality
$user_hex = 'YOUR_HEX';    $date_to_watch = 'YYYY-MM-DD%';    $speed_factor = 10;

$sql = "select * from aircrafts where hex='$user_hex' and message_date like '$date_to_watch' and squawk!='' and flight!='' and lat!='' and lon!='' and nucp!='' and seen_pos!='' and altitude!='' and vert_rate!='' and track!='' and speed!='' and category!='' and messages!='' and seen!='' and rssi!='' order by id asc";
$db = new PDO('mysql:host=127.0.0.1;dbname=adsb', 'root', 'YOUR_PASSWORD');
$stmt = $db->prepare($sql);
$stmt->execute();
$select = $stmt->fetchAll();

$i = 0;

foreach  ($select as $result) {

	$aircraft_json = '{ "now" : ' . $result['now'] . ',' . PHP_EOL;
	$aircraft_json .= '  "messages" : 60296972,' . PHP_EOL;
	$aircraft_json .= '  "aircraft" : [' . PHP_EOL;
	$aircraft_json .= '    {"hex":"' . $result['hex'] . '","squawk":"' . $result['squawk'] . '","flight":"' . $result['flight'] . '","lat":' . $result['lat'] . ',"lon":' . $result['lon'] . ',"nucp":' . $result['nucp'] . ',"seen_pos":' . $result['seen_pos'] . ',"altitude":' . $result['altitude'] . ',"vert_rate":' . $result['vert_rate'] . ',"track":' . $result['track'] . ',"speed":' . $result['speed'] . ',"category":"' . $result['category'] . '","mlat":"","tisb":"","messages":' . $result['messages'] . ',"seen":' . $result['seen'] . ',"rssi":' . $result['rssi'] . '}' . PHP_EOL;
	$aircraft_json .= '  ]' . PHP_EOL;
	$aircraft_json .= '}' . PHP_EOL;
	$i++;

	if ($i == $speed_factor) { file_put_contents('/YOUR_PATH/data/aircraft.json', $aircraft_json, LOCK_EX); $i = 0; sleep(1); }

}

?>
1 Like

Hopefully I have my Pi configured correctly but I get this error in my lighttpd error log:

2017-10-28 23:09:59: (mod_fastcgi.c.2702) FastCGI-stderr: PHP Parse error: syntax error, unexpected ‘now’ (T_STRING) in /var/www/html/replay.php on line 16

Cheers,
Michael

this snippet (replay.php) does not run from webserver but terminal. e.g. put replay.php in /home/pi/replay.php → run ‘php /home/pi/replay.php’ in terminal. and of course you can’t run it while dump1090 runs - at least if you use the same html directory for both …

OK TomMuc thanks for that will give it a try.

I think I’ve done everything right… probably not.

Error message:
Could not open input file: radar.php

Can someone help, please?

OK – I figured out I needed to customize and put the files into my home directory.

Now this:

PHP Warning: file_get_contents(http://127.0.0.1/dump1090/data/aircraft.json): failed to open stream: HTTP request failed! HTTP/1.0 404 Not Found
in /home/pi/radar.php on line 96
PHP Notice: Undefined offset: -1 in /home/pi/radar.php on line 109
PHP Notice: Undefined offset: 0 in /home/pi/radar.php on line 109
PHP Notice: Undefined offset: -1 in /home/pi/radar.php on line 110
PHP Notice: Undefined offset: 0 in /home/pi/radar.php on line 110
PHP Warning: Invalid argument supplied for foreach() in /home/pi/radar.php on line 114
upt(us): 0005025 - 1.000000 loops/s avg - since 0 days 00:01:12 - run 72 @ 0,0 msg/s → 000 dataset(s) =>
PHP Warning: file_get_contents(http://127.0.0.1/dump1090/data/aircraft.json): failed to open stream: HTTP request failed! HTTP/1.0 404 Not Found
in /home/pi/radar.php on line 96
PHP Notice: Undefined offset: -1 in /home/pi/radar.php on line 109
PHP Notice: Undefined offset: 0 in /home/pi/radar.php on line 109
PHP Notice: Undefined offset: -1 in /home/pi/radar.php on line 110
PHP Notice: Undefined offset: 0 in /home/pi/radar.php on line 110
PHP Warning: Invalid argument supplied for foreach() in /home/pi/radar.php on line 114
upt(us): 0005358 - 1.000000 loops/s avg - since 0 days 00:01:13 - run 73 @ 0,0 msg/s → 000 dataset(s) =>

It will give that error every second. I know I’m getting closer! Please help.

you have to set the correct path to aircraft.json file here in line 28:

// set path to aircraft.json file
$user_set_array['url_json'] = 'http://127.0.0.1/dump1090/data/';

First, thank you for your work. This seems like such a cool concept. I
love numbers. I love columns of numbers.

I’m still confused. I have no idea what I should replace the path with.
I’m already 127.0.0.1, aren’t I? Isn’t that the standard loopback address?

I am running a plain vanilla Flightaware install and have cut and pasted
your quick install instructions. I have no idea where aircarft.json is or
if it even exists.

does the script run on the same machine your dongle runs?
what is the path in your browser when you open the map to watch the aircrafts?

192.168.0.14 – on my network

// set path to aircraft.json file
$user_set_array[‘url_json’] = ‘http://192.168.0.14/dump1090/data/’;

Still doesn’t work.

PHP Warning: file_get_contents(
http://192.168.0.14/dump1090/data/aircraft.json): failed to open stream:
HTTP request failed! HTTP/1.0 404 Not Found
in /home/pi/radar.php on line 96
PHP Notice: Undefined offset: -1 in /home/pi/radar.php on line 109
PHP Notice: Undefined offset: 0 in /home/pi/radar.php on line 109
PHP Notice: Undefined offset: -1 in /home/pi/radar.php on line 110
PHP Notice: Undefined offset: 0 in /home/pi/radar.php on line 110
PHP Warning: Invalid argument supplied for foreach() in /home/pi/radar.php
on line 114
upt(us): 0005277 - 1.000000 loops/s avg - since 0 days 00:00:03 - run 3 @
0,0 msg/s → 000 dataset(s) =>
PHP Warning: file_get_contents(
http://192.168.0.14/dump1090/data/aircraft.json): failed to open stream:
HTTP request failed! HTTP/1.0 404 Not Found
in /home/pi/radar.php on line 96
PHP Notice: Undefined offset: -1 in /home/pi/radar.php on line 109
PHP Notice: Undefined offset: 0 in /home/pi/radar.php on line 109
PHP Notice: Undefined offset: -1 in /home/pi/radar.php on line 110
PHP Notice: Undefined offset: 0 in /home/pi/radar.php on line 110
PHP Warning: Invalid argument supplied for foreach() in /home/pi/radar.php
on line 114
upt(us): 0005525 - 1.000000 loops/s avg - since 0 days 00:00:04 - run 4 @
0,0 msg/s → 000 dataset(s) =>

I apologize for taking so much of your time. I’m sure I’m doing something
wrong.