Products

Solutions

Learn More

Partners

Community

Blog

About

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

The Community Blog is a personal opinion of community members and by no means the official standpoint of DNN Corp or DNN Platform. This is a place to express personal thoughts about DNNPlatform, the community and its ecosystem. Do you have useful information that you would like to share with the DNN Community in a featured article or blog? If so, please contact .

The use of the Community Blog is covered by our Community Blog Guidelines - please read before commenting or posting.


Identifying and removing SPAM User Account Registrations from a DNN site

They say any publicity is good publicity, and a fate far worse than people talking about you is nobody talking about you.  That probably applies to starlets and aspiring celebrities, or perhaps struggling TV shows.  But when it comes to software, good talking points is better than bad talking points.  But I guess in a way you haven’t made it until someone writes a crack for your licensing scheme or tries to attack your users with malicious software. 

I’m referring to Registration SPAM bots, those pesky automated programs who roam the internet, looking for sites to post fake users to, for the purposes of updating user profiles with junky links and text, or filling up forums with spam posts.  This is a long-standing problem in the web world, something which has long been an issue with other Content delivery software like Wordpress, Drupal and others.   Given the longevity and reach of DNN, it was only a matter of time before the bot-writers turned their attention to the large amount of live DNN sites on the internet.

This post is going to cover identifying and removing those SPAM user registrations in DNN.   I’m not going to cover preventative measures here because that’s another topic entirely.  The most important thing I can see is keep up with DNN releases as they come out, because the community and DNN teams are working on these issues continually.

What SPAM bots do, and why they do it

Usually, it’s all about Search Engine Optimization (SEO).  Sites are considered by search engines to be more authoritative and popular if they contain many other links pointing at them.  The link text of those links matters – a site linked by many others for ‘Red Top Hats’ is likely to rank well for that term.   So a site owner might go around the internet and try and generate as many links as possible back to their site with the anchor text of ‘Red Top Hats’.  All going well, they’ll rise to the top of the list for that phrase, sell oodles of Red Top Hats, and retire to the Caribbean.

That’s a great plan to corner the vividly coloured millinery market – but it is rather tedious trying to generate all those links.  The less-scrupulous (or, those paying the less scrupulous, either knowingly or unknowingly) do what anyone does when faced with a large, repetitive task – they automate it.  The automation is done by writing a program which will crawl all over the internet, looking for forms that accept input, and attempt to post html to those forms, in the hope that the html stays on the site somewhere, thus creating the links.

For the innocent site owner, what this looks like is a torrent of broken form submissions and errors, and a pile of weird looking links and posts if they happen to be successful.   These usually are run on infected computers so they come from all over the place – it’s not easy to just block a specific IP address.

SPAM bots generally come in two flavours – the generic and dumb, and the tailored.   Generic bots look for any form with input fields and a submit button, and just post content.  This is the type that tends to fill up blog comments and end up in enquiry forms.  (Hey Joe, we just got an enquiry for <a href=http://www.redtophats.com>buy red top hats</>do you know them?)

The tailored are bots written specifically to target a specific type of software.  And that’s the type that has been making the rounds of DNN sites since about May 2014.  And boy, are they annoying or what?   Specifically, this one registers a fake user account, then goes to update the user profile biography field, filling it with ‘Hey, here’s a great Red Top Hat shop where you can buy Red Top Hats’.

Pictured above is the type of payload delivered in a User Profile.   It’s typical in that it has keyword anchor text.

With enough of these around, then the net affect of all those links from all those sites can help a site rank for a specific term.   There is a problem for the spammers though – the first is that they usually are not very careful with crafting their payloads, and usually a lot of links are broken (an affected site will see plenty of 404 messages in the log with weird looking URLs as a result).  In the above example – not one single link went to the intended location because the html wasn’t crafted properly.   The second is that the Google anti-spam team have rolled out several updates to try and combat the practice by penalizing sites which have dubious links.   But that still leaves the unfortunate site owner with a mess that needs cleaning up.

Identifying SPAM User Registrations in DNN

The difficulty with identifying fake users is sorting them from real users.  If you don’t have any real users registering on your site, your job is that much easier because all users from a certain point onwards will be spam users (and you should switch off public registration is that is the case). 

The key to identifying fake users is that they don’t behave like ‘real’ users.  Real users log in more than once, they fill out their profile fields in random ways, and they use real names in different ways.   The SPAM created users generally use lists of emails of real people to register on the site, so that they look like real people to the incurious administrator.

I hesitate to proscribe a one size fits all solution to identifying fake users because every site has a different set of users.  Keeping that in mind, here are some queries I have used to identify fake users in SPAM affected DNN sites.   I advise running these through your Host->SQL page (or direct in a SQL query tool, if you use one).  See if they are identifying only fake users, and not catching real users in the queries.  Then I’ll show how you can use these to delete the fake ones.

First Query – Identify Users which have never logged in, and where the DisplayName isn’t two words.

select top 100 *
from {databaseOwner}{objectQualifier}Users
where (LastIPAddress = '' or LastIPAddress is null)
and IsDeleted = 0
and DisplayName not like ('% %')
and CreatedOnDate > '2014-04-01'
order by UserId desc

I have found that SPAM registered users use a single-word Display Name like ‘JoeBloggs’ instead of Joe Bloggs.  Because this is a pattern, it makes it easier to identify.  The other part of this query is identifying users created after a specific date, which is when the site started getting affected by registration bots.

Note that I have a restriction on the number of rows returned – this is because I use these queries to see if I am capturing only fake users, and I don’t want to return more rows than I could possibly look through.

Second Query – Identify Users with a big chunk of Html in their profile

select top 100 *
from {databaseOwner}{objectQualifier}Users u inner join {databaseOwner}{objectQualifier}UserProfile up on u.UserId = up.UserId and PropertyDefinitionId = 36
where u.UserId in (
select UserId from {databaseOwner}{objectQualifier}UserProfile
where PropertyDefinitionId = 36
and PropertyValue like '<%')
and datediff(d,LastModifiedOnDate, CreatedOnDate) > -7

This looks for encoded Html in the User Profile field of Property Definition ID ‘36’ – which in this site is the profile field for the biography.  You may want to check your profile definitions to see which one is your biography profile feed – not all DNN installs will have the same ID of 36.

The second part of it looks for a difference between the LastModifiedOnDate – which is a proxy for the last login – and the CreatedOnDate, which is the date it was created.  This separates people in your user list who have a ‘real’ biography – who are likely to have logged on more than a few times – with those who logged on once, updated their profile and never came back.

Take a look through the results.  You’ll see that the user profile feed is encoded Html, so you can find an online html decoder to see the result.

Html in User Profile Table

Copying / pasting one of these values into an online Html decoder shows you what the spammers are up to:

However, typically, after it gets posted to a DNN User Profile, the Html is messed up and the links, formatting and images don’t work:

If they actually took care with this stuff, they’d be twice as effective.  Being malicious and sloppy just makes it all the more frustrating.

With these two queries, I found I could identify about 90% of the fake users without catching any of the ‘real’ users in the net.  In truth, it’s likely that one or two real users got caught in the trap.  It all depends on how important your user records are (is it a blogging site where people register to comment, or a paid membership site?)

There are other methods identified as well – see Sebastian Leupolds blog ‘Getting rid of SPAM users’.  I suggest trying a few different queries out, and being creative with the fields in the User table  and UserProfile tables until you can reliably identify fake users by SQL query.  Keep it simple by writing multiple queries rather than trying to write one query to rule them all.

Deleting Fake Users

Deleting users can either be done through the DNN Administrative interface – if you have 1 or 2 – but if you have 10, 20 or 100,000 fake users, then it’s going to take some batch database updates.

What you need to do is set the IsDeleted column in the UserPortals table to ‘1’.  This ‘soft’ deletes the user, so that the account is disabled and the profile is no longer available.

Here’s the accompanying Update statements to match the above queries.

First Query

update {databaseOwner}{objectQualifier}UserPortals
set IsDeleted = 1
where UserId in (
select u.UserId from {databaseOwner}{objectQualifier}UserProfile up
inner join {databaseOwner}{objectQualifier}Users u
on u.UserId = up.UserId
where PropertyDefinitionId = 36
and PropertyValue like '<%'
and datediff(d,LastModifiedOnDate, CreatedOnDate) > -7)

Second Query

update {databaseOwner}{objectQualifier}UserPortals
set IsDeleted = 1
where UserId in (select UserId from {databaseOwner}{objectQualifier}Users
where (LastIPAddress = '' or LastIPAddress is null)
and IsDeleted = 0
and DisplayName not like ('% %')
and CreatedOnDate > '2014-04-01'

A soft delete is used because it’s easy to reverse if you have problems or inadvertently delete a real user.  The accounts cannot be used or seen once they are soft deleted.

Completely Removing Deleted Users

If you’ve soft deleted your fake users, and haven’t had any problems with real users not appearing, then you might want to completely remove the users.  The DNN Admin interface provides a way to do this – but, again, if you have 100,000 of these fake accounts to delete, then you’ll need a batch process.   The problem with just deleting the users from the database is that it leaves behind the user folders in the Portal directory.  If you had a bad infection of SPAM users, you’ll have 10s of thousands of empty user folders.  This can cause delays in creating backups, make it slow to navigate the site, and cause other issues.   Unfortunately, you can’t easily identify the folders by user id, because the folder naming uses an algorithm to ensure that the folder hierarchy grows organically. 

The only way to do it properly is to use the DNN API itself to delete the user, which cleans up the database record correctly, deletes the folders and completely removes the user safely from the site.

I wrote a small utility to achieve this, called Dnn Bulk User Delete.  This is a two-part install, where one part is installed to your DNN site, and the other is run from your local machine.  The DNN extension is a library which calls the correct API and exposes a WebAPI endpoint.  The console is a simple Windows App that calls the WebAPI endpoint on your site to delete users.  It’s designed to repetitively delete small batches of users so that the deletion doesn’t overwhelm the database and file system of your site – which it will if you try and delete 100,000 users at once.

You can obtain the Bulk User Delete from here : https://github.com/brucerchapman/DnnBulkUserDelete – just download the Console App and Bulk User Delete install package from the 01.00.00_Release folder.   The Wiki for the utility is here: https://github.com/brucerchapman/DnnBulkUserDelete/wiki

Steps to use the Dnn Bulk User Delete Utility

  1. Obtain the console and Dnn install packages from the Dnn Bulk User Delete Github repository.
  2. Install the Dnn.BulkUserDelete_01.00.00_Install.zip into the affected Dnn site by using the Host->Extensions page
  3. Extract the Console program from the Dnn.BulkUserDelet_01.00.00_Console.zip archive and put it on your local machine somewhere, then run it
  4. You’ll see the console as below:

  5. Enter your site alias (domain name, or domain name and path if that is what the site alias is).  This works with one single portal at a time.  Enter your Host user and password.  You can click on ‘Ping Host’ to check the authentication is correct.  If not, try the ‘Ping Anon’ to see if you have basic connection to the site working.
  6. Use the ‘Check Soft Deleted Users’ button to ensure that everything is correct and that there are users to delete.  This will identify the size of the task at hand.  This is simply checking the Users in the portal for how many are soft deleted.
  7. Check the ‘Test Mode’ and try running a ‘Hard Delete 1 Batch’.  This will do a mock run and see if everything is working, that the users can be identified and that everything is set up. 
  8. When ready, uncheck the ‘Test Mode’ and do an actual ‘Hard Delete 1 Batch’.  This will delete the users and the folders for those users.
  9. If the Hard delete 1 batch worked, set the batch size and the wait time, and click on ‘Start Deleting Batches’.  The program will chew away deleting batches of users, and give you a predicted completion time, based on the speed of the last delete.  This number jumps around a bit because different batches take different times.
  10. Leave it running until the very last fake SPAM user is gone from your site.  You’ll see something like this:

 

The option for ‘Fast Delete’ does a direct delete of the user folder, instead of calling the DNN Folder delete.  This is because the DNN Folder delete also updates the cache and resynchs, which can take much longer.  The Fast Delete directly deletes the folders from the server.  When completed, you’ll need to re-synchronize the DNN File System in the File Manager.  You’ll also notice that ‘Parent’ folders are cleaned up as users are deleted – this ensures that the Users folder hierarchy shrinks back down as you remove users – so that you don’t end up with a messy set of parent folders with no subfolders.  In theory, if you had 100,000 spam users and only two legitimate users, you’d go from 200,00+ folders back to about 4.

It’s not very pretty, and the interface is JSON results into a plain text field, but it does work and I have used it to clean up some very bad infestations of SPAM users.  Please try it out and see if it works for you – it’s a cleaner and more robust way of deleting users than attacking it piecemeal with direct database and folder deletions.

If you have other tips for identifying SPAM users, please share via the comments.

Comments

HSD DNN
Thank you so much for the SQL queries! I was able to identify and 'soft' delete the SPAM user accounts. Didn't have to use Bruce's delete tool i was able to simply delete all the 4k accounts from the user accounts admin console.
HSD DNN Thursday, March 19, 2015 4:20 PM (link)
mohammad azarbara
Dear Bruce,
thanks for your great article,
i have a problem to run console and hard delete the users
i get the permission error in local machine
401 - Unauthorized:  Access is denied due to invalid credentials.
You do not have permission to view this directory or page using the credentials that you supplied.


and the following error accord after run the console in server machine directly!


Dnn Bulk User Delete 1.0.0.0 started at 13/Apr/2015 09:57:59
HTTP Error 401.1 - Unauthorized


could you please help to fix this issues?
mohammad azarbara Monday, April 13, 2015 1:41 AM (link)
Bruce Chapman
@mohammad - please don't post detailed error messages in blog comments. I have snipped the detail out.

The issue is that you have authentication problems as per the 401 status response. You will need to determine your local setup until you can issue the 'ping' command from the console to the server without receiving these responses. You should check the ability of your username/password combination to logon to the server. Other people have reported issues with having expired user accounts or passwords that required a reset. There is nothing I can do to help here because it's all in your local machine setup. The software works when configured correctly.

Please post any issues with the code at the project home page at github : https://github.com/brucerchapman/DnnBulkUserDelete/wiki
Bruce Chapman Monday, April 13, 2015 2:52 AM (link)
Chris Hammond
Bruce, thanks for providing the bulk delete tool!
Chris Hammond Monday, April 13, 2015 9:25 AM (link)
Bruce Chapman
@Chris - you've used it successfully?
Bruce Chapman Monday, April 13, 2015 6:06 PM (link)
Scott Wilkinson
@mohammad,

I was getting the same problem. I was able to resolve by opening IIS Manager on the server, go to the site. Click on Authentication. Disable Windows Authentication. The only two authentication options that should be enabled are: Anonymous and Forms. That fixed the 401 issue.
Scott Wilkinson Wednesday, August 19, 2015 4:25 PM (link)
Joe Wolner
When trying to run the console, I never could get the Ping Host or check soft deleted users to function unless I changed the web.config membership provider AspNetSqlMembershipProvider to have the requiresQuestionAndAnswe=false. This won't work for us as this is a requirement of our site. Is there a way to use the AspNetSqlMembershipProviderAdmin instead as that has the requiresQuestionAndAnswer setting to false.
Joe Wolner Monday, November 7, 2016 2:11 PM (link)
Dave Diehl
Does anyone know if this is still compatible with DNN 7.4.2?
Dave Diehl Saturday, February 11, 2017 10:40 PM (link)
Bruce Chapman
@Dave it should work, and if it doesn't you can recompile the code to suit. The underlying idea is still the same, using the API to cal the DNN Delete User routine.
Bruce Chapman Monday, February 13, 2017 9:31 PM (link)
Sean Tsang
I really hope I could use this tool.
However, I am stuck with an old version of DNN 6 with about 1.8M fake users. What should I do then....
All I have done is just turn off the registration.

Sean Tsang Monday, March 27, 2017 8:56 PM (link)
Bruce Chapman
@Sean - you can either open up the supplied source code and back-date it to suit DNN 6, or look into overcoming whatever blockage you have to going forwards to the latest version. It is important to get sites onto the latest version because of not just issues like this, but other security vulnerabilities that get fixed over time. There are a number of critical patches that have been released since V6 was released.
Bruce Chapman Monday, April 3, 2017 1:27 AM (link)

Comment Form

Only registered users may post comments.

NewsArchives


Aderson Oliveira (22)
Alec Whittington (11)
Alessandra Davies (3)
Alex Shirley (10)
Andrew Hoefling (3)
Andrew Nurse (30)
Andy Tryba (1)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (37)
Ben Schmidt (1)
Benjamin Hermann (25)
Benoit Sarton (9)
Beth Firebaugh (12)
Bill Walker (36)
Bob Kruger (5)
Bogdan Litescu (1)
Brian Dukes (2)
Brice Snow (1)
Bruce Chapman (20)
Bryan Andrews (1)
cathal connolly (55)
Charles Nurse (163)
Chris Hammond (213)
Chris Paterra (55)
Clint Patterson (108)
Cuong Dang (21)
Daniel Bartholomew (2)
Daniel Mettler (181)
Daniel Valadas (48)
Dave Buckner (2)
David Poindexter (12)
David Rodriguez (3)
Dennis Shiao (1)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (80)
Francisco Perez Andres (17)
Geoff Barlow (12)
George Alatrash (12)
Gifford Watkins (3)
Gilles Le Pigocher (3)
Ian Robinson (7)
Israel Martinez (17)
Jan Blomquist (2)
Jan Jonas (3)
Jaspreet Bhatia (1)
Jenni Merrifield (6)
Joe Brinkman (274)
John Mitchell (1)
Jon Henning (14)
Jonathan Sheely (4)
Jordan Coopersmith (1)
Joseph Craig (2)
Kan Ma (1)
Keivan Beigi (3)
Kelly Ford (4)
Ken Grierson (10)
Kevin Schreiner (6)
Leigh Pointer (31)
Lorraine Young (60)
Malik Khan (1)
Matt Rutledge (2)
Matthias Schlomann (16)
Mauricio Márquez (5)
Michael Doxsey (7)
Michael Tobisch (3)
Michael Washington (202)
Miguel Gatmaytan (3)
Mike Horton (19)
Mitchel Sellers (40)
Nathan Rover (3)
Navin V Nagiah (14)
Néstor Sánchez (31)
Nik Kalyani (14)
Oliver Hine (1)
Patricio F. Salinas (1)
Patrick Ryan (1)
Peter Donker (54)
Philip Beadle (135)
Philipp Becker (4)
Richard Dumas (22)
Robert J Collins (5)
Roger Selwyn (8)
Ruben Lopez (1)
Ryan Martinez (1)
Sacha Trauwaen (1)
Salar Golestanian (4)
Sanjay Mehrotra (9)
Scott McCulloch (1)
Scott S (11)
Scott Wilkinson (3)
Scott Willhite (97)
Sebastian Leupold (80)
Shaun Walker (237)
Shawn Mehaffie (17)
Stefan Cullmann (12)
Stefan Kamphuis (12)
Steve Fabian (31)
Steven Fisher (1)
Timo Breumelhof (24)
Tony Henrich (3)
Torsten Weggen (3)
Tycho de Waard (4)
Vicenç Masanas (27)
Vincent Nguyen (3)
Vitaly Kozadayev (6)
Will Morgenweck (40)
Will Strohl (180)
William Severance (5)

Content Layout

Subscribe to DNN Digest

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  

What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out