facebook transparent twitter
Go Back  

SawFirst Forum

> SawFirst - About Your Virtual Presence > Websites

Websites Let us know about your website or any other website you find helpful, here.

Thread Tools
Old 12-31-2019, 02:40 AM
Pretty Girl's Avatar
Pretty Girl Pretty Girl is offline
Join Date: Jul 2019
Location: Manila
Posts: 3,116
Default Text Replace and other Useful SQL Queries Vbulletin

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, 'OLD', 'NEW'); 

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

To replace them with vbulletin quote tags you run this query:
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]+\]';  
Reply With Quote

queries, vbulletin
Thread Tools

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.