Ravens PHP Scripts: Forums
 

 

View next topic
View previous topic
Post new topic   Reply to topic    Ravens PHP Scripts And Web Hosting Forum Index -> Security - PHP Nuke
Author Message
Gremmie
Former Moderator in Good Standing


Joined: Apr 06, 2006
Posts: 2415
Location: Iowa, USA

PostPosted: Thu Aug 09, 2007 5:47 pm Reply with quote

For the first time ever. It was done by a user. It looks like he had a script that just walked through all the news articles (increased the sid by one) and blasted away.

What do you suggest I do? And how to clean up about 400 comments? Evil or Very Mad

_________________
Only registered users can see links on this board! Get registered or login! - An Event Calendar for PHP-Nuke
Only registered users can see links on this board! Get registered or login! - A Google Maps Nuke Module 
View user's profile Send private message
Gremmie
PostPosted: Thu Aug 09, 2007 6:00 pm Reply with quote

Well since it was all done by a single user it was easy to delete them all.

DELETE FROM nuke_comments WHERE name = 'grytner'
 
Gremmie
PostPosted: Thu Aug 09, 2007 6:08 pm Reply with quote

Well I banned his 2 IP's in sentinel, and put his username and email address in the string blocker. Anything else? Sad
 
montego
Site Admin


Joined: Aug 29, 2004
Posts: 9449
Location: Arizona

PostPosted: Thu Aug 09, 2007 6:10 pm Reply with quote

Yeah, see this: Only registered users can see links on this board! Get registered or login!

He only got through the first page. Did you have the spam captcha from 2.10.x turned on for news? The guy I ran into in the above post was definitely manually spamming.

_________________
Only registered users can see links on this board! Get registered or login!
Only registered users can see links on this board! Get registered or login! 
View user's profile Send private message Visit poster's website
Gremmie
PostPosted: Thu Aug 09, 2007 6:12 pm Reply with quote

I guess I am going to write a script to display the last x new news comments and watch for this kind of crap now.
 
Gremmie
PostPosted: Thu Aug 09, 2007 6:14 pm Reply with quote

I have comments set so that only members can make them, but no captcha.

I can see someone paying someone in a 3rd world country to manually spam websites. grrrrrr.
 
Gremmie
PostPosted: Thu Aug 09, 2007 6:16 pm Reply with quote

Most of the comments were about a second apart, so I think he had a script.
 
montego
PostPosted: Thu Aug 09, 2007 6:17 pm Reply with quote

Guardian has already done this. Its on Only registered users can see links on this board! Get registered or login!
 
montego
PostPosted: Thu Aug 09, 2007 6:18 pm Reply with quote

Gremmie wrote:
I have comments set so that only members can make them, but no captcha.

I can see someone paying someone in a 3rd world country to manually spam websites. grrrrrr.


Yep. This is why this other person didn't get very far. He had to hand enter them and the captcha. I only had to delete 5 comments. It is worth it IMO turning on the spam captcha.
 
Gremmie
PostPosted: Thu Aug 09, 2007 6:23 pm Reply with quote

Yes I reluctantly agree. It is on now.

Thanks for the tip for Guardian's script.
 
fkelly
Former Moderator in Good Standing


Joined: Aug 30, 2005
Posts: 3312
Location: near Albany NY

PostPosted: Thu Aug 09, 2007 7:19 pm Reply with quote

I use Guardian's comments module and will endorse it too.

Quote:
Well since it was all done by a single user it was easy to delete them all.

DELETE FROM nuke_comments WHERE name = 'grytner'


Unfortunately this only gives you half of a result. After I did something like that I found out that the stories table also stores the number of comments. I had to write an ad hoc that went something like this to clean up. (This was some time ago so I forget all the gory details).

Code:
require("config.php");

require("db/db.php");
$justreport = true;
$fixit = true; // set to true to set comments in stories table to number found in comments
$result = $db->sql_query('SELECT title, sid, comments FROM '.$prefix.'_stories');
while ($row = $db->sql_fetchrow($result)) {
        $counter = 0;
         $sid = intval($row['sid']);
         $comments = intval($row['comments']);
$counter = intval($db->sql_numrows($db->sql_query('SELECT sid FROM '.$prefix.'_comments WHERE sid =\''.$sid.'\'')));
if ($justreport) {
echo $sid . ' ' . $row['title'] . ' --comments table=  ' .  $counter . ' --stories table = ' . $comments . '<br>';   }
if ($fixit) {
    if ($comments != $counter) {
    $result2 = $db->sql_query('UPDATE '.$prefix.'_stories SET comments=\''.$counter.'\' WHERE sid=\''.$sid.'\'');
    if (!$result2) {
        echo mysql_errno($result2) . mysql_error($result2) ; }
    echo 'fixing comments field in ' . $row['title'] . '<br>';
    }
}
}
 


Use at your own risk ... there's a report only flag you can set to see what the results would be before you do any updating. You might want to verify what I'm saying using PHPmyadmin before proceeding too and make a backup of your tables first. I guess ideally the two scripts (deleting all comments by a user and decrementing the number in the story table) should be integrated.
 
View user's profile Send private message Visit poster's website
Gremmie
PostPosted: Thu Aug 09, 2007 7:22 pm Reply with quote

Ah....that's dumb to store the number of comments in the story table. It is totally redundant and can get out of sync, as in a case like this.

Thanks for the tip though.
 
Gremmie
PostPosted: Thu Aug 09, 2007 7:24 pm Reply with quote

It might be easier to write a script that updated the story table based on how many comments are actually in the comment table for that story. I'll look into that since I already blew away the spam.
 
Gremmie
PostPosted: Thu Aug 09, 2007 8:04 pm Reply with quote

Ok, I'm sure there is like one whiz-bang SQL statement that would do this, but I couldn't figure it out, so here is a PHP script that gets the comment counts back in sync. Smile

Code:


<?php
include 'mainfile.php';
include 'header.php';
OpenTable();

$sql = "SELECT sid, comments FROM {$prefix}_stories ORDER BY sid";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
   $sid = $row['sid'];
   $comments = $row['comments'];

   $sql = "SELECT COUNT(*) FROM {$prefix}_comments WHERE sid = '$sid'";
   $result2 = $db->sql_query($sql);
   $row2 = $db->sql_fetchrow($result2);
   $actComments = $row2[0];

   if ($comments != $actComments)
   {
      echo "Updating story $sid because it thinks it has $comments comments, but it really has $actComments<br />";
      $sql = "UPDATE {$prefix}_stories SET comments = '$actComments' WHERE sid = '$sid'";
      if (false === $db->sql_query($sql))
      {
         echo "Oops it didn't work for story $sid<br />";
      }
   }
}

CloseTable();
include 'footer.php';
?>
 
Gremmie
PostPosted: Thu Aug 09, 2007 8:10 pm Reply with quote

fkelly, wow...I'm so rattled I didn't even read your posting thoroughly. I just reinvented what you already wrote.

Embarassed Embarassed Embarassed Rolling Eyes
 
fkelly
PostPosted: Thu Aug 09, 2007 8:48 pm Reply with quote

Gremmie: LOL. That's okay. You got me concerned enough that I just went in and verified that there is a comments field in the stories table. From a design point of view you are perfectly right of course, it's redundant and subject to error. I guess from a programming point of view it's easier to just pull the story table up and look at the comments field for a value rather than going to the comments table and counting the number for a given sid. But that's neither here nor there.
 
montego
PostPosted: Fri Aug 10, 2007 6:22 am Reply with quote

Hey, BTW, any improvements upon the comments module would most definitely be appreciated by Guardian. He has a lot on his plate right now. Some of what I was thinking:

1. Allow for multiple select of comments to delete.
2. Finish up the Poll comments.
3. Possibly add one for Forum posts.
4. Now, given Gremmie's recent "adventure", possibly add a delete by user.

Just thought I'd get it documented somewhere and if anybody felt a strong need for one of these and wanted to pick it up for their own benefit... I've had 1 - 3 on my personal list for months now and you see how far I have gotten? Laughing
 
Gremmie
PostPosted: Fri Aug 10, 2007 6:43 am Reply with quote

fkelly wrote:
Gremmie: LOL. That's okay.


Yeah I wasn't thinking straight. This was the first time something like this has happened to my site since it was started 1.5 years ago. I've been lucky I guess. LOL. I'm a little more rational now. Smile

fkelly wrote:

You got me concerned enough that I just went in and verified that there is a comments field in the stories table. From a design point of view you are perfectly right of course, it's redundant and subject to error. I guess from a programming point of view it's easier to just pull the story table up and look at the comments field for a value rather than going to the comments table and counting the number for a given sid. But that's neither here nor there.


A database purist would laugh at Nuke I think. Having a redundant comments field in the stories table may make it easier for one thing, but now you have two tables to update whenever you add or delete a comment. Can we change that?
 
Gremmie
PostPosted: Fri Aug 10, 2007 6:44 am Reply with quote

montego wrote:
Hey, BTW, any improvements upon the comments module would most definitely be appreciated by Guardian. He has a lot on his plate right now. Some of what I was thinking:

1. Allow for multiple select of comments to delete.
2. Finish up the Poll comments.
3. Possibly add one for Forum posts.
4. Now, given Gremmie's recent "adventure", possibly add a delete by user.

Just thought I'd get it documented somewhere and if anybody felt a strong need for one of these and wanted to pick it up for their own benefit... I've had 1 - 3 on my personal list for months now and you see how far I have gotten? Laughing


I'm interested now. Smile
 
fkelly
PostPosted: Fri Aug 10, 2007 7:40 am Reply with quote

Quote:
A database purist would laugh at Nuke I think. Having a redundant comments field in the stories table may make it easier for one thing, but now you have two tables to update whenever you add or delete a comment. Can we change that?


We can change ANYTHING; the only question would be do we want to fix it for the next patch release or wait for a major release. Then we'd have to search for any place where the comments field in the stories table is used and change it to do a count on the comments table. And test the heck out of the results ... a lot of the news screens pull comments and there's inconsistencies in how various themes handle this (I vaguely recall) so we'd need to make sure we didn't unhinge anything there. Probably we'd want to look at Guardian's module too, though that's not part of the distribution currently, and make sure we didn't mess that up.

I suppose the appropriate way to do this would be to put an issue in Mantis and see how and when Raven wants to handle interweaving it with the rest of the work.

Smile I guess that since M. wrote the specs for the larger revision here he doesn't have to do the programming.
 
montego
PostPosted: Sat Aug 11, 2007 5:30 pm Reply with quote

killing me

Just a comment about "database purist". That would be the full normalization camp. However, there are trade-offs. For example, let us just say that we have a site which has loads of activity, especially in the reading category. One does not mind taking a little extra time to post something right? But, you might want your readers to have the fastest speed possible (not to mention be able to serve up many of these at the same time). Which is faster and less resource usage?

1. Pulling in a counter off the main comment table, or

2. Pull the comment table information and then go get the count for the comments.

Everything in my mind boils down to the objectives and situation at hand. Hmmmm, sounds like requirements and design. Laughing
 
Gremmie
PostPosted: Sat Aug 11, 2007 7:38 pm Reply with quote

Why can't you do both in one query? This works:

SELECT s.title, count(*) FROM nuke_stories AS s, nuke_comments AS c WHERE s.sid = 1 AND c.sid = 1 GROUP BY s.title

I think this is still better than storing the same data in two places.
 
montego
PostPosted: Sat Aug 11, 2007 7:51 pm Reply with quote

Yes, of course that works, functionally, but the point is not can you combine this all into one SQL. The point is that if the goal is to reduce resource utilization and performance on a very head loaded site, it can actually be better to store redundant data.

Again, these are "trade-offs" that every good designer must make for him/herself based on the requirements.

I am not disagreeing with you guys on the original premise of this post/thread. Gremmie made a comment about "database purist" and I wanted to elaborate with some practical experience.
 
Gremmie
PostPosted: Sun Aug 12, 2007 9:13 am Reply with quote

I certainly agree that you can loosen your database scheme if performance shows that it needs it. In the absence of that data it is just premature optimization and making trouble for yourself. Given Nukes other famous notorious inefficiencies, I'd wager this little feature was just the author not knowing any better. Smile

Anyway.....the vandal came back, but he was blocked by the string blocker I set up on his user name.
 
Gremmie
PostPosted: Sun Aug 12, 2007 9:18 am Reply with quote

Forgot to add: I am so grateful for NukeSentinel. Smile
 
Display posts from previous:       
Post new topic   Reply to topic    Ravens PHP Scripts And Web Hosting Forum Index -> Security - PHP Nuke

View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum


Powered by phpBB © 2001-2007 phpBB Group
All times are GMT - 6 Hours
 
Forums ©