[Imc-tech] Fw: Re: re poker spam

[David Gehrig]

At Zach's request, here is some discussion of what I saw
in My Night At the Databases.

(1) I didn't try to associate the spam with specific IPs; I 
agree that there are spambots and blocking a few IPs won't
make any difference in the long run.

(2) IMC articles are stored in a MySQL database called 
[you'll figure it out] in a table called "articles."  One of
the columns in that table is the field "deleted"; this is
0 unless the article is marked as deleted, in which case
it goes to 1.

(3) What I did last night was just go into the interactive
MySQL client and issue a bunch of commands of the
form

select authors from articles where authors regexp "poker";

That returns a list of authors who have "poker" in their
names.  I looked over the list to make sure there wasn't
anyone who shouldn't be deleted, and then issued the
command 

update articles set deleted = 1 where authors regexp "poker";

That command marks the suckers as deleted.

By doing this for the various buzzwords the spambot 
generates, I was able to get 'em.  I didn't keep count, but
I'd estimate it was over a thousand.

One of the tricks the spambot uses is to escape out characters
in the names -- "poker" instead of "poker" --
just to dodge my searches.  Didn't help the spambot any.

(4) Having slept on it -- I'm going to create a new MySQL
user that has DELETE rights on just the database in 
question, at which point I'll no longer need root and 
will surrender it back to zach.  With that access, and the
MySQL clients I have on other machines, I should be
able to come up with a MySQL script that will automate
what I did.  In the meantime, doing this by hand is
not a big problem.  I will continue to do this so that
other site admins won't have to worry about it.

(5) In the long term, I wonder what would be involved
in upgrading dada, partly to foil the spambot but partly
because some new features are available.  Should I/we 
look into it?

@%<

-----Forwarded Message-----
From: "Zachary C. Miller" <zach at chambana.net>
Sent: Jun 28, 2005 1:34 AM
To: David Gehrig <zemblan at earthlink.net>
Cc: Mike Lehman <rebelmike at earthlink.net>, 
	"Zachary C. Miller" <zach at chambana.net>
Subject: Re: re poker spam

Please communicate your results and actions to tech at ucimc.org so we
have documentation. Thanks so much for taking this on.

David Gehrig wrote:
> Mike + Zach --
> 
> With Zach's pointers I was able to find a way to delete the poker  
> spam.  (Maybe 30 mins once I knew what I was doing.)  PResumably  
> there will be more in the morning.
> 
> I'm going to sleep on it and see what I can do to make this more  
> automatic.
> 
> @%<
> 

-- 
Zachary C. Miller - @= - http://zach.chambana.net/
IMSA 1995 - UIUC 2000 - Just Another Leftist Muppet - Ya Basta!
 Social Justice, Community, Nonviolence, Decentralization, Feminism,
 Sustainability, Responsibility, Diversity, Democracy, Ecology