Delete users from the DB?

[drw]

Hi,
I'm working on an old OJS site (version 2.1.1.0) which has a couple of thousand 'fake' registered users.
Given that it is possible to identify them, Is it possible to just delete their records from the DB users table?
If not, what else do I need to do?

many thanks,

David

[JasonNugent]

Hi David,

This can be a frustrating problem on older versions of OJS that did not have captcha enabled. Normally, the mergeUsers tool in the tools directory handles this sort of thing, but it only does one user at a time. There are several tables in the database that may contain user information and which wouldn't be referenced directly by username (like the user_settings table). Deleting those records can be tricky. Since these fake users would probably not have any roles or important content in OJS, my suggestion would be to write a script that wrapped the mergeUsers.php tool and called it for each of the users you wanted removed. You could assign the fake users' content (of which there would be none) to a normal user in your system. The mergeUsers tool removes the old account once it is done.

It is usually a good idea to back up your database before doing mass prunes of user accounts. Just in case.

Regards,
Jason

[drw]

Thanks so much Jason.
Seems pretty complicated. Can you suggest somewhere I can look to see if anyone might have written such a script already?
Perhaps I'll have a go at what you suggest if the deleting from the users table causes problems.

I'm trying to instantiate a new v2.3.6 following the instructions in the README file and it fails, so I'm not particularly encouraged at this stage. I'll post separately about this.

David

[jmacgreg]

Hi David,

Just deleting the user information from the users table will likely cause problems, or at the very least leave cruft lying around in the DB, as user information is stored in other tables than the users table. This is what I do when faced with the same situation (and there are probably many ways to do this):

1) get all the spam users' user IDs (usually by querying the database to select all user IDs against a common spam identification trait -- for example, if the phone or fax field in the users table is "123456");
2) save these IDs to a text file, eg. called userIds.txt;
3) run the following PHP script

Code: Select all

<?php

$ids = file('userIds.txt', FILE_SKIP_EMPTY_LINES);

foreach ($ids as $member_id => $id) {
        echo exec ("php ojs/tools/mergeUsers.php admin " .escapeshellcmd($id));
}
?>

... where "admin" is the name of the user you want all the spam users merged to.

NB: don't place/store the script anywhere where it's web-accessible. And always make a backup of your database before running something like this.

Cheers,
James