Strange thing we came across today. While looking through the DB for spammers with spam signatures: I can see a spam member in the ACP, he has a spam signature. When I query the sigparsed table for that userid, it returns 0 results. In the User table, a query shows him. How can he have a signature yet it is not stored in sigparsed?
SQL question about vB db
Collapse
X
-
Tags: None
-
He is adding it manually to every post and does not have an actual signature.
Also please be insanely careful with messing with the db directly. -
The signature is stored in usertextfield.signature.
Please don't PM or VM me for support - I only help out in the threads.
vBulletin Manual & vBulletin 4.0 Code Documentation (API)
Want help modifying your vbulletin forum? Head on over to vbulletin.org
If I post CSS and you don't know where it goes, throw it into the additional.css template.
W3Schools <- awesome site for html/css helpComment
-
Oh, thanks, Lynne, you are right as usual!
Zach, anything in particular I should be wary of? We are trying to ID all the spam accounts, and will be deleting this directly from the db. Is there a better way to do this? Cheers!Comment
-
Why are you not deleting the users via the admincp?
Please don't PM or VM me for support - I only help out in the threads.
vBulletin Manual & vBulletin 4.0 Code Documentation (API)
Want help modifying your vbulletin forum? Head on over to vbulletin.org
If I post CSS and you don't know where it goes, throw it into the additional.css template.
W3Schools <- awesome site for html/css helpComment
-
I'm guessing there are 2000+ spam acounts in our user table of 60,000, maybe as much as 10,000 spam accounts. Can I query and delete in mass via the ACP?Comment
-
Unless those account has something in common that you can search for in the Admin CP - there is no option to do that.
Admin CP -> Users -> Prune / Move UsersSteve Machol, former vBulletin Customer Support Manager (and NOT retired!)
Change CKEditor Colors to Match Style (for 4.1.4 and above)
Steve Machol Photography
Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.
Comment
-
Nice, Steve. Is there a benefit to using Prune in ACP over just deleting from the db? I'm assuming using ACP will remove all records from all tables with the same userid ?
I guess I could assign all the results I get with SQL queries to a "Spam" usergroup, then prune with ACP.Comment
-
It will remvoe them from all tables rather than what you choose to delete from - it's also supported...Comment
-
Hmmm...Ok, Trevor, that sounds like what I need to do. I enabled the ACP SQL Queries in my config, I see I can use that to delete by usergroup...
How does this sound: I can create a new usergroup called spamhos. Then I can run my join queries in phpMyAdmin, and see who has spam sigs, spam homepages, and spam visitor messages.
Something like this
SELECT U1.userid, U1.username, U1.homepage, S1.signature
FROM user AS U1, usertextfield AS S1
WHERE U1.userid = S1.userid
AND U1.joindate >1291165261
AND ( U1.lastactivity - U1.joindate ) < 86400
AND U1.posts < 2
AND S1. signature NOT LIKE ''
ORDER BY U1.username
Then I run a query to change their userid from 2 (registered) to 99 (spamhos). And then I can go into the ACP and delete all members of that usergroup.
Would that be a good method? Will it also get their visitor messages?
thanks!!Comment
-
...or, better yet, I see I can select from the spam group and delete in the Admin CP -> Users -> Prune / Move Users section, nice!!!Comment
-
I hate bumping old threads, but I'm starting to consider upgrading one of my older 3.x forums to 4.x. However, before I do, I want to get some things in order so my upgrade goes smoothing and my forum can move forward. I've been recently going through my users and found I have about 2,700 signature spammers -- folks that registered and have 0 posts but set a signature. I created a spammer usergroup but I don't want to go through hundreds of pages and manually move them, especially since you can't search on signature in the prune/move options. How could the above mentioned query be modifed so it can move users to the spammer usergroup so I could then easily use the prune/move feature?Comment
-
That would be a question better asked over on vbulletin.org, the modification site.
Please don't PM or VM me for support - I only help out in the threads.
vBulletin Manual & vBulletin 4.0 Code Documentation (API)
Want help modifying your vbulletin forum? Head on over to vbulletin.org
If I post CSS and you don't know where it goes, throw it into the additional.css template.
W3Schools <- awesome site for html/css helpComment
-
Blast, I just now saw your query, Retro. Sorry, man, I guess you have tackled this by now. I will say yes, we were able to write and test a query that allowed us to move all members who had signatures, with less than 2 posts, and had not logged in after xx number of years into the Spammer usergroup, and then delete them after browsing and checking.Comment
Related Topics
Collapse
-
by CichlidgeekI have a user on my forum that would really like ti include a photo in his signature. He and I have both tried uploading a photo to his signature several different ways, and still no luck. His signature...
-
Channel: vB Cloud Support & Troubleshooting.
Tue 2 Feb '16, 7:34pm -
Comment