• Gestion d'un forum phpBB
• Suppression des comptes non validés

Suppression des comptes créés par des robots

Ces comptes sont créés automatiquement par des robots, et ne sont jamais validés, car leurs adresses électroniques n'existent pas.

Attention ! N'utilisez pas ces commandes sans avoir préalablement réalisé une sauvegarde de votre base de données (en plus, avec PostgreSQL, c'est très simple — cf. les commandes pg_dump et pg_dumpall).

Dans tous les cas, l'utilisation de ces scripts se fait à vos risques et périls.

Afficher la liste des compte jamais validés (PostgreSQL)

Cette commande PostgreSQL affiche les comptes créés depuis plus de 2 jours (mais depuis moins de 20 jours) et qui n'ont pas été activés.

SELECT username FROM phpbb_users

    WHERE ( SELECT COUNT(*) FROM phpbb_posts
            WHERE phpbb_posts.poster_id=phpbb_users.user_id ) = 0
    -- L'utilisateur n'a rien posté.

    AND   user_active=0
    -- Utilisateur non validé.

    AND   user_id>0
    -- Ce n'est pas l'utilisateur anonyme.

    AND   user_regdate > EXTRACT ( EPOCH FROM ( now() - interval '20 days' ) )
    -- Inscrit depuis moins de 20 jours.

    AND   user_regdate < EXTRACT ( EPOCH FROM ( now() - interval '2 days' ) ) ;
    -- Inscrit depuis plus de 2 jours.

Supprimer les comptes jamais activés (PostgreSQL)

Cette commande PostgreSQL supprime les comptes créés depuis plus de 2 jours (mais depuis moins de 20 jours) et qui n'ont pas été activés.

DELETE FROM phpbb_users 

    WHERE ( SELECT COUNT(*) FROM phpbb_posts
            WHERE phpbb_posts.poster_id=phpbb_users.user_id ) = 0
    -- L'utilisateur n'a rien posté.

    AND   user_active=0
    -- Utilisateur non validé.

    AND   user_id>0
    -- Ce n'est pas l'utilisateur anonyme.

    AND   user_regdate > EXTRACT ( EPOCH FROM ( now() - interval '20 days' ) )
    -- Inscrit depuis moins de 20 jours.

    AND   user_regdate < EXTRACT ( EPOCH FROM ( now() - interval '2 days' ) ) ;
    -- Inscrit depuis plus de 2 jours.

DELETE FROM phpbb_user_group
    WHERE user_id NOT IN ( select user_id from phpbb_users );

DELETE FROM phpbb_groups
    WHERE group_id NOT IN ( select group_id from phpbb_user_group );

Le script complet

Ce script suppose que l'utilisateur en cours peut se connecter à PostgreSQL sans utiliser de mot de passe. SI ce n'est pas le cas, quelques adaptations mineures sont nécessaires.

# Liste des bases correspondant aux forums

BASES="forum1 forum2 forum3"

# Requête SQL

nettoyage() {

  echo "Base de données $1"
  /usr/bin/psql -t $1 << FDT

-- Affichage de la liste des utilisateurs.

SELECT username FROM phpbb_users
    WHERE ( SELECT COUNT(*) FROM phpbb_posts
            WHERE phpbb_posts.poster_id=phpbb_users.user_id ) = 0
    -- L'utilisateur n'a rien posté.

    AND   user_active=0
    -- Utilisateur non validé.

    AND   user_id>0
    -- Ce n'est pas l'utilisateur anonyme.

    AND   user_regdate > EXTRACT ( EPOCH FROM ( now() - interval '20 days' ) )
    -- Inscrit depuis moins de 20 jours.

    AND   user_regdate < EXTRACT ( EPOCH FROM ( now() - interval '2 days' ) ) ;
    -- Inscrit depuis plus de 2 jours.

-- Recopie des lignes correspondantes dans une table temporaire.

SELECT * INTO TEMPORARY TABLE nettoyage_phpbb_users FROM phpbb_users
    WHERE ( SELECT COUNT(*) FROM phpbb_posts
            WHERE phpbb_posts.poster_id=phpbb_users.user_id ) = 0
    -- L'utilisateur n'a rien posté.

    AND   user_active=0
    -- Utilisateur non validé.

    AND   user_id>0
    -- Ce n'est pas l'utilisateur anonyme.

    AND   user_regdate > EXTRACT ( EPOCH FROM ( now() - interval '20 days' ) )
    -- Inscrit depuis moins de 20 jours.

    AND   user_regdate < EXTRACT ( EPOCH FROM ( now() - interval '2 days' ) ) ;
    -- Inscrit depuis plus de 2 jours.

-- Affichage de la table temporaire

COPY nettoyage_phpbb_users TO STDOUT ;

-- Destruction de la table temporaire

DROP TABLE nettoyage_phpbb_users ;

DELETE FROM phpbb_users
    WHERE ( SELECT COUNT(*) FROM phpbb_posts
            WHERE phpbb_posts.poster_id=phpbb_users.user_id ) = 0
    -- L'utilisateur n'a rien posté.

    AND   user_active=0
    -- Utilisateur non validé.

    AND   user_id>0
    -- Ce n'est pas l'utilisateur anonyme.

    AND   user_regdate > EXTRACT ( EPOCH FROM ( now() - interval '20 days' ) )
    -- Inscrit depuis moins de 20 jours.

    AND   user_regdate < EXTRACT ( EPOCH FROM ( now() - interval '2 days' ) ) ;
    -- Inscrit depuis plus de 2 jours.

-- Recopie des liens utilisateurs groupes correspondants

SELECT * INTO TEMPORARY TABLE nettoyage_phpbb_user_group FROM phpbb_user_group
    WHERE user_id NOT IN ( SELECT user_id FROM phpbb_users ) ;

-- Affichage de la table temporaire

COPY nettoyage_phpbb_user_group TO STDOUT ;

-- Destruction de la table temporaire

DROP TABLE nettoyage_phpbb_user_group ;

-- Suppression des liens utilisateurs groupes correspondants

DELETE FROM phpbb_user_group
    WHERE user_id NOT IN ( SELECT user_id FROM phpbb_users ) ;

-- Recopie des groupes correspondants

SELECT * INTO nettoyage_phpbb_groups FROM phpbb_groups
    WHERE group_id NOT IN ( SELECT group_id FROM phpbb_user_group ) ;

-- Affichage de la table temporaire

COPY nettoyage_phpbb_groups TO STDOUT ;

-- Destruction de la table temporaire

DROP TABLE nettoyage_phpbb_groups ;

-- Suppression des groupes correspondants

DELETE FROM phpbb_groups
    WHERE group_id NOT IN ( SELECT group_id FROM phpbb_user_group ) ;

FDT

  echo

}

/bin/mkdir -p "${HOME}/journaux"

/bin/echo >> "${HOME}/journaux/nettoyage_phpbb.jdb"
/bin/echo "************ Nettoyage PhpBB" >> "${HOME}/journaux/nettoyage_phpbb.jdb"
/bin/echo >> "${HOME}/journaux/nettoyage_phpbb.jdb"
/bin/date >> "${HOME}/journaux/nettoyage_phpbb.jdb"
/bin/echo >> "${HOME}/journaux/nettoyage_phpbb.jdb"

for i in ${BASES} ; do

  nettoyage "${i}" | /bin/grep -v -e '^DROP TABLE$' -e '^SELECT$' | \
      /usr/bin/tee -a "${HOME}/journaux/nettoyage_phpbb.jdb" | \
      /bin/grep -e "^Base" -e "^ " -e "^DELETE " -e "^$"

done