pihole Porn / Pushbullet alerting

porn-FTL-noty.php

<?php

/**
 * Created by Dreamweaver
 * User: Sebastian Sascha Helfinger
 * Github: @SHelfinger
 * Date: 24.01.2023 (d.m.Y)
 * Time: 12:53
 *
 * Requires: crontab, php-curl
 * Install: crontab -e (run via wget or php) and apt install php-curl
 */

// Maximal execution time set to 600
ini_set( 'max_execution_time', '600' );
// Debug (true, false)
define( 'NOTY_DEBUG', false );
if ( NOTY_DEBUG ) {
	// Display errors
	ini_set( 'display_errors', 1 );
	// Startup Display errors
	ini_set( 'display_startup_errors', 1 );
	// Show all errors on site
	error_reporting( E_ALL );
}
// API Access Tokens from Pushbullet API's Console (https://www.pushbullet.com/#settings/account)
define( 'API_ACCESS_KEY', 'API_KEY' );
// Pushbullet iden key, (Tip: Export Entire Push History under Settings -> Push History)
define( 'PUSHBULLET_IDEN', 'PUSHBULLET_IDEN' );
// Define PiHole pihole-FTL.db file
define( 'PIHOLE_QUERIES', '/etc/pihole/pihole-FTL.db' );
// Define Porn.list file (https://raw.githubusercontent.com/chadmayfield/pihole-blocklists/master/lists/pi_blocklist_porn_top1m.list)
define( 'PIHOLE_PORN_LIST', 'pi_blocklist_porn_top1m.list' );
// Between how many minutes
define( 'MINUTES_SEARCH', 1 );

$db = null;
$debug = [];

$db = new SQLite3( PIHOLE_QUERIES );
if ( !$db ) {
	if ( NOTY_DEBUG ) array_push( $debug, [ "Failed", "DB FILE NOT FOUND" ] );
} else {
	if ( NOTY_DEBUG ) array_push( $debug, [ "Success", "DB FILE FOUND" ] );
}

try {
	// Enable SQLite exceptions
	$db->enableExceptions( true );
	// Load Porn List file (Download before)
	$domaines = "";
	if ( is_array( PIHOLE_PORN_LIST ) ) {
		foreach( PIHOLE_PORN_LIST as $list ) {
			$lines = file( $list, FILE_IGNORE_NEW_LINES );
			$domaines = $domaines . ", " . str_replace( '"', "'", str_replace( ']', '', str_replace( '[', '', json_encode( $lines, JSON_UNESCAPED_SLASHES ) ) ) );
		}
	} else {
		$lines = file( PIHOLE_PORN_LIST, FILE_IGNORE_NEW_LINES );
		$domaines = str_replace( '"', "'", str_replace( ']', '', str_replace( '[', '', json_encode( $lines, JSON_UNESCAPED_SLASHES ) ) ) );
	}
	
	$timestamp = time();
	
	$sql = "SELECT * FROM queries WHERE timestamp BETWEEN " . ( $timestamp - (MINUTES_SEARCH * 60 ) ) . " AND " . $timestamp . " AND domain IN (" . $domaines . ") GROUP BY domain ORDER BY timestamp DESC";
	if ( NOTY_DEBUG ) array_push( $debug, [ "SQL", $sql ] );
	
	$res = $db->query( $sql );
	
	$dataFromFTLDB = [];

    if ( !empty( $res ) ) {
		if ( NOTY_DEBUG ) array_push( $debug, [ "Success", "We have found queries " ] );
        while ( $row = $res->fetchArray( SQLITE3_ASSOC ) ) {
            array_push( $dataFromFTLDB, $row );
        }
    } else {
		if ( NOTY_DEBUG ) array_push( $debug, [ "Failed", "We have not found any queries" ] );
    }
	
	$count = count( $dataFromFTLDB );
	if ( $count > 0 ) {
		if ( NOTY_DEBUG ) array_push( $debug, [ "Success", "Passing through the rows" ] );
		$client = $dataFromFTLDB[0]['client'];
		$sql = "SELECT * FROM client_by_id WHERE ip = '" . $client . "' AND name IS NOT NULL AND name != '' LIMIT 1";
		if ( NOTY_DEBUG ) array_push( $debug, [ "SQL", "Show client IP name SQL: " . $sql ] );
		$ras = $db->query( $sql );
		if ( !empty( $ras ) ) {
			$rew = $ras->fetchArray( SQLITE3_ASSOC );
			if ( isset( $rew['name'] ) ) {
				$client = $rew['name'];
				if ( NOTY_DEBUG ) array_push( $debug, [ "Success", "Assign IP to name: " . $dataFromFTLDB[0]['client'] . " -> " . $rew['name'] ] );
			} else {
				$client = $client;
				if ( NOTY_DEBUG ) array_push( $debug, [ "Failed", "Assign IP to name" ] );
			}
		} else {
			$client = $client;
			if ( NOTY_DEBUG ) array_push( $debug, [ "Failed", "Assign IP to name" ] );
		}
		$date = "";
		if ( date( "d.m.Y", $dataFromFTLDB[0]['timestamp'] ) != date( "d.m.Y" ) ) {
			$date = date( "d.m.Y " );
		}
		$text = $date . date( "H:i:s", $dataFromFTLDB[0]['timestamp'] ) . ": We have found, " . $client . " is visiting " . $dataFromFTLDB[0]['domain'];
		if ( NOTY_DEBUG ) array_push( $debug, [ "Success", "Text to Pushbullet: " . $text ] );
		$pushbullet = pushbullet( API_ACCESS_KEY, 'Porn Notification', $text, PUSHBULLET_IDEN );
		if ( NOTY_DEBUG ) array_push( $debug, [ "DEBUG", $pushbullet ] );
	} else {
		if ( NOTY_DEBUG ) array_push( $debug, [ "DEBUG", "No rows found" ] );
	}
	$db->close();
	if ( NOTY_DEBUG ) array_push( $debug, [ "DEBUG", "DB closed" ] );
}
catch( Exception $e ) {
	if ( NOTY_DEBUG ) array_push( $debug, [ "Failed", "Exception: " . $e->getMessage() ] );
}

if ( NOTY_DEBUG ) echo json_encode( $debug );

/*
	Gist Pushbullet from @EdwinHoksberg
	URL: https://gist.github.com/EdwinHoksberg/7286e7a0123b59f9ca80698bac324958
	Copied: 24.01.2023 (d.m.Y)
*/
function pushbullet( string $accessToken, string $title, string $body, string $device_iden = '', string $type = 'note' ) {
    if ( !in_array( $type, [ 'note', 'url' ] ) ) {
        return 'Invalid pushbullet push type';
    }
    $curl = curl_init();
    curl_setopt_array( $curl, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_URL => 'https://api.pushbullet.com/v2/pushes',
        CURLOPT_SSL_VERIFYHOST => 2,
        CURLOPT_SSL_VERIFYPEER => true,
        CURLOPT_HTTPHEADER => [
            "Access-Token: " . $accessToken,
            'Content-Type: application/json',
        ],
        CURLOPT_POST => true,
        CURLOPT_POSTFIELDS => json_encode( compact( 'title', 'body', 'type', 'device_iden' ) ),
    ]);
    $result = curl_exec( $curl );
    $json = json_decode( $result );

    curl_close( $curl );

    if ( json_last_error() !== JSON_ERROR_NONE ) {
        return 'Invalid json returned from pushbullet api';
    }

    if ( !empty( $json->error ) ) {
        return "Invalid api request: {$json->error->message}";
    }
	
    return $json;
}

?>

Install as root

crontab -e
*/1 * * * * wget "http://PIHOLE_URL/porn-FTL-noty.php" > /dev/null 2>&1

PHP-curl
apt install php-curl