deleting comment spam

Is there any way to delete large blocks of comments? I have litterally thousands of spam comments on my gallery and there is no way to delete them other than clicking on each one at a time, then clicking on the confirm buton. That approach is not an option with thousands of items to delete.

Searching the forum about a way to use SQL to delete the comments were met with calls never to do anything with the g2 tables directly or else it will all get corrupted.

Is there any solution to this big problem?

Thanks,
Jeff Billimek

thanx for posting this, total useful.
i extended mine a bit to add a couple more search phrases (for people posting urls)
and it also searches through the comment text rather than just the subjects:

CREATE TEMPORARY TABLE tmptable
SELECT gid
FROM `g2Comment` gc
WHERE
gc.gsubject like '%url%' or
gc.gsubject like '%http%' or
gc.gsubject like '%personals%' or
gc.gsubject like '%poker%' or
gc.gsubject like '%blackjack%' or
gc.gsubject like '%gambling%' or
gc.gsubject like '%viagra%' or
gc.gsubject like '%phentermine%' or
gc.gsubject like '%casino%' or
gc.gsubject like '100%' or
gc.gsubject like '%slots%' or
gc.gsubject like '%Cialis%' or
gc.gsubject like '%url%' or
gc.gcomment like '%http%' or
gc.gcomment like '%personals%' or
gc.gcomment like '%poker%' or
gc.gcomment like '%blackjack%' or
gc.gcomment like '%gambling%' or
gc.gcomment like '%viagra%' or
gc.gcomment like '%phentermine%' or
gc.gcomment like '%casino%' or
gc.gcomment like '100%' or
gc.gcomment like '%slots%' or
gc.gcomment like '%Cialis%';

DELETE g2Entity
FROM g2Entity
INNER JOIN tmptable ON g2Entity.gid = tmptable.gid;

DELETE g2ChildEntity
FROM g2ChildEntity
INNER JOIN tmptable ON g2ChildEntity.gid = tmptable.gid;

DELETE g2Comment
FROM g2Comment
INNER JOIN tmptable ON g2Comment.gid = tmptable.gid;

DROP TEMPORARY TABLE tmptable;

I had to make changes to most of the field names but was able to successfully delete a couple thousand spam comments with this. Thank you! Below are the changes I had to make. Careful, I really don't know what I'm doing, just got lucky.

CREATE TEMPORARY TABLE tmptable
SELECT g_id
FROM `g2_Comment` gc
WHERE
g_subject like '%url%' or
g_subject like '%http%' or
g_subject like '%personals%' or
g_subject like '%poker%' or
g_subject like '%blackjack%' or
g_subject like '%gambling%' or
g_subject like '%viagra%' or
g_subject like '%phentermine%' or
g_subject like '%casino%' or
g_subject like '100%' or
g_subject like '%slots%' or
g_subject like '%Cialis%' or
g_subject like '%url%' or
g_comment like '%http%' or
g_comment like '%personals%' or
g_comment like '%poker%' or
g_comment like '%blackjack%' or
g_comment like '%gambling%' or
g_comment like '%viagra%' or
g_comment like '%phentermine%' or
g_comment like '%casino%' or
g_comment like '100%' or
g_comment like '%slots%' or
g_comment like '%Cialis%';

DELETE g2_Entity
FROM g2_Entity
INNER JOIN tmptable ON g2_Entity.g_id = tmptable.g_id;

DELETE g2_ChildEntity
FROM g2_ChildEntity
INNER JOIN tmptable ON g2_ChildEntity.g_id = tmptable.g_id;

DELETE g2_Comment
FROM g2_Comment
INNER JOIN tmptable ON g2_Comment.g_id = tmptable.g_id;

DROP TEMPORARY TABLE tmptable;

How ironic!

billimek---- ironic is not the word I would
willingly use for these people - sick? - criminal?

valiant---- thank you for taking this stuff
seriously. My analysis appended. Use as
appropriate, I have already on my server;~)

----best wishes, Robert

http://www.hcs.k12.nc.us
206.107.110.4 (apparently RBL clean)
United States - North Carolina - Raeford - Hoke County Schools
US Sprint 206.104.0.0/14

http://www.ussbremerton.org
71.18.54.157 (apparently clean)
United States - Kentucky - Hopkinsville - Ecommerce Corporation
US XO 71.0.0.0/8

http://www.glasshillgolf.com
12.39.78.19 (apparently RBL clean)
United States - New York - Clinton - Total Solutions
unlimitedlifestyleincome.com
US AT&T 12.0.0.0/8

http://www.chikungunya.net
84.40.5.130 (apparently RBL clean)
United Kingdom - Hostway
wsh1008.lon.gb.securedata.net (yes, right, really secure)
UK 84.40.0.0/17

http://www.coralblog.com
203.174.83.43 (apparently RBL clean)
Singapore - Singapore - Singapore - Mizuwork Singapore
Singapore 203.174.83.0/24

http://www.wauknet.com
168.215.63.10 (apparently RBL clean)
United States - Wisconsin - Waukesha - Comstar
sirius.comstarllc.com
US Time Warner Telecom 168.215.0.0/16

http://clearblogs.com
206.222.26.18 (apparently RBL clean)
United States - Enet Inc
server.wiseme.com
US eNet Inc 206.222.0.0/19

http://www.serbisyopilipino.org
65.254.250.104 (apparently RBL clean)
United States - Massachusetts - Burlington - Endurance International Group Inc
65-254-250-104.yourhostingaccount.com
US Endurance 65.254.224.0/19

http://www.brianprucey.com
64.202.163.154 (apparently RBL clean)
United States - Arizona - Scottsdale - Go Daddy Software
linhost125.prod.mesa1.secureserver.net (yes, really, secure... all 2.8million sites)
US Go Daddy 64.202.160.0/19

http://www.goldcoastonlinetutoring.com
64.202.163.9 (apparently RBL clean)
United States - Arizona - Scottsdale - Go Daddy Software
linhost218.prod.mesa1.secureserver.net (yes, really, secure... all 2.8million sites)
US Go Daddy 64.202.160.0/19

http://www.riversideca.gov
192.248.248.34 (apparently RBL clean)
United States - California - Riverside - City Of Riverside
portcullis.riversideca.gov
US 192.248.128.0/17

http://www.sambets.com
64.251.197.195 (apparently RBL clean)
United States - Texas - Dallas - 2 Coolweb Inc
US 64.251.192.0/20

http://www.smaaonline.com
65.114.252.14 (good grief... this is the only one that's on an RBL ...since 2002!)
NOMOREFUNN TXT= "qwest.net - added 2002-04-11; spam support - netblk-q0228-65-125-188-0"
United States - Colorado - Sterling Computer Center
markmcdonaldphysicaltherapy.com
US Qwest 65.112.0.0/12

MANY thanks - top post. Bookmarked! I just deleted nearly 3,000 spams with this.

I'll add my thanks as well. I put this query in a php page that I set up to run nightly as a cron job. This keeps my gallery spam free with little intervention. Thank you!

Make sure you're using kkinderen's correct version of the post, and not the first one.

Ooh, commentblaster sounds interesting. I'll give it a go.

Now I have to work out why CAPTCHA is not working for comments on the vslider3 theme...hmmm.

Where is this code supposed to be executed? Myphpadmin?
Thanks,
VB

I am running MySQL 3.23 and had the same issues. We worked around it with some PHP code. It works for me, but use are your own risk (built from kkinderen's code). Put this code in a .php file on your web server (replacing "database_username" and "database_password" and "database_name" with your values) and open the file in a web browser:

<?
#connect to database
$connection = mysql_connect("","database_username","database_password") or die ("dind't connect");
$db - mysql_select_db("database_name",$connection) or die ("couldn't connect to mysql");

#create temprary database
$query = "CREATE TABLE TEMPCOM
SELECT g_id
FROM `g2_Comment` gc
WHERE
g_subject like '%url%' or
g_subject like '%http%' or
g_subject like '%personals%' or
g_subject like '%poker%' or
g_subject like '%blackjack%' or
g_subject like '%gambling%' or
g_subject like '%viagra%' or
g_subject like '%phentermine%' or
g_subject like '%casino%' or
g_subject like '100%' or
g_subject like '%slots%' or
g_subject like '%Cialis%' or
g_subject like '%url%' or
g_comment like '%http%' or
g_comment like '%personals%' or
g_comment like '%poker%' or
g_comment like '%blackjack%' or
g_comment like '%gambling%' or
g_comment like '%viagra%' or
g_comment like '%phentermine%' or
g_comment like '%casino%' or
g_comment like '100%' or
g_comment like '%slots%' or
g_comment like '%Cialis%';";
$result = mysql_query($query) or die (mysql_error());

#select everything from temp table
$query = "SELECT * FROM TEMPCOM";
$result = mysql_query($query) or die (mysql_error());

#walk through temp table items
while ( $row = mysql_fetch_array($result)) {

#print item number that is being deleted
print "deleting " . $row[0] . "<BR>\n";

#delete from Entity table
$query1 = "DELETE FROM g2_Entity WHERE g_id=$row[0]";
$result1 = mysql_query($query1) or die (mysql_error());


#delete from ChildEntity table
$query2 = "DELETE FROM g2_ChildEntity WHERE g_id=$row[0]";
$result2 = mysql_query($query2) or die (mysql_error());


#delete from Comment table
$query3 = "DELETE FROM g2_Comment WHERE g_id=$row[0]";
$result3 = mysql_query($query3) or die (mysql_error());

}
#delete temp table
$query4 = "DROP TABLE TEMPCOM";
$result4 = mysql_query($query4) or die (mysql_error());
print "DONE!"

do you guys get spam comments even with captcha set to high for guest comments?

Yes.
G2 now has http://akismet.com/ in the comment module to protect against spam as well as an improved captcha.
download a nightly or wait for G2.3

Dave
_____________________________________________
Blog & G2 || floridave - Gallery Team

The following content worked better for me.
For me it worked fine and safely on a MySQL 5.0 database, but it did need the ending "?>" php-part.

Here's my How to:

Just copy paste the code below to a /something.php file in your G2 main dir, then change all the xxx_ values in the top of the file to yours (they can be found in /config.php ).
Then open the /something.php from your browser, et voila!

<?
#connect to database
$connection = mysql_connect("xxx_mysqlserveraddress","xxx_username","xxx_password") or die ("dind't connect");
$db - mysql_select_db("xxx_database_name",$connection) or die ("couldn't connect to mysql");

#create temprary database
$query = "CREATE TABLE TEMPCOM
SELECT g_id
FROM `g2_Comment` gc
WHERE
g_subject like '%url%' or
g_subject like '%http%' or
g_subject like '%handbags%' or
g_subject like '%poker%' or
g_subject like '%blackjack%' or
g_subject like '%gambling%' or
g_subject like '%viagra%' or
g_subject like '%phentermine%' or
g_subject like '%casino%' or
g_subject like '%order%' or
g_subject like '%slots%' or
g_subject like '%Cialis%' or
g_subject like '%url%' or
g_comment like '%http%' or
g_comment like '%handbags%' or
g_comment like '%poker%' or
g_comment like '%blackjack%' or
g_comment like '%gambling%' or
g_comment like '%viagra%' or
g_comment like '%phentermine%' or
g_comment like '%casino%' or
g_comment like '%buy%' or
g_comment like '%antibiotic%' or
g_comment like '%order%';";
$result = mysql_query($query) or die (mysql_error());

#select everything from temp table
$query = "SELECT * FROM TEMPCOM";
$result = mysql_query($query) or die (mysql_error());

#walk through temp table items
while ( $row = mysql_fetch_array($result)) {

#print item number that is being deleted
print "deleting " . $row[0] . "<BR>\n";

#delete from Entity table
$query1 = "DELETE FROM g2_Entity WHERE g_id=$row[0]";
$result1 = mysql_query($query1) or die (mysql_error());


#delete from ChildEntity table
$query2 = "DELETE FROM g2_ChildEntity WHERE g_id=$row[0]";
$result2 = mysql_query($query2) or die (mysql_error());


#delete from Comment table
$query3 = "DELETE FROM g2_Comment WHERE g_id=$row[0]";
$result3 = mysql_query($query3) or die (mysql_error());

}
#delete temp table
$query4 = "DROP TABLE TEMPCOM";
$result4 = mysql_query($query4) or die (mysql_error());
print "DONE!"
?>

I love how stuff like this evolves and grows into what we see today! That's for making it even easier to delete the spam. I'm anxiously awaiting the akimset (however you spell it) functionality of G2 v2.3

By the way, is anyone else still getting spam even with capatcha turned on? I just noticed I had a ton of spam today and the past few days and I just verified last night actually that the capatcha is active and working - have the spammers found a way around it?

Deletes 10413 comments at about 30MB mySQL trash with this - thanks a lot! :D

What's the best version of this script to use with mysql Ver 12.22 Distrib 4.0.27?

I have been trying to implement this PHP file to delete comment spam but to no avail.

Tech info:
http://www.whatsthatpicture.com/?q=gallery&g2_view=comment.ShowAllComments
Gallery version = 2.2-rc-1 core 1.1.26
PHP version = 4.4.7 cgi
Webserver = Apache/1.3.37 (Unix) mod_auth_passthrough/1.8 mod_log_bytes/1.2 mod_bwlimited/1.4 mod_ssl/2.8.28 OpenSSL/0.9.7e-p1 PHP-CGI/0.1b
Database = mysql 4.1.22, lock.system=flock

My theme has been modified and does not give a box for a subject.
I allow non-registered users to comment (it's the whole point of the site) but have captcha enabled.
I regularly get blocks of spam comments, 20 or 30 at a time, typically with three or four links as the start of the comment.

Right now I have been in to phpMyAdmin and deleted all but one of the comments to clean things up, but at the same time leave one in for testing. As a short-term fix I would like to have this PHP based method working, and then cron it so it is automated. Beyond that I would like to have Akismet or at least a more robust Captcha.

As far as the PHP file goes, I have tried the code given by guru_gary above, but when I call the page in a browser I just get a blank page. I have tried putting an echo right at the beginning and not even that gets written out to teh page. Frustratingly I can't get at my PHP error logs so can't see what might be happening behind the scenes. Can anyone suggest some debugging tips? I'm afraid my PHP is rather basic!

Thanks, James

Awesome, thank you so much for this! I was dreading cleaning up my comments after importing my old messy G1...

http://www.discobug.com

Here is a slight mod to get rid of a couple more... just added the '%url%' to the g_comment like section..

CREATE TEMPORARY TABLE tmptable
SELECT g_id
FROM `g2_Comment` gc
WHERE
g_subject like '%url%' or
g_subject like '%http%' or
g_subject like '%personals%' or
g_subject like '%poker%' or
g_subject like '%blackjack%' or
g_subject like '%gambling%' or
g_subject like '%viagra%' or
g_subject like '%phentermine%' or
g_subject like '%casino%' or
g_subject like '100%' or
g_subject like '%slots%' or
g_subject like '%Cialis%' or
g_subject like '%url%' or
g_comment like '%http%' or
g_comment like '%personals%' or
g_comment like '%poker%' or
g_comment like '%blackjack%' or
g_comment like '%gambling%' or
g_comment like '%viagra%' or
g_comment like '%phentermine%' or
g_comment like '%casino%' or
g_comment like '100%' or
g_comment like '%slots%' or
g_comment like '%Cialis%' or
g_comment like '%url%';
DELETE g2_Entity
FROM g2_Entity
INNER JOIN tmptable ON g2_Entity.g_id = tmptable.g_id;

DELETE g2_ChildEntity
FROM g2_ChildEntity
INNER JOIN tmptable ON g2_ChildEntity.g_id = tmptable.g_id;

DELETE g2_Comment
FROM g2_Comment
INNER JOIN tmptable ON g2_Comment.g_id = tmptable.g_id;

DROP TEMPORARY TABLE tmptable;

Hi voice903fm:

Something must have gone wrong during the delete spam part, as the table TEMPCOM is deleted after the succesful completion of the php cleanup script.

To delete the table log into mysql:

# ./mysql -u <username> -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 51536
Server version: 5.1.25-rc-standard Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gallery2 |
| mysql |
| wp |
+--------------------+

mysql> use gallery2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------------+
| Tables_in_gallery2 |
+--------------------------+
| TEMPCOM |
...
...

mysql> drop table TEMPCOM;
mysql> show tables;
+--------------------------+
| Tables_in_gallery2 |
+--------------------------+
| g2_AccessMap |
...
...

mysql> quit
Bye

I also added some lines to the php script above, to include more spam and which totally cleaned up my albums of spam, just copy and replace this with the subject and comment like from the above script:

g_subject like '%url%' or
g_subject like '%http%' or
g_subject like '%handbags%' or
g_subject like '%poker%' or
g_subject like '%blackjack%' or
g_subject like '%gambling%' or
g_subject like '%viagra%' or
g_subject like '%phentermine%' or
g_subject like '%casino%' or
g_subject like '%order%' or
g_subject like '%slots%' or
g_subject like '%Cialis%' or
g_subject like '%url%' or
g_subject like '%sexo%' or
g_subject like '%mortgage%' or
g_subject like '%teen%' or
g_subject like '%discount%' or
g_subject like '%orgasm%' or
g_comment like '%http%' or
g_comment like '%handbags%' or
g_comment like '%poker%' or
g_comment like '%blackjack%' or
g_comment like '%gambling%' or
g_comment like '%viagra%' or
g_comment like '%phentermine%' or
g_comment like '%casino%' or
g_comment like '%buy%' or
g_comment like '%antibiotic%' or
g_comment like '%sexo%' or
g_comment like '%chicas%' or
g_comment like '%mortgage%' or
g_comment like '%teen%' or
g_comment like '%discount%' or
g_comment like '%orgasm%' or
g_comment like '%order%';";

If you just want to get rid of all comments, just uninstall the Comments plugin.

Site Admin > Plugins
____________________________________________
Like Gallery? Like the support? Donate now!!! See G2 live here

Yes, disable just deactivates the plugin, but keeps all the data and settings in the database. Uninstalling should remove all data associated to a plugin from the database.
____________________________________________
Like Gallery? Like the support? Donate now!!! See G2 live here

exploring the stats of my site more closely, I saw that the last two months' stats included a single IP "host" that was 100 x anything else, and not there in the July stats at all. 94.23.51.115 I reported it to project honeypot, since that coincides with the thousands of spam comments to my images.
Astrid