mySQL - Datensätze automatisch mitlöschen

[Linuchs]

Moin,

ich zähle Web-Besucher von Veranstaltungsterminen, indem ich 10 s nach Aufruf per Ajax den Counter weiterstelle.

Wer nach 10 s noch auf der Seite ist, sollte kein Bot, sondern ein Mensch sein.

Bisher wird jeder Aufruf auf diese Weise gezählt, ich möchte aber pro IP nur einmal zählen, also muss ich die IPs speichern.

Wie kann ich die IPs per mySQL automatisch mitlöschen, wenn die entspr. termin_id gelöscht wird?

fragt Linuchs

[Auge]

Hallo

ich zähle Web-Besucher von Veranstaltungsterminen, indem ich 10 s nach Aufruf per Ajax den Counter weiterstelle.

Bisher wird jeder Aufruf auf diese Weise gezählt, ich möchte aber pro IP nur einmal zählen, also muss ich die IPs speichern.

Wie kann ich die IPs per mySQL automatisch mitlöschen, wenn die entspr. termin_id gelöscht wird?

Das kommt – wie wohl meist – darauf an. SQL-Datenbanken haben üblicherweise den Mechanismus von Foreign Keys (Fremdschlüssel). Das heißt, dass Felder in einer Tabelle mit Feldern in einer anderen Tabelle verknüpft werden. Bei dir könnte das die ID eines Termins sein, die als Hauptdatum definiert ist, auf die die Spalte Termin_ID (passender aber fiktiver Name) in der Zugriffszähltabelle verweist.

Wenn also der Datensatz mit dem Hauptdatum gelöscht wird, werden alle Datensätze, die in der Spalte Termin_ID auf diesen Hauptdatensatz verweisen, ebenfalls gelöscht. Ob und wie das bei dir umsetzbar ist, kommt auf die Struktur deiner Datenbanktabellen an.

Tschö, Auge

[Rolf B]

Hallo Linuchs,

also muss ich die IPs speichern.

DSGVO-Alarm. Darfst Du das ungefragt?

IPs mitlöschen, wenn die entspr. termin_id gelöscht wird?

Das ist nicht unbedingt der richtige Moment. Die IP ist ein personenbezogenes Merkmal (weil der Staatsanwalt sie per Anordnung einer Person zuordnen kann). Deshalb ist ihre Speicherung kritisch zu sehen, bzw. Du musst ein berechtigtes Interesse nachweisen oder die Erlaubnis zum Speichern eingeholt haben. Ist ein Besucherzähler ein berechtigtes Interesse? Hm. Die Datenschutzerklärungen mancher Webseiten behaupten das. Du musst die Speicherdauer auf jeden Fall auf ein paar Tage limiteren. Und Du musst bedenken, dass eine dynamische IP täglich anders sein kann (heutzutage nicht mehr muss) - so dass eine Speicherung über mehr als eine Woche wenig Sinn ergibt. Andersrum können sich mehrere Besucher eine IP teilen. Ein IP basierender Zähler ist also ziemlich ungenau.

Davon abgesehen, denke ich, dass Du die termin_id in der Zähltabelle als Foreign Key mit ON DELETE CASCADE deklarieren möchtest. Die termin_id ist sicherlich Indexspalte in der Termine-Tabelle, dann sollte das unproblematisch sein.

In der Zähltabelle solltest Du (termin_id, ip) als Primary Key haben. Also die termin_id als erstes. Sonst führt der DELETE auf einen Termin zu einem 100% Indexscan in der Zähltabelle - das willst Du sicher nicht.

Das kann man per SQL Befehlen erstellen, oder im phpmyadmin zusammenklicken.

Rolf

[Der Martin]

Hallo,

Und Du musst bedenken, dass eine dynamische IP täglich anders sein kann

ich habe mal gelesen, dass IP-Adressen bei Mobilfunk-Verbindungen sich sogar alle paar Sekunden ändern können.

so dass eine Speicherung über mehr als eine Woche wenig Sinn ergibt.

Andersrum können sich mehrere Besucher eine IP teilen.

Richtig, zum Beispiel eine Familie oder WG mit einem gemeinsamen Internet-Zugang über eine Fritzbüx. Oder Mitarbeiter eines Gewerbebetriebs, die nach außen hin auch oft alle mit derselben IP-Adresse auftauchen. Im Unterschied zu Privatanschlüssen, wo die IP-Adresse in der Regel dynamisch ist, haben Firmen oft eine fest zugeteilte IP-Adresse (oder mehrere).

Ein IP basierender Zähler ist also ziemlich ungenau.

Ja.

Einen schönen Tag noch
 Martin

[Linuchs]

ich habe mal gelesen, dass IP-Adressen bei Mobilfunk-Verbindungen sich sogar alle paar Sekunden ändern können.

Wie kann dann eine Antwort zugestellt werden?

[Der Martin]

Hallo,

ich habe mal gelesen, dass IP-Adressen bei Mobilfunk-Verbindungen sich sogar alle paar Sekunden ändern können.

Wie kann dann eine Antwort zugestellt werden?

ein Request, eine Response. Innerhalb der Zeit muss die IP-Adresse natürlich gleich bleiben. Aber sobald der Besitzer das Ding ein paar Sekunden nicht mehr benutzt, z.B. die Displaybeleuchtung sich ausschaltet oder so, könnte sich das Gerät auch kurzfristig vom Netz abmelden und erst beim nächsten Bedarf (der auch durch eine im Hintergrund laufende App entstehen kann) erneut eine Verbindung aufbauen.

Das ist so, als ob du bei einem großen Konzern die Telefon-Hotline in Anspruch nehmen musst, und es ist nicht mit einem einzigen Anruf getan. Du landest jedesmal wieder bei einem anderen Mitarbeiter.

Wenn du eine andere Art von Antwort meinst, etwa bei Messenger-Diensten: Die identifizieren die Teilnehmer oft über ihre Handynummer, nicht über die IP-Adresse.

Einen schönen Tag noch
 Martin

[Linuchs]

Hallo Rolf,

DSGVO-Alarm. Darfst Du das ungefragt?

Nicht die IP interessiert mich, auf die dann womöglich Staatsanwälte*) scharf sind, sondern mir ist wichtig, dass eine Person den Zähler möglichst nicht hochjubeln kann.

*) „rücken Sie alle IPs raus, die sich den Termin x angeschaut haben" Ja leider sind wir davon nicht weit entfernt

alternativ md5 Gibt den Hash als 32 Zeichen lange Hexadezimalzahl zurück.

Ein IP basierender Zähler ist also ziemlich ungenau.

Wie geht's genauer? Es sind anonyme Besucher, nicht angemeldet.

Die termin_id ist sicherlich Indexspalte in der Termine-Tabelle

Ja klar, heißt dort nur id und in allen anderen Tabellen termin_id

[Rolf B]

Hallo Linuchs,

alternativ md5

Angesichts der begrenzten Anzahl von IPs ist ein MD5 Hash einer IP nicht wirklich verschlüsselt. Da ist schnell ein Dictionary gemacht.

Die Idee ist nicht verkehrt - vielleicht solltest Du den Useragent-String zusammen mit der IP hashen und als Key verwenden.

Rolf

[Raketenwilli]

Klar. Er kann neben $_SERVER['REMOTE_ADDR'] zusätzlich auch

$_SERVER['HTTP_SEC_CH_UA']
$_SERVER['HTTP_SEC_CH_UA_MOBILE']
$_SERVER['HTTP_SEC_CH_UA_PLATFORM']

und, sehr gerne, auch

$_SERVER['HTTP_DNT']

mit hashen…

[MudGuard]

Hi,

Angesichts der begrenzten Anzahl von IPs ist ein MD5 Hash einer IP nicht wirklich verschlüsselt. Da ist schnell ein Dictionary gemacht.

dann salzt man das halt ein wenig ...

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo MudGuard,

ahem, ja, in dieser Datenhaschee-Küche bin ich nicht wirklich zu Hause.

Jetzt wird's aber schon zeitaufwändig. Denn md5 ist ja eigentlich auch mit Salz nicht empfohlen für Daten, die man nicht rückrechnen können soll. Und ein password_hash für jeden Besuch - uiuiuiui.

Rolf

[Raketenwilli]

alternativ md5 Gibt den Hash als 32 Zeichen lange Hexadezimalzahl zurück.

Hm. MD5 liefert genau genommen 128 bit oder 16 Byte. Und das kann man so auch speichern.

4.294.967.296 (mögliche IPv4-Adressen) * 16 Byte sind gerade mal 68.719.476.736 oder 69GB für die Hashes und, falls Du eine Tabelle bauen willst, nochmal 17.179.869.184 oder 18 GB für die IPs (Das sind „D-Worte“ oder 32-Bit-Integers).

Wenn ich das schlau aufteilen will, lege für deren erste drei Bytes zwei verschachtele Verzeichnis-Ebenen (Namen: 0-255) und darin jeweils 255 eben so benamte Datendateien (0-225) an (das sind dann insgesamt 16.777.216) Dateien und Verzeichnisse - wobei die Dateien mit dem sortierten Rest des Hashes + der zugehörigen IP gerade mal 4096 Bytes (für die Hashes) + 1024 Bytes (für die zugehörigen IPs) groß sind.

Ups. Das müssen die ja gar nicht. Ich brauche ja nur noch 13 statt 16 Byte pro Hash, der Rest steckt ja in den Datei-oder Verzeichnisnamen.

Dann speichere ich das zusammen mit einem kompletten Linux auf einer 128 GB großen SD-Card und werte es auf einem Raspi aus. Da ich fixe Längen der Items habe kann ich die zielgerichtet wählbare und recht kleine Datei einfach „durchsteppen“, also eine fixe Anzahl an Bytes aus der Datei lesen (13 Resthash +4 IP) danach die 13 Bytes vergleichen und im Fall des Treffers die 4 Bytes dann als IP formatiert ausgeben. Im schlimmsten Fall habe ich 254 Fehlgriffe, die zu einer Wiederholung führen...

Wenn ich frech bin mach ich das mit einem schnöden Bash-Skript und dd mit bs und skip (für das byteweise Lesen) aus den Dateien.

Also ich glaube, der Datenschutzbeauftrage ist mit md5 unzufrieden.

[Raketenwilli]

Jede Menge IP-Adressen sind für die Anwendung irrelevant. Das bedeutet, man muss deutlich weniger speichern als von mir bisher angegeben.