We had a lot of spam users in our multisite wordpress system. This was because we had self-registration enabled for a period. Not a smart thing to do…
anyway, I wrote a bash script in order to find which users id’s from the Mysql database that could potentially be spam users. With this list of ID’s I would run another SQL directly where I would set the “spam” to the number “1”. This would make sure the user could not log in, and after a month or so, the user could be deleted.
As you can see from one of the sql’s, the “%uib.no” is the trusted emails from our organization.
[bash]
#!/bin/bash
# Which one of all the users in the multisite blog system are spamusers?
# If the user doesn’t have any relations in users_blogg, most likely the user is a spam user.
# But the user can have a meta_value in wp_usermeta table like source_domain.
# These are valid users, and should not be marked as spam
# Find all users ID’s
dbquery=$(mysql -u root -p`cat /root/mysql` -e “use blog; select ID from wp_users;”)
array=($(for i in $dbquery; do echo $i; done))
# echo ${array[@]}
# for all the users, do:
for i in ${array[@]}
do
dbquery2=$(mysql -u root -p`cat /root/mysql` -e “use blog;SELECT wp_bp_user_blogs.id FROM wp_bp_user_blogs WHERE wp_bp_user_blogs.user_id = $i”;)
array2=($(for j in $dbquery2; do echo $j; done))
if [ ${#array2[@]} -eq 0 ];then
dbquery3=$(mysql -u root -p`cat /root/mysql` -e “use blog; select user_email from wp_users WHERE ID = $i and user_email not like ‘%uib.no’;”)
dbquery4=$(mysql -u root -p`cat /root/mysql` -e “use blog; select meta_value from wp_usermeta WHERE user_id = $i and meta_key like ‘source_domain’;”)
array4=($(for r in $dbquery4; do echo $r; done))
for n in ${array4[@]}
do
echo “User $i has a blogg with name $n! Please don’t delete this user.”
done
array3=($(for k in $dbquery3; do echo $k; done))
for m in ${array3[@]}
do
echo “User $i with email $m is not connected to any blog and should be marked as a spam user!”
done
fi
done
[/bash]