How to delete multiple Activity spam posts?

OnlyAnExcuse · October 2012

Been having a problem with spammers signing up, commenting in Activity and becoming [deleted users] themselves. I've counted over 30 pages with spam from 2 days.

I've installed botstop and stop forum spam and while they aren't bothering with the forums, it's still leaving hundreds of spam comments.

Any way to purge the activity section of these? It's not like they are registered users, so can't delete all content with a ban.

Have a look, I gave up after deleting 2 1/2 pages worth: http://forum.onlyanexcuse.com/activity

OnlyAnExcuse · October 2012

Just checked, there's over 200 pages of the stuff. So obviously need to shift all of this.

UnderDog · October 2012

Turn on SQL debug for a couple of seconds.
Check which SQL is executed for that activity page. Then turn off the SQL debug again :-).
Can you post the SQL that is executed for that activity page?

OnlyAnExcuse · October 2012

It's a long one...

`
Debug Information
Canonical Url: http://forum.onlyanexcuse.com/activity
Cache Information

Cache Revision: 1
Permissions Revision: 1

19 queries in 0.0351860523224s
Gdn_Model->GetWhere(array('UserID' => '13'))0.014536s

select *
from GDN_User User
where UserID = '13';

PermissionModel->PermissionColumns()0.000161s

select *
from GDN_Permission Permission
limit 1;

PermissionModel->CachePermissions('13')0.000215s

select MAX(p.PermissionID) as PermissionID, MAX(p.Garden.Email.View) as Garden.Email.View, MAX(p.Garden.Email.Manage) as Garden.Email.Manage, MAX(p.Garden.Settings.Manage) as Garden.Settings.Manage, MAX(p.Garden.Settings.View) as Garden.Settings.View, MAX(p.Garden.Routes.Manage) as Garden.Routes.Manage, MAX(p.Garden.Messages.Manage) as Garden.Messages.Manage, MAX(p.Garden.Applications.Manage) as Garden.Applications.Manage, MAX(p.Garden.Plugins.Manage) as Garden.Plugins.Manage, MAX(p.Garden.Themes.Manage) as Garden.Themes.Manage, MAX(p.Garden.SignIn.Allow) as Garden.SignIn.Allow, MAX(p.Garden.Registration.Manage) as Garden.Registration.Manage, MAX(p.Garden.Applicants.Manage) as Garden.Applicants.Manage, MAX(p.Garden.Roles.Manage) as Garden.Roles.Manage, MAX(p.Garden.Users.Add) as Garden.Users.Add, MAX(p.Garden.Users.Edit) as Garden.Users.Edit, MAX(p.Garden.Users.Delete) as Garden.Users.Delete, MAX(p.Garden.Users.Approve) as Garden.Users.Approve, MAX(p.Garden.Activity.Delete) as Garden.Activity.Delete, MAX(p.Garden.Activity.View) as Garden.Activity.View, MAX(p.Garden.Profiles.View) as Garden.Profiles.View, MAX(p.Garden.Profiles.Edit) as Garden.Profiles.Edit, MAX(p.Garden.Moderation.Manage) as Garden.Moderation.Manage, MAX(p.Garden.AdvancedNotifications.Allow) as Garden.AdvancedNotifications.Allow, MAX(p.Conversations.Moderation.Manage) as Conversations.Moderation.Manage, MAX(p.Vanilla.Settings.Manage) as Vanilla.Settings.Manage, MAX(p.Vanilla.Categories.Manage) as Vanilla.Categories.Manage, MAX(p.Vanilla.Approval.Require) as Vanilla.Approval.Require, MAX(p.Vanilla.Discussions.View) as Vanilla.Discussions.View, MAX(p.Vanilla.Discussions.Add) as Vanilla.Discussions.Add, MAX(p.Vanilla.Discussions.Edit) as Vanilla.Discussions.Edit, MAX(p.Vanilla.Discussions.Announce) as Vanilla.Discussions.Announce, MAX(p.Vanilla.Discussions.Sink) as Vanilla.Discussions.Sink, MAX(p.Vanilla.Discussions.Close) as Vanilla.Discussions.Close, MAX(p.Vanilla.Discussions.Delete) as Vanilla.Discussions.Delete, MAX(p.Vanilla.Comments.Add) as Vanilla.Comments.Add, MAX(p.Vanilla.Comments.Edit) as Vanilla.Comments.Edit, MAX(p.Vanilla.Comments.Delete) as Vanilla.Comments.Delete, MAX(p.Plugins.Flagging.Notify) as Plugins.Flagging.Notify, MAX(p.Plugins.Attachments.Upload.Allow) as Plugins.Attachments.Upload.Allow, MAX(p.Plugins.Attachments.Download.Allow) as Plugins.Attachments.Download.Allow, MAX(p.Plugins.Signatures.Edit) as Plugins.Signatures.Edit, MAX(p.Plugins.WhosOnline.ViewHidden) as Plugins.WhosOnline.ViewHidden, MAX(p.Plugins.WhosOnline.Manage) as Plugins.WhosOnline.Manage, MAX(p.Plugins.OpenGraph.Manage) as Plugins.OpenGraph.Manage, MAX(p.Plugins.CleanLog.Manage) as Plugins.CleanLog.Manage, MAX(p.Plugins.Debugger.View) as Plugins.Debugger.View, MAX(p.Plugins.Debugger.Manage) as Plugins.Debugger.Manage, p.JunctionTable as JunctionTable, p.JunctionColumn as JunctionColumn, p.JunctionID as JunctionID
from GDN_Permission p
join GDN_UserRole ur on p.RoleID = ur.RoleID
where ur.UserID = '13'
group by p.JunctionTable, p.JunctionColumn, p.JunctionID;

UserModel->SetField(13, array('LastIPAddress' => '176.25.20.229', 'AllIPAddresses' => array(9)))0.007252s

update GDN_User User
set LastIPAddress = '176.25.20.229',
AllIPAddresses = '0.0.0.0,10.12,10.124.218.174,10.125.104.144,10.125.189.158,10.125.62.171,10.126.173.80,10.126.211.17,176.25.20.229'
where UserID = '13';

ActivityModel->GetWhere(array('NotifyUserID' => -1), 0, 30)0.001414s

select a2.*, t.FullHeadline as FullHeadline, t.ProfileHeadline as ProfileHeadline, t.AllowComments as AllowComments, t.ShowIcon as ShowIcon, t.RouteCode as RouteCode, t.Name as ActivityType
from GDN_Activity a
join GDN_Activity a2 on a.ActivityID = a2.ActivityID
join GDN_ActivityType t on a2.ActivityTypeID = t.ActivityTypeID
where a.NotifyUserID = '-1'
order by a.DateUpdated desc
limit 30;

UserModel->GetIDs(array('0' => 0, '1' => ''))0.000328s

select *
from GDN_User User
where UserID in ('0');

UserModel->GetIDs(array('0' => 0))0.000149s

select *
from GDN_User User
where UserID in ('0');

ActivityModel::GetActivityType('18')0.000544s

select *
from GDN_ActivityType ActivityType;

ActivityModel->GetComments(array(30))0.000894s

select c.*
from GDN_ActivityComment c
where c.ActivityID in ('11408', '11405', '11404', '11403', '11402', '11400', '11368', '11348', '11347', '11346', '11345', '11344', '11343', '11342', '11341', '11340', '11339', '11338', '11337', '11336', '11335', '11334', '11333', '11332', '11331', '11329', '11328', '11327', '11326', '11325')
order by c.ActivityID, c.DateInserted asc;

Gdn_Model->GetWhere(array('UserID' => 0), 'Name')0.000216s

select *
from GDN_UserMeta UserMeta
where UserID = '0'
order by Name asc;

DiscussionModel->Get(0, '10')0.000248s

select d.*, d.InsertUserID as FirstUserID, d.DateInserted as FirstDate, d.DateLastComment as LastDate, d.LastCommentUserID as LastUserID, w.UserID as WatchUserID, w.DateLastViewed as DateLastViewed, w.Dismissed as Dismissed, w.Bookmarked as Bookmarked, w.CountComments as CountCommentWatch
from GDN_Discussion d
left join GDN_UserDiscussion w on d.DiscussionID = w.DiscussionID and w.UserID = 13
order by d.DateLastComment desc
limit 10;

CategoryModel::Categories('4')0.003891s

select c.*, lc.DateInserted as DateLastComment
from GDN_Category c
left join GDN_Comment lc on c.LastCommentID = lc.CommentID
order by c.TreeLeft asc;

CategoryModel::JoinRecentPosts(array(20))0.000177s

select *
from GDN_Discussion Discussion
where DiscussionID in ('1556', '1561', '1508', '1560', '1550', '1546', '1556', '1512', '1534', '507', '1541', '1533', '1532', '1531', '1530', '1341');

CategoryModel::JoinRecentPosts(array(20))0.000143s

select *
from GDN_Comment Comment
where CommentID in ('9172', '9186', '8810', '9166', '9089', '9075', '9098', '9163', '8991', '7787', '9000', '9185', '9184', '9183', '9182');

CategoryModel::JoinUserData(array(20), 1)0.000121s

select *
from GDN_UserCategory UserCategory
where UserID = '13';

UserModel->GetIDs(array(5))0.003786s`

`

OnlyAnExcuse · October 2012

`select *
from GDN_User User
where UserID in ('84', '24', '74', '147');

RoleModel->GetApplicantCount()0.000223s

select count(u.UserID) as UserCount
from GDN_User u
join GDN_UserRole ur on u.UserID = ur.UserID
where ur.RoleID = '4'
and u.Deleted = '0';

LogModel->GetCountWhere(array('Operation' => array('0' => 'spam')))0.000146s

select count(l.LogID) as CountLogID
from GDN_Log l
where Operation in ('spam');

LogModel->GetCountWhere(array('Operation' => array('0' => 'moderate')))0.000089s

select count(l.LogID) as CountLogID
from GDN_Log l
where Operation in ('moderate');

Page completed in 1.0103s
Controller Data

Breadcrumbs:
0:
Name: 'Activity'
Url: '/activity'
Title: 'Recent Activity'
Filter: 'public'
Activities:
0:
ActivityID: '11408'
ActivityTypeID: '18'
NotifyUserID: '-1'
ActivityUserID: '0'
RegardingUserID: NULL
Photo: 'http://www.gravatar.com/avatar.php?gravatar_id=d41d8cd98f00b204e9800998ecf8427e&size=50&default=http://vanillicon.com/d41d8cd98f00b204e9800998ecf8427e.png'
HeadlineFormat: '{ActivityUserID,user}'
Story: 'comment2, adderall, 636, '
Format: 'BBCode'
Route: NULL
RecordType: NULL
RecordID: NULL
InsertUserID: NULL
DateInserted: '2012-10-25 12:48:28'
InsertIPAddress: '188.92.75.82'
DateUpdated: '2012-10-25 12:48:28'
Notified: '0'
Emailed: '0'
Data:
FullHeadline: NULL
ProfileHeadline: NULL
AllowComments: '0'
ShowIcon: '0'
RouteCode: NULL
ActivityType: 'Status'
ActivityName: NULL
ActivityEmail: NULL
ActivityGender: NULL
ActivityPhoto: 'http://www.gravatar.com/avatar.php?gravatar_id=d41d8cd98f00b204e9800998ecf8427e&size=50&default=http://vanillicon.com/d41d8cd98f00b204e9800998ecf8427e.png'
RegardingName: NULL
RegardingEmail: NULL
RegardingGender: NULL
RegardingPhoto: NULL
PhotoUrl: '/profile/0/'
Url: 'http://forum.onlyanexcuse.com/activity'
Headline: ''
Comments:
20mg, '
1:
ActivityID: '11405'
ActivityTypeID: '18'
NotifyUserID: '-1'
ActivityUserID: '0'
RegardingUserID: NULL
Photo: 'http://www.gravatar.com/avatar.php?gravatar_id=d41d8cd98f00b204e9800998ecf8427e&size=50&default=http://vanillicon.com/d41d8cd98f00b204e9800998ecf8427e.png'
HeadlineFormat: '{ActivityUserID,user}'
Story: '2, plavix, '
Format: 'BBCode'
Route: NULL
RecordType: NULL
RecordID: NULL
InsertUserID: NULL
DateInserted: '2012-10-25 08:19:17'
InsertIPAddress: '91.201.64.28'
DateUpdated: '2012-10-25 08:19:17'
Notified: '0'
Emailed: '0'
Data:
FullHeadline: NULL
ProfileHeadline: NULL
AllowComments: '0'
ShowIcon: '0'
RouteCode: NULL
ActivityType: 'Status'
ActivityName: NULL
ActivityEmail: NULL
ActivityGender: NULL
ActivityPhoto: 'http://www.gravatar.com/avatar.php?gravatar_id=d41d8cd98f00b204e9800998ecf8427e&size=50&default=http://vanillicon.com/d41d8cd98f00b204e9800998ecf8427e.png'
RegardingName: NULL
RegardingEmail: NULL
RegardingGender: NULL
RegardingPhoto: NULL
PhotoUrl: '/profile/0/'
Url: 'http://forum.onlyanexcuse.com/activity'
Headline: ''
Comments:
2: `

OnlyAnExcuse · October 2012

You need any more from debug?

peregrine · October 2012

why do you allow users who are not signed in to make comments. You are going to always have spam as long as you allow that. you need to fix your permissions so if you not signed in you can't make a comment. Something may be wrong with your guest permissions. Also Verify permissions for all roles.

OnlyAnExcuse · October 2012

The guest permissions are barely editable as it stands:

peregrine · October 2012

the user profile for not signed in defaults to user 0. your activity page does not have correct permissions.

OnlyAnExcuse · October 2012

Applicants could post. It seems no matter how often I alter the settings for Applicants, they revert back.

For example, changing the plugin permissions such as down/uploading attachments, it reverts to ticked boxes every time.

OnlyAnExcuse · October 2012

And, just testing, yes guests can post in activity. I have no idea how to turn that off.

peregrine · October 2012

If it was vanilla 2.0.18.4 I could pursue it. But with vanilla 2.1a27 - I won't even attempt. since its still alpha. You might post this on github.

peregrine · October 2012

you might disallow viewing activity for guests until it gets resolved.

OnlyAnExcuse · October 2012

Understand. I'll have to look at the database, the site was 2.0.x and was upgraded to 2.1 a while ago. Guest permissions from the old site may have stuck, and maybe they can't be unstuck?

peregrine · October 2012

I asked this question a while back but no one responded. Did you use the /utility/upgrade on a vanilla 2.0.81.4 database to upgrade to 2.1? no one answered.

or did you manually move things over?

OnlyAnExcuse · October 2012

Just tried this. As soon as I disable it and save, I go back in and the permissions are reverted back.

peregrine

@peregrine said:

you might disallow viewing activity for guests until it gets resolved.

OnlyAnExcuse · October 2012

I did use the utility/upgrade method, along with deleting all .ini files etc. I had 2.0.x, upgraded to 2.1, then set up another forum (2.1, clean install) and moved it (same database)

peregrine · October 2012

try changing in the permissions table for roleid 2 - assuming that is guest.

change the value from 1 to 0 under garden.activity.view column for guest role.

that should make it unviewable by brute force.

OnlyAnExcuse · October 2012

Thanks @peregrine i'm going to give it a try - if I can find garden.activity.view!

It's not the first permissions-based problem i've had, some i've fixed with ease and others i've adapted to.

I'm going to set up a fresh install and see how it behaves in comparison.

peregrine · October 2012

it looks like it worked, I can't view it.

now you probably need to delete all the spam.

check the activity id in the activity table for spam messages. It may be 0 or null.

backup up the activity table first.

select  FROM GDN_Activity` WHERE `GDN_Activity`.`ActivityID` = 0  ;

if that pulls up only spam

you could 

then do 

Delete  FROM GDN_Activity` WHERE `GDN_Activity`.`ActivityID` = 0  ;

OnlyAnExcuse · October 2012

Thanks Peregrine you're a star. Strangely, looking at the database under UserID, there's one with UserID 0 with RoleID 1, and UserID 0 with RoleID 2.

Checking RoleID, they begin at 2 (guest) and go up. There's no RoleID there for 1.