Removing looping redirects from a Drupal 7 build

We recently upgraded the Redirect module on a couple of D7 sites. While doing so, we noticed in passing that we had somehow ended up with a stack of old redirects which were essentially replicating (in redirect form) what Drupal's internal URL aliasing does: binding paths of the form node/[NODE_ID] to more human URLs like /blog/my-blogpost.

Before the upgrade, it seemed like Redirect would back off if this were the case; afterwards, though, it caused an infinite loop for anonymous users and an error message with an ironically unprofessional tone (blue smurf, anyone?) for logged-in users with the right privileges.

Quite how we ended up with all these spurious redirects is lost in the mists of time, but below is a neat little Drush script to remove them:

<?php
// Set up the query using the database API
$query = db_select('redirect', 'r');
$query->join('url_alias', 'ua', 'r.redirect = ua.source AND r.source = ua.alias');
$query->fields('r', array('rid', 'redirect', 'source'));
 
// Execute it and fetch the results, one by one
$result = $query->execute();
$any_results = FALSE;
while($r = $result->fetchAssoc()) {
  // For each result, flag we've got at least one result, notify the CLI user
  // of what we're doing, and delete it using Redirect's own API
  $any_results = TRUE;
  drush_log(dt("Deleting unwanted redirect !r (!s -> !t)",
    array("!r" => $r['rid'], "!s" => $r['source'], "!t" => $r['redirect'])), "success");
  redirect_delete($r['rid']);
}
 
// No results? Report if that's the case
if (!$any_results) {
  drush_log(dt("Nothing to delete!"), "ok");
}

Save it to a file and run it from within your site's codebase using drush php-script [PATH_TO_SCRIPT], and it will delete any redirects which clash with the URL aliases.