We faced of one of the projects that we supported with large amount of spam registrations. But there were valid users with assigned roles that should not be deleted.

Short script was written to resolve this task:

<span style="color: #000000"><span style="color: #0000BB">&lt;?php<br></span><span style="color: #007700">require_once </span><span style="color: #DD0000">'./includes/bootstrap.inc'</span><span style="color: #007700">;<br></span><span style="color: #0000BB">define</span><span style="color: #007700">(</span><span style="color: #DD0000">'DRUPAL_ROOT'</span><span style="color: #007700">, </span><span style="color: #DD0000">'&lt;path_to_you_Drupal_folder&gt;'</span><span style="color: #007700">); </span><span style="color: #FF8000">// optional<br></span><span style="color: #0000BB">drupal_bootstrap</span><span style="color: #007700">(</span><span style="color: #0000BB">DRUPAL_BOOTSTRAP_FULL</span><span style="color: #007700">); </span><span style="color: #FF8000">// load Drupal to use Drupal API<br></span><span style="color: #0000BB">$query </span><span style="color: #007700">= </span><span style="color: #DD0000">'SELECT users.uid as uid FROM {users} LEFT JOIN {users_roles} ON users.uid = users_roles.uid WHERE users.uid != 0 AND users_roles.uid IS null ORDER BY users.uid DESC LIMIT 300'</span><span style="color: #007700">;<br></span><span style="color: #0000BB">$result </span><span style="color: #007700">= </span><span style="color: #0000BB">db_query</span><span style="color: #007700">(</span><span style="color: #0000BB">$query</span><span style="color: #007700">);<br>while (</span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">$result</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">fetchObject</span><span style="color: #007700">()) {<br>&nbsp; </span><span style="color: #0000BB">user_delete</span><span style="color: #007700">(</span><span style="color: #0000BB">$row</span><span style="color: #007700">-&gt;</span><span style="color: #0000BB">uid</span><span style="color: #007700">);<br>}<br>echo </span><span style="color: #DD0000">'300 spam users were deleted!'<br></span><span style="color: #0000BB">?&gt;</span></span>

Let me explain this code in details:

If we would like to use Drupal API and DB connection in any separate script not in our custom Drupal module we need to start with these lines:

<span style="color: #000000"><span style="color: #0000BB">&lt;?php<br></span><span style="color: #007700">require_once </span><span style="color: #DD0000">'./includes/bootstrap.inc'</span><span style="color: #007700">;<br></span><span style="color: #0000BB">define</span><span style="color: #007700">(</span><span style="color: #DD0000">'DRUPAL_ROOT'</span><span style="color: #007700">, </span><span style="color: #DD0000">'&lt;path_to_you_Drupal_folder&gt;'</span><span style="color: #007700">); </span><span style="color: #FF8000">// optional<br></span><span style="color: #0000BB">drupal_bootstrap</span><span style="color: #007700">(</span><span style="color: #0000BB">DRUPAL_BOOTSTRAP_FULL</span><span style="color: #007700">); </span><span style="color: #FF8000">// load Drupal to use Drupal API<br></span><span style="color: #0000BB">?&gt;</span></span>

Attention! You should put your path instead ''.
Some times script can work without 'DRUPAL_ROOT' definition, but some times can't like in our case.

Authenticated User role ID is 2. But there is no any record with that role ID in {users_roles} table, so we should select all user IDs that have no records in {users_roles} table users_roles.uid IS null.

And do not forget to exclude Anonymous user record users.uid != 0.
Other wise we can delete record with 0 UID from {users} table, that serve for Anonymous user purpose.

Start to delete most resent users ORDER BY users.uid DESC.

Due to user_delete() function require pretty large number of SQL requests it is reasonable to set some limit LIMIT 300.

If there are a lot of spam users you can set cron task, for example:

*/02 * * * * /usr/bin/wget -O - -q -t 1 http://your_site.com/your_script_name.php >/dev/null 2>&1

Blog tags: Drupal FAQPlanet Drupal