Delete spammers by partial IP address?

[Max Taxable]

I am helping someone with their long neglected board that's got alot of spam accounts with IP address starting with 121 in the profile.

They are all in the registered users group, and I want to know what a query would be to delete them all. Anyone with a idea? Can a partial IP be entered in a query, with a asterisk as wildcard? Like 121* ??

[ForceHSS]

If all ips were the same yes you could be as more than one table holds user info it would not be a good way to go

[Max Taxable]

I'm asking specifically about the profile field, "IP Address" the admin can see in profiles when looking them up in AdminCP. This as a identifier similar to the user profile fields used to mass move spam accounts to a custom usergroup as in this article: https://vborg.vbsupport.ru/showthread.php?t=276547

If we know the profile field number and the first three digits of the offending IP address, can we craft a query to delete all such accounts? Would such a query accept the asterisk as a wildcard, as in 121.* ??

[ForceHSS]

As I said in my first post you need the full ip if all spammers IPs were the same then you could but if doing it from a query it will cause problems

UPDATE `user` SET `usergroupid`='8', `ipaddress`='FULL IP HERE' WHERE (`userid`='2')
this will move them from group 2 to 8 with x ip
then you could use the prune option to delete all in that group

is only the first 3 numbers of the ip the same and how many spammers and do they have posts. If the forum you are helping I can give you a plugin that will delete all there posts and delete the accounts

[Max Taxable]

Need the FULL Ip address, wildcards won't work? That's what I am asking. Never been tried? How do we know?



is only the first 3 numbers of the ip the same and how many spammers and do they have posts. If the forum you are helping I can give you a plugin that will delete all there posts and delete the accountsI missed this before....

Yes there's a couple thousand or so with first three numbers of the IP the same. Some of those accounts have 1 or 2 posts, most have zero. Some of them have exact IP address (all numbers) the same, vast majority, not.

Only common denominator is first three digits of the registration IP.

[ForceHSS]

UPDATE `user` SET `usergroupid`='8' WHERE ipaddress LIKE '121%';

change group if you need then you can use the prune options in vb but before you do make sure all spammers posts are deleted first

Always backup first

PM sent I gave you a custom plugin that will help

[Max Taxable]

UPDATE `user` SET `usergroupid`='8' WHERE ipaddress LIKE '121%';

change group if you need then you can use the prune options in vb but before you do make sure all spammers posts are deleted first

Always backup first

PM sent I gave you a custom plugin that will helpThanks Force! Does that query scare anyone besides me? Part of me wants to run it part of me wants to run from it!

I appreciate your time and help.

[Simon Lloyd]

That query will move users to usergroup 8 if their ip address is:
121.123.00....etc
1212.....etc

so any ip address that starts with 121 will get the user moved, the wildcard operator (%)will account for anything at all after 121.

[Max Taxable]

That query will move users to usergroup 8 if their ip address is:
121.123.00....etc
1212.....etc

so any ip address that starts with 121 will get the user moved, the wildcard operator (%)will account for anything at all after 121.I'm not as skeered of it now, as I was. Thanks Simon!

[ForceHSS]

Thanks Force! Does that query scare anyone besides me? Part of me wants to run it part of me wants to run from it!

I appreciate your time and help.

Dont be scared lol when it comes to things like this I know what I am doing been at it for over 20 years

[Max Taxable]

Dont be scared lol when it comes to things like this I know what I am doing been at it for over 20 yearsI am not doubting you at all and never would. :D

But you got to admit that is a scary looking query, with all it can do especially in the wrong hands, with wrong numbers entered... A fella could really screw alot of things up with that one!

[ForceHSS]

I am not doubting you at all and never would. :D

But you got to admit that is a scary looking query, with all it can do especially in the wrong hands, with wrong numbers entered... A fella could really screw alot of things up with that one!

I know its something am sure you and others have never seen on here before thats why i asked you to backup first i always do even if i know 100% it will work but i had to put in the warnings in my other post it just covers me in many ways. I did run tests on this query after i posted that so will edit that first warning and if you can do the same to post 7

[Max Taxable]

The warnings aren't the scary part. The scary part is if I enter wrong number, etc. People who use that better be damn sure what they're doing.

Thanks again!

[ForceHSS]

Wrong number do you mean ip?

[Max Taxable]

Wrong number do you mean ip?Yeah...

I've run that query now many times, with different first three IP numbers, works great. Herding them all into a usergroup where I can examine them individually, decide if it's legit, or not. Then when done with the examination, purge what's left in that group.

Just exactly what i needed for the task.

[ForceHSS]

Good to hear, and use that mod I gave you, it will delete there posts in all places then them will save you errors

[Max Taxable]

Good to hear, and use that mod I gave you, it will delete there posts in all places then them will save you errorsI installed that one and set up its parameters. Will come in handy for that board's owner and staff. Nice hack.

[ForceHSS]

I installed that one and set up its parameters. Will come in handy for that board's owner and staff. Nice hack.

Happy to help its my own I only give to a few

[Max Taxable]

Happy to help its my own I only give to a fewI pruned over 12,000 spammers, 54,000 of their threads.... 1000s of blog entries, many thousands of their posts that weren't OP... all their private messages and visitor messages.... Using the query posted here and that tool. Excellent.

[ForceHSS]

Nice

[Max Taxable]

What's scary is when it tells you "1,820 rows affected" and such.

:eek:

[ForceHSS]

Thats why you backup first so you dont have to worry if you make a mistake

[Max Taxable]

Backups are nice... Especially when there's been no human activity for at least three years! Didn't have to worry about recent posts or anything being lost!

[ForceHSS]

Then no worries