Support Forum

Compromised PM System Cleanup

JI jim
jim
Member
Member since Feb 2008
650 posts
Oct 25, 2019 · 3:44 pm · Edited

Hi! I need help cleaning up from a spam user who compromised our Private Messaging system.

We implement first post moderation to prevent new users from spamming the forums. Unfortunately, I had not set PM system component options to require any post approval before allowing new members to send private messages. I only discovered this setting after a user sent private messages to many (or all) of our users. We do have the cc limit set to 5, yet this user (or bot?) somehow sent a private message including spam content to at least hundreds, if not all of our 14000+ users.

So…I took immediate action and deleted the offending user account, and selected to delete all of that user’s forum posts. It seems, however, that this action did not delete that user’s private messages. It has been a week since the event, and one member forwarded the spam message to me. Assuming the message had been deleted, I inquired how they saw it, since I deleted the user immediately and it has been over a week. This member informed me that the message was in her inbox.

So…how can I ensure that this message is deleted from all of our users’ Private Message inboxes and discussions? I’m thinking this may be tricky since I no longer have the offending user’s ID, though I do know the username, but the account has been deleted. I still have a hard time grasping how private messages are stored in the various sfpm_ database tables. But there must be a way to get rid of this spam via PHP MyAdmin.

Thanks in advance for any direction!

If @yellow-swordfish still participates here, I’d love some feedback since I believe you may know our user who reported the message… wink

2 Answers

New Answer
SP Simple Press
Simple Press
Admin
Member since Jul 2018
395 posts
Oct 29, 2019 · 2:29 pm

Hi:

If you sort your sfpmmessages table by the thread_id in descending order you should be able to quickly find the messages that were sent.  Then, you’ll see the user_id for the user.  

You can then delete all messages with that user_id in both the sfpmmessages and sfpmrecipients table. 

The only place that message id isn’t referenced is the sfpmthreads table – in this case you can look at the thread_slug or title columns to identify the threads – the title column will have the same title for all the spam threads.

JI jim
jim
Member
Member since Feb 2008
650 posts
Oct 29, 2019 · 5:56 pm

Thank You! Got it all…

Note to self. And for anyone else who may encounter such an issue, here are the steps I took to clear out the spam from all user inboxes. (This spammer apparently messaged 406 of our users before we deleted the account.)

As directed by @spsupport :

1. Sort wp_x_sfpmmessages table by the thread_id in descending order

2. Identify offending messages, they should all have common user_id

3. Run SELECT * query to ensure all the identified messages are spam. (optional)

see attachment

NOTE: replace “12345” with user_id you identified and replace “wp_x_” with your database prefix.

4. Review results, and run DELETE query to delete all the offending messages

see attachment

5. Review title and/or thread_slug columns in wp_x_sfpmthreads table to identify threads that contained the spam messages.

6. Run query to delete empty spam threads.

see attachment

NOTE: replace “offending-slug” with unique term from identified spam thread slugs. Keep “%” to accept any characters before and after that term.

EDIT: Wordfence blocked my post due to the queries…see attached image for query details.

queries.png
queries.png