Deleting users from the OJS database

[piotreba]

I also have hundreds of spam users, although using reCaptcha (I use OJS 2.4.7.1). Neither Merge Users nor command-line approaches seem to be handy. Either way, you need to choose or enter hundreds of usernames. I just wanted to remove all users who didn’t validate their accounts directly from database. Anyone has tried it and encountered any problems?

I tested removing disabled users from users table, but the entries in dependent tables were not deleted, i.e., in tables: user_settings, user_interests, roles. Any other tables to include? This could help to remove those users.

[andrewdion04]

how to delete users without role using SQL Query? I need to delete all spam users from the past.

[asmecher]

Hi @andrewdion04,

Have a look at the command-line tool tools/mergeUsers.php.

Regards,
Alec Smecher
Public Knowledge Project Team

[j1shin]

Hi @asmecher,

may i ask why there has never been an option to delete a user completely? I mean, I have never seen an application where you have to “merge into an existing user” to get rid of another one which brings the next question to the table: what do I do with the merged user then? I simply registered a user in a live environment to test something in expectation that I can remove the user later easily via admin … but to no avail. I’m a bit shocked frankly speaking.

So lonG
Daniel

[asmecher]

Hi @j1shin,

If you want to get rid of a test account, merge it into your administrator account. The distinction between “merge” and “delete” is that the merged account’s submissions etc. are preserved.

Regards,
Alec Smecher
Public Knowledge Project Team

[dalemoore]

I too would love a way to just check a checkbox(es) and either delete or merge users automatically through the UI.

In the mean time, as a novice bash user, this worked for me.

SSH into your hosting environment/bash, cd into your OJS directory, and create a space-separated array of usernames and hit Enter/Return.

Example:
users=(username1 username2 username3 username4)

Assuming you’re in the main OJS directory, then type this:
for i in "${users[@]}"; do php tools/mergeUsers.php username_to_merge_into_here $i; done

This will loop through your array and merge each user in the “users” array into the “username_to_merge_into_here” username. Obviously replace that with the username you want to merge into. Also be sure that the mergeUsers.php has execute permissions for your user account.

[kaluosi]

Spam users not registered have not a role, they are not in the table roles. The “good” user have role and theirs user_id are present in the roles table.

The query to delete them is:

delete from users where user_id not in (select user_id from roles);

That sql sentence works in my case.

[ctgraham]

@kaluosi

delete from users where user_id not in (select user_id from roles);

Be aware that this will leave dangling foreign key references in the database, for example in the sessions table, the user_settings table, etc.

[kaluosi]

Thank you ctgraham. I suspected that, but is there any other way to delete 22.000 false users? It seems incredible but OJS have no way to delete these spam users. The recommendation to merge users one by one to delete them afterwards is not very exciting.
One wonders how is it possible that OJS allows the existence of these users in the first place.

[ctgraham]

You can combine your SQL query with the mergeUsers tool to remove the users in batch. One example is given here:

How do I combat SPAM?

There are a number of configuration steps you can take to combat SPAM and other forms of malicious registration activity on your site. CAPTCHA/ReCAPTCHA Configure a CAPTCHA and enable it for user registration, notifications and commenting. We strongly advise using Google’s ReCAPTCHA (https://www.google.com/recaptcha/intro/). (Older OJS 2 versions include a PHP captcha, but that has proven to be less secure than ReCaptcha.) If you are using OJS older than 2.4.8-3, you will not be able to use t…

If new spam registrations are ongoing, there are several plugins which can help:

Removing spam registrants

The mergeUsers tool would be most helpful, I think, in the case of a large number of users. In the other post, James describes using a PHP script to call the mergeUsers tool on a list of hundreds or thousands of usernames. ReCAPTCHA and email validation are setup in config.inc.php. In my personal experience, we found that spammers had completed both ReCAPTCHA and email validation, so our best response was using the plugins:

[asmecher]

Hi all,

I have a github issue open here: Permit the "merge users" CLI tool to handle multiple users at once · Issue #4772 · pkp/pkp-lib · GitHub

It adds support for merging multiple users at once to the command-line tool, removing the need for a shell script loop. It also adds support for specifying users to be merged by user ID, not just username.

Regards,
Alec Smecher
Public Knowledge Project Team

[kaluosi]

Thank you so much to ctgraham and asmecher. Very useful information!!

[ronniebrito]

@ctgraham

delete from users where user_id not in (select user_id from roles);

this could be is usefull to retrieve a list of users to be merged by the tools/mergeUsers.php .

however it seems like some spammers are registered as authors, so they do have a role.

What we want is to refine the query to list all users who:

• are authors but dont have a submission, and
• are not reviewers and dont have any other role in any journal.

The problem is that roles’ table uses an integer to registetr this information in the role_id column. I suspect it is a bitwise encoding, but couldn’t find any documentation about it.

So our query to list suspecious users would be:

select user_id from users where user_id not in
 (
select user_id from roles where role_id = <integer for reviewers, managers, admin, etc..>
UNION
select user_id from roles r, articles a  where r.user_id = a.user_id and r.role_id = <integer for authors> 
)

or something like that…

could anyone help us discover what are the integer codes for the roles mentioned on the query?

regards!

[asmecher]

Hi @ronniebrito,

The role IDs are defined in lib/pkp/classes/security/Role.inc.php:

define('ROLE_ID_MANAGER',               0x00000010);
define('ROLE_ID_SITE_ADMIN',            0x00000001);
define('ROLE_ID_SUB_EDITOR',            0x00000011);
define('ROLE_ID_AUTHOR',                0x00010000);
define('ROLE_ID_REVIEWER',              0x00001000);
define('ROLE_ID_ASSISTANT',             0x00001001);
define('ROLE_ID_READER',                0x00100000);
define('ROLE_ID_SUBSCRIPTION_MANAGER',  0x00200000);

(Check your own to make sure you’re looking at the right value! They may vary slightly in different versions of OJS/OMP.)

You can turn these hexadecimal numbers into integers e.g. using PHP:

php -r "echo 0x00200000 . \"\n\";"

Regards,
Alec Smecher
Public Knowledge Project Team

[obi]

Hi,
Did you find a refined query to delete (some) spammers that are registered as authors? We have the same problem.

-Obi

[ronniebrito]

thanks @asmecher,

so
is 65536

is 1114112

<integer for reviewers, managers, admin, etc…> is always bellow 65536, except when also readers/authors…

@obi, i think the query could be:

select user_id from users where user_id not in
(
select user_id from roles where role_id < 65536
UNION
select r.user_id from roles r, articles a where r.user_id = a.user_id and (r.role_id = 65536 or r.role_id = 1114112)
)

but did not tested it

[obi]

Thanks. I will test it this week and report to the forum.

Obi

[IdeaFix]

Hi. I have an OJS portal with a half of million users. 99% of them are spam users.

I made some search via users table and merged about 150000 users. It is not difficult to find all users with “porno” or “casino” in url and merge them.

Now i am searching users in user_settings table. I can get not names, but user ids in this table. Is it possible to use mergeUsers.php with user ids? Any way to convert ids to names? I know not enough about sql, and i need a simple tool to make this cleanup.

[IdeaFix]

select username from ojs.users WHERE user_id in (select user_id from ojs.user_settings WHERE setting_value like '%porno%');

Something like this seems to be good…

[obi]

I tested the query but it is wrong:

select user_id from users where user_id not in
(
select user_id from roles where role_id < 65536
UNION
select r.user_id from roles r, articles a where r.user_id = a.user_id and (r.role_id = 65536 or r.role_id = 1114112)
).

I cannot figure out where you got “articles a”.