SQL query to mass delete users based on Biography data

[kaggy]

I recently had a few hundred spam accounts registered on my board. The one common attribute among all the accounts is that they entered "Man" in the biography profile field. Is there a SQL query I could run that would delete all these accounts? The mass/prune users feature has a very limited search. Thank you.

[Jake Bunce]

If there is some common value within their user table records then you can query the table to set yourself up to prune by group, like this:

http://www.vbulletin.com/forum/showthread.php?t=210859

The biography is stored in the joined userfield table and I don't have a query for that. If no one else here has a query for you then the people on www.vbulletin.org might be able to help.

[Riki]

If anyone could help us figure this out it would be greatly appreciated. I tried for hours and searched for many more. The best I could come up with is this

UPDATE 
    user 
LEFT JOIN
    userfield
ON
    user.userid = userfield.userid 
SET 
    usergroupid = '25'
WHERE
    userfield.field1 = 'man'

which returns with this error

error number: 1054 
error desc: Unknown column 'userfield.field1' in 'where clause'

[Lats]

The query looks fine and you should have a field1 in the userfield table.

What do you get when you run this query...

SHOW COLUMNS FROM userfield

[Riki]

When I run that query I get what is shown in the attached image. (sorry I couldnt paste the text due to the formatting).

I am able to run a basic query that lets me know there is over 1k users with biography man, I just cant seem to move them to the new banned usergroup.

I appreciate the help

[Lats]

Looks normal, I'm guessing you're running the query in the ACP?

Try running it in phpmyadmin, or remove the vertical spacing from the query.

[Riki]

Thanks for the quick reply. Its encouraging to know it should be working.

Something I havent told yet is I have the vb prefix on my database. This is exactly what I am putting in the query.

UPDATE vbuser LEFT JOIN vbuserfield ON user.userid = userfield.userid SET usergroupid = '25' WHERE userfield.field1 = 'man'

I tried this in phpmyadmin and get the same error. Am I missing a prefix somewhere?

[Lats]

Am I missing a prefix somewhere?

One or two

Try this...

UPDATE 
 vbuser 
LEFT JOIN 
 vbuserfield 
ON 
 vbuser.userid = vbuserfield.userid 
SET 
 vbusergroupid = '25' 
WHERE 
 vbuserfield.field1 = 'man'

[Riki]

Thanks alot, for some reason it wont work for me if I use vbusergroupid, I have to leave it as usergroupid

here is the final code for anyone who wants it:

UPDATE 
 vbuser 
LEFT JOIN 
 vbuserfield 
ON 
 vbuser.userid = vbuserfield.userid 
SET 
 usergroupid = '25' 
WHERE 
 vbuserfield.field1 = 'man'

[losedude]

Thanks, this solved my problem

[Rebirth]

I know this post is old, but I wanna thank you for this query and hope it can help more people. I got 11,000+ bot registrations with exactly the same custom profile set as '123456' and this helped tremendously.

Thank you!

[CRISTIAN C.]

Hope to help. For who using vbulletin 4, the command is more clean, no 'vb' in front of user, so:

UPDATE
user
LEFT JOIN
userfield
ON
user.userid = userfield.userid
SET
usergroupid = '12'
WHERE
userfield.field1 = 'man'

Change 'man' with anything else your spammer wrote in the profile field
Change '12' with your spamers usergroup.

Great people here, found what I need and finished with bot spam. Thanks!

[MusicMan]

Glad to see someone has found a way to fix this problem. I'm very thankful that these spammers/bots were considerate enough to use the same "Man" entry in the Biography field, but I couldn't figure out how to mass prune them all, until finding this thread. One question though...If I have some legitimate users with "man" somewhere in that field, such as a user with 2,000 posts whose biography is "Manager of yadda yadda", will it delete them as well? Or does the use of single apostrophes in the script above only target those whose fields are "man" and nothing else? I need a definitive answer to this, because the last thing I want to do is to prune legitimate users, especially very active ones.

[Lats]

You don't need to worry there, the query as is will only match the whole field if it is equal to 'man'.

For info, if you wanted to match the Manager example the query would be "like '%man%".

[MusicMan]

You don't need to worry there, the query as is will only match the whole field if it is equal to 'man'.

For info, if you wanted to match the Manager example the query would be "like '%man%".

Nice, thanks for the confirmation.