SPAM-Kunden-Profile per SQL löschen

[Hajosch]

Hallo allerseits, speziell die SQL-Experten!

Ich leide - wie so viele hier - unter den zahlreichen SPAM-Kunden-Profilen, die täglich angelegt und von mir dann manuell in der Kundenverwaltung gelöscht werden müssen. Die Zeitstempel-Lösung bringt leider auch keine 100%ige Lösung, es kommen immer noch etliche Fake-Kunden-Registrierungen (wie auch immer) durch.

Das Löschen der Fake-Kunden-Profile ist leider sehr aufwendig, weil es im Bereich der Kundenverwaltung keine detaillierte Übersicht gibt, bei der man alle Datenfelder des Kundenprofils angezeigt und so schnell die Fake-Kunden aussortieren kann. So muss ich erst einmal mich in das Kundenprofil reinklicken, um dann anhand der Daten den fake zu erkennen, wieder rausgehen, und dann den einzelnen Datensatz löschen. Bei den vielen Fake-Kunden, die sich im Laufe der Woche ansammeln, ist das  ein ziemlicher Aufwand.

Leichter wäre es, wenn man mit Hilfe eine SQL-Skriptes die Fake-Kunden identifizieren und in einem Schwung löschen könnte. Mir ist aufgefallen, dass die Fake-Kunden-Profile alle das Feld Postleitzahl mit alphabetischen Zeichen, also nicht mit Ziffern, füllen.  Ich habe mal als Beispiel einen Screenshot eines solchen Fake-Kunden-Profils gemacht.

[ Für Gäste sind keine Dateianhänge sichtbar ]

Vielleicht gibt es ja einen SQL-Experten hier in der Community, der mir und allen anderen Spam-Leidgeplagten sagen kann, wie man diese Fake-Kunden-Profile in SQL mit einem Skript löschen kann. Ich stelle mir das so vor, dass das SQL-Skript

1) im Feld Postleitzahl nachschaut, ob dort ausschließlich alphabetische Zeichen eingetragen (das wäre dann ein Fake-Kunde).

2) Wenn das der Fall ist, dann lösche diesen Kunden, andern falls ignoriere den echten Kunden

Wäre echt super und eine große Hilfe, wenn dazu jemand etwas einfallen würde.

Danke und Gruß
Hajosch

Linkback: https://www.modified-shop.org/forum/index.php?topic=41620.0

[hpzeller]

Hallo Hajosch,

mit folgendem SELECT Statement welches du z.B. in phpMyAdmin eingibst, kannst du die Kunden ermitteln welche das Kriterium "Postleitzahl besteht ausschliesslich aus Buchstaben (ASCII)" erfüllen.

Code: SQL  [Auswählen]

SELECT
    *
FROM
    customers,
    address_book
WHERE
    address_book.entry_postcode REGEXP '^[a-zA-Z]+$' AND address_book.customers_id = customers.customers_id AND customers.customers_default_address_id = address_book.address_book_id;

Alternativ zu phpMyAdmin kannst du, sofern deine Shop die Version 2.0.4.2 oder neuer hat, auch dieses Modul -> https://module-loader.de/modules/hpzeller/adminer-dba-tool in deinen Shop installieren. Wenn du den MMLC noch nicht in deinem Shop installiert hast, müsstest du natürlich zuerst diesen hier -> https://module-loader.de/ herunterladen und installieren, bevor du das Modul installieren kannst.

Gruss
Hanspeter

[noRiddle (revilonetz)]

@Hajosch
Versuche mal zusätzlich zur Zeitfalle noch das von mir ins Forum gestellte Würfel-Captcha:
MODUL: Captcha mit Würfeln ab Shopversion 2.0.5.0.
Das geht auch mit der Shop-Version 5.0.2.1 .

Gruß,
noRiddle

[karsta.de]

In der nächsten Shop-Version sind Checkboxen vor den Kundenkonten, was das Löschen in Zukunft vielleicht erleichtert.
Ich verwende seit Jahren unsichtbare Felder (Honeypot), die nicht ausgefüllt sein dürfen, und habe die genannten Probleme nicht.

BG Karsta

[hpzeller]

Hallo Hajosch,

jetzt muss ich dir natürlich noch zeigen wie du die allenfalls betroffenen und im SELECT Statement angezeigten Accounts in eine Aufwisch löschen kannst.

Ich denke das lässt sich am übersichtlichsten mit einer Prozedur bewerkstelligen. Gib also folgende Prozedur ein, ersetze dabei die Zahlen '2,3,4' (letzte Zeile) mit den customers_id (erste Spalte) der angezeigten Acounts welche du nach Eingabe des SELECT Statements aus Antwort #1 erhalten hast.

Code: SQL  [Auswählen]

DELIMITER $$
DROP PROCEDURE IF EXISTS DeleteFakeCustomers $$
CREATE PROCEDURE DeleteFakeCustomers(cid INT)
BEGIN
DELETE FROM `address_book` WHERE `customers_id` = cid;
DELETE FROM `customers` WHERE `customers_id` = cid;
DELETE FROM `customers_basket` WHERE `customers_id` = cid;
DELETE FROM `customers_basket_attributes` WHERE `customers_id` = cid;
DELETE FROM `customers_info` WHERE `customers_info_id` = cid;
DELETE FROM `customers_ip` WHERE `customers_id` = cid;
DELETE FROM `customers_memo` WHERE `customers_id` = cid;
DELETE FROM `customers_status_history` WHERE `customers_id` = cid;
END$$
DELIMITER;
CALL DeleteFakeCustomers('2,3,4');
 

Hinweis:
Die Liste der bei Kundenlöschungen zu berücksichtigenden Tabellen ist von hier -> https://www.modified-shop.org/wiki/Kunden_löschen, ich hoffe sie ist aktuell.

Gruss
Hanspeter

[hpzeller]

[...]
Ich verwende seit Jahren unsichtbare Felder (Honeypot), die nicht ausgefüllt sein dürfen, und habe die genannten Probleme nicht.
[...]

Meiner Meinung nach spricht doch eigentlich nichts dagegen Honeypot,  Zeitfalle/Zeitsperre und ev. noch weitere Fake-Account-Verhinderungsmethoden zu einem einzigen Modul zusammenzuführen.

Gruss
Hanspeter

[hpzeller]

Nachtrag zu meiner Antwort #4:

Mir ist in der Prozedur ein Fehler unterlaufen.
Hier die korrigierte Prozedur.

Code: SQL  [Auswählen]

DELIMITER $$
DROP PROCEDURE IF EXISTS DeleteFakeCustomers $$
CREATE PROCEDURE DeleteFakeCustomers(cid INT)
BEGIN
DELETE FROM `address_book` WHERE `customers_id` = cid;
DELETE FROM `customers` WHERE `customers_id` = cid;
DELETE FROM `customers_basket` WHERE `customers_id` = cid;
DELETE FROM `customers_basket_attributes` WHERE `customers_id` = cid;
DELETE FROM `customers_info` WHERE `customers_info_id` = cid;
DELETE FROM `customers_ip` WHERE `customers_id` = cid;
DELETE FROM `customers_memo` WHERE `customers_id` = cid;
DELETE FROM `customers_status_history` WHERE `customers_id` = cid;
END$$
DELIMITER ;
CALL DeleteFakeCustomers('2,3,4');

Gruss
Hanspeter

[hpzeller]

Sorry, zweimal das gleiche gepostet.

Gruss
Hanspeter

[hpzeller]

Nachtrag zu meiner Antwort #6:

Die Prozedur ist leider immer noch fehlerhaft.
Hier die korrigierte Prozedur.

Code: SQL  [Auswählen]

DELIMITER $$
DROP PROCEDURE IF EXISTS DeleteFakeCustomers $$
CREATE PROCEDURE DeleteFakeCustomers(IN cid VARCHAR(1024))
BEGIN
DELETE FROM address_book WHERE FIND_IN_SET(customers_id, cid);
DELETE FROM customers WHERE FIND_IN_SET(customers_id, cid);
DELETE FROM customers_basket WHERE FIND_IN_SET(customers_id, cid);
DELETE FROM customers_basket_attributes WHERE FIND_IN_SET(customers_id, cid);
DELETE FROM customers_info WHERE FIND_IN_SET(customers_info_id, cid);
DELETE FROM customers_ip WHERE FIND_IN_SET(customers_id, cid);
DELETE FROM customers_memo WHERE FIND_IN_SET(customers_id, cid);
DELETE FROM customers_status_history WHERE FIND_IN_SET(customers_id, cid);
END $$
DELIMITER ;
CALL DeleteFakeCustomers('2,3,4');

Gruss
Hanspeter

[noRiddle (revilonetz)]

Es würde evtl. noch *_wishlist* fehlen, insofern das Merkzettel-Modul installiert ist.
Imho ist das mit der Procedure auch ein wenig aufwändig. Man kann doch in phpMyAdmin nachdem man das Ergebnis angezeigt bekommt einfach alle Einträge mit einem Klick anhaken und mit einem weiteren Klick löschen.

@karsta
Honeypot-Felder dürften  mangels Hook in der /create_account.php nicht so einfach einzubauen sein.
(Ja, ich weiß, kann man evtl. über eine Datei in application_top_end machen...)
Man sollte die Honeypot-Felder übrigens nicht schlicht auf display:none setzen, denn das können "kluge" Bots bereits verstehen, sondern sie mittels z.B. eines hohen negativen text-indent (und height:0) oder einem position:absolute aus dem Sichtbereich schieben.

Gruß,
noRiddle

[karsta.de]

Honeypot-Felder dürften  mangels Hook in der /create_account.php nicht so einfach einzubauen sein.

Habe ich nicht updatesicher drin, aber der Zweck heiligt die Mittel. Und wenn das nichts gebracht hätte, hätte ich wohl inzwischen eine andere Lösung gefunden.

BG Karsta

[hpzeller]

Es würde evtl. noch *_wishlist* fehlen, insofern das Merkzettel-Modul installiert ist.
[...]

Stimmt wenn das Merkzettel-Modul installiert ist werden noch die Tabellen customers_wishlist und customers_wishlist_attributes erzeugt, welche man gegebenenfalls in der Prozedur noch behandeln müsste.

[...]
Man kann doch in phpMyAdmin nachdem man das Ergebnis angezeigt bekommt einfach alle Einträge mit einem Klick anhaken und mit einem weiteren Klick löschen.
[...]

Nein das geht nicht, wenn wie hier mit phpMyAdmin eine Abfrage über mehrere Tabellen gemacht wird, und wenn man eine Abfrage nur über eine Tabelle durchführt, kann man zwar die Ergebnisse durch abhaken löschen, aber natürlich nur in dieser einen, abgefragten Tabelle.

Gruss
Hanspeter

[noRiddle (revilonetz)]

Äh, ja, stimmt  .

Gruß,
noRiddle

[Duncan]

In den obigen Vorschlägen fehlt auch noch "customers_dsgvo"

Es gibt noch eine simple Lösung,
- da Spamkonten keinen Umsatz machen
- die Abfrage auch, zum Beispiel wöchentlich, per Cron laufen kann
- allerdings auch div. umsatzlose Testkonten löscht...

Code: SQL  [Auswählen]

DELETE FROM customers WHERE customers.customers_id NOT IN (SELECT  orders.customers_id FROM orders) AND customers_id > 1;
DELETE FROM address_book WHERE address_book.customers_id NOT IN (SELECT  orders.customers_id FROM orders) AND customers_id > 1;
DELETE FROM customers_basket WHERE customers_basket.customers_id NOT IN (SELECT  orders.customers_id FROM orders) AND customers_id > 1;
DELETE FROM customers_basket_attributes WHERE customers_basket_attributes.customers_id NOT IN (SELECT  orders.customers_id FROM orders) AND customers_id > 1;
DELETE FROM customers_dsgvo WHERE customers_dsgvo.customers_id NOT IN (SELECT  orders.customers_id FROM orders) AND customers_id > 1;
DELETE FROM customers_ip WHERE customers_ip.customers_id NOT IN (SELECT  orders.customers_id FROM orders) AND customers_id > 1;
DELETE FROM customers_memo WHERE customers_memo.customers_id NOT IN (SELECT  orders.customers_id FROM orders) AND customers_id > 1;
DELETE FROM customers_status_history WHERE customers_status_history.customers_id NOT IN (SELECT  orders.customers_id FROM orders) AND customers_id > 1;
DELETE FROM customers_wishlist WHERE customers_wishlist.customers_id NOT IN (SELECT  orders.customers_id FROM orders) AND customers_id > 1;
DELETE FROM customers_wishlist_attributes WHERE customers_wishlist_attributes.customers_id NOT IN (SELECT  orders.customers_id FROM orders) AND customers_id > 1;
 

[hpzeller]

In den obigen Vorschlägen fehlt auch noch "customers_dsgvo"
[...]

Danke für die Information

Gruss
Hanspeter