Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 26 Jan 2015, 12:15
X-or X-or is offline
 
Join Date: Nov 2005
need a sql query to clean up spam bots

Hello,

I need help to clean up spam bots, I have literally thousands of them in my db and I think the easiest way is a sql query

Here is a sample of my db


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.


We have here a typical spam bot account, they can be easily retrieved because they have `vb_userfield`.`field2`='Afghanistan', as bots often pick the first choice in selector

So I want to update their `vb_user`.`usergroupid` value (to move them from registered to banned group) and I want to update all the `vb_userfield`.`fieldX` to empty them because the content is spam, as well as empty their `vb_user`.`homepage` value (spam too)

Could you help me come up with a sql query? Thank you

Last edited by X-or; 26 Jan 2015 at 14:21.
Reply With Quote
  #2  
Old 26 Jan 2015, 12:33
Dave Dave is offline
 
Join Date: Jun 2010
Real name: Dave
Fairly easy with an UPDATE query with an INNER JOIN.


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

__________________
https://technidev.com - security, development, exploits, vBulletin
dave[at]technidev[dot]com

Contact me for custom vBulletin 3/4 work & server/website management.
Reply With Quote
  #3  
Old 26 Jan 2015, 13:29
X-or X-or is offline
 
Join Date: Nov 2005
Perfect, these buggers were cleaned up. You have my utmost respect Sir.

I didn't know the JOIN command could be used like that, much appreciated, thank you.

3240 spam bot accounts cleaned up in one query, got to love sql.
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 08:52.

Layout Options | Width: Wide Color: