The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 22, 2019, 07:29:12 PM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: Quick SQL queries to look for backfill spam  (Read 11620 times)
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« on: September 11, 2010, 03:06:51 PM »

I just thought this might be handy for anyone else running a board. I scan for comments that have been backfilled with spam (editing previously dropped seemingly-innocuous comments with spam links) as well as signatures by people that have been edited after they've dropped messages and come back later. It's very simple, but handy.

First, get rid of bonehead signatures:
update smf_members set signature='No links in signatures please' where posts < (your threshold)

Set this to run daily automatically.

Second, show me all posts where there is a link, the message has not been moved to our private Spam and Quarantine board and posters have less than (a threshold) of posts:

select body
from smf_messages, smf_members
where
   smf_messages.body like '%[url%' and
   id_board <> (your spam board ID_BOARD value) and
   smf_members.id_member = smf_messages.id_member and
   smf_members.posts < (your threshhold)


Using phpMyAdmin it's easy to see what you've already allowed through and what makes sense, vs what is selling something and pointing where it shouldn't. There is a subtle problem with running this automatically and having a cure in it. You may well have people that have less than (your threshold) posts but the message is valid and/or desired. So you can add a field called "approved" and set that to true, once you've approved the post (thereby excluding it in the future). But if you do that, you risk that same person (being the enormously clever spammer they are) coming back and backfilling that very post with spam. Once it's approved in that model, it would be invisible to you. So although it takes a bit longer, I just look through them and know which ones to ignore.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Phaėton
Lifer
*****
Offline Offline

Posts: 555


⎝⏠⏝⏠⎠


View Profile
« Reply #1 on: September 12, 2010, 10:21:58 PM »

nice!
Logged

When I was your age we used to walk to the TV to change the channel....  _̴ı̴̴̡̡̡ ̡͌l̡̡̡ ̡͌l̡*̡̡ ̴̡ı̴̴̡ ̡̡͡|̲̲̲͡͡͡ ̲▫̲͡ ̲̲̲͡͡π̲̲͡͡ ̲̲͡▫̲̲͡͡ ̲|̡̡̡ ̡ ̴̡ı̴̡̡
alex1978
n00b
*
Offline Offline

Posts: 1


View Profile WWW
« Reply #2 on: March 01, 2011, 12:55:50 AM »

Great post!”
Logged

No links in sigs.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #3 on: March 01, 2011, 07:16:41 AM »

 ROFLMAO ROFLMAO ROFLMAO

This post was put into THIS thread?  ROFLMAO
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!