Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#1595 closed task (fixed)

Message limit quickfix

Reported by: planetcruiser Owned by: coroa
Priority: blocker Milestone: 0.5.7 - bugfixing
Component: BW Mail Keywords:
Cc:

Description

Issue:

  • We have some pretty active spammers right now. A proper defence mechanism will be introduced with #1588, but we need a quick solution for the current spam wave

Possible solution:

  1. Limit number of messages per hour to 5 for new members
  2. Others? Please add

Clues:

  • Solution A could be implemented with one SQL query that checks before sending

Change History (13)

comment:1 Changed 7 years ago by planetcruiser

  • Owner set to coroa
  • Status changed from new to assigned

comment:2 follow-up: Changed 7 years ago by coroa

  • follow_up changed from none to move to alpha

ok, just pushed a temporary fix as 2f12de2d, where members without any positive comments may not send more than 5 messages per hour.

it has become two mysql querys. i'd be interested how to best contract it into one.

comment:3 in reply to: ↑ 2 ; follow-up: Changed 7 years ago by planetcruiser

Replying to coroa:

it has become two mysql querys. i'd be interested how to best contract it into one.

i would use sub queries:

SELECT
    (
    SELECT
        COUNT(*)
    FROM
        comments
    WHERE
        comments.IdToMember = $IdSender
        AND
        comments.Quality = 'Good'
    ) AS numberOfComments,
    (
    SELECT
        COUNT(*)
    FROM
        messages
    WHERE
        messages.IdSender = $IdSender
        AND
        Status = 'Sent'
        AND
        DateSent > DATE_SUB(NOW(), INTERVAL 1 HOUR)
    ) AS numberOfMessages

the only problem here is that we always count the number of messages, even if the member has positive comments. but the query takes 6 ms on the live db as it is, so i think we can neglect this.

comment:4 in reply to: ↑ 3 Changed 7 years ago by coroa

Replying to planetcruiser:

i would use sub queries

adoption of your query is pushed as 32257a9.

comment:5 Changed 7 years ago by globetrotter_tt

Any change to get this online tonight? I am a bit tired to get fooled by the same scam guy for already 5 days now.

comment:6 follow-up: Changed 7 years ago by planetcruiser

  • Resolution set to fixed
  • Status changed from assigned to closed

tested and deployed live

the only gotcha is that a spammer can queue as many messages as (s)he manages before the mailbot sent 5 messages, because the query looks for "Sent" messages

comment:7 in reply to: ↑ 6 ; follow-up: Changed 7 years ago by coroa

Replying to planetcruiser:

the only gotcha is that a spammer can queue as many messages as (s)he manages before the mailbot sent 5 messages, because the query looks for "Sent" messages

this would be most easily remedied. what's the worst case delay?

comment:8 in reply to: ↑ 7 Changed 7 years ago by planetcruiser

Replying to coroa:

Replying to planetcruiser:

the only gotcha is that a spammer can queue as many messages as (s)he manages before the mailbot sent 5 messages, because the query looks for "Sent" messages

this would be most easily remedied. what's the worst case delay?

5 mins.

i patched via:

comment:9 follow-up: Changed 7 years ago by coroa

using the creation time is a bad idea, as one could circumvent the limit completely by creating drafts and delaying the sending. Fix:

https://gitorious.org/bewelcome/rox/commit/381993711b65de983bf7280e19362791ae5ba18e

comment:10 in reply to: ↑ 9 Changed 7 years ago by planetcruiser

Replying to coroa:

using the creation time is a bad idea, as one could circumvent the limit completely by creating drafts and delaying the sending

the problem here is that "DateSent?" is only set after the mailbot ran. is "updated" better? or do we need a new field?

comment:11 Changed 7 years ago by planetcruiser

i just had a merge conflict. so i will stay away from messages.model.php

i think we need a limit of 10 (or 20?) messages per day, too, because the current spammer is really insisting. i implemented this locally:

        $query = "
            SELECT
                (
                SELECT
                    COUNT(*)
                FROM
                    comments
                WHERE
                    comments.IdToMember = $id
                    AND
                    comments.Quality = 'Good'
                ) AS numberOfComments,
                (
                SELECT
                    COUNT(*)
                FROM
                    messages
                WHERE
                    messages.IdSender = $id
                    AND
                    (
                        Status = 'ToSend'
                        OR
                        Status = 'Sent'
                        AND
                        DateSent > DATE_SUB(NOW(), INTERVAL 1 HOUR)
                    )
                ) AS numberOfMessagesLastHour,
                (
                SELECT
                    COUNT(*)
                FROM
                    messages
                WHERE
                    messages.IdSender = $id
                    AND
                    (
                        Status = 'ToSend'
                        OR
                        Status = 'Sent'
                        AND
                        DateSent > DATE_SUB(NOW(), INTERVAL 1 HOUR)
                    )
                ) AS numberOfMessagesLastDay
            ";
        $row = $this->singleLookup($query);
        $comments = $row->numberOfComments;
        $lastHour = $row->numberOfMessagesLastHour;
        $lastDay = $row->numberOfMessagesLastDay;

        // TODO: Add config options for limits
        if ($comments < 1 && ($lastHour >= 5 || $lastDay >= 10)) {
            // TODO: Add translations
            return "You sent too many messages in a short period of time. "
                . "Please try again later.";
        } else {
            return false;
        }

what do you think? want to come to #bewelcome? :)

comment:13 Changed 7 years ago by globetrotter_tt

  • follow_up changed from move to alpha to release

Message limit works, spammer use profile comments now :P

Note: See TracTickets for help on using tickets.