OK, I'm getting sick and tired of the spammers that keep registering with my Conspiracy Forums. They compose of probably over 90% of the members list and that number almost doubles the number of posts! I've decided that I want to Hard Delete them from my MySQL Database.

The one thing that they have in common is that under the Biography field they all have the word "Man". So I figured the best way to remove these spammers is to create a query to move them to a certain usergroup I created (spammers) and then get the option to prune them all at once from that specific group, similar to what's going on here:
http://www.vbulletin.com/forum/showthread.php?p=1650640

The query I've got so far is:

PHP Code:

UPDATE user
SET usergroupid = 9
WHERE userfield1 LIKE 'man' 


9 is the ID of the spammer group I created and userfield1 should be the name of the biography field, but I'm not completely sure (the DB shows a "field1" record for biography data, but it's under the table "userfield", so I don't know how to translate this into the WHERE portion of the query).

Thoughts?


Also, I know this won't stop new spammers from registering, but performing this bit of maintenance will clean up my forum greatly.

UPDATE user SET usergroupid  = 9 LEFT JOIN userfield  ON userfield.[useridcolumn] = user.[idcolumn] WHERE field1 LIKE 'man' ;

NOT TESTED just keyboard coding. Don't try it on live data

Alright, I'll take your word on the query working for now .

Is there any way to effectively test it? If not then I could just back up my database and run it on the forum right away (closing it for a bit to make sure everything works well).

I would suggest installing MySql locally, mirroring the live DB and using HeidiSQL to test run the query.

Alright, I tested the above query and got this error:

PHP Code:

An error occurred while attempting to execute your query.
The following information was returned.
error number: 1064 
error desc: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN userfield ON userfield.[useridcolumn] = user.[idcolumn] WHERE field1 ' at line 1 


So I tried it again spacing it out evenly:

PHP Code:

UPDATE user
SET usergroupid = 9
LEFT JOIN userfield
ON userfield.[useridcolumn] = user.[idcolumn]
WHERE field1
LIKE 'man'; 


And got this error:

PHP Code:

An error occurred while attempting to execute your query. The following information was returned. 
error number: 1064 
error desc: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN userfield
ON userfield.[useridcolumn] = user.[idcolumn]
WHERE field1' at line 3 


Does this mean LEFT JOIN cannot be used? (Both the line numbers changed at the end, from line 1 to line 3, so I assume this is where the error is, but honestly though, I haven't the slightest idea what to do here.)

Does this mean LEFT JOIN cannot be used?

Nope, it's because I have no idea what the column names actually are, so stuck a place holder in there....



... And then didn't make that clear in the post.

The useridcolumn is just called "userid" and then the column where all the man data is stored in "field1". These are both in the userfield table.

The new group I created (group 9) is in the usergroup table, and the column for the IDs is just called "usergroupid".

So would I change the last part to something like:

PHP Code:

ON userfield.[userid] = usergroup.[usergroupid]
WHERE userfield.[field1]
LIKE 'man'; 


Or do you need more info (or are the changes I made just plain wrong )?

this bit
= usergroup.[usergroupid] needs to be user.userid (use the column name of the ID column in the user list table.)

and remove the square brackets and it should be ok.

OK, the userid column in the user table has the primary key set to it, so I don't think thats the column, however there is a "usergroupid" column showing which usergroup they belong to. So I assume here that's the table I replace the usergroup with (and remove the brackets)?

aka: user.usergroupid

The one thing that they have in common is that under the Biography field they all have the word "Man".

That intrigues me. What are their IPs? I presume they are different, since you are a forum owner and would see if they were the same. Are they one person on lots of proxies? People very rarely have common identifiers like that - spam is generally chaotic. How come they all have the word "man" in this field and nonspammers don't? Are you sure... (1) that it's different spammers? {using the same jive could indicate the same gob} (2) that your method won't remove legitimate members without your stopping it in time?

I have thought of some ideas for how to change the spamming on, for example, this forum - you could query every new post for words like seo, social network, and then download the signature link, see what it's selling, maybe factor a few other things in (personally I would look for ungrammatical english) and after a bit of hard work and pattern-match engineering, i reckon you'd reduce spam by a big percentage.

I wonder if I'm a member of your forum! I doubt I'd use "man" in the field you mention, but you'll spot me easily because of the fact that the only thing I'm going to talk about there, if you're already in my spreadsheet, is how to boycott ------. (self-censored, keep politics out of programming).

It DOES sound worryingly like it cannot be lots of different people coincidentally dropping the same kind of footprint.

Here's my biography field for the current forum:

Biography:Have signed a petition against calendars

whereas on other sites, I just don't fill it in at all.

However, I can't see how it's possible that the letters "man" are somehow exclusive to spammers, for that field?! I reckon you must have the same guy, managing to fool you. Hell I've seen people fool the forum owner (not spam, political activism, it's a different game) by being 15 different people across various proxies, and they actually speak in different languages as different members - it's a beautiful sight to see. But then i've seen much - i saw the hacking of a hamas forum into nonexistence from the point of view of the hacker! Not that I regret it. Hate is hate. Whoever breeds it.

the usergroupid is the one you are updating (UPDATE user SET usergroupid = 9). The JOIN is to extract the users with id of xx who have an row that matches the WHERE clause

LEFT JOIN userfield.userid ON user.id WHERE ...

Originally Posted by chrishirst

the usergroupid is the one you are updating (UPDATE user SET usergroupid = 9). The JOIN is to extract the users with id of xx who have an row that matches the WHERE clause

LEFT JOIN userfield.userid ON user.id WHERE ...

OK, so that's not actually changing the data then, just reading the original id of the member then, I'll test it out.

Originally Posted by witnesstheday

That intrigues me. What are their IPs? I presume they are different, since you are a forum owner and would see if they were the same. Are they one person on lots of proxies? People very rarely have common identifiers like that - spam is generally chaotic. How come they all have the word "man" in this field and nonspammers don't? Are you sure... (1) that it's different spammers? {using the same jive could indicate the same gob} (2) that your method won't remove legitimate members without your stopping it in time?

They do all have different IP's. I'm guessing someone wrote a spam program and sent it out to many various forums, even WMT had a few spammers with the "Man" in the bio field.

I gave my members a fair warning though to make sure they don't have the word "Man" in that particular field:
http://www.conspiracy-forums.com/for...intenance.html

And it's been over a week so far, and honestly, my forum hasn't grown very much over the past year (an entirely seperate issue). Out of the entire fourm member list it has, I'd guess only about 25-30 members are legitimate (just sorting through their post counts will show you that roughly only the first page actually has people that have contributed).

Originally Posted by witnesstheday

I wonder if I'm a member of your forum! I doubt I'd use "man" in the field you mention, but you'll spot me easily because of the fact that the only thing I'm going to talk about there, if you're already in my spreadsheet, is how to boycott ------. (self-censored, keep politics out of programming).

I don't think I've seen you there, haven't seen any boycott threads.

I've already taken a look at the profiles of my regulars and I haven't seen any with the "Man" word in their biographies. So for right now, it shouldn't be an issue, and any legitimately new members should take a look at the thread I posted in the announcements section on my forum.

Originally Posted by witnesstheday

It DOES sound worryingly like it cannot be lots of different people coincidentally dropping the same kind of footprint.

However, I can't see how it's possible that the letters "man" are somehow exclusive to spammers, for that field?! I reckon you must have the same guy, managing to fool you. Hell I've seen people fool the forum owner (not spam, political activism, it's a different game) by being 15 different people across various proxies, and they actually speak in different languages as different members - it's a beautiful sight to see. But then i've seen much - i saw the hacking of a hamas forum into nonexistence from the point of view of the hacker! Not that I regret it. Hate is hate. Whoever breeds it.

Truthfully, the "man" field isn't the only thing the spammers have in common. If you look at the list I posted earlier, and find a spammer (like this one for example), the "Interests" and "Name" fields always have the spammer's username; even the "recent blog post" field has it, and that's suppose to contain a feed link.

But the "Man" value is a value that is consistent amongst ALL these spammers that have been registering each day. I probably have over a thousand of these spammers on my forum. I figured the easiest way to clean up my forum would be to take this path, given that writing something in SQL to match those variables too is far beyond my capabilities.

it makes sense now - if it's some kind of software, then clearly that makes it eminently plausible! It's so sad, spam software. Software is... how can I put it into words. But how can people bastardize software like that? That's so wrong. So very wrong. I do pity people who make evil software - and I don't just mean MS word and Excel!!!

OK, I tried all the following queries (because I'm not sure what exactly is correct):

PHP Code:

UPDATE user
SET usergroupid = 9
LEFT JOIN userfield
ON userfield.[useridcolumn] = user.userid
WHERE field1
LIKE 'man'; 


PHP Code:

UPDATE user
SET usergroupid = 9
LEFT JOIN userfield.userid
ON user.id
WHERE field1
LIKE 'man'; 


PHP Code:

UPDATE user
SET usergroupid = 9
LEFT JOIN userfield.userid
ON user.userid
WHERE field1
LIKE 'man'; 


...and they all return errors. Chris, did I misread one of your posts on what the correct query to use is?

Maybe it'd be easier to understand if just the entire query is posted to try out...

the last one looks to be the correct syntax, what is the error this one throws?

The whole error it gives is:

PHP Code:

An error occurred while attempting to execute your query. The following information was returned. 
error number: 1064 
error desc: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN userfield.userid
ON user.userid
WHERE field1
LIKE 'man'' at line 3 


Ok

I gave in and threw some temporary tables and data up

UPDATE user as u
LEFT JOIN userfield as uf ON uf.userid = u.id
SET u.usergroupid = 9
WHERE instr(uf.field1,"man");

works Ok on my temp setup.

New Error:

PHP Code:

An error occurred while attempting to execute your query. The following information was returned. 
error number: 1054 
error desc: Unknown column 'u.id' in 'on clause' 


I really wish I could understand SQL better . I'm not exactly sure what those new variables are.

error desc: Unknown column 'u.id' in 'on clause'

u.id is not a variable...
it's the field "id" from the table that is aliased by "u" (user, in this case).
Look:

UPDATE --Ok, so we are going to update a table
  user as u  --Here, you declare that the table "user" will be referenced as "u" later. It's an alias. The field that we update is in that table too.
LEFT JOIN userfield as uf --Same thing here, userField is declared with the "uf" alias.
  ON uf.userid = u.id  --And you tell the DB that the mapping between the 2 tables should be done between the field user.id (u.id) and userField.userid (uf.userid)
SET u.usergroupid = 9  --And here, you tell the db that for each rows that match the criterias, the field "userGroupId" in the table "user" must be changed to the value 9
WHERE instr(uf.field1,"man"); --but only for rows in the table "userField" that have the string "man" into the field "userField.field1"

error desc: Unknown column 'u.id' in 'on clause'

And what that error is telling you, is that there is no column called "id" in the table called "user".