SQL newbie looking for needed SQL queries

[X-or]

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. :o

[kh99]

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

UPDATE vb_user LEFT JOIN vb_userfield USING(userid)
SET usergroupid=N WHERE field2=Y




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,

UPDATE vb_user set homepage='' WHERE usergroupid=N


'' 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. :o


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.

[kh99]

...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.