By Sam308 on

Would anybody know of an SQL line statement that can delete users on cron?

Here is my problem:
I keep getting spam user registrations on my Drupal sites and either of the following two profile fields always get filled in with the character string 123456.

Profile Fields
profile_phone_number
profile_zipcode

I would like to place two SQL line statements in the SQL edit boxes using SQL Cron to delete all the user accounts that contain the character string 123456 in either their profile_phone_number or their profile_zipcode profile fields.

I would like one SQL line statement to delete all the user accounts that contain the character string 123456 in their profile field profile_phone_number

and another SQL line statement to delete all the user accounts that contain the character string 123456 in their profile field profile_zipcode

I have already looked at the spam prevention modules available and I am currently using the LoginToboggan and captcha modules on all websites. The spam bots that perform the user registrations bypass the captcha check and always come from different IP addresses.

Thanks in advance,

Sam

⋅ Categories: Drupal 6.x

Comments

Drave Robber’s picture

Drave Robber commented

DELETE FROM users WHERE profile_phone_number LIKE '%123456%' OR profile_zipcode LIKE '%123456%';

It's pretty simple, but I would first test this on a localhost copy of the site though.

Sam308’s picture

Sam308 commented

Thanks, I will give it try

Sam308’s picture

Sam308 commented

Drave:

The statement:

DELETE FROM users WHERE profile_phone_number LIKE '%123456%' OR profile_zipcode LIKE '%123456%';

did not work.

I got the following error message when I ran cron via Drupal:

user warning: Unknown column 'profile_phone_number' in 'where clause' query: DELETE FROM users WHERE profile_phone_number LIKE '%123456%' OR profile_zipcode LIKE '%123456%'; in /home/iamtotal/public_html/sites/all/modules/sqlcron/sqlcron.module on line 96.

Any thoughts or modification to make it work?

Thanks,

gbrands’s picture

gbrands commented

Hey Sam,

I think the sql should look something like this as the profile fields are stored in a separate table from the users.
* Please backup your db before running these queries for the first time, just to be safe and to make sure they work for your instance

DELETE FROM users, profile_values
USING profile_values 
    INNER JOIN users USING(uid) 
    INNER JOIN profile_fields USING(fid) 
WHERE profile_values.uid=users.uid 
 AND profile_values.value LIKE '%123456%' 
 AND (profile_fields.name = 'profile_phone_number' OR profile_fields.name = 'profile_zipcode')

That should remove all the user accounts and the profile_values records with the unwanted data; however, it will leave the profile_values records that do not match the above criteria so you will have entries that do not have an associated user. To remove those records as well, run the following after the above sql:

DELETE profile_values 
FROM profile_values
LEFT JOIN users ON users.uid = profile_values.uid
WHERE  users.uid IS NULL

Hope this helps!

-= Gerrit Brands

Sam308’s picture

Sam308 commented

Gerrit / Drave

It seems to work, thank you, I really appreciate the help from both of you..

I entered in the following code in the SQLcron module using two different line statements exacyly as shown, including the line breaks:

I assume that this was the proper way to enter the code into SQLcron.

Line 1 - Delete Spam Users (123456) Part 1

DELETE FROM users, profile_values
USING profile_values
 INNER JOIN users USING(uid)
 INNER JOIN profile_fields USING(fid)
WHERE profile_values.uid=users.uid
 AND profile_values.value LIKE '%123456%'
 AND (profile_fields.name = 'profile_phone_number' OR profile_fields.name = 'profile_zipcode')

Line 2 - Delete Spam Users (123456) Part 2

DELETE profile_values
FROM profile_values
LEFT JOIN users ON users.uid = profile_values.uid
WHERE users.uid IS NULL

I know that other Drupal users can use this code, because they are probably getting the same type of spam user account signups too.

Thanks again,

drupal-ite’s picture

drupal-ite commented

Two things, first, I wanted to just post a big thank you for this post. I have had the same exact problem from the same exact spammers. It's horrible. It makes it difficult to detect the real people from the bots. Using the two sql statements above cleaned out hundreds of bogus users. I appreciate the thoroughness. I ran the scripts manually from within my sql admin pages execution area.

However, secondly, I need to know exactly where to put the sql statments above so that it runs in the cron job. Is that in the file cron.php in the root of the drupal site? I'm not sure where it goes. I want it to run on cron jobs so that I don't have to keep doing this manually.

Any help for this neophyte is appreciated.

Michael

gbrands’s picture

gbrands commented

Hi Michael,

If you're using D6 you can use the SQL cron module and be done with it. However, if you don't want to use that module you will have to role your own and use hook_cron()

-= Gerrit Brands

Drave Robber’s picture

Drave Robber commented

I simply did not know the fields are in another table :/

(myself, I'm not using Profile module (or whatever its name) at the moment, so I couldn't look it up either)