Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 14 Jan 2013, 10:30
X-or X-or is offline
 
Join Date: Nov 2005
SQL newbie looking for needed SQL queries

Hi,

I've had a lot of issues lately which could only be fixed with sql queries due to vbulletin software not being powerful enough, I have three requests which I'll detail further, thanks in advance for anyone willing to help.
1) prune spamers
2) delete spamers homepage info
3) force some option for all users

1) I have like 850 spam bots accounts to ban due to a massive attack of my forums by spamers who could go past all standard human verifications trials. Fortunately it's easy to spot them because I had some custom profile field with selector and they always select the first option in first field which is a very unusual option (country)

Now while vb can show them to be it won't let me mass move them to banned usergroup, the move/prune filters are kind of anemic, sql query seems to be the only way to go, here are the sql tables which matter

vb_user // userid // usergroupid
vb_userfield // userid // field2

should be something like this
UPDATE vb_user SET usergroupid="n" WHERE .... and that where I'm lost, it should be possible to ask that whenever you have userid=X and field2= a certain value then move the user with that userid in vb_user table to banned group

2) related, these spamers filled the homepage field to promote malicious websites, so I want to delete the homepage field of all spamers, it should be easy enough to cook a query and apply it on all users of banned usergroup once step 1) is done

3) unrelated, I had issues with default option at registration due to some addon, but I only noticed recently so I want to force the default options I really wanted to have on all users of the most common group (registered users)
Here are the options I want to force to Yes:
-Receive Private Messages
-Send Notification Email When a Private Message is Received
-Save a copy of sent messages in my Sent Items folder by default
-Enable Visitor Messaging
-Display Signatures
-Display Avatars
-Display Images
-Display Reputation

And also:
Automatic Thread Subscription Mode -> Instant email notification
Message Editor Interface -> Show Enhanced (WYSIWYG) Editor Toolbar

Please help, thank you very much.
Reply With Quote
  #2  
Old 14 Jan 2013, 13:57
kh99 kh99 is offline
 
Join Date: Aug 2009
Real name: Kevin
Originally Posted by X-or View Post
should be something like this
UPDATE vb_user SET usergroupid="n" WHERE .... and that where I'm lost, it should be possible to ask that whenever you have userid=X and field2= a certain value then move the user with that userid in vb_user table to banned group

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



2) related, these spamers filled the homepage field to promote malicious websites, so I want to delete the homepage field of all spamers, it should be easy enough to cook a query and apply it on all users of banned usergroup once step 1) is done

Assuming step 1 has been done,


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


'' is two single quotes, i.e. and empty string.


3) unrelated, I had issues with default option at registration due to some addon, but I only noticed recently so I want to force the default options I really wanted to have on all users of the most common group (registered users)
Here are the options I want to force to Yes:
-Receive Private Messages
-Send Notification Email When a Private Message is Received
-Save a copy of sent messages in my Sent Items folder by default
-Enable Visitor Messaging
-Display Signatures
-Display Avatars
-Display Images
-Display Reputation

And also:
Automatic Thread Subscription Mode -> Instant email notification
Message Editor Interface -> Show Enhanced (WYSIWYG) Editor Toolbar

Please help, thank you very much.

A lot of those can be done by going to the admincp, Maintenance > Execute SQL Query and finding the query under "Automatic Queries" (you need to have your userid in the config.php file, in the $config['SpecialUsers']['canrunqueries'] variable, or else have the site in debug mode). If I have it right, that leaves:

-Save a copy of sent messages in my Sent Items folder by default
-Enable Visitor Messaging

that can't be set with an automatic query from that menu. When I get a chance I'll try to figure out what the queries would be for those.
Reply With Quote
  #3  
Old 14 Jan 2013, 13:59
kh99 kh99 is offline
 
Join Date: Aug 2009
Real name: Kevin
...oh yeah, the usual warning: I haven't tested the queries I posted, and for safety you should have a backup of your database before you start changing it manually.
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 15:56.

Layout Options | Width: Wide Color: