OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



SQL to delete SPAM accounts (please review)

Are you responsible for making OCS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, michael, John

Forum rules
What to do if you have a technical problem with OCS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved. Please note that this FAQ is OJS-centric, but most issues are applicable to both platforms.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OCS Conference Support and Discussion subforum; if you have a development question, try the OCS Development subforum.

SQL to delete SPAM accounts (please review)

Postby omacneil » Mon Nov 15, 2010 5:55 pm

We are running 2.2.4.0 of OJS

We have a bunch of SPAM accounts, all are from Afghanistan, None of our legitimate accounts are from Afghanistan.

Will the SQL below delete them without unhappy side effects ?

This thread is encouraging but not official:

viewtopic.php?f=2&t=5610

DELETE FROM user_settings
WHERE user_id IN (
SELECT user_id
FROM users
WHERE country = 'AF'
);

DELETE FROM users
WHERE country = 'AF';

It is my plan to enable captcha tonight.
omacneil
 
Posts: 7
Joined: Sat Apr 24, 2010 1:07 pm

Re: SQL to delete SPAM accounts (please review)

Postby asmecher » Mon Nov 15, 2010 6:30 pm

Hi omacneil,

That should be fine -- if there are other entities that are associated with these accounts, like submissions, then you may run into the odd problem, but I've never encountered spam registrations that did anything more than just create accounts like those you're observing.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 9208
Joined: Wed Aug 10, 2005 12:56 pm

Re: SQL to delete SPAM accounts (please review)

Postby omacneil » Mon Nov 15, 2010 7:05 pm

Thank you

I'm working on a variation of the query that contains the word 'Viagra'
omacneil
 
Posts: 7
Joined: Sat Apr 24, 2010 1:07 pm

Re: SQL to delete SPAM accounts (please review)

Postby omacneil » Mon Nov 15, 2010 7:23 pm

SELECT count(*) from users WHERE country ='AF' AND user_id NOT IN (SELECT USER_id FROM `article_event_log`)

and:

SELECT count(*) FROM users WHERE country ='AF'

both give me '543' so I think it is all ok.
omacneil
 
Posts: 7
Joined: Sat Apr 24, 2010 1:07 pm


Return to OCS Technical Support

Who is online

Users browsing this forum: No registered users and 3 guests