Skip navigation.
Chez Augustin
Home » For webmasters » Strategies for spammers; Understand your ennemy.

Drupal php code snippet to find stealth spam

Another friend of mine was deeply affected by the stealth-spam problem that I described in the previous page in this booklet.

In order to help them find affected comments, I wrote the following php code snippet for their Drupal 5 web site (the code should work as is for Drupal 6).

<?php
$result = db_query("SELECT c.cid, c.nid, c.uid, c.subject, c.timestamp, u.name, n.title FROM {comments} c
JOIN {users} u ON u.uid = c.uid
JOIN {node} n ON n.nid = c.nid
WHERE c.comment LIKE '%%href%%'  AND c.status = 0 ORDER BY c.timestamp DESC");

echo "<table><tr><th>Date</th><th>User</th><th>Thread</th><th>Comment</th></tr>";
while ($c = db_fetch_object($result)) {
  echo "<tr>";
  echo '<td>' . format_date($c->timestamp) . '</td>';
  echo '<td><a href="/user/' . $c->uid . '/edit">' . $c->name  . '</a></td>';
  echo '<td><a href="/node/' . $c->nid . '#comment-' . $c->cid . '">' . check_plain($c->title)  . '</a></td>';
  echo '<td><a href="/comment/edit/' . $c->cid . '">' . check_plain($c->subject)  . '</a></td>';
  echo "</tr>";
}
?>

This will give you a table with all the comments having some hard coded link and links to the user edit tab, the comment in the thread and the comment edit page (to check the source).

Just add this code to into the body of a node with a php input format. Of course, this is exclusively intended for administrative use and the node shouldn't be published so that only the site administrators can access it.