Deleting lots of spam content on a Drupal website

Deleting Spam on FUDCON.in website

After FUDCon Pune event in 2011, the website has been running as is. Just a couple of days back, I noticed a lot of spam accumulated on the website. However it is that content which is not displayed on the website, unless you know its URL. I located the last known sane activity and began estimating how much spam content I have to delete.

Here I use Drush and a simple PHP script.

$ drush sql-cli

mysql> select unix_timestamp('2012-02-28 19:57:11 +0530') from dual;
+---------------------------------------------+
| unix_timestamp('2012-02-28 19:57:11 +0530') |
+---------------------------------------------+
|                                  1330487831 | 
+---------------------------------------------+

mysql> SELECT count(*) FROM node AS n WHERE n.type = 'session' and  n.created > 1330487831  ;
+----------+
| count(*) |
+----------+
|    22110 | 
+----------+
1 row in set (0.22 sec)

Twenty two thounsand plus entries! That is so much content to be deleted from the Admin UI.

A simple solution was to script it.

$ cat delete_spam.php
<?php
  require_once './includes/bootstrap.inc';
  drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

  global $user;
  $original_user = $user;
  $user = user_load(1);

  echo $user->uid . " " . $user->mail;
  echo "\n";

  // $aquery= db_query("SELECT nid FROM {node} AS n WHERE n.type = 'session' and n.created > 1330487831");
  $aquery= db_query("SELECT nid FROM {node} AS n WHERE n.type = 'session' and n.nid >= 315");
  while ($row = db_fetch_object($aquery)) {
    // node_delete($row->nid);
    $nid = $row->nid;
    $node = node_load(array("nid" => $nid));
    echo "Deleting " . $nid . ": " . $node->title . "\n" ;
    node_delete($nid);
  }

  $user = $original_user;

?>

Now we can execute this script:

$ drush php-script delete_spam.php | tee delete_spam.out
$ wc -l delete_spam.out
22110 delete_spam.out

Now, all 22110 entries were deleted!

Next step is to clean the old cache as well:

$ drush cc
Enter a number to choose which cache to clear.
 [0]  :  Cancel         
 [1]  :  all            
 [2]  :  drush          
 [3]  :  theme-registry 
 [4]  :  menu           
 [5]  :  css-js         
 [6]  :  block          
 [7]  :  module-list    
 [8]  :  theme-list     
 [9]  :  nodeaccess     

1
'all' cache was cleared                   [success]

Thats how I deleted so much spam content quickly.

References: