Message limit quickfix

[planetcruiser]

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

[coroa]

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.

[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.

[coroa]

Replying to planetcruiser:

i would use sub queries

adoption of your query is pushed as 32257a9.

[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.

[planetcruiser]

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

[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?

[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:

•  https://gitorious.org/bewelcome/rox/commit/1cf817da3c906e87c0d98c2c26a14829ad15e762
•  https://gitorious.org/bewelcome/rox/commit/5f97842a90521667dee1901ba4ef835b939ed97a

[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

[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?

[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? :)

[planetcruiser]

introduced 15 messages per day limit via  https://gitorious.org/bewelcome/rox/commit/4ac1a8368a2cd5ff87f0fe37636b559e0ed27d7d

[globetrotter_tt]

Message limit works, spammer use profile comments now :P