Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
How to delete multiple Activity spam posts?
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
0
Comments
Just checked, there's over 200 pages of the stuff. So obviously need to shift all of this.
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?
There was an error rendering this rich post.
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
) asPermissionID
, MAX(p.Garden.Email.View
) asGarden.Email.View
, MAX(p.Garden.Email.Manage
) asGarden.Email.Manage
, MAX(p.Garden.Settings.Manage
) asGarden.Settings.Manage
, MAX(p.Garden.Settings.View
) asGarden.Settings.View
, MAX(p.Garden.Routes.Manage
) asGarden.Routes.Manage
, MAX(p.Garden.Messages.Manage
) asGarden.Messages.Manage
, MAX(p.Garden.Applications.Manage
) asGarden.Applications.Manage
, MAX(p.Garden.Plugins.Manage
) asGarden.Plugins.Manage
, MAX(p.Garden.Themes.Manage
) asGarden.Themes.Manage
, MAX(p.Garden.SignIn.Allow
) asGarden.SignIn.Allow
, MAX(p.Garden.Registration.Manage
) asGarden.Registration.Manage
, MAX(p.Garden.Applicants.Manage
) asGarden.Applicants.Manage
, MAX(p.Garden.Roles.Manage
) asGarden.Roles.Manage
, MAX(p.Garden.Users.Add
) asGarden.Users.Add
, MAX(p.Garden.Users.Edit
) asGarden.Users.Edit
, MAX(p.Garden.Users.Delete
) asGarden.Users.Delete
, MAX(p.Garden.Users.Approve
) asGarden.Users.Approve
, MAX(p.Garden.Activity.Delete
) asGarden.Activity.Delete
, MAX(p.Garden.Activity.View
) asGarden.Activity.View
, MAX(p.Garden.Profiles.View
) asGarden.Profiles.View
, MAX(p.Garden.Profiles.Edit
) asGarden.Profiles.Edit
, MAX(p.Garden.Moderation.Manage
) asGarden.Moderation.Manage
, MAX(p.Garden.AdvancedNotifications.Allow
) asGarden.AdvancedNotifications.Allow
, MAX(p.Conversations.Moderation.Manage
) asConversations.Moderation.Manage
, MAX(p.Vanilla.Settings.Manage
) asVanilla.Settings.Manage
, MAX(p.Vanilla.Categories.Manage
) asVanilla.Categories.Manage
, MAX(p.Vanilla.Approval.Require
) asVanilla.Approval.Require
, MAX(p.Vanilla.Discussions.View
) asVanilla.Discussions.View
, MAX(p.Vanilla.Discussions.Add
) asVanilla.Discussions.Add
, MAX(p.Vanilla.Discussions.Edit
) asVanilla.Discussions.Edit
, MAX(p.Vanilla.Discussions.Announce
) asVanilla.Discussions.Announce
, MAX(p.Vanilla.Discussions.Sink
) asVanilla.Discussions.Sink
, MAX(p.Vanilla.Discussions.Close
) asVanilla.Discussions.Close
, MAX(p.Vanilla.Discussions.Delete
) asVanilla.Discussions.Delete
, MAX(p.Vanilla.Comments.Add
) asVanilla.Comments.Add
, MAX(p.Vanilla.Comments.Edit
) asVanilla.Comments.Edit
, MAX(p.Vanilla.Comments.Delete
) asVanilla.Comments.Delete
, MAX(p.Plugins.Flagging.Notify
) asPlugins.Flagging.Notify
, MAX(p.Plugins.Attachments.Upload.Allow
) asPlugins.Attachments.Upload.Allow
, MAX(p.Plugins.Attachments.Download.Allow
) asPlugins.Attachments.Download.Allow
, MAX(p.Plugins.Signatures.Edit
) asPlugins.Signatures.Edit
, MAX(p.Plugins.WhosOnline.ViewHidden
) asPlugins.WhosOnline.ViewHidden
, MAX(p.Plugins.WhosOnline.Manage
) asPlugins.WhosOnline.Manage
, MAX(p.Plugins.OpenGraph.Manage
) asPlugins.OpenGraph.Manage
, MAX(p.Plugins.CleanLog.Manage
) asPlugins.CleanLog.Manage
, MAX(p.Plugins.Debugger.View
) asPlugins.Debugger.View
, MAX(p.Plugins.Debugger.Manage
) asPlugins.Debugger.Manage
, p.JunctionTable asJunctionTable
, p.JunctionColumn asJunctionColumn
, p.JunctionID asJunctionID
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 asProfileHeadline
, t.AllowComments asAllowComments
, t.ShowIcon asShowIcon
, t.RouteCode asRouteCode
, t.Name asActivityType
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 asFirstDate
, d.DateLastComment asLastDate
, d.LastCommentUserID asLastUserID
, w.UserID asWatchUserID
, w.DateLastViewed asDateLastViewed
, w.Dismissed asDismissed
, w.Bookmarked asBookmarked
, w.CountComments asCountCommentWatch
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`
`
`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: `
You need any more from debug?
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.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
The guest permissions are barely editable as it stands:
the user profile for not signed in defaults to user 0. your activity page does not have correct permissions.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
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.
And, just testing, yes guests can post in activity. I have no idea how to turn that off.
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.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
you might disallow viewing activity for guests until it gets resolved.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
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?
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?
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
Just tried this. As soon as I disable it and save, I go back in and the permissions are reverted back.
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)
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.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
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.
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.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
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.