Running vb 3.7.2, have corrupted PM data

[wacnstac]

I was hit over the 4th of July weekend by a script that scans the entire database of users looking for those who have matching username and passwords. It goes through all users one by one until it finds an account that fits this description, then once it's able to log in, send several nasty PMs to multiple other users. These PM had links to sites that had viruses on them.

Anyway to make a long story short, a whole lot of PM's with similar titles were sent out in a very short time. Lacking a solution and feeling very bad that somehow my users accounts had been comprised (not knowing at the time it had hit those with matching username and password) and not wanted innocent users who had the PM sent to them to get a virus, I went in the database and directly deleted a range of PM's using something like

DELETE * FROM pm WHERE pmtextid >430323 AND pmtextid <446690

I didn't realize at the time the harm this could do and am now in desperate need to clean this up. Any help from vbulletin on both how to clean this up, and how to prevent it from happening again would be greatly appreciated.

[Steve Machol]

You also have to delete the relative entries for those PMs in the pmreceipt and pmtext tables, if you can, The problem is there is no simple way I know of to correlate those PMs to the relevant entries in those tables.

[wacnstac]

So what will the effects of this be? Are my user's PMs going to be permanently messed up? Can we start over and delete all PM's after we give our users some warning? Oddly enough, none of our users have complained about anything odd going on with PM as of yet and it's been a month since I took this action.

[Steve Machol]

Then I'm confused. If this was done a month ago, what is the problem you are trying to fix?

[wacnstac]

You also have to delete the relative entries for those PMs in the pmreceipt and pmtext tables, if you can, The problem is there is no simple way I know of to correlate those PMs to the relevant entries in those tables.

Based on your statement in the post above, it sounds like my previous action could have unpredictable side effects that may not even be realized yet, such as user's PM counts not matching the real number of PMs that they have and perhaps worse.

[Steve Machol]

The most likely affect would be PMs counts that are not accurate. Has anyone reported any specific problems?

[wacnstac]

No, but it would seem at some point someone is going to see a negative PM count or worse. Based on your previous comments, I would expect worse.

[wacnstac]

I guess the real problem here could be that since I just deleted textid records, if someone goes to look at a PM where the table is pointed to one of these deleted records we could go off in the weeds?....

[wacnstac]

Well this is starting to bite me now. Users who haven't been on my site for some time are beginning to log in and report that they are being notified that they have a new PM but there isn't one in their box. Does vbulletin have a utility to recalculate the number of PM's for everyone? Do I need to fill out a Vbulletin ticket to get this fixed?

[Steve Machol]

Honestly I don't know of any way to fix this. The only solution may be to delete all PMs for everyone.

[wacnstac]

Why couldn't it be done with a "update PM counters" feature similar to other update counter features that we already have?

[Steve Machol]

That would possibly work if we had such an option. But we don't. Also it may not work if the data is messed us from the partial deletion you performed.

[wacnstac]

So what do I do with all these users who are coming to me now and telling me that they have a notification that they have a new PM, but there are no PM's in their box when they check. I know I put myself in this situation, but at the time we didn't know any better way to handle the porn spam that was put into everybody's box (which has viruses linked to it by the way). Only later did vbulletin issue a patch that prevented people from using a password that matched their username and helped us weed out those who had done so in the past.

[Steve Machol]

The only other recommendation I have to to delete ALL PMs and start over.

To delete all PMs, run these 5 queries:

UPDATE `user` SET `pmtotal` = '0';
UPDATE `user` SET `pmunread` = '0';
TRUNCATE TABLE `pm`;
TRUNCATE TABLE `pmtext`;
TRUNCATE TABLE `pmreceipt`;

Of course, backup your database first.

[wacnstac]

That kindof puts me between a rock and a hard place. There will be those with PM boxes chuck full of message who will log on after prolonged absence to find them all deleted. They will be surprised even with multiple warning prior to deletion and probably angry. On the other hand I have the others with empty boxes that keep getting annoying "you have a new PM" messages.