Some useful SQL

Need some custom code changes to the phpBB core simple enough that you feel doesn't require an extension? Then post your request here so that community members can provide some assistance.

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: NO OFFICIAL SUPPORT IS PROVIDED IN THIS SUB-FORUM
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6601
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Some useful SQL

Post by thecoalman »

This first one will allow you to review the changes before committing them. As always make sure you have backup of database.

Code: Select all

SELECT post_text, REGEXP_REPLACE(
	post_text,
	'What you are finding', 
	'What you are replacing it with'
) AS new_url FROM phpbb_posts  WHERE post_text REGEXP 'What you are finding';

This will change all links http/https with or without www to one route:

Code: Select all

UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
	post_text, 
	# What you are finding, no trailing slash. If you remove anysubfolder remove the slash before it.   
	'(http|https)://(www\\.)?anydomainname\\.com/anysubfolder)',
 	# What you are replacing it with, no trailing slash.
	'https://www.yourdomain.com/subfolder'
);

phpBB no longer uses f parameter for topic links. This will remove it from links stored in DB..

Code: Select all

UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
	post_text, 
	'(http|https)://(www\\.)?yourdomain\\.com/subfolder/viewtopic\\.php\\?f\\=([0-9]+)\\&amp\\;', 
	'https://www.yourdomain.com/subfolder/viewtopic.php?'
);

This will find broken bbcode, e.g. user removed end quote. The <t> is a post in plain text and since the brackets are not common character chances are it's broken or errant bbcode. You can refine with'%[q%','%[/q%', etc.

Code: Select all

SELECT post_id FROM phpbb_posts WHERE post_text LIKE '%<t>%' AND (post_text LIKE '%[%' OR post_text LIKE '%]%'); 
Go to bottom of results and find the link for export under query result operations. On next page switch to custom and select CSV, under "Format-specific options" remove the values for columns. Export it. Open results in text editor, on new line and end of line you can insert html for link. Read the manual. :D
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6601
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Some useful SQL

Post by thecoalman »

Giant word of warning about this, it's doesn't account for everything. Specifically with custom BBcodes, extensions like the media embed extension or even basic BBcodes. For example if someone wraps the bold tag around a URL it's going to leave the bold BBcode with empty content. If you want to remove a domain that is included in media extension, disabling it in ACP and re-parsing using CLI should allow you to use this SQL.

Use the SQL I posted at the top of the page to test. If you break something don't come crying to me, you've been warned. As always backup your DB.

If you are like me you have a lot of links from domains that are no longer going to original source. The user ends up at casino site, malware or who knows what. Identifying them is hard enough let alone removing them.

The following will replace most if not all occurrences of the various links auto generated by phpBB when posted as plain text, in [url] tags or [img] tags.

SET @domain_name = 'example.com'; This is really the only thing you need to edit. You only need the domain name and TLD. The pattern will match http/https, www/non-www and subdomains. I haven't tested it but if you use a subdomain here it should target URL's only with the subdomain.

You will also need to edit all occurrences of phpbb_posts if you are using different prefix for your tables.

Code: Select all

# Set the domain
SET @domain_name = 'example.com';

# Replacement text for links
SET @link_text_replacement = '**Link removed, it is no longer valid**';

# Replacement text for images
SET @image_text_replacement = '**Image removed, it is no longer valid**';

# Replacement text where domain name is just plain text without a link and has space before and after.
SET @plain_text_replacement = 'Example';

# Should match any common URL that includes the  domain.tld excluding ftp:// etc.
SET @url_pattern = CONCAT('(https?://(?:www\\.|[a-z0-9-]+\\.)?', @domain_name, '(/[^<]*)?)');

# Matches [img] bbcode nested in [url=].
UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
	post_text, 
	CONCAT('<URL url="', @url_pattern, '"><s>\\[url=', @url_pattern, '\\]</s><IMG src="', @url_pattern, '"><s>\\[img\\]</s>', @url_pattern, '<e>\\[/img\\]</e></IMG><e>\\[/url\\]</e></URL>'), 
	@image_text_replacement
);

# Matches [img] bbcode with long URL with shortened text, must go before link matching first because pattern below can match inner text.
UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
	post_text, 
	CONCAT('<IMG src="', @url_pattern, '"><s>\\[img\\]</s><URL url="', @url_pattern, '"><LINK_TEXT text="', @url_pattern, '">', @url_pattern, '</LINK_TEXT></URL><e>\\[/img\\]</e></IMG>'), 
	@image_text_replacement
);

# Matches [img] bbcode with URL that is not shortened. 
UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
	post_text, 
	CONCAT('<IMG src="', @url_pattern, '"><s>\\[img\\]</s><URL url="', @url_pattern, '">', @url_pattern, '</URL><e>\\[/img\\]</e></IMG>'), 
	@image_text_replacement
);

# Matches [url]  bbcode with either the URL or text used between tags. 
UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
	post_text, 
	CONCAT('<URL url="', @url_pattern, '"><s>\\[url\\]</s>(.*?)<e>\\[/url\\]</e></URL>'), 
	@link_text_replacement
);

# Matches [url=] bbcode with either the URL or text used between tags. 
UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
	post_text, 
	CONCAT('<URL url="', @url_pattern, '"><s>\\[url=', @url_pattern, '\\]</s>(.*?)<e>\\[/url\\]</e></URL>'), 
	@link_text_replacement
);

# Matches URL pasted as plain text that has been shortened
UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
	post_text, 
	CONCAT('<URL url="', @url_pattern, '"><LINK_TEXT text="', @url_pattern, '">', @url_pattern, '</LINK_TEXT></URL>'), 
	@link_text_replacement
);

# Matches URL pasted as plain text that has not been shortened
UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
	post_text, 
	CONCAT('<URL url="', @url_pattern, '">', @url_pattern, '</URL>'), 
	@link_text_replacement
);

# Matches  domain.tld where it should be plain text at this point. If there is any links that have been missed it will at least break the link. 
UPDATE phpbb_posts SET post_text = replace(post_text, @domain_name, @plain_text_replacement);
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6601
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Some useful SQL

Post by thecoalman »

The phpbb_topics_track table can get enormous over many years because most users never use the mark forums read link and it has a lot of records that can exist since you installed phpBB. Mine was up to almost 2 million rows The following SQL will mark topics read for everyone where the last post was more than a year ago. It will not unmark topics.

Code: Select all

DELETE FROM phpbb_topics_track WHERE mark_time< UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR));
DELETE FROM phpbb_forums_track WHERE mark_time< UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR));
UPDATE phpbb_users SET user_lastmark=UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) WHERE user_lastmark<UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND user_id != 1;
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6601
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Some useful SQL

Post by thecoalman »

Disable email notifications for a user. This will also disable the email contact if they have it set to yes.

123456 need to edited to the users ID

Code: Select all

SET @user_id = 123456;

UPDATE phpbb_user_notifications SET notify = 0 WHERE user_id = @user_id  AND method = 'notification.method.email'; 
UPDATE phpbb_users SET user_allow_viewemail =  0 WHERE user_id = @user_id; 
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6601
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Some useful SQL

Post by thecoalman »

If you have an older forum like mine and check the user_login_attempts column you are probably going to find it's at 99 for many older accounts. We have instances here on phpBB.com and other sites where spammers have successfully hijacked accounts. It's not an issue particular to phpBB, the usernames and passwords are already known.

See here for more information: https://haveibeenpwned.com/

The following will remove a users password that has no activity in 10 years where the registration date is also older than 10 years. They will need to to use "I forgot my password" link in the event they return. The one caveat is no indication to user it will not be possible to login without resetting the password. If you want to change the time edit both instances of 10 YEAR.

Code: Select all

UPDATE phpbb_users SET user_password = '0' WHERE user_regdate < UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 10 YEAR)) AND user_lastvisit < UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 10 YEAR)) AND user_id != 1;
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6601
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Some useful SQL

Post by thecoalman »

This will set all bots to hidden. I don't know if this makes any difference but first go to permissions tab, under Global Permissions heading select group permissions, select the bot group, check the misc tab to make sure "Can hide online status" is set to yes.

Code: Select all

UPDATE phpbb_users SET user_allow_viewonline = '0' WHERE user_type = 2 AND user_id != 1;
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6601
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Some useful SQL

Post by thecoalman »

This will set all links stored in posts, signatures, the website in profiles and private messages to https.

This shouldn't be much of an issue since most sites are https these days but just be aware if they aren't links/embedded images are going to break.

You will need to edit phpbb_ prefix if yours is different.

Code: Select all

UPDATE phpbb_posts SET post_text = replace(post_text, '="http://', '="https://');
UPDATE phpbb_privmsgs SET message_text = replace(message_text, '="http://', '="https://');
UPDATE phpbb_users SET user_sig = replace(user_sig, '="http://', '="https://');
UPDATE phpbb_posts SET post_text = replace(post_text, '">http://', '">https://');
UPDATE phpbb_privmsgs SET message_text = replace(message_text, '">http://', '">https://');
UPDATE phpbb_users SET user_sig = replace(user_sig, '">http://', '">https://');
UPDATE phpbb_profile_fields_data SET pf_phpbb_website = replace(pf_phpbb_website, 'http://', 'https://');
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
RomaamoR
Registered User
Posts: 313
Joined: Tue Feb 24, 2015 4:45 pm
Location: Ukraine
Name: Roman

Re: Some useful SQL

Post by RomaamoR »

In this query -

Code: Select all

UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
post_text,
'(http|https)://(www\\.)?yourdomain\\.com/subfolder/viewtopic\\.php\\?f\\=([0-9]+)\\&amp\\;',
'https://www.yourdomain.com/subfolder/viewtopic.php?'
);
www.yourdomain.com replace with my domain ?
User avatar
SpIdErPiGgY
Registered User
Posts: 330
Joined: Sun May 02, 2021 2:11 pm
Location: Erpe-Mere, Aalst, BE
Name: Andy Dm

Re: Some useful SQL

Post by SpIdErPiGgY »

RomaamoR wrote: Tue Dec 24, 2024 6:56 pm In this query -

Code: Select all

UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
post_text,
'(http|https)://(www\\.)?yourdomain\\.com/subfolder/viewtopic\\.php\\?f\\=([0-9]+)\\&amp\\;',
'https://www.yourdomain.com/subfolder/viewtopic.php?'
);
www.yourdomain.com replace with my domain ?
Yes
Image
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6601
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Some useful SQL

Post by thecoalman »

If it was seron.com and phpbb is installed in root:

Code: Select all

UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
post_text,
'(http|https)://(www\\.)?seron\\.com/viewtopic\\.php\\?f\\=([0-9]+)\\&amp\\;',
'https://www.seron.com/viewtopic.php?'
);
If phpbb was installed in seron.com/forum/

Code: Select all

UPDATE phpbb_posts SET post_text = REGEXP_REPLACE (
post_text,
'(http|https)://(www\\.)?seron\\.com/forum/viewtopic\\.php\\?f\\=([0-9]+)\\&amp\\;',
'https://www.seron.com/forum/viewtopic.php?'
);
I can't emphasize enough to test, this gives you side by side comparison before making any changes.

Code: Select all

SELECT post_text, REGEXP_REPLACE(
	post_text,
	'(http|https)://(www\\.)?seron\\.com/forum/viewtopic\\.php\\?f\\=([0-9]+)\\&amp\\;', 
	'https://www.seron.com/forum/viewtopic.php?'
) AS new_url FROM phpbb_posts  WHERE post_text REGEXP '(http|https)://(www\\.)?seron\\.com/forum/viewtopic\\.php\\?f\\=([0-9]+)\\&amp\\;'
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
RomaamoR
Registered User
Posts: 313
Joined: Tue Feb 24, 2015 4:45 pm
Location: Ukraine
Name: Roman

Re: Some useful SQL

Post by RomaamoR »

Yes, it is seron.com and it is installed in the root.
User avatar
Stoker 4.0
Registered User
Posts: 1594
Joined: Sun Feb 13, 2011 1:33 pm
Location: Funen, Denmark
Name: Ulrik Christensen

Re: Some useful SQL

Post by Stoker 4.0 »

thecoalman wrote: Wed Oct 23, 2024 1:01 pm The phpbb_topics_track table can get enormous over many years because most users never use the mark forums read link and it has a lot of records that can exist since you installed phpBB. Mine was up to almost 2 million rows The following SQL will mark topics read for everyone where the last post was more than a year ago. It will not unmark topics.

Code: Select all

DELETE FROM phpbb_topics_track WHERE mark_time< UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR));
DELETE FROM phpbb_forums_track WHERE mark_time< UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR));
UPDATE phpbb_users SET user_lastmark=UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) WHERE user_lastmark<UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND user_id != 1;
1.7 million on an 18 year old board.
Thanks ;)
~ The greatest achievement in life is to inspire ~
Regards Stoker
User avatar
Stoker 4.0
Registered User
Posts: 1594
Joined: Sun Feb 13, 2011 1:33 pm
Location: Funen, Denmark
Name: Ulrik Christensen

Re: Some useful SQL

Post by Stoker 4.0 »

thecoalman wrote: Fri Nov 08, 2024 11:48 am Disable email notifications for a user. This will also disable the email contact if they have it set to yes.

123456 need to edited to the users ID

Code: Select all

SET @user_id = 123456;

UPDATE phpbb_user_notifications SET notify = 0 WHERE user_id = @user_id  AND method = 'notification.method.email'; 
UPDATE phpbb_users SET user_allow_viewemail =  0 WHERE user_id = @user_id; 
I dont think this one works when a user has subscribed to a forum?
I ran this sql with the correct user ID but keep getting Undelivered Mail Returned to Sender notices
~ The greatest achievement in life is to inspire ~
Regards Stoker
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 6601
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.

Re: Some useful SQL

Post by thecoalman »

You can test it yoursel, click notification link and then settings. Note how many check you have for email. Run the query, board admin is usually user id 2.

Code: Select all

SET @user_id = 2;

UPDATE phpbb_user_notifications SET notify = 0 WHERE user_id = @user_id  AND method = 'notification.method.email'; 
The affected rows should be the same as amount of checks, recheck your notification settings and they should now be all unchecked for email.

The only reason I can think of you might still be getting emails is if they are stored in cache, purging the cache will fix that.
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
User avatar
halil16
Registered User
Posts: 1489
Joined: Fri Jul 24, 2020 11:30 pm
Location: Turkiye
Name: Halil

Re: Some useful SQL

Post by halil16 »

There are topics that appear in forums and similar topics but are not actually there. Clicking it does not yield any results. Are these deleted shadows? Is there a query to clean them?
Introducing Mobile Upgrade! *Make your phpBB board like an app! 📱
Hire me for your phpBB board. 🚩
"The day we'll need ideas more than possessions, we'll find the secret to true wealth." - Peyami Safa /peˈjɑːmi saˈfɑː/

Return to “phpBB Custom Coding”