Help with old accounts, getting hacked

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • dnikola
    New Member
    • Jan 2019
    • 5
    • 4.2.x

    [Forum] Help with old accounts, getting hacked

    Hello guys,

    i would to get some kind of help, more advice what should we do in situation (or you will if you were in our situation)

    - Few years ago our forum get hacked (that was situation when a lot of VB forums get hacked)
    - All users information's like usernames / passwords / email's were published on some forums
    - Some of our forum users (spammers) get that information and grab that list before it was down
    - In main time we have done reset of all user passwords, but problem is that a lot of users had same password on forum and email, so this guys can access emails and reset users passwords

    This is what we suspect that is ongoing situation, because each day we have account which last post was more than 5-6 + years ago and than account start to troll and make spam on some political hot topics and etc.

    What should we do, if you have some suggestion?
    Do we miss something in order to "protect" this old account?
    Is there any tool to monitor a detailed account changes with IP's or etc?
    Should we just deactivate account if they don't have post's more than x years?


    Thanks in advance
    Nikola
  • OrganForum
    Senior Member
    • Feb 2013
    • 131
    • 5.7.X

    #2
    I've found deleting inactive accounts is a good idea, but deleting their account will set the username displayed in their posts to Guest. If you wish to retain their information, create a usergroup Closed Account with no posting and messaging permissions and move the inactive accounts to it. Closing an account instead of deleting it allows you to restore it if the legit user someday returns.

    I also delete any accounts where the join date = the last activity date and where the dates are older than two years. I've found that some of these accounts lie dormant for years before coming back with spam. I suspect that such accounts are traded on the dark web.
    VB 5.7.2
    PHP 7.4
    MySQL 8.0.28

    Comment

    • dnikola
      New Member
      • Jan 2019
      • 5
      • 4.2.x

      #3
      Originally posted by OrganForum
      I've found deleting inactive accounts is a good idea, but deleting their account will set the username displayed in their posts to Guest. If you wish to retain their information, create a usergroup Closed Account with no posting and messaging permissions and move the inactive accounts to it. Closing an account instead of deleting it allows you to restore it if the legit user someday returns.

      I also delete any accounts where the join date = the last activity date and where the dates are older than two years. I've found that some of these accounts lie dormant for years before coming back with spam. I suspect that such accounts are traded on the dark web.

      Hi OrganForum thanks for your replay.

      Could you please tell me what is a best solution such kind of job?

      Some kind of plugin or manual via SQL query?
      VB version is 4.2.5

      Regards Nikola

      Comment

      • OrganForum
        Senior Member
        • Feb 2013
        • 131
        • 5.7.X

        #4
        This is the query I used to identify users that signed up but never returned
        Code:
        /*
        * list of Registered Users who joined over two years ago and never returned [LEFT][COLOR=#000000][FONT=Helvetica][SIZE=13px]* NOTE: IT IS ASSUMED THAT USERGROUPID 2 = REGISTERED USER. View the Usergroup table to confirm for your installation.[/SIZE][/FONT][/COLOR][/LEFT]
         */
        SELECT
        userid,
        usergroupid,
        username,
        FROM_UNIXTIME(joindate) AS joindate,
        FROM_UNIXTIME(lastactivity) AS lastactivity,
        DATE(FROM_UNIXTIME(lastvisit)) AS lastvisit
        FROM
        vb_user
        WHERE
        DATE(FROM_UNIXTIME(lastactivity)) = DATE(FROM_UNIXTIME(joindate))
        AND usergroupid = 2
        AND Posts = 0
        AND FROM_UNIXTIME(joindate) < DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
        ORDER BY joindate , username
        This query will delete them from the user table
        Code:
        /* Delete NoActivityAfterJoinUsers
        * Registered Users who joined and never returned
        * NOTE: IT IS ASSUMED THAT USERGROUPID 2 = REGISTERED USER.
        * View the Usergroup table to confirm for your installation.
        */
        DELETE FROM vb_user
        WHERE
        DATE(FROM_UNIXTIME(lastactivity)) = DATE(FROM_UNIXTIME(joindate))
        AND posts = 0
        AND usergroupid = 2
        AND FROM_UNIXTIME(joindate) < DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
        This has worked for me with VB 4.2.5 but I'm not sure deleting the user from the user table is really the best approach as it could break other table relationships. Again, I've not had a problem, but proceed with caution. It might be better to ban them, rather than delete them.

        Code:
        /* THIS CODE IS OFF THE TOP OF MY HEAD AND IS COMPLETELY UNTESTED.  PROCEED AT YOUR OWN RISK
        * [LEFT][COLOR=#000000][FONT=Helvetica][SIZE=13px]* NOTE: IT IS ASSUMED THAT USERGROUPID 2 = REGISTERED USER and USERGROUPID 8 = BANNED USERS.
         * View the Usergroup table to confirm for your installation.[/SIZE][/FONT][/COLOR][/LEFT]
         
          */
        UPDATE vb_user
        SET usergroupid = 8
        WHERE [LEFT][COLOR=#000000][FONT=Helvetica][SIZE=13px]DATE(FROM_UNIXTIME(lastactivity)) = DATE(FROM_UNIXTIME(joindate))[/SIZE][/FONT][/COLOR][/LEFT]
         
        [LEFT][COLOR=#000000][FONT=Helvetica][SIZE=13px]AND posts = 0[/SIZE][/FONT][/COLOR][/LEFT]
         
        [LEFT][COLOR=#000000][FONT=Helvetica][SIZE=13px]AND usergroupid = 2[/SIZE][/FONT][/COLOR][/LEFT]
         
        [LEFT][COLOR=#000000][FONT=Helvetica][SIZE=13px]AND FROM_UNIXTIME(joindate) < DATE_SUB(CURDATE(), INTERVAL 2 YEAR)[/SIZE][/FONT][/COLOR][/LEFT]
        Ideally, these scripts would lookup the usergroupid by title, but I'm unsure if what is stored in the usergroup table is language dependent, so I just looked them up and hardcoded them.

        IF YOU'RE UNSURE OF WHAT YOU'RE DOING, DO NOT RUN THESE SCRIPTS
        Last edited by OrganForum; Wed 16 Jan '19, 6:56am.
        VB 5.7.2
        PHP 7.4
        MySQL 8.0.28

        Comment

        • Wayne Luke
          vBulletin Technical Support Lead
          • Aug 2000
          • 73976

          #5
          You can delete inactive accounts in the AdminCP under Users -> Prune / Move Users. You do not need to run queries directly.

          You can also move them to a "quarantined" usergroup and restrict their access until they change their password and contact you. Or use a query to update their password.

          Using a query, you can change their password if their last visit date is more than XXX days ago.

          If you were to upgrade to vBulletin 5, users would have their passwords stored in a more secure manner. This won't help with passwords that were already exposed though. You can learn more about vBulletin 5 Password Handling in our article section.
          Last edited by Wayne Luke; Wed 16 Jan '19, 12:15pm.
          Translations provided by Google.

          Wayne Luke
          The Rabid Badger - a vBulletin Cloud demonstration site.
          vBulletin 5 API

          Comment


          • Wayne Luke
            Wayne Luke commented
            Editing a comment
            They also create broken user profiles since they only touch one of the user tables in the system. When in fact, a user profile is spread out over three different tables. However it isn't my database and I don't have to worry about things breaking in the future.

            Deleting users from the AdminCP also makes sure that Threads and posts are updated and extraneous information like Private Messages, Visitor Messages, and Photo Albums are deleted.

          • OrganForum
            OrganForum commented
            Editing a comment
            Yes, I mentioned the possibility of broken table relationships in my post #4; however, as these users signed up and never signed in again, they don't make much of an impact on the database. I've done this for two years without any problem, but your point is well taken and I suggested that banning might be a better option than deletion.
            Last edited by OrganForum; Thu 17 Jan '19, 4:44am.

          • dnikola
            dnikola commented
            Editing a comment
            Hi,

            thanks both Wayne Luke and OrganForum for your replays.
            It si not a problem to write down a sql function to delete users, if i could know what tables are using all info about user ID
            Could you please help me with that info?

            In any case thanks for your kind replay!
        • dnikola
          New Member
          • Jan 2019
          • 5
          • 4.2.x

          #6
          Originally posted by OrganForum
          This is the query I used to identify users that signed up but never returned
          Code:
          /*
          * list of Registered Users who joined over two years ago and never returned [LEFT][COLOR=#000000][FONT=Helvetica][SIZE=13px]* NOTE: IT IS ASSUMED THAT USERGROUPID 2 = REGISTERED USER. View the Usergroup table to confirm for your installation.[/SIZE][/FONT][/COLOR][/LEFT]
          */
          SELECT
          userid,
          usergroupid,
          username,
          FROM_UNIXTIME(joindate) AS joindate,
          FROM_UNIXTIME(lastactivity) AS lastactivity,
          DATE(FROM_UNIXTIME(lastvisit)) AS lastvisit
          FROM
          vb_user
          WHERE
          DATE(FROM_UNIXTIME(lastactivity)) = DATE(FROM_UNIXTIME(joindate))
          AND usergroupid = 2
          AND Posts = 0
          AND FROM_UNIXTIME(joindate) < DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
          ORDER BY joindate , username
          This query will delete them from the user table
          Code:
          /* Delete NoActivityAfterJoinUsers
          * Registered Users who joined and never returned
          * NOTE: IT IS ASSUMED THAT USERGROUPID 2 = REGISTERED USER.
          * View the Usergroup table to confirm for your installation.
          */
          DELETE FROM vb_user
          WHERE
          DATE(FROM_UNIXTIME(lastactivity)) = DATE(FROM_UNIXTIME(joindate))
          AND posts = 0
          AND usergroupid = 2
          AND FROM_UNIXTIME(joindate) < DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
          This has worked for me with VB 4.2.5 but I'm not sure deleting the user from the user table is really the best approach as it could break other table relationships. Again, I've not had a problem, but proceed with caution. It might be better to ban them, rather than delete them.

          Code:
          /* THIS CODE IS OFF THE TOP OF MY HEAD AND IS COMPLETELY UNTESTED. PROCEED AT YOUR OWN RISK
          * [LEFT][COLOR=#000000][FONT=Helvetica][SIZE=13px]* NOTE: IT IS ASSUMED THAT USERGROUPID 2 = REGISTERED USER and USERGROUPID 8 = BANNED USERS.
          * View the Usergroup table to confirm for your installation.[/SIZE][/FONT][/COLOR][/LEFT]
          
          */
          UPDATE vb_user
          SET usergroupid = 8
          WHERE [LEFT][COLOR=#000000][FONT=Helvetica][SIZE=13px]DATE(FROM_UNIXTIME(lastactivity)) = DATE(FROM_UNIXTIME(joindate))[/SIZE][/FONT][/COLOR][/LEFT]
          
          [LEFT][COLOR=#000000][FONT=Helvetica][SIZE=13px]AND posts = 0[/SIZE][/FONT][/COLOR][/LEFT]
          
          [LEFT][COLOR=#000000][FONT=Helvetica][SIZE=13px]AND usergroupid = 2[/SIZE][/FONT][/COLOR][/LEFT]
          
          [LEFT][COLOR=#000000][FONT=Helvetica][SIZE=13px]AND FROM_UNIXTIME(joindate) < DATE_SUB(CURDATE(), INTERVAL 2 YEAR)[/SIZE][/FONT][/COLOR][/LEFT]
          Ideally, these scripts would lookup the usergroupid by title, but I'm unsure if what is stored in the usergroup table is language dependent, so I just looked them up and hardcoded them.

          IF YOU'RE UNSURE OF WHAT YOU'RE DOING, DO NOT RUN THESE SCRIPTS


          Hi

          thanks for you kind replay!

          I will test, and let you know results

          Comment

          • dnikola
            New Member
            • Jan 2019
            • 5
            • 4.2.x

            #7
            Originally posted by Wayne Luke
            You can delete inactive accounts in the AdminCP under Users -> Prune / Move Users. You do not need to run queries directly.

            You can also move them to a "quarantined" usergroup and restrict their access until they change their password and contact you. Or use a query to update their password.

            Using a query, you can change their password if their last visit date is more than XXX days ago.

            If you were to upgrade to vBulletin 5, users would have their passwords stored in a more secure manner. This won't help with passwords that were already exposed though. You can learn more about vBulletin 5 Password Handling in our article section.

            Hi

            thanks for your replay!

            I just checked this part also, it is useful for me, but for second one i will need a query.

            I made a test with this query from OrganicFarm, but should there be any another table which need update after user delete with query?


            Thanks!

            Comment

            • Wayne Luke
              vBulletin Technical Support Lead
              • Aug 2000
              • 73976

              #8
              You would have to develop a list of USERIDs to delete then use them to delete records in the user, userfield, and usertextfield tables. You should also run a query against the thread and post tables to set those userids to 0 where they exist.
              Translations provided by Google.

              Wayne Luke
              The Rabid Badger - a vBulletin Cloud demonstration site.
              vBulletin 5 API

              Comment

              • dnikola
                New Member
                • Jan 2019
                • 5
                • 4.2.x

                #9
                Originally posted by Wayne Luke
                You would have to develop a list of USERIDs to delete then use them to delete records in the user, userfield, and usertextfield tables. You should also run a query against the thread and post tables to set those userids to 0 where they exist.
                Thanks for your kind replay.

                Setting to 0 it will show post author guest?

                Thanks

                Comment

                • Wayne Luke
                  vBulletin Technical Support Lead
                  • Aug 2000
                  • 73976

                  #10
                  It will change the usergroup to 0 which is the same as a guest. In vBulletin 4, it will retain the user's name on the post.
                  Translations provided by Google.

                  Wayne Luke
                  The Rabid Badger - a vBulletin Cloud demonstration site.
                  vBulletin 5 API

                  Comment

                  widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                  Working...