GHRake
07-24-2017, 10:18 PM
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.
#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 """ all over your forum?
#This is how you change " to the quote thingy in all posts:
UPDATE post SET pagetext = replace(pagetext, '"', '"');
#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:
update post set pagetext =
REPLACE(`pagetext`,TRIM(MID(`pagetext`,LOCATE('quo te 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_I NDEX(`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 (https://bitbucket.org/snippets/GH-Rake/bEyA8A/useful-vbulletin-sql-queries)
#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 """ all over your forum?
#This is how you change " to the quote thingy in all posts:
UPDATE post SET pagetext = replace(pagetext, '"', '"');
#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:
update post set pagetext =
REPLACE(`pagetext`,TRIM(MID(`pagetext`,LOCATE('quo te 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_I NDEX(`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 (https://bitbucket.org/snippets/GH-Rake/bEyA8A/useful-vbulletin-sql-queries)