Change script for accessing MySQL database to PostgreSQL

S

sharbich

New member
Hello,
I'm trying to change a PHP script that writes entries in the MySQL database (fail2ban) to a PostgreSQL database. I'm trying to change two PHP files (config.php and fail2ban.php). I need your help here. Everything marked with --- should be deleted. Everything marked with +++ should be added. But I'm still missing something.
Code: 
root@dsme01:/etc/fail2ban# cat config.php
#!/usr/bin/php
<?php
// jail to be used
$jail = "pf-*";
// database configuration, use only one central mysql server
$dbserver="localhost";
$dbuser="fail2ban";
$dbpass="########";
$dbname="fail2ban";
$tablename="fail2ban";

--- // connect to mysql database
--- $link = mysqli_connect($dbserver, $dbuser, $dbpass, $dbname) or die('Could not connect: ' . mysqli_error());
--- mysqli_select_db($link,$dbname) or die('Could not select database');
+++ // connect to postgresql database
+++ $link = new PDO("pgsql:host=$dbserver;dbname=$dbname", $dbuser, $dbpass);
????
?>

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

root@dsme01:/etc/fail2ban# cat fail2ban.php
#!/usr/bin/php
<?php
//REQUIREMENTS:
--- // mysql
--- //sudo apt-get install php7.0 php-mysql
+++ // postgresql
+++ // sudo apt-get install php-pgsql
//MANUAL COMMANDS:
//
//UNBAN: sudo fail2ban-client set domoticz unbanip Ban 5.90.201.166
/*
Open the "jail.local" file and find the "banaction" used by the rule
It's necessary to add the following line to the "banaction" rule used.
php /home/domoticz/fail2ban-central/fail2ban.php <name> <protocol> <port> <ip>
EXAMPLE: if you use "iptables-multiport.conf" replace:
---------------------------------------------------------
actionban = <iptables> -I f2b-<name> 1 -s <ip> -j <blocktype>
---------------------------------------------------------
with:
---------------------------------------------------------
actionban = <iptables> -I f2b-<name> 1 -s <ip> -j <blocktype>
            php /home/domoticz/fail2ban-central/fail2ban.php <name> <protocol> <port> <ip>
---------------------------------------------------------
*/

require_once((dirname(__FILE__))."/config.php");
$name = $_SERVER["argv"][1];
$protocol = $_SERVER["argv"][2];
$port = $_SERVER["argv"][3];
if (!preg_match('/^\d{1,5}$/', $port))
    $port = getservbyname($_SERVER["argv"][3], $protocol);
$ip = $_SERVER["argv"][4];
$hostname = gethostname();
--- // query mysql
--- $query = "INSERT INTO
---               `".$tablename."`
---                   (`hostname`,
---                    `created`,
---                   `name`,
---                    `protocol`,
---                    `port`,
---                    `ip`)
---               VALUES
---                   ('".addslashes($hostname)."',
---                    NOW(),
---                    '".addslashes($name)."',
---                    '".addslashes($protocol)."',
---                    '".addslashes($port)."',
---                    '".addslashes($ip)."')
---               ON DUPLICATE KEY UPDATE
---                    `hostname` = '$hostname',
---                    `created` = NOW(),
---                    `name` = '$name',
---                    `protocol` = '$protocol',
---                    `port` = '$port'";
--- if (mysqli_query($link, $query)) {
---     echo "Ip to BAN added to DATABASE";
--- } else {
---     echo "Error: " . $query . "<br>" . mysqli_error($link);
--- }
--- mysqli_close($link);
+++ // query postgresql
????

exit;
?>
Can you help me?
Greetings from Stefan Harbich
 
Is this what you are after? Untested.
PHP: 
#!/usr/bin/php
<?php
// jail to be used
$jail = "pf-*";
// database configuration, use only one central PostgreSQL server
$dbserver = "localhost";
$dbuser = "fail2ban";
$dbpass = "########";
$dbname = "fail2ban";
$tablename = "fail2ban";

// connect to PostgreSQL database
try {
    $link = new PDO("pgsql:host=$dbserver;dbname=$dbname", $dbuser, $dbpass);
    $link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die('Could not connect: ' . $e->getMessage());
}

require_once((dirname(__FILE__)) . "/config.php");
$name = $_SERVER["argv"][1];
$protocol = $_SERVER["argv"][2];
$port = $_SERVER["argv"][3];

if (!preg_match('/^\d{1,5}$/', $port)) {
    $port = getservbyname($_SERVER["argv"][3], $protocol);
}

$ip = $_SERVER["argv"][4];
$hostname = gethostname();

// query PostgreSQL
$query = "INSERT INTO $tablename (hostname, created, name, protocol, port, ip) 
          VALUES (:hostname, NOW(), :name, :protocol, :port, :ip) 
          ON CONFLICT (ip) DO UPDATE SET 
              hostname = :hostname, 
              created = NOW(), 
              name = :name, 
              protocol = :protocol, 
              port = :port";

$stmt = $link->prepare($query);
$stmt->bindParam(':hostname', $hostname);
$stmt->bindParam(':name', $name);
$stmt->bindParam(':protocol', $protocol);
$stmt->bindParam(':port', $port);
$stmt->bindParam(':ip', $ip);

if ($stmt->execute()) {
    echo "IP to BAN added to DATABASE";
} else {
    echo "Error: " . $stmt->errorInfo()[2];
}

exit;
?>
is untested.
I am a little confused about what you are asking but have a go.
 
Hello Moorcam,
thanks, the script works. Now I want the created data record to be deleted after 1440 minutes. The following script was used for the mysql server. What would I have to change to be able to use it for the PostgreSQL server?
Code: 
root@dsme01:/etc/fail2ban# cat cron2ban.php
#!/usr/bin/php
<?php
// This must be added to Cronjob to run each 1 minute
//Check the PHP path with the command "which php" in terminal
// sudo crontab -e
// */1 * * * * /usr/bin/php /home/domoticz/fail2ban-central/cron2ban.php
// phpconfig.php will have database configuration settings
require_once((dirname(__FILE__))."/config.php");
// Delete Record in fail2ban mysql Database
$query = "DELETE FROM fail2ban WHERE DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 1440 MINUTE) > created";
if (mysqli_query($link, $query)) {
    echo "Delete Ip from DATABASE";
} else {
    echo "Error: " . $query . "<br>" . mysqli_error($link);
}
?>
Thank you in advance for your support, greetings from Stefan
 
Hello,
i've made a little progress. I can now delete the records in the PostgreSQL database fail2ban that are older than the current date. OK, so far so good. But what I want to achieve is that only the records that are older than 24 hours are deleted. I still have to adapt the script for this.
Code: 
#!/usr/bin/php
<?php
require_once((dirname(__FILE__)) . "/config_new.php");
$curdate=date(Y/m/d*);
// query PostgreSQL
$query = "DELETE FROM fail2ban WHERE created < '$curdate'";
$stmt = $link->prepare($query);                          # $link Information in config_new.php
$stmt->execute();
if ($stmt->execute()) {
    echo "IP delete in the DATABASE";
} else {
    echo "Error: " . $stmt->errorInfo()[2];
}
exit;
?>
Do you have any suggestions?
Greetings from Stefan Harbich
 
sharbich said:
Hello,
i've made a little progress. I can now delete the records in the PostgreSQL database fail2ban that are older than the current date. OK, so far so good. But what I want to achieve is that only the records that are older than 24 hours are deleted. I still have to adapt the script for this.
Code: 
#!/usr/bin/php
<?php
require_once((dirname(__FILE__)) . "/config_new.php");
$curdate=date(Y/m/d*);
// query PostgreSQL
$query = "DELETE FROM fail2ban WHERE created < '$curdate'";
$stmt = $link->prepare($query);                          # $link Information in config_new.php
$stmt->execute();
if ($stmt->execute()) {
    echo "IP delete in the DATABASE";
} else {
    echo "Error: " . $stmt->errorInfo()[2];
}
exit;
?>
Do you have any suggestions?
Greetings from Stefan Harbich
Click to expand...

Code: 
require_once(dirname(__FILE__) . "/config_new.php");
// Get the current date and time, formatted correctly
$curdate = date('Y-m-d H:i:s');
// Prepare the query to delete records older than 24 hours
$query = "DELETE FROM fail2ban WHERE created < NOW() - INTERVAL '24 hours'";
$stmt = $link->prepare($query);
// Execute the statement and check for success
if ($stmt->execute()) {    
echo "IP deleted from the DATABASE";
} 
else 
{    echo "Error: " . implode(", ", $stmt->errorInfo());
}
exit;
Your original code used date(Y/m/d*), which is incorrect. The correct format for the date is now specified as date('Y-m-d H:i:s'), ensuring that the date and time are captured accurately.
Instead of comparing the created field with a static date, the query now uses NOW() - INTERVAL '24 hours'. This approach dynamically calculates the cutoff time for deletion, ensuring that any records older than 24 hours are removed without needing to manually update the date.
The error handling has been improved by using implode(", ", $stmt-&gt;errorInfo()) to provide a clearer and more detailed error message if the execution fails.
$stmt->execute() only needs to be done once.
 
You must log in or register to reply here.
Back
Top