deleting comment spam
|
billimek
Joined: 2005-04-13
Posts: 10 |
Posted: Wed, 2006-10-04 19:20
|
|
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, |
|
|
billimek
Joined: 2005-04-13
Posts: 10 |
Posted: Wed, 2006-10-04 20:56
|
|
Ok I got impatient and came up with some SQL (mysql). In a previous thread valliant mentioned: Quote:
GalleryEntity <-> GalleryComment <-> GalleryChildEntity have a 1 to 1 relationship. I came up spam subjects that were in the vast majority of my spam and used them in the query below. I'm sure there is a much better way to approach this but it worked for me quickly. Subsitute g2_ with your table prefix and of course only do this if you have a backup and know what you are doing. CREATE TEMPORARY TABLE tmptable SELECT g_id FROM `g2_Comment` gc WHERE gc.g_subject like '%Levitra%' or gc.g_subject like '%personals%' or gc.g_subject like '%poker%' or gc.g_subject like '%blackjack%' or gc.g_subject like '%gambling%' or gc.g_subject like '%viagra%' or gc.g_subject like '%phentermine%' or gc.g_subject like '%casino%' or gc.g_subject like '100%' or gc.g_subject like '%slots%' or gc.g_subject 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; It may be necessary to wrap the whole thing in a transaction. If anyone reading this discovers a better approach please post so others can benefit from your knowledge. I only did this because I had thousands of spam comments before I realized that capata was not enabled for commenting. There has got to be a better way to deal with a ton of spam other than ging to SQL though. |
|
|
doktorj
Joined: 2005-02-07
Posts: 6 |
Posted: Wed, 2006-11-29 07:34
|
|
thanx for posting this, total useful. CREATE TEMPORARY TABLE tmptable DELETE g2Entity DELETE g2ChildEntity DELETE g2Comment DROP TEMPORARY TABLE tmptable; |
|
|
enek
Joined: 2006-12-19
Posts: 4 |
Posted: Thu, 2006-12-28 02:20
|
|
This is very useful code. I'm surprised something like this isn't built into the commenting system. I had over 100 spam comments in my gallery and this got rid of them all. -- |
|
|
kkinderen
Joined: 2004-08-22
Posts: 10 |
Posted: Sun, 2007-03-04 16:28
|
|
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 DELETE g2_Entity DELETE g2_ChildEntity DELETE g2_Comment DROP TEMPORARY TABLE tmptable; |
|
|
rpyne
Joined: 2003-02-10
Posts: 22 |
Posted: Sat, 2007-03-10 01:40
|
|
For those who might be using Postgresql, here is the version for it: SELECT g_id INTO TEMPORARY tmptable FROM g2_comment WHERE DELETE FROM g2_entity WHERE g_id IN (SELECT g_id FROM tmptable); DELETE FROM g2_childentity WHERE g_id IN (SELECT g_id FROM tmptable); DELETE FROM g2_comment WHERE g_id IN (SELECT g_id FROM tmptable); DROP TABLE tmptable; |
|
|
billimek
Joined: 2005-04-13
Posts: 10 |
Posted: Fri, 2007-03-23 13:45
|
|
How ironic! |
|
|
valiant
Joined: 2003-01-04
Posts: 32509 |
Posted: Fri, 2007-03-23 14:34
|
|
i've deleted the spam that was posted before billimek's comment. -------------- |
|
|
robert070612
Joined: 2003-08-05
Posts: 565 |
Posted: Fri, 2007-03-23 14:51
|
|
billimek---- ironic is not the word I would valiant---- thank you for taking this stuff ----best wishes, Robert http://www.hcs.k12.nc.us http://www.ussbremerton.org http://www.glasshillgolf.com http://www.chikungunya.net http://www.coralblog.com http://www.wauknet.com http://clearblogs.com http://www.serbisyopilipino.org http://www.brianprucey.com http://www.goldcoastonlinetutoring.com http://www.riversideca.gov http://www.sambets.com http://www.smaaonline.com |
|
|
valiant
Joined: 2003-01-04
Posts: 32509 |
Posted: Fri, 2007-03-23 15:24
|
|
for those who wonder what RBL means: -------------- |
|
|
toastmaster
Joined: 2003-05-01
Posts: 219 |
Posted: Sat, 2007-04-28 19:06
|
|
MANY thanks - top post. Bookmarked! I just deleted nearly 3,000 spams with this. |
|
|
Hooloovoo
Joined: 2007-06-26
Posts: 1 |
Posted: Tue, 2007-06-26 08:49
|
|
Yes, thank you. 60588 (yes 60k +) spam comments deleted from my site with this method. |
|
|
Kadina
Joined: 2006-04-26
Posts: 31 |
Posted: Tue, 2007-06-26 14:48
|
|
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! |
|
|
cleb
Joined: 2005-07-26
Posts: 14 |
Posted: Thu, 2007-06-28 21:43
|
|
I have been trying to run this to remove 70k spam messages... and I get the following error : SQL query: DELETE g2_Entity FROM g2_Entity INNER JOIN tmptable ON g2_Entity.g_id = tmptable.g_id; MySQL said: Documentation any idea's... I am not a sql expert in the least so I am kinda stuck thanks |
|
|
toastmaster
Joined: 2003-05-01
Posts: 219 |
Posted: Thu, 2007-06-28 21:57
|
|
cleb wrote:
I have been trying to run this to remove 70k spam messages... and I get the following error : Make sure you're using kkinderen's correct version of the post, and not the first one. |
|
|
cleb
Joined: 2005-07-26
Posts: 14 |
Posted: Thu, 2007-06-28 23:12
|
|
toastmaster wrote:
I am... I just gave up and am running the commentblaster script and removing all comments... I did not have many real ones anyways... although I would live to get the running |
|
|
toastmaster
Joined: 2003-05-01
Posts: 219 |
Posted: Thu, 2007-06-28 23:20
|
|
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. |
|
|
alevander5
Joined: 2007-07-15
Posts: 1 |
Posted: Sun, 2007-07-15 15:20
|
|
I think, It will not help to delete all spam. |
|
|
VegasBoxers
Joined: 2006-04-20
Posts: 18 |
Posted: Thu, 2007-07-19 18:03
|
|
doktorj wrote:
thanx for posting this, total useful. Where is this code supposed to be executed? Myphpadmin? |
|
|
egger
Joined: 2007-07-20
Posts: 1 |
Posted: Fri, 2007-07-20 06:56
|
|
cleb wrote:
cleb - i get the same error. I think I discovered that it is because we are using mysql 3.23. Multiple table deletes as written above only work in mysql 4.0 and up. I spent so long trying to get it running before reading that in the mysql documentation. I have not been able to work around yet. the syntax with g_id IN (SELECT * from tmptable) does not work either. |
|
|
guru_gary
Joined: 2007-07-24
Posts: 2 |
Posted: Tue, 2007-07-24 06:31
|
|
egger wrote:
cleb - i get the same error. I think I discovered that it is because we are using mysql 3.23. Multiple table deletes as written above only work in mysql 4.0 and up. I spent so long trying to get it running before reading that in the mysql documentation. 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!"
|
|
|
digitalcyanide
Joined: 2005-01-10
Posts: 1 |
Posted: Thu, 2007-07-26 12:47
|
|
I just threw pretty much all my available votes at this project.... I have about 75,000 comments to delete and am not savvy enough to use the code posted in this thread... grrrr @ spammers. I should add: the subject lines of the spam comments I am getting, are random strings of letters.... so in my understanding, that code won't work for those anyway, right? |
|
|
aharami
Joined: 2007-04-16
Posts: 47 |
Posted: Tue, 2007-07-31 20:23
|
|
do you guys get spam comments even with captcha set to high for guest comments? |
|
|
guru_gary
Joined: 2007-07-24
Posts: 2 |
Posted: Sun, 2007-08-05 14:09
|
|
digitalcyanide wrote:
the subject lines of the spam comments I am getting, are random strings of letters.... so in my understanding, that code won't work for those anyway, right? digitalcyanide: The code above probably would work for you. The reason I think it would work is because of the searching for the strings "url" and "http". The reason (most) spammers are doing this is to provide links to web sites, and those are the 2 most common ways to do that. So chances are very good that most or all of your spam comments have a link in them, and the code posted here (pick the one that works for your database) should take them out. aharami wrote:
do you guys get spam comments even with captcha set to high for guest comments? aharami: I don't get any more spam comments since enabling captcha ... but in the week between the time they started and the time I noticed and shut them down, I had about 30,000 spam comments that I wanted to delete. If I remember correctly, didn't Gallery v1 have a feature built-in to detect / remove spam comments? |
|
|
floridave
Joined: 2003-12-22
Posts: 27300 |
Posted: Mon, 2007-08-20 04:20
|
|
Quote:
If I remember correctly, didn't Gallery v1 have a feature built-in to detect / remove spam comments? Yes. Dave |
|
|
iemand
Joined: 2007-03-12
Posts: 15 |
Posted: Mon, 2007-08-20 12:31
|
|
Pardon the interruption, but WHY is this code not built in G2 ??? This looks like one of the basic simplest mass comment delete options possible, yet it's not there for the user? |
|
|
iemand
Joined: 2007-03-12
Posts: 15 |
Posted: Mon, 2007-08-20 12:40
|
|
The following content worked better for me. 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 ). <?
#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!"
?>
|
|
|
floridave
Joined: 2003-12-22
Posts: 27300 |
Posted: Mon, 2007-08-20 13:27
|
|
Quote:
but WHY is this code not built in G2 ??? Because it has to be written by somebody. Quote:
then open the /something.php from your browser, et voila! Thanks for the contribution. Quote:
then change all the xxx_ values in the top of the file to yours I think an improvement might be to do a include() of config.php then the user does not have to edit the file manually. Another step, for security, might be to place the file in /lib/support. With the akismet addition to the comment module you can 'rescan' for comment spam. Dave |
|
|
billimek
Joined: 2005-04-13
Posts: 10 |
Posted: Mon, 2007-08-20 21:16
|
|
iemand wrote:
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 ). 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? |
|
|
toastmaster
Joined: 2003-05-01
Posts: 219 |
Posted: Wed, 2007-09-19 21:26
|
|
OK, the Akismet is looking good for the spam, only problem is that I'm having the same old issue with Gallery as I've always had, which is that it gives me white pages and times out (even though I set php.ini etc). When I check "delete all comments" (there were about 1,800) it deletes a third of them, but then says there are none left to delete, then I have to wait 8 minutes for Akismet to run again, then delete more spam, then run Akismet again...I'm just worried that this extra load on Akismet might not make gallery popular with them!! Anyway, looking much better. I just wish I could find where the timeout was stored, as I already have But anyway, 2.3 is looking good! |
|
|
Svobi
Joined: 2004-07-15
Posts: 46 |
Posted: Sun, 2007-09-23 15:16
|
|
Deletes 10413 comments at about 30MB mySQL trash with this - thanks a lot! :D |
|
|
grishkin
Joined: 2006-10-27
Posts: 23 |
Posted: Sat, 2007-11-03 22:43
|
|
Can you help with the syntax for including config.php and replacing "xxx_mysqlserveraddress","xxx_username","xxx_password", and "xxx_database_name" accordingly? Thanks. |
|
|
voice903fm
Joined: 2007-01-06
Posts: 126 |
Posted: Thu, 2007-11-22 05:47
|
|
What's the best version of this script to use with mysql Ver 12.22 Distrib 4.0.27? |
|
|
bosworth
Joined: 2007-04-15
Posts: 8 |
Posted: Wed, 2007-12-12 20:25
|
|
I've got Gallery 2.2.1, php 4.4.1 and mySQL 4.1.22 for the lines the script completed successfully and removed 176865 spam comments! Nice |
|
|
james789
Joined: 2005-11-10
Posts: 96 |
Posted: Wed, 2008-01-23 19:17
|
|
I have been trying to implement this PHP file to delete comment spam but to no avail. Tech info: My theme has been modified and does not give a box for a subject. 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 |
|
|
Kakastavi
Joined: 2008-03-17
Posts: 2 |
Posted: Mon, 2008-03-24 14:36
|
|
Iemand, thank you so much for the script, it worked wonder for me! I must tell I'm totally unfamiliar with php and such... :o) |
|
|
ppmnt
Joined: 2003-01-12
Posts: 238 |
Posted: Fri, 2008-10-03 17:48
|
|
Awesome, thank you so much for this! I was dreading cleaning up my comments after importing my old messy G1... |
|
|
nivekiam
Joined: 2002-12-10
Posts: 16504 |
Posted: Fri, 2008-10-03 19:45
|
|
Also check out Comment Blaster: http://codex.gallery2.org/Downloads:CommentBlaster That and other tools are linked to here: http://codex.gallery2.org/Downloads 2.3 is suppose to have better anti-spam features. |
|
|
phylo
Joined: 2002-09-25
Posts: 19 |
Posted: Thu, 2008-11-13 06:06
|
|
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 DELETE g2_ChildEntity DELETE g2_Comment DROP TEMPORARY TABLE tmptable; |
|
|
voice903fm
Joined: 2007-01-06
Posts: 126 |
Posted: Sat, 2008-11-22 20:42
|
|
i ran this file and got the following return. Table 'TEMPCOM' already exists and the spam is still there. What went wrong? |
|
|
mglas
Joined: 2007-10-21
Posts: 4 |
Posted: Tue, 2008-12-09 01:51
|
|
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 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; mysql> use gallery2 Database changed mysql> drop table TEMPCOM; mysql> quit 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 |
|
|
AstridGuri
Joined: 2008-05-19
Posts: 4 |
Posted: Fri, 2009-09-11 20:28
|
|
OK -- I'm totally confused. my web manager migrated me from Gallery 1 to 2.2.4 I enabled captcha, and thought it was working. but they eventually found me, the nasty spammers, so I guess captcha didn't work. Now there are hundreds, of course. I just disabled comments (so I can't see them anymore), but evidently their bulk has put me way over my size limit!! I don't want to pay more just to keep spam! So -- I did download the "comment blaster" and uploaded the entire php file to my gallery directory, which I can see in "files" on my Speedy puppy account. I understand that it is a test run, but won't actually delete comments until the "true" is changed to "false". I know that I need to edit that and upload a new file with that change. However, when I'm in gallery admin mode, I can't see the comment blaster to run in the "test" mode, and I don't know how to make it run just from my browser. I'm running Safari on a g4 mac, if that helps. As for the other script. I don't know how to run anything like that, and might not be able to do it, since my host administrator might have things set up in a different way? But I'd like some clues so I could try, at least. Astrid |
|
|
nivekiam
Joined: 2002-12-10
Posts: 16504 |
Posted: Fri, 2009-09-11 20:33
|
|
AstridGuri
Joined: 2008-05-19
Posts: 4 |
Posted: Fri, 2009-09-11 20:59
|
|
hmmmm--- so more than just disable -- uninstall? I'll try that right now |
|
|
nivekiam
Joined: 2002-12-10
Posts: 16504 |
Posted: Fri, 2009-09-11 21:00
|
|
AstridGuri
Joined: 2008-05-19
Posts: 4 |
Posted: Fri, 2009-09-11 21:42
|
|
wow. it seems to have worked. 19.6 MB worth of spam comments gone in a flash. I had received a notice I wonder if it has to do with the permanent link on Bing images? THANKS!!!!!! Astrid |
|
|
AstridGuri
Joined: 2008-05-19
Posts: 4 |
Posted: Sat, 2009-09-12 01:08
|
|
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. |
|
|
spommere
Joined: 2007-12-06
Posts: 4 |
Posted: Sun, 2010-01-10 00:32
|
|
This morning I realized had 93 spam comments in my Gallery2, so I started removing them manually. Only later I found out that the actual count was more than 10000, 9693 alone on one photo. Thanks to all who posted the SQL to remove comments. I used a simplified SQL to remove the spam: CREATE TEMPORARY TABLE tmptable DELETE g2_ChildEntity DELETE g2_Comment DROP TEMPORARY TABLE tmptable; Plain and simple, all comments were gone. Final step: uninstalling the Comments plugin. Thanks! |
|
