Useful SQL Queries

[GHRake]

Here is just a list of useful mysql queries I've saved over the years and I thought others might find them useful as well. Warning: If you aren't experienced with SQL and modifying databases you must be extremely careful what you do and always make a backup first.

PHP Code:

#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; 

And this one is a doozy. Some old imported posts include this whacky quote tag:
[quote author=Sleinzel link=topic=73.msg304#msg304 date=1337956060]

To replace them with vbulletin quote tags you run this query:

PHP Code:

update post set pagetext =
REPLACE(`pagetext`,TRIM(MID(`pagetext`,LOCATE('quote author=', `pagetext`),IF(LOCATE(' ',`pagetext`,LOCATE('date=',`pagetext`))>0,LOCATE(']',`pagetext`,LOCATE('date=',`pagetext`)+5),LOCATE('date=',`pagetext`)+15)-LOCATE('quote author=', `pagetext`))),TRIM(CONCAT('quote=',MID(SUBSTRING_INDEX(`pagetext`,'link=',1),LOCATE('quote author=', `pagetext`)+13))))
WHERE `pagetext` REGEXP '\[quote author=[A-Za-z0-9 ]+ link=topic=[0-9]+.msg[0-9]+#msg[0-9]+ date=[0-9]+\]'; 

For the latest updates to my list you can visit my bitbucket snippet page