All posts tagged 'YAF-Forums'

Delete Spam Users from YetAnotherForum

by JBrooks 5. September 2010 14:21

 

I use YetAnotherForum (YAF) for my Forums here.   Like others, I get hundreds of spam users created each month.  I wrote the following SQL to delete any user that hasn’t visited in the last day, never posted or emailed a message, isn’t the last user on a forum and isn’t in the Active list.
 
-- first delete the UserGroup rows.
DELETE FROM dbo.yaf_UserGroup
WHERE UserID in
(
SELECT u.UserID
FROM dbo.yaf_User u
WHERE u.LastVisit < getdate() -1
AND u.NumPosts = 0)
AND UserID NOT IN
(
SELECT a.UserID
FROM dbo.yaf_Active a)
AND UserID NOT IN
(
SELECT f.LastUserID
FROM dbo.yaf_Forum f
WHERE f.LastUserID IS NOT NULL)
AND UserID NOT IN
(
SELECT pm.FromUserID
FROM dbo.yaf_PMessage pm)
AND UserID NOT IN
(
SELECT m.UserId
FROM dbo.yaf_Message m)
 
--
-- now delete the User rows.
--

DELETE FROM dbo.yaf_User
WHERE LastVisit < getdate() -1
AND NumPosts = 0
AND UserID NOT IN
(
SELECT a.UserID
FROM dbo.yaf_Active a)
AND UserID NOT IN
(
SELECT f.LastUserID
FROM dbo.yaf_Forum f
WHERE f.LastUserID IS NOT NULL)
AND UserID NOT IN
(
SELECT pm.FromUserID
FROM dbo.yaf_PMessage pm)
AND UserID NOT IN
(SELECT m.UserId
FROM dbo.yaf_Message m)

Tags:

SQL

RecentPosts