#guidedhacking.com

#Remove logged IP Addresses for a certain userid
UPDATE ipdata set ip='' WHERE userid = 123456;
UPDATE ipdata set altip='' WHERE userid = 123456;
UPDATE post set ipaddress='' WHERE userid = 123456;

#Force password reset on next login for all users
update user set passworddate='2000-01-01';
update usergroup set passwordexpires = '3650';

#Migrated or upgraded vBulletin and some users dont have updated stronger password salts/hashes?
#Find those users with short Password salts:
SELECT userid, username, PASSWORD , salt FROM  user WHERE length(salt) < 10;

#Force users with less secure salts to reset password and update encryption on next login:
update user set passworddate='2000-01-01' WHERE length(salt) < 10;
update usergroup set passwordexpires = '3650';

#Find users that havent updated their passwords since the above query
#Use this to keep track of how many people have updated:
#Best solution would be to create a seperate usergroup, move them into it and then
#Move them out when theyve changed their password
select userid, username, passworddate from user where passworddate = '2000-01-01';

#Find users that havent signed in recently:
select userid, username, lastvisit from user where lastvisit < UNIX_TIMESTAMP('2017-01-01')

#Alternatively find users that have signed in recently:
select userid, username, lastvisit from user where lastvisit > UNIX_TIMESTAMP('2017-01-01')

#Unsubscribe all users from all threads:
truncate subscribethread;

#Find all posts containing certain text:
SELECT *  FROM post WHERE pagetext LIKE '%viagra%'

#Replace text in all posts:
UPDATE post SET pagetext = replace(pagetext, 'Microsoft', 'MicroShit');

#Board migration left lots of "&quot;" all over your forum? 
#This is how you change &quot; to the quote thingy in all posts:
UPDATE post SET pagetext = replace(pagetext, '&quot;', '"');

#Replace all old quote tags with correct vbulletin quote tags:
SELECT *  FROM post WHERE pagetext LIKE '%[quote author=%'
UPDATE post SET pagetext = replace(pagetext, 'quote author', 'quote');

#Find all attachments by specific user:
SELECT *  FROM attachment WHERE userid = 3746;

#Remove and disable thread ratings:
truncate threadrate;
UPDATE forum SET options = options - 2048 WHERE (options & 2048)

#Select all threads from forum
SELECT * FROM thread where forumid = 41;

#Change thread prefix for all threads in forum:
UPDATE thread SET prefixid = 'Help' WHERE forumid = 41;

#Close all threads in a forum
UPDATE thread SET open = '0' WHERE forumid = 33;

#Recently enabled thread titles to propogate to all replies but want to make it retroactive?
#Make all post have same title as thread prepended with "re: "
UPDATE post
LEFT JOIN thread
ON post.threadid = thread.threadid
SET post.title = concat('Re: ',thread.title)
WHERE post.title = '';

#Disable "Who Read This Thread" on all forums:
UPDATE forum SET options = options - 1048576 WHERE (options & 1048576);

#Delete all soft deleted posts
delete from post where visible = 2;