vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB4 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=251)
-   -   need a sql query to clean up spam bots (https://vborg.vbsupport.ru/showthread.php?t=316933)

X-or 01-26-2015 11:15 AM
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

Code:
INSERT INTO `vb_userfield` (`userid`, `temp`, `field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`, `field8`, `field9`, `field10`, `field11`, `field12`) VALUES
(10248, NULL, 'Female', 'Afghanistan', 'RussellVomIL', 'Afghanistan', 'RussellVomIL', '', 'RussellVomIL', '123456', '', 'levitra', '', ''),

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 :)

Dave 01-26-2015 11:33 AM
Fairly easy with an UPDATE query with an INNER JOIN.

PHP Code:
UPDATE vb_user AS a
INNER JOIN vb_userfield AS b on a.userid = b.userid
SET a.usergroupid = 8, a.homepage = '', b.field1 = '', b.field2 = '', b.field3 = '', b.field4 = '',  b.field5 = '',  b.field6 = '',  b.field7 = '',  b.field8 = '',  b.field9 = '',  b.field10 = '',  b.field11 = '',  b.field12 = ''
WHERE b.field2 = 'Afghanistan' 

X-or 01-26-2015 12:29 PM
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. :)


All times are GMT. The time now is 10:56 PM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01632 seconds
  • Memory Usage 1,720KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)bbcode_code_printable
  • (1)bbcode_php_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (3)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete