Mass "Safe" Post Deletes - SQL Code

Get help with installation and running phpBB 3.3.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
Tony Berry
Registered User
Posts: 19
Joined: Wed Dec 28, 2005 10:29 pm
Location: Lafayette, LA

Mass "Safe" Post Deletes - SQL Code

Post by Tony Berry »

Good afternoon fellow phpBBers.

Spammers/spambots somehow got into my forum and posted 20K messages as 20K users. I've since patched the hold. Using the phpBB GUI to delete the users and all their posts is/would be insanely time consuming. I could easily write a SQL query to run against the database directly, but I don't know what else the GUI is doing when I tell it to delete a user and all that user's posts. I don't want to carry around the baggage of orphaned database records by using a hammer to remove the offending users/posts.

The MCP only allows 50 posts at a time to be selected for deletion. Users I can do 999 at a time.

What would be a safe SQL statement to run to basically delete a user by user_id and all posts related to that user_id?

Thanks all.
Tony Berry
Lafayette, LA
User avatar
Kailey
Community Team Leader
Community Team Leader
Posts: 3730
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Snay
Contact:

Re: Mass "Safe" Post Deletes - SQL Code

Post by Kailey »

It might be better to request some custom coding or an extension. The user_delete() function performs operations on the following tables:
  • USERS_TABLE
  • USER_GROUP_TABLE
  • TOPICS_WATCH_TABLE
  • FORUMS_WATCH_TABLE
  • ACL_USERS_TABLE
  • TOPICS_TRACK_TABLE
  • TOPICS_POSTED_TABLE
  • FORUMS_TRACK_TABLE
  • PROFILE_FIELDS_DATA_TABLE
  • MODERATOR_CACHE_TABLE
  • DRAFTS_TABLE
  • BOOKMARKS_TABLE
  • SESSIONS_KEYS_TABLE
  • PRIVMSGS_FOLDER_TABLE
  • PRIVMSGS_RULES_TABLE
Kailey Snay - Community Team Leader
Knowledge Base | Documentation | Community rules

If you have any questions about the rules/customs of this website, feel free to send me a PM.
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: Mass "Safe" Post Deletes - SQL Code

Post by 3Di »

Kailey wrote: Wed Dec 01, 2021 11:43 pm ... The user_delete() function performs operations on the following tables:
  • USERS_TABLE
  • etc...
Well, not only.
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: Mass "Safe" Post Deletes - SQL Code

Post by 3Di »

Tony Berry wrote: Wed Dec 01, 2021 11:27 pm ...
What would be a safe SQL statement to run to basically delete a user by user_id and all posts related to that user_id?
...
https://phpbbstudio.com/viewtopic.php?f=11&t=372
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
Tony Berry
Registered User
Posts: 19
Joined: Wed Dec 28, 2005 10:29 pm
Location: Lafayette, LA

Re: Mass "Safe" Post Deletes - SQL Code

Post by Tony Berry »

Thanks all for responding.

Thanks to Kailey for pointing out the specific function being called. I think 3Di's solution is exactly what I was looking for! Using an array to feed the function. Thanks 3Di... and I absolutely adore your avatar. Huge, old school, fan of the Commodore. I have amassed an extensive collection of old Commodore's, most still in OEM retail boxes. I also have a few C-65 components. ;)
Tony Berry
Lafayette, LA
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: Mass "Safe" Post Deletes - SQL Code

Post by 3Di »

Tony Berry wrote: Thu Dec 02, 2021 5:17 pm Thanks 3Di... and I absolutely adore your avatar. Huge, old school, fan of the Commodore. I have amassed an extensive collection of old Commodore's, most still in OEM retail boxes. I also have a few C-65 components.
No probs. I was thinking about an array of example 5.000 IDs... perhaps you may want to chunk that array into arrays and send them to the loop so to avoid any kind of issues. See array_chunk in this case, any questions? Post at our site right in the devoted topic, I will be more than glad to assist you.
Tony Berry wrote: Thu Dec 02, 2021 5:17 pm I also have a few C-65 components.
I think you mean c-64 isn't? ;) I do still have vic-20 and spectrum and what not... LOL.
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
Tony Berry
Registered User
Posts: 19
Joined: Wed Dec 28, 2005 10:29 pm
Location: Lafayette, LA

Re: Mass "Safe" Post Deletes - SQL Code

Post by Tony Berry »

I carefully picked a specific user_id, verified existence (including posts) and entered the user_id number into the $user_ids variable...

Parse error: syntax error, unexpected '}' in mass_user_delete.php on line 16

I don't see any open [],{},() brackets or unterminated lines. I'll continue looking though. I'll even retype them to ensure the code cut and paste didn't mess with something. It's a pretty simple PHP code block.

There was a C-65 concept. VERY few parts exist. It was to be the successor to the C-64. My first PC was a C-64. I have several C-64's in original boxes, VIC-20's, C-16's, Plus4's, C-128's, 1540's, 1541's, SFD-1001's, 1531's, VIC-1670's, etc., etc.
Last edited by Tony Berry on Thu Dec 02, 2021 7:59 pm, edited 1 time in total.
Tony Berry
Lafayette, LA
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: Mass "Safe" Post Deletes - SQL Code

Post by 3Di »

Fixed that code, I wrote it on the fly and a semicolon was missing. Sorry. :|
Please try again. Thanks.

BTW, wow... never heard of a c-65, very interesting. 8-)
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
User avatar
thecoalman
Community Team Member
Community Team Member
Posts: 5871
Joined: Wed Dec 22, 2004 3:52 am
Location: Pennsylvania, U.S.A.
Contact:

Re: Mass "Safe" Post Deletes - SQL Code

Post by thecoalman »

Tony Berry wrote: Wed Dec 01, 2021 11:27 pm The MCP only allows 50 posts at a time to be selected for deletion. Users I can do 999 at a time.
This is based on the configuration for Topics per page: under post settings. You can either change it temporarily which will affect entire forum or can edit includes/mcp/mcp_forum.php which will only affect the MCP. Around line 116 find:

Code: Select all

	$topics_per_page = ($forum_info['forum_topics_per_page']) ? $forum_info['forum_topics_per_page'] : $config['topics_per_page'];
Replace with:

Code: Select all

	// Mod - Set Custom number of topics to display in MCP
	$topics_per_page = 300;
The 300 value can be any number but be careful about making it too high because it will cause timeouts if it's too high.
“Results! Why, man, I have gotten a lot of results! I have found several thousand things that won’t work.”

Attributed - Thomas Edison
Tony Berry
Registered User
Posts: 19
Joined: Wed Dec 28, 2005 10:29 pm
Location: Lafayette, LA

Re: Mass "Safe" Post Deletes - SQL Code

Post by Tony Berry »

Sorry 3Di, I could never get your code to function. I did some PHP self-educating, but couldn't make it function. Likely an authentication issue with the database. Wasn't prompting and I wasn't feeding it manually. I still appreciate your suggestions and assistance.

Thecoalman, I ended up using your code change and was able to clean things up quickly at "500". Didn't time out and only had about 25 pages to Mark All -> Delete.

Thanks for the help all. I'm good now.
Tony Berry
Lafayette, LA
User avatar
3Di
I've Been Banned!
Posts: 17538
Joined: Mon Apr 04, 2005 11:09 pm
Location: I'm with Ukraine 🇺🇦
Name: Marco
Contact:

Re: Mass "Safe" Post Deletes - SQL Code

Post by 3Di »

Tony Berry wrote: Tue Dec 07, 2021 10:08 pm Sorry 3Di, I could never get your code to function. I did some PHP self-educating, but couldn't make it function. Likely an authentication issue with the database. Wasn't prompting and I wasn't feeding it manually. I still appreciate your suggestions and assistance.
...
Very strange, instructions are clear. :)
🆓 Free support for our extensions also provided here: phpBB Studio
🚀 Looking for a specific feature or alternative option? We will rock you!
Please PM me only to request paid works. Thx. Buy me a coffee -> Image
My development's activity º PhpStorm's proud user º Extensions, Scripts, MOD porting, Update/Upgrades
Post Reply

Return to “[3.3.x] Support Forum”