Avatar

SPAM Einträge ohne tid bzw. id != tid (Bugs)

by Micha ⌂, Friday, July 03, 2020, 08:21 (117 days ago) @ Micha

Hallo,

ich habe ein wenig weiter gesucht nach der Ursache. Ich meine, das Problem liegt an den (geänderten) daily_actions:

Dort wird SPAM derzeit(automatisch) mit einem SQL entfernt:

@mysqli_query($connid, "DELETE FROM `". $db_settings['forum_table'] ."` WHERE `time` < (NOW() - INTERVAL ". intval($settings['auto_delete_spam']) ." HOUR) AND 
         `id` IN (SELECT `". $db_settings['akismet_rating_table'] ."`.`eid` 
         FROM `". $db_settings['akismet_rating_table'] ."` 
         JOIN `". $db_settings['b8_rating_table'] ."` ON `". $db_settings['akismet_rating_table'] ."`.`eid` = `". $db_settings['b8_rating_table'] ."`.`eid` 
         WHERE `". $db_settings['akismet_rating_table'] ."`.`spam` = 1 OR `". $db_settings['b8_rating_table'] ."`.`spam` = 1); ");

Das funktioniert auch zuverlässig. ABER: die ids des jeweiligen Postings stehen noch in anderen Tabellen drin, siehe die Liste an abhängigen Tabellen
in delete_posting_recursive.

Wenn aber nur die Postings entfernt werden, dann bleiben die ids in den anderen Tabellen enthalten. Ein neuer Eintrag führt dann u.U. zu einer Kollision, wodurch das Script abgebrochen ist, bevor die tid gesetzt wurde.

Ich habe zwei Lösungsvorschläge für die daily_actions:

Wir lösen es via SQL, indem wir die abhängigen Tabellen auch bereinigen:

   @mysqli_query($connid, "DELETE FROM `". $db_settings['forum_table'] ."` WHERE `time` < (NOW() - INTERVAL ". intval($settings['auto_delete_spam']) ." HOUR) AND 
         `id` IN (SELECT `". $db_settings['akismet_rating_table'] ."`.`eid` 
         FROM `". $db_settings['akismet_rating_table'] ."` 
         JOIN `". $db_settings['b8_rating_table'] ."` ON `". $db_settings['akismet_rating_table'] ."`.`eid` = `". $db_settings['b8_rating_table'] ."`.`eid` 
         WHERE `". $db_settings['akismet_rating_table'] ."`.`spam` = 1 OR `". $db_settings['b8_rating_table'] ."`.`spam` = 1); ");
 
   // DELETE dependent entries - see delete_posting_recursive
   @mysqli_query($connid, "DELETE FROM `". $db_settings['entry_cache_table'] ."`    WHERE `cache_id`   NOT IN (SELECT `id` FROM `". $db_settings['forum_table'] ."`); ");         
   @mysqli_query($connid, "DELETE FROM `". $db_settings['bookmark_table'] ."`       WHERE `posting_id` NOT IN (SELECT `id` FROM `". $db_settings['forum_table'] ."`); ");
   @mysqli_query($connid, "DELETE FROM `". $db_settings['read_status_table'] ."`    WHERE `posting_id` NOT IN (SELECT `id` FROM `". $db_settings['forum_table'] ."`); ");
   @mysqli_query($connid, "DELETE FROM `". $db_settings['entry_tags_table'] ."`     WHERE `bid`        NOT IN (SELECT `id` FROM `". $db_settings['forum_table'] ."`); ");
   @mysqli_query($connid, "DELETE FROM `". $db_settings['subscriptions_table'] ."`  WHERE `eid`        NOT IN (SELECT `id` FROM `". $db_settings['forum_table'] ."`); ");
   @mysqli_query($connid, "DELETE FROM `". $db_settings['akismet_rating_table'] ."` WHERE `eid`        NOT IN (SELECT `id` FROM `". $db_settings['forum_table'] ."`); ");
   @mysqli_query($connid, "DELETE FROM `". $db_settings['b8_rating_table'] ."`      WHERE `eid`        NOT IN (SELECT `id` FROM `". $db_settings['forum_table'] ."`); ");


Die Lösung hat das Problem, dass wir nun an zwei Stellen das Löschen beachten müssen. Daher die Alternative, bei der die delete_posting_recursive direkt aufgerufen wird.

$spam_ids_result = mysqli_query($connid, "SELECT `id` FROM `" . $db_settings['forum_table'] ."` WHERE `time` < (NOW() - INTERVAL ". intval($settings['auto_delete_spam']) ." HOUR) AND 
         `id` IN (SELECT `". $db_settings['akismet_rating_table'] ."`.`eid` 
         FROM `". $db_settings['akismet_rating_table'] ."` 
         JOIN `". $db_settings['b8_rating_table'] ."` ON `". $db_settings['akismet_rating_table'] ."`.`eid` = `". $db_settings['b8_rating_table'] ."`.`eid` 
         WHERE `". $db_settings['akismet_rating_table'] ."`.`spam` = 1 OR `". $db_settings['b8_rating_table'] ."`.`spam` = 1); ");
 
   while ($spam_ids_data = mysqli_fetch_array($spam_ids_result)) {
    delete_posting_recursive(intval($spam_ids_data['id']));
   }

Was denkt Ihr?

/Micha

--
applied-geodesy.org - OpenSource Least-Squares Adjustment Software for Geodetic Sciences


Complete thread:

 RSS Feed of thread

powered by my little forum