Fullsix Blog

Hi all,

I would like start this new year with a full dedicated post of one of hell head pain, that’s your usual community server 200x installation with tons of spammers users and content…

I’ve been working with Community Server from Telligent a while ago, and it’s a nicer community driven web platform, and if we could get the CS with Umbraco in the same web site I could say that solution would be the 90% useful in most websites of this days.

Has we have some CS community web sites, we notice that they have been a nice target for the tons of spam and unwanted content… really? who wants sex terms related ads, in your peacefully community site? So he had our mission to go…

First – Protect, close the gates

Sure, the first step is get something that kept the registration form (ie. /yourTheme/users/createuser.aspx) protected against the spammer robot, so the one that we’ve chosen was a captcha mechanism, in order to require a code that only the user can decipher from the image. Sounds promising… from the paper into the code we found a captcha control named Upupo Captcha … nice! It comes with the dll and a sample that we can apply on our pages.

So we first insert the control into the createuser.aspx registration form in order to avoid new spam registrations

<%@ Register Assembly="Upupo.Captcha" Namespace="Upupo.Captcha" TagPrefix="Captcha" %>

In page look like:

With a required control inside of it the automatic spammer cannot advance on this…. at least for now.

So now we close the entrance for new registrations… now let’s deal with the spammer content…

Note, if you want to improve protection with this control you can add it into the comment blogs forms, forum post form and media post form. Same way as the sample createuser.aspx sample page.

Second – Delete, kill all spam traces

Now that we close doors for unwanted “fake persons”, maybe you notice that your community has increased in 2500 new users in some days… wow… all spammers, all trash… so another head pain was how we can remove this guys and grant the database data integrity. When cs has a new user he create a lot of references inside the ASP.NET Membership tables and in CS_ tables… and to help the field UserID is different from the Memebership UserId … yeah… not great implementation.

Although this little issue, you can remove using the ControlPanel, on Memebership administration section, but you have to search and then remove one by one… NO WAY!

That’s right so how we can automate this process, in order to remove the unwanted users and delete related content?

If you explore the StoredProcedures from your CS database you will find something like this:

Hum… <dbo.cs_User_Delete> sounds good, and it sounds! This Sp is used each time that you delete a user using the control panel on the membership admin section. So all we needed to do in order to automate the remove process of this users and contents was a custom SQL to execute this SP with some additional prams and features.

So we used this sp to execute for each registration that we want to find so we did something like this:

SELECT 'EXEC cs_User_Delete '+ CONVERT(int,UserId) + 'Anonymous'
FROM  dbo.cs_Users
WHERE UserName like '%.%.%.%'

So now we’ve only made some adjustments on the LIKE statement in order to catch all spammers, so spaces, keywords that we know like casino or hotel or pharmacy, this query selects all that and draws the execution SQL of the CS delete SP necessary and deletes from all tables the user data and assign their related content with the Anonymous user. Sweet ha?

With this we were capable of remove all spammers from our CS based sites. this is only one tip maybe there are others but the telligent community unfortunately don’t care anymore and I couldn’t find anything related with this.

Stays another journey with CS.

Acordei eram 4:10 da manhã… estava sem sono. A bolonhesa que havia feito para jantar pontapeava-me agora o estômago pelo que decidi levantar-me para beber uma água das pedras. Bolas… acabaram! Não fazia sentido tentar voltar a dormir, por isso, liguei o portátil e fui ao Google pesquisar por quantos dias aguenta um gato sem comer. Pobre Sushi! Mal sabe o que a espera…

Acabei no youtube… e depois de escolher o sonzinho que me apetecia ouvir, passei pelo facebook e servi as Impossible Quiche que havia feito 2 dias atrás… eheheheh, continuo em primeiro lugar entre todos os meus vizinhos (desde que o Cruz desistiu)!

Lembrei-me então que no dia anterior tinha circulado um e-mail pelo departamento, que perguntava se alguém havia feito um “métodozinho” que devolvesse uma frase à la facebook que indicasse quanto tempo faltava para determinada data ou quanto tempo havia passado desde então.

Hoje não vou perder muito tempo a explicar o que fiz ou porque fiz desta ou daquela maneira. Mas para quem quiser saber há quanto tempo foi o 25 de Abril de 1974… aqui fica mais um exercício que pode dar jeito.

Porque uma imagem vale por 1000 palavras…

Este resultado foi obtido através das seguintes linhas de código:

// Output the time capsule result
TimeCapsuleResult1 = new TimeCapsule(new DateTime(1974, 4, 25)).TakeUsToWarpSpeedMrScott().ToString();

// Output the time capsule result
TimeCapsuleResult2 = new TimeCapsule(new DateTime(2010, 1, 14, 14, 53, 12)).TakeUsToWarpSpeedMrScott().ToString();

// Output the time capsule result
TimeCapsuleResult3 = new TimeCapsule(DateTime.Now.Subtract(new TimeSpan(7, 0, 0, 0))).TakeUsToWarpSpeedMrScott().ToString();

Para quem quiser os ficheiros com a implementação deste exercício (sem os quais a chamada de cima não faz qualquer sentido), podem retirá-los em Source Code.

Curiosidades

Ao que parece o PHP já faz isto de forma nativa… alguém se deve ter dado ao mesmo trabalho!