A mysterious member disappearance...

In a one-out-of-forty-six-thousand chance of events, an important moderator and long time member's account on my big board (www.zeldauniverse.net/forums) has been suspiciously deleted. I do not know the cause and am seeking help, naturally, from someone who might.

It's not that his account was removed that puzzles me so much... but that all traces of him ever having registered were completely erased from the vB database. Everything connected to his User ID# is gone with the exception of his sent and received PM's. Which means all several thousand posts he made are deleted. There isn't a trace of his account ever having existed in our database aside from his PM's.

What could have cause this? We weren't hacked, and someone wouldn't try to destroy just one user's account. No other staff member did anything, nor do they have enough knowledge of MySQL databases to pull this kind of stunt, to my knowledge. Around the time of this incident I did receive an e-mail from the company I rent my server from, stating some gobbledygook that I don't quite remember, but I think it was to the tune of "your server isn't running all processes it should be." I wonder if that might have caused an issue?

But finding out the concern isn't what's MOST important to me or to my lost staff member. We have plentiful backups. His user account exists somewhere on a backup database and I can certainly yank it out with some help. So I need to know how, or if that's possible. And then I'd need to know: Can I take that, and "inject" his entire user information, posts, User CP settings and all, back into the current vB database?

If I can know how to do that, then everything will be just fine.

[Marco van Herwaarden]

You really should start with figuring out what has happened, before even thinking on how to restore it.

Is there any trace in the Moderator or Admin logs regarding this?

[kmike]

Looks like the data was deleted directly from the database. I'd start with the investigation how it could happen, because until you find out who did it and how it was done, there is no point in restoring the erased data as it could be deleted the same way again.

As for the restore itself, you can start another MySQL instance populated with the backup data and then run a couple of queries like "SELECT * FROM <table> WHERE userid=<nnn> INTO OUTFILE '/tmp/<table>.sql' " against the tables with erased data.
Then copy the generated SQL files to the live MySQL server and run "LOAD DATA INFILE '/tmp/<table>.sql' INTO TABLE 'table' ".

[GoldenChaos]

Originally Posted by Marco van Herwaarden

You really should start with figuring out what has happened, before even thinking on how to restore it.

Is there any trace in the Moderator or Admin logs regarding this?

I've already searched the logs - there is no record of any recent user deletions (the most recent was on Jan. 1), and nobody but myself and my co-webmasters have access to the database. I'm not concerned if it happens again, so long as I can continually restore his user.

As for restoring from the backup, I need to get it first - but once I do, I'll try what you said, kmike, and see if that can restore his account. If simply his account is restored, I'll work to get his posts and such back as well.

[Marco van Herwaarden]

If it is not in the logs then your most likely suspect is that they gained access to your database from outside vBulletin.

I'm not concerned if it happens again, so long as I can continually restore his user.

You should be, who can tell you what they will do next time.

[GoldenChaos]

Originally Posted by Marco van Herwaarden

You should be, who can tell you what they will do next time.

They can't do much... we keep daily backups. We've been in situations where the entire site has been wiped out before (nchan brute force attacks, etc), so we keep good backups hanging around just in case. I'm actually more worried that somebody would do something specific like this, because it's much more work than simply restoring everything from a few hours ago.

[Lynne]

Originally Posted by GoldenChaos

They can't do much... we keep daily backups. We've been in situations where the entire site has been wiped out before (nchan brute force attacks, etc), so we keep good backups hanging around just in case. I'm actually more worried that somebody would do something specific like this, because it's much more work than simply restoring everything from a few hours ago.

But a solution would be to restore everything to a few hours ago - to right before this database deletion.

Seriously though, I would be very, VERY concerned that someone has access to my database in this way. That means that they could take your whole user table if they wanted along with all the email addys. They could read everyone's PM. They could read what is being said in private forums. They have ACCESS TO YOUR DATABASE!!! and you don't seem to be concerned. I don't get this at all.

[GoldenChaos]

Well, I'm prioritizing. It's much harder to find out if some rouge has access to your database than it is to simply fix the broken database.

So, I'd rather do that first, then go on a hunt on my own. And, in the meantime, the current database is in no real danger, and the most important things to my members are encrypted. I do hope that nobody was frivolously sharing information that could be easily read in a PM, but again - I'm prioritizing.

Also, I don't truly believe that anybody gained access to the database. It's too unlikely - we have a great password, and only a complete idiot would traverse our database to delete a specific member. And no way were they choosing a random member. It doesn't fit with our history of being hacked. The morons who hack our site usually go for something bigger than one member's posts and account. Most of my staff have been inclined to believe that it was a bug, or just somebody's accident.

If anybody has a more simple method of getting all his user information back into the database - possibly step-by-step instructions just in case I happen to get lost - I'd really appreciate that. Really, I would. Thanks!

[kmike]

Also, I don't truly believe that anybody gained access to the database.

Then how did they selectively deleted the data from some tables and not the others? If they used vBulletin "delete user" feature, all user data would be gone.
You also can check some other secondary tables like 'subscribethread', 'subscribeforum', 'editlog', 'deletionlog', 'moderatorlog', if there are some records with deleted member's userid, it'll be another proof the attackers have a direct access to the database.

I also don't understand your priorities, ignoring the issue of someone messing with your database with malicious purposes is on the verge of light-mindedness. You can't even really tell if your database is still intact! They could have already altered or deleted some small things here and there. I understand that you only found that the member's posts are missing because he's a high profile member and a mod. Who knows how much more data is missing?

If anybody has a more simple method of getting all his user information back into the database - possibly step-by-step instructions just in case I happen to get lost - I'd really appreciate that. Really, I would. Thanks!

There's no simple step-by-step instructions for your case because you don't even know what data is missing. But generally you can use my instructions above for all tables containing "userid" field - you can find them by searching mysql-schema.php in your /install/ directory. And then running "LOAD DATA INFILE '<table>.sql' IGNORE INTO <table>" for each data file (note the "IGNORE" keyword, it'll skip the rows with duplicate primary key, i.e. those that already exist in the main database).

[Mkvenner]

Right... I happen to be the more technical administrator of GC's boards, and I've had all the passwords changed again, and I'm looking into ways for someone to have gotten access, but I've not found anything yet, aside from an earlier vulnerability that I am fairly certain I've fixed. So, basically, leave the poor guy alone on the "why are you ignoring this?!" front, because the answer is "it's my problem."

I am of the opinion that there is not an effective way to restore everything from a single user... and running that SQL command for every relevant table is...painful.