Neuer Eintrag in der Datenbank, Duplikat soll vorhanden Eintrag ersetzen

[sharbich]

Hallo Ihr Lieben,
ich habe folgende Datenbank.

MariaDB [fail2ban]> SELECT * FROM `fail2ban` ORDER BY `created` ASC;
+----+----------+---------------------+---------+----------+------+--------------+
| id | hostname | created             | name    | protocol | port | ip           |
+----+----------+---------------------+---------+----------+------+--------------+
|  6 | dsme01   | 2023-12-31 10:34:41 | pf-sasl | tcp      |      | 51.161.83.73 |
|  7 | dsme01   | 2023-12-31 21:42:45 | pf-sasl | tcp      |      | 2.57.149.168 |
|  8 | dsme01   | 2024-01-01 12:33:32 | pf-sasl | tcp      |      | 51.161.83.73 |
+----+----------+---------------------+---------+----------+------+--------------+
​

Wenn ein neuer Eintrag hinzugefügt wird möchte ich vorher prüfen ob die $IP schon vorhanden ist. Falls ja soll der Eintrag nur aktualisiert werden, ohne das die $ID geändert wird. Es geht sich um folgenden Code:

$query = "INSERT INTO `".$tablename."`(`hostname`, `created`, `name`, `protocol`, `port`, `ip`) VALUES ('".addslashes($hostname)."',NOW(),'".addslashes($name)."','".addslashes($protocol)."','".addslashes($port)."','".addslashes($ip)."')"; 

Ich habe im Netz folgenden Vorschlag gesichtet:

INSERT INTO table (id,a,b,c,d,e,f,g) VALUES (1,2,3,4,5,6,7,8)
ON DUPLICATE KEY UPDATE a=VALUES(a),b=VALUES(b),c=VALUES(c),d=VALUES(d),e=VALUES(e),f=VALUES(f),g=VALUES(g)​ 

Wie setze ich das korrekt um? Könnt Ihr mich da etwas unterstützen?
Vorab herzlichen Dank.

Gruß von Stefan Harbich

[scatello]

id,a,b,c,d,e,f,g ersetzt du durch deine Spaltennamen und 1,2,3,4,5,6,7,8 ersetzt du durch deine Werte. Wo ist jetzt das Problem?

[sharbich]

1,2,3,4,5,6,7,8 ersetzt du durch deine Werte. Wo ist jetzt das Problem?

Wo ich nicht weiter komme sind die Werte.
Welche Werte muss ich eintragen? Alle Werte aus der Tabelle? Ich möchte das nur die $IP überprüft wird. Alle anderen Werte können ggf. gleich sein. Muss ich dann für jede $IP einen Wert vergeben? Also im obigen Beispiel 3 Werte? Was ist wenn weitere Werte dazu kommen? Die Tabelle kann sehr schnell auf mehrere Hundert Einträge anwachsen. Je nachdem wie sehr ich aus dem Netzt angegriffen werde.

[sharbich]

Ich versuche es mal. Etwa so:

$query = "INSERT INTO `".$tablename."`(`hostname`, `created`, `name`, `protocol`, `port`, `ip`) VALUES ('".addslashes($hostname)."',NOW(),'".addslashes($name)."','".addslashes($protocol)."','".addslashes($port)."','".addslashes($ip)."') ON DUBLICATE KEY UPDATE hostname=VALUES('".addslashes($hostname)."'),. . ."; 
​ 

[scatello]

Etwa so

Einfach mal ausprobieren, eventuell auch einfach mit phpMyAdmin testen.

[sharbich]

Hallo Ihr Lieben,

ich habe den Query wie folgt erstellt:

$query = "INSERT INTO `".$tablename."`(`hostname`, `created`, `name`, `protocol`, `port`, `ip`) VALUES ('".addslashes($hostname)."',NOW(),'".addslashes($name)."','".addslashes($protocol)."','".addslashes($port)."','".addslashes($ip)."') ON DUBLICATE KEY UPDATE hostname=VALUES('".addslashes($hostname)."'),created=VALUES('".addslashes($created)."'),name=VALUES('".addslashes($name)."'),protocol=VALUES('".addslashes($protocol)."'),port=VALUES('".addslashes($port)."'),ip=VALUES('".addslashes($ip)."')"; 

Leider mit folgender Fehlermeldung:

root@dsme01:/etc/fail2ban# ./fail2ban.php jailname ssh 22 123.123.123.123
#!/usr/bin/php
PHP Notice:  Undefined variable: created in /etc/fail2ban/fail2ban.php on line 42
Error: INSERT INTO `fail2ban`(`hostname`, `created`, `name`, `protocol`, `port`, `ip`) VALUES ('dsme01',NOW(),'jailname','ssh','22','123.123.123.123') ON DUBLICATE KEY UPDATE hostname=VALUES('dsme01'),created=VALUES(''),name=VALUES('jailname'),protocol=VALUES('ssh'),port=VALUES('22'),ip=VALUES('123.123.123.123')<br>You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DUBLICATE KEY UPDATE hostname=VALUES('dsme01'),created=VALUES(''),name=VALUES...' at line 1​

[scatello]

Frage: hat deine Tabelle überhaupt eine Unique-Spalte oder eine Spalte mit Primary Key? Denn nur dann funktioniert "ON DUBLICATE KEY"
Bei `hostname`, `created`, `name`, `protocol`, `port`, `ip` kann ich mir das nicht vorstellen.

[sharbich]

Frage: hat deine Tabelle überhaupt eine Unique-Spalte oder eine Spalte mit Primary Key?

Im Post https://www.php-resource.de/forum/sq...en#post1046505 sind die Spalten ersichtlich. Die erste Spalte ist eine ID gekennzeichnet als PRIMARY. Muss ich diese ggf. auf Unique setzen? Oder muss ich eine weitere Spalte hinzufügen?

[scatello]

sind die Spalten ersichtlich

Ja, aber nur das Ergebnis eines Selects.

Die erste Spalte ist eine ID gekennzeichnet als PRIMARY.

Das hilft dir nicht weiter, denn du hast ja keine ID, die du beim Insert nehmen kannst.

Muss ich diese ggf. auf Unique setzen?

Deine Tabellenspalte kannst du nicht auf Unique setzen, denn es können ja durchaus doppelte Werte vorkommen.


Ich schätze, du musst vor dem Insert ein Select mit diesen Daten durchführen und prüfen, ob es einen Treffer gibt. Wenn ja, dann Update, wenn nein, dann Insert. Allerdings darfst du beim Prüfen das Datum nicht berücksichtigen.

Beispiel (ungeteset):

$dies  = "Hallo";
$das   = "Welt";
$jenes = "Dummy"

$query = "select `id`, `dies`, `das`, `jenes` from `tabelle` where `dies`='$dies' and `das`='$das' and `jenes`='$jenes'";

$result = mysqli_query($con, $query)
   or die("MySQL-Error: " . mysqli_error($con));

$rowcount = mysqli_num_rows($result);

if ($rowcount)
{
    $data = mysqli_fetch_assoc($result);

    $query = "Update `tabelle` (`created`) values(now()) where 'id'=" . $data['id'];

    $result = mysqli_query($con, $query)
       or die("MySQL-Error: " . mysqli_error($con));
}
else
{
    $query = "insert into  `tabelle` (`dies`, `das`, `jenes`, `created`) values('$dies', '$das', '$jenes', now())";

    $result = mysqli_query($con, $query)
       or die("MySQL-Error: " . mysqli_error($con));
}

​ 

[sharbich]

Deine Tabellenspalte kannst du nicht auf Unique setzen, denn es können ja durchaus doppelte Werte vorkommen.

Die id wird bei jeder neuen Zeile automatisch gesetzt +1. Die Nummer ist eindeutig und kommt nur einmal vor.

Warum geht dann "ON DUBLICATE KEY UPDATE" nicht?

[scatello]

Warum geht dann "ON DUBLICATE KEY UPDATE" nicht?

Die ID hast du doch nicht, wenn du einen Eintrag einfügen willst. Und die ID ist bei deinem Vergleich, ob es schon einen passenden Eintrag in der DB gibt, völlig wurscht, die spielt doch keine Rolle. Du müsstes beim Insert schon die richtige ID kennen, aber woher? Hast du einfach nicht.

[sharbich]

Guten Morgen,

es scheint aber auch anders zu gehen. Das habe ich im Netz gefunden.
https://stackoverflow.com/questions/...last-insert-id

[scatello]

Das habe ich im Netz gefunden.

Dann viel Erfolg damit. Ich sehe da keinen Hinweis, womit es bei dir funktionieren könnte.

[sharbich]

Hallo scatello,

Du hast Recht. Es geht wohl nur mit Deinem Code. Allerdings bekomme ich das nicht hin. Hier nochmals meine Tabelle:

MariaDB [(none)]> use fail2ban;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [fail2ban]> show columns from fail2ban;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| hostname | varchar(255)        | YES  | MUL | NULL    |                |
| created  | datetime            | NO   |     | NULL    |                |
| name     | text                | NO   |     | NULL    |                |
| protocol | varchar(16)         | NO   |     | NULL    |                |
| port     | varchar(32)         | NO   |     | NULL    |                |
| ip       | varchar(64)         | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
7 rows in set (0,001 sec)
​

Ich habe Deinen Code wie folgt geändert:

$query = "select `id`, `hostname`, `name`, `protocol`, `port`, `ip` from `".$tablename."` where `hostname`='$hostname' and `name`='$name' and `protocol`='$protocol' and `port`='$port' and `ip`='$ip'";

$result = mysqli_query($link, $query)
   or die("MySQL-Error: " . mysqli_error($link));

$rowcount = mysqli_num_rows($result);

if ($rowcount)
{
    $data = mysqli_fetch_assoc($result);

    $query = "Update `".$tablename."` (`created`) values(now()) where 'id'=" . $data['id'];

    $result = mysqli_query($link, $query)
       or die("MySQL-Error: " . mysqli_error($link));
}
else
{
    $query = "INSERT INTO `".$tablename."`(`hostname`, `created`, `name`, `protocol`, `port`, `ip`) VALUES ('".addslashes($hostname)."',NOW(),'".addslashes($name)."','".addslashes($protocol)."','".addslashes($port)."','".addslashes($ip)."')";

    $result = mysqli_query($link, $query)
       or die("MySQL-Error: " . mysqli_error($link));
}
​ 

Das IP Adresse Feld 'ip' muss eindeutig sein. Wenn die ip wieder auftaucht müssen die anderen Werte in der Spalte geändert werden. Sonst soll ein neuer Eintrag erstellt werden.

Es werden auch zwei Datensätze angelegt und es kommt folgende Fehlermeldung:

root@dsme01:/etc/fail2ban# ./fail2ban.php jailname http 80 123.123.123.124
#!/usr/bin/php
MySQL-Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(`created`) values(now()) where 'id'=7' at line 1root@dsme01:/etc/fail2ban# ./fail2ban.php jailname http 80 123.123.123.124
#!/usr/bin/php
Ip to BAN added to DATABASEroot@dsme01:/etc/fail2ban#

[scatello]

$query = "Update `".$tablename."` (`created`) values(now()) where 'id'=" . $data['id'];​ 

where 'id' da nimmst du die falschen Anführungszeichen, du musst Backticks nehmen, also `

Das IP Adresse Feld 'ip' muss eindeutig sein.

Also doch eine Spalte mit eindeutigen Werten. Wenn du die Spalte auf Unique setzt, dann sollte der Ansatz mit Duplicate Key funktionieren. In dem Eingangspost ist aber eine IP-Adresse doppelt zu sehen, daher bin ich nicht auf die Idee gekommen, dass diese Spalte eindeutig sein soll.