vBulletin hacked forums: Clean Up Time
Next Steps:
Step 5. Then if you know when the breach happened figure out how many days ago it was
Step 6. Then in admincp > usergroup manager edit all usergroups particularly admin and moderators usergroups and set password expiry to around numbers of days just under the breach time i.e. if breach was 8 days ago, set password expiry to 5 days. This will force all members to change their password if they haven’t done so in the past 5 days. Then around 4 days later set password expiry to around 14-30 days to catch the less frequently visiting members. Then a month later set expiry to 30-90 days to force folks to change password every 1-3 months for maybe next 6 months citing security as the reason. Then change password expiry to something more reasonable 180-365 days. If you are restoring MySQL database from a clean backup (Step 9), you may need to redo this step on the restored MySQL database instance of your vB forum.
Password Reset Status
Some MySQL queries you can run to determine the current state of password resets on your member base’s vB user table. Replace mysqlusername with your database’s MySQL username and when prompted enter your MySQL username’s password and replace DBNAME with your vB database name.
1. Find usergroupids with admin permissions. If you have table prefix set in vB config.php also need to change FROM usergroup to FROM yourprefixusergroup.
mysql -u mysqlusername -p -t -e "SELECT usergroupid, adminpermissions FROM usergroup WHERE adminpermissions <> 0;" DBNAME
resulting output
+-------------+------------------+ | usergroupid | adminpermissions | +-------------+------------------+ | 5 | 1 | | 6 | 3 | +-------------+------------------+
This reveals usergroupids 5 and 6
2. Next if you have determined the date your forum was initially hacked, you can look up how many of your users have already reset their passwords and how many have not.
Below example assumes hack date was September 22, 2013.
So first look at how many people in usergroupids 5 and 6 (your admin and mods) have reset their passwords after September 22, 2013. For all MySQL queries below remember to change the date in example (2013-09-22) to your specific date and change IN(5,6) to your usergroupids which have admin permissions. If you have table prefix set in vB config.php also need to change FROM user to FROM yourprefixuser.
mysql -N -e "SELECT userid, usergroupid, username, PASSWORD , passworddate, email FROM user WHERE usergroupid IN(5,6) AND passworddate > '2013-09-22' ORDER BY passworddate ASC;" DBNAME | grep -v '+-' | wc -l 13
reveals 13 admins/mods have reset their password after September 22, 2013
To see the exact details, remove | wc -l from the line
mysql -N -e "SELECT userid, usergroupid, username, PASSWORD , passworddate, email FROM user WHERE usergroupid IN(5,6) AND passworddate > '2013-09-22' ORDER BY passworddate ASC;" DBNAME
3. Next look at how many users in usergroupid 5 and 6 have yet to reset their before (before September 22, 2013)
mysql -N -e "SELECT userid, usergroupid, username, PASSWORD , passworddate, email FROM user WHERE usergroupid IN(5,6) AND passworddate < '2013-09-22' ORDER BY passworddate ASC;" DBNAME | grep -v '+-' | wc -l 3
reveals 3 users have yet to still reset their passwords
To see the exact details, remove | wc -l from the line
mysql -N -e "SELECT userid, usergroupid, username, PASSWORD , passworddate, email FROM user WHERE usergroupid IN(5,6) AND passworddate < '2013-09-22' ORDER BY passworddate ASC;" DBNAME | grep -v '+-'
4. Next find out how many of your users that are not in usergroupids 5 or 6, have not reset their passwords before September 22, 2013
mysql -N -e "SELECT userid, usergroupid, username, PASSWORD , passworddate, email FROM user WHERE usergroupid NOT IN(5,6) AND passworddate < '2013-09-22' ORDER BY passworddate ASC;" DBNAME | grep -v '+-' | wc -l 118228
reveals 118,228 users have yet to reset their passwords
Then to see how many users have reset their passwords after September 22, 2013 and not in usergroupids 5 or 6
mysql -N -e "SELECT userid, usergroupid, username, PASSWORD , passworddate, email FROM user WHERE usergroupid NOT IN(5,6) AND passworddate > '2013-09-22' ORDER BY passworddate ASC;" DBNAME | grep -v '+-' | wc -l 3066
reveals 3,066 users not in usergroupid 5 or 6 have reset their passwords after September 22, 2013
