How to Remove Spam Users from a Site Manually

DNN > How To
IMPORTANT NOTE:  This method should only be used on sites that are not supposed to have real users signing up. It doesn't have a way to detect which users are real and which are fake.  By default, it will only leave the initial 'admin' account created by DNN.
 
Requirements:  You can follow most of the instructions in this article on shared hosting with little to no experience.  The PowerShell script to remove folders requires remote desktop access to the server.  If you don't have remote desktop, let a support technician know that you'd like this script run (and which portal ID to run it on).  The time the script takes to complete varies based on the number of users folders, it often takes several hours.
 
Versions:  One of the changes between DNN 4 and 5 was the change to Soft Delete in DNN 5.  This method will only work on DNN 5+ sites, as previous versions do not include the Soft Delete method.  Some versions of DNN 5 may not include the function to remove the deleted users, these sites will need upgraded to continue.
 
Background: Due to an exploit in DNN, bots were able to query the site with a special string included in the URL to programatically create users.  The bot would then edit the profile, inserting hyperlinks to sites that had paid the bots operator for backlinks.  These backlinks gave those sites an advantage for SEO purposes.  Creating the user modifies the user tables in DNN, and editing the profile would create folders for that users profile.  Since the bots never uploaded any files, these profile folders were empty.  Due to the structure of DNN, large numbers of users create a lot of problems.  Most of the problems fell into a few categories, but the affects are far reaching.
  • Inability to upgrade to any version that changes how users are stored
  • Admin pages don't load because they attempt to query the users profile tables
  • SQL Queries, including scheduled DNN tasks, time out
  • DNN File manager not functioning properly
  • Site frequently loses connection to database - showing "Page unavailable" or "Error Connecting to Database"
Additionally, the excess users and registrations tax the servers heavily, causing more problems elsewhere.
 
Before you begin:   Make sure you've stopped the registration spam bot from adding more spam users.  Most of the bots call the register function through the URL, but some will use a virtual browser.  Here is an article about stopping registration spam. Here is a short summary of that article, but visit it if you need more detail.
 
Stopping function call:
You'll need to add request filtering to your web.config file.  This blackholes any request that attempts to call the function.  This requires at least IIS 7, so if you are on an older server you might need to move.  The support team can handle the move for you.  Here's the section you'd need to add to the web.config:
 
<system.webServer>
  <security>
    <requestFiltering>
         <denyQueryStringSequences>
              <add sequence="ctl=Register" />
              <!--added by PowerDNN due to Registration exploit-->
        </denyQueryStringSequences>
    </requestFiltering>
  </security>
</system.webServer>   
The web.config file is located in C:\inetpub\vhosts\<sitename>\httpdocs\
Make sure not to add duplicate <tags>, or the site will crash. If the site crashes please remove the code and contact Support.
 
 
Stopping virtual browser:
You'll need to add reCaptcha to your site, bots do not currently have the ability to analyze visual patterns this complex.  They may develop this in the future, but it won't be a viable option for this type of spamming for some years, as it is computationally expensive.
 
 
These methods together should be enough to stop all spam registration, but it's always possible that a new way will pop up.  When added and functional, we've confirmed that they stop all known registration bots.  If they aren't working, it's most likely that they aren't fully implemented - you should have the support team take a look.
 
Now we are ready to begin cleaning up the sites users.  
 
 
Step 1:  Soft Deleting users in the database
 
The following query will mark all users that do not have administrative roles as deleted in the database.  You'll need to replace the bolded DatabaseName with the name of your database.
  1. Open Microsoft SQL Server Management Studio, and start a New Query
  2. Enter the following text, replacing DatabaseName with the name of the database for the website in question.  
    use DatabaseName
    UPDATE UserPortals
        SET IsDeleted='True'
        WHERE 
          UserID NOT IN( 
            SELECT UserID FROM UserRoles 
            WHERE RoleID IN(SELECT RoleID FROM Roles WHERE RoleName LIKE '%Admin%'
    		AND Status='1')
    		)
    
    
    GO
    
    UPDATE Users
    	SET IsDeleted = 'True'
    	WHERE
    		UserID NOT IN(
    		SELECT UserID from UserPortals
    		WHERE IsDeleted='False')
    		AND IsSuperUser != 'False'
     
  3. Once entered, Execute the Query by pressing the red exclamation point or hitting F5
  4. This will run through the database and mark each user who wasn't specified as deleted.  They'll still show up in the user list, but their name will be crossed out.  This is a pretty safe query to adjust, as we can just set them back to IsDeleted='False' if the results don't match what was expected.
    1. Here's a query to look at what you're keeping
      use DatabaseName
      SELECT * From Users
      WHERE IsDeleted='False'
    2. And if you'd like to undo the soft deletion, you'd use the following query to mark all the users as not being deleted.
      use DatabaseName
      UPDATE Users
      SET IsDeleted='False'
      
      GO
      
      UPDATE UserPortals
      SET IsDeleted='False'
       
 
 
Advanced SQL Selection:
 
While our support team does not perform advanced selection for you - here are some sample queries that can be used to retain specific users at your discretion. 
  1. Open Microsoft SQL Server Management Studio, and start a New Query
  2. Enter the following text, replacing databasename with the name of the database for the website in question.  Replace rest of the bolded words with the appropriate pieces of information to restrict the deletion. 
    use DatabaseName
    UPDATE UserPortals
        SET IsDeleted='True'
        WHERE 
          UserID != '2'
          AND UserID !='OtherUserID'
          AND PortalId = 'The ID # of the portal you want to target for removal'
          AND CreatedDate >= 'The date fake users started registering, Year-Month-Day'
          AND UserID NOT IN( 
            SELECT UserID FROM Users 
            WHERE LOWER(Username) = LOWER('username')
          )
  3. Once entered, Execute the Query by pressing the red exclamation point or hitting F5
  4. This will run through the database and mark each user who wasn't specified as deleted.  They'll still show up in the user list, but their name will be crossed out.  This is a pretty safe query to adjust, as we can just set them back to IsDeleted='False' if the results don't match what was expected.
About this SQL Query: 
  • use databasename:   This tells SQL which database to run the query on.  replace databasename with the name of your database.
  • UPDATE UserPortals:  UserPortals is the master table of users that belong to a portal (all users that are not SuperUsers)
  • SET IsDeleted='True':  This "Soft Deletes" the any user selected by the query
  • WHERE ... :  Sets the conditions for selection
  • UserID != '2' :  Explicitly excludes the default DNN "admin" account.  Not necessary, but many people like to use their admin account.
  • AND ... : Allows you to have multiple conditions, you'll need one before each new condition.  You can have as many of each condition as you want, except for CreatedDate (see Advanced Section).
  • UserID !='otherUserID' : By replacing otherUserID with an ID number, we can save another admin accounts that we know that ID for.  You can add as many of these as you want to save multiple users.
  • AND PortalId='###':  This restricts the deletion to only a single portal.  Or if you add multiple of these, the portals you select.  You can find the Portal ID in Host > Site Management or Host > Portals, depending on your version.
  • AND CreatedDate >= 'date':  This restricts the deletion to only users after a certain date.  If you don't care about when they were created, just remove this line.
  • AND UserID NOT IN(...):  This looks a bit more complicated, but what it's doing is looking at another table to compare the name you put in with the name of each user before it marks it as deleted.  By doing this, you can specify a user to not delete by name.  For example, if you have an admin named "John" and can't get into the site to bring up the ID for the other criteria, you can put "john" into the username spot here to make sure that user doesn't get deleted.
 
Some versions of SQL server will only automatically change one date to the date data format per query.  In these cases, if you want to use a date range you will need to use the following instead:
WHERE convert(date, CreatedOnDate) = convert(date,'20141001')
For example, if your database is named DNN_Database and wanted to have it select all users created on portal '6' between June 1st 2014 and August 15th 2014, except for the default DNN admin, and another two users named 'Jon' and 'Carla' , you could use the following query:
 
use DNN_Database
UPDATE UserPortals
    SET IsDeleted='True'
    WHERE 
        convert(date, CreatedOnDate) >= convert(date, '20140601')
        AND PortalID = '6'
        AND UserId = '2'
        AND UserID NOT IN(
            SELECT UserId FROM Users
            WHERE Lower(Username) = Lower('jon')
            )
        AND UserID NOT IN(
            SELECT UserId FROM Users
            WHERE Lower(Username) = Lower('CaRlA')
            )
        AND convert(date, CreatedOnDate) <= convert(date, '20140815')
 
 
 
 
 
Step 2:  Fully removing the "Deleted" users
 
Now that the extra users are set to 'deleted', all we need to do is use DNN's built in functions to remove the users from the installation.  DNN versions below 7.3 do not remove the folders from the file system, so we need to take a few extra steps to delete them.  If the folders aren't deleted the site will still have trouble with file manager and admin menus. 
 
IMPORTANT NOTE:  There is a bug in 7.2.2 that can cause extra folders to be removed when removing users.  Take a backup first if the site is using 7.2.2
 
To remove users, just go to Admin > Users > Remove Deleted Users.  It may time out several times depending on the number of users and the load on the server.  This can cause some stress on the server, and you might notice the site being slow while it is running. 
 
 
Step 3:  Clean up the extra folders in the file system
 
With the users removed, we can remove the folders that their profiles created.  If we skip this step, any time DNN tries to re-sync the files through the file manager it will break the file manager.  Additionally, it makes backing up the site take a long time, as windows must index the folders whenever it transfers the files.  
 
  1. Create a PowerShell script in C:\temp\ named "Find-Or-Remove-Empty-Dirs.ps1".
  2. Open the file in PowerShell ISE or Notepad++
  3. Copy the following script into the file and save it
    <#
    .SYNOPSIS
    Svendsen Tech's generic script for removing empty directories from a
    directory tree structure.
    
    Finds or removes/deletes empty directories recursively from the drive or
    directory you specify.
    
    You will need to use the -VerifyNoEmpty parameter or multiple
    runs to get rid of nested empty directories. See the -VerifyNoEmpty parameter's
    description for further details.
    
    This isn't the most efficient approach as the ones I can think of seem to
    increase the script's complexity considerably. It should be useful for a
    multitude of use cases.
    
    Author: Joakim Svendsen
    
    .PARAMETER Path
    Required. Base root path to iterate recursively.
    .PARAMETER Find
    Default behaviour where it just prints. Overrides -Remove if both are specified.
    .PARAMETER Remove
    Removes all empty dirs (as in actually deletes them with Remove-Item).
    .PARAMETER VerifyNoEmpty
    Makes the script run until no empty directories are found. This is in order
    to handle nested empty directories, as in a directory that currently only
    contains an empty directory would be empty after the first run/iteration and
    need to be remove in a subsequent run. Specifying this parameter causes the
    script to run until it's done a complete run without finding a single empty
    directory. This might be time-consuming depending on the size of the directory
    tree structure.
    
    If there is an error while deleting a directory, it will not run again, to
    avoid an infinite loop.
    #>
    
    param(
        [Parameter(Mandatory=$true)][string] $Path,
        [switch] $Find,
        [switch] $Remove,
        [switch] $VerifyNoEmpty
        )
    
    Set-StrictMode -Version 2.0
    $ErrorLog   = 'remove-empty-dirs-error.log'
    
    ######## START OF FUNCTIONS ########
    
    function Iterate-And-Remove-Empty-Dirs {
        
        $FoundEmpty = $false
        
        Write-Host "Iterating '$Path'"
        
        Get-ChildItem -Force -Recurse -Path $Path -ErrorAction SilentlyContinue | Where-Object { $_.PSIsContainer } | ForEach-Object {
            
            if ($Find -or -not $Remove) {
                
                if (-not (Get-ChildItem -Force $_.FullName)) {
                    
                    # Directory should be empty
                    $_.FullName + ' is empty'
                    
                }
                
            }
            
            # This is the dangerous part
            elseif ($Remove) {
                
                if (-not (Get-ChildItem -Force $_.FullName)) {
                    
                    $FoundEmpty = $true
                    
                    # Directory should be empty
                    Remove-Item -Force $_.FullName
                    
                    if (-not $?) {
                        
                        Write-Host -ForegroundColor Red "Error: $(Get-Date): Unable to delete $($_.FullName): $($Error[0].ToString))"
                        "Error: $(Get-Date): Unable to delete $($_.FullName): $($Error[0].ToString))" | Out-File -Append $ErrorLog
                        
                        $FoundEmpty = $false # avoid infinite loop
                        
                    }
                    
                    else {
                        
                        Write-Host -ForegroundColor Green "$(Get-Date): Successfully deleted the empty folder: $($_.FullName)"
                        
                    }
                    
                }
                
            }
            
        } # end of ForEach-Object
        
        $FoundEmpty
        
    } # end of function Iterate-And-Remove-Empty-Dirs
    
    ######## END OF FUNCTIONS ########
    
    if (-not (Test-Path -Path $Path -PathType Container)) {
        
        "The specified path does not exist. Exiting with code 1."
        exit 1
        
    }
    
    if ($Remove -and $VerifyNoEmpty) {
        
        $Counter = 0
        
        while (($OutsideFoundEmpty = Iterate-And-Remove-Empty-Dirs) -eq $true) {
            
            $Counter++
            Write-Host -ForegroundColor Yellow "-VerifyNoEmpty specified. Found empty dirs on run no ${Counter}. Starting next run."
            
        }
        
        $Counter++
        
        Write-Host "Made $Counter runs in total"
        
    }
    
    else {
        
        Iterate-And-Remove-Empty-Dirs
        
    }
    The script comes from here, but is included in the article just in case that site goes down.
  4. Save the script and exit
  5. Open PowerShell and run the following command,
    Set-ExecutionPolicy
  6. When prompted, enter the following
    RemoteSigned
    y
     
  7. Now run these commands, changing <SiteName> to the site you are working on and <PortalNumber> to the number of the portal that the users are registered to
    cd C:\temp\
    .\Find-Or-Remove-Empty-Dirs.ps1 -Path C:\inetpub\vhosts\<SiteName>\httpdocs\Portals\<PortalNumber>\Users -Remove -VerifyNoEmpty
  8. After the script has run successfully, switch powershell back to Restricted mode by using the following commands:
    Set-ExecutionPolicy
    Restricted
    y
     
Step 4:  Cleaning up the folders table in the database
 
The last thing we'll need clean up is the dbo.Folders table in the database.  This table keeps a record of the folder structure of the entire site, along with some extra information about the folder.  When DNN creates a folder, it records the user that created it.  We can use this to purge all folders created by users that no longer exist.  
If the folders table is cleared and then regenerated by a file re-synchronization, the system displays that the user who initiated that action created the folder.   In this case, you'll need to delete all users folders from the database and Re-synchronize.
 
Method 1:  Deleting all folders table entries made by a non-existent user
 
  1. Open Microsoft SQL Server Management Studio, and start a New Query
  2. Enter the following text, replacing <DatabaseName> with the name of the database you are working with
    use DatabaseName
    DELETE FROM dbo.Folders
    WHERE 
    	CreatedByUserID NOT IN(
    	SELECT UserID FROM dbo.Users
    	)
    	AND CreatedByUserID != '-1'
            AND CreatedByUserID != ''
            AND CreatedByUserID != 'NULL'
    	AND FolderPath != ''
  3. Recycle the Application pool, references to folders created by users that no longer exist have been removed.
 
Method 2:  Delete all user folder entries and then re-synchronize 
  1. Open Microsoft SQL Server Management Studio, and start a New Query
  2. Enter the following text, replacing <DatabaseName> with the name of the database you are working with
    use DatabaseName
    DELETE FROM dbo.Folders
        WHERE FolderPath LIKE '%users/%'
    Execute the query by pressing the red exclamation point icon, or hitting F5.
  3. Recycle the application pool for the site, all users folder references are now removed
  4. Go to Host > File Manager and Re-synchronize recursively
  5. Go to Admin > File Manager and Re-synchronize recursively on each portal you removed users from
 
The spam users and all the references to them in core DNN should now be removed, as well as the folders they have created in the file system.  If you have any questions, or if you would like assistance, please let one of the members of our support team know!

Feedback

Add Feedback
This is a great step-by-step instruction on how to remove spam users and the empty folders because I unknowingly had over 22k spam users inserted over a span of about 3 months. I searched around the DNN forums and read a few posts regarding this but nothing comprehensive or detailed. As I was writing the subject for the ticket the topic appeared in the related section. It was perfect! However Step 3 I did not do. I had to have support get involved because we are on shared hosting so I did not execute the shell script. Jon called and then took care of that personally - he was already running the script as we spoke. Then I took over at step 4 with no issues. What can you take away from this post? Well -notice the related topics when you write up a tickets - you might be able to handle it on your own and learn some SQL in the process. <br /> <br />Best Regards, <br /> <br />Kyle Hagel
Bryan Miller (October 2, 2014 at 6:40 PM)

Add Feedback