Re-implementing Lastlog - Part I

This is the last feature to be migrated to Rosemary.

Goals

The concept of last log is simple. Log each login attempts to database. This is used to make brute force attacks more impractical.

Although this blog is not popular, protecting it against attacks seemed a waste of effort. But it always gives a better feeling of safety. Realistically if you are targeted, the chance of you to survive is very unlikely. This site is not protected against any DDoS attacks. Small websites just can't withstand it.

The spirit is to show that at least I am aware of those things and I did paid some effort on them.

Deconstruction of the current last_log impl

In cranberry, there were two things that I wrote which I thought was brilliant within my scope of "good code". One was the notification model. The other one is this last_log mechanism.

DESC last_log;

+------------+------------------------------------------------------+------+-----+-------------------+----------------+
| Field      | Type                                                 | Null | Key | Default           | Extra          |
+------------+------------------------------------------------------+------+-----+-------------------+----------------+
| id         | int(10) unsigned                                     | NO   | PRI | NULL              | auto_increment |
| ip         | varbinary(16)                                        | NO   |     | NULL              |                |
| status     | enum('IMMUNE','GOOD','FAILED','SUSPICIOUS','BANNED') | NO   |     | GOOD              |                |
| attempt_id | varchar(255)                                         | NO   |     | NULL              |                |
| attempt_pw | varchar(255)                                         | NO   |     | NULL              |                |
| date       | timestamp                                            | NO   |     | CURRENT_TIMESTAMP |                |
+------------+------------------------------------------------------+------+-----+-------------------+----------------+

I logged the attempted username and password because I was thinking to make a chart of the most password used in brute force attacks. Well, since I didn't encountered one of those. This time I will leave this field off to save some space.

session.php

Hmm, it seemed that there is these two variables plays the crucial part of this mechanism.
private $lifespan = 86400;
private $cool_down = 1800;

// Get ip's heat status
$st = $dbh->queryFirstRow(<<<___SQL___
    SELECT status FROM last_log
        WHERE ip=? AND UNIX_TIMESTAMP() - UNIX_TIMESTAMP(date) < {$this->lifespan}
    ORDER BY date DESC LIMIT 1
___SQL___
    , [ inet_pton($_SERVER['REMOTE_ADDR']) ]
);

// [...]

if(empty($st)) {
    $this->heat = 'GOOD';
} else {
    $this->heat = $st['status'];
}

I have no idea what was this $lifespan yet but I will get into it. The heat is display at the login page above¹.

Before the login checks, it checks whether the $ip is $healthy, then proceed the login process:

$healthy = $dbh->queryFirstRow(<<<___SQL___
    SELECT 1 FROM last_log
        WHERE ip=? AND status='BANNED' AND UNIX_TIMESTAMP() - UNIX_TIMESTAMP(date) < {$this->cool_down}
___SQL___
    , [ &$ip ]);

$healthy = empty($healthy);

This query checks whether the $ip is BANNED within $cool_down interval. If yes, then this $ip is considered not healthy. If not healthy, prohibit this login attempt.

The big magic

The entire mechanism works based on the following query. This is one of the things I like when composing a SQL query:

$dbh->exec(<<<___SQL___
    INSERT INTO last_log (ip, status, attempt_id, attempt_pw)
        VALUES (
            :ip, COALESCE (
                (SELECT
                    # If the total "FAILED" attempts is < 2 ( i.e. reached 3 times ), then set the status to "SUSPICIOUS"
                    CASE 1 WHEN 2 < attempt AND status = 'FAILED' THEN 'SUSPICIOUS'
                        # If the total "SUSPICIOUS" attempts is < 1 ( i.e. reached 2 times ), then set the status to "BANNED"
                        WHEN 1 < attempt AND status = 'SUSPICIOUS' THEN 'BANNED'
                        # If the status is "BANNED", keep it "BANNED"
                        WHEN status = 'BANNED' THEN 'BANNED'
                        # If the status is "SUSPICIOUS", keep it "SUSPICIOUS"
                        WHEN status = 'SUSPICIOUS' THEN 'SUSPICIOUS'
                        ELSE 'FAILED'
                    END
                FROM (
                    # Counts the login attempts and return the latest status
                    SELECT status, COUNT(*) attempt FROM last_log
                        WHERE ip=:ip AND UNIX_TIMESTAMP() - UNIX_TIMESTAMP(date) < {$this->lifespan}
                    GROUP BY status ORDER BY status DESC LIMIT 1) llog
                )
            , 'FAILED'), :attempt_id, :attempt_pw
        )
___SQL___
, [
    ':ip' => &$ip,
    ':attempt_id' => $username,
    // Encode it but will be further inspected if needed
    ':attempt_pw' => base64_encode($password)
]);

With only 3 components forms the entire workflow:

The idea

To clear this out. Let's try an example login prcodure:
1. 1st failed login -> insert failed attempt, failed count is 1
2. 2nd failed attempt -> insert failed attempt, failed count is 2
3. 3rd failed attempt -> insert failed attempt, failed count is 3
4. 4th failed attempt -> status is suspicious -> insert suspicious attempt -> suspcious count is 1
5. 5th failed attempt -> status is suspicious -> insert suspicious attempt -> suspcious count is 2
6. 6th failed attempt -> status is banned -> insert banned attempt -> banned count is 1

as time goes by, status count will be shifted to lesser specified by $cool_down and $lifespan.
Now if another login attempt goes on after the lifespan period. The count may now be:
suspicious count is 1, which in case considered as $healthy.

7. 7th failed attempt -> status is suspicous -> insert suspicious attempt -> suspicious count is 2
8. 8th failed attempt -> status is banned -> insert banned attempt

Thus, when an attacker is banned, before the $cool_down time ellapsed. Who will be banned for any further login.
The SUSPICIOUS status will also lasts for $lifespan period of time.

Here's the status plot of each login attempt:

Since the lifespan is generally larger then the $cooldown period, as the status goes more suspicious, the more easily for an attempt to get banned.

That's it for today's topic. I am going to re-implement this function in node.js + mongodb, since it is NoSQLs. Just like the Notification Model , I'll have to figure a way to archive this behaviour.

Continue to "Re-implementing LastLog" Part II >>

GOOD means you are free to login, FAILED means the last login attempt is failed, SUSPICIOUS means you are now suspicious to brute force attacks, BANNED means you are banned within $lifespan hours

Tag(s): rosemary MySQL lastlog cranberry php

斟酌鵬兄

Thu Mar 10 2016 11:08:19 GMT+0000 (Coordinated Universal Time)

Last modified: Sat Apr 09 2022 12:37:34 GMT+0000 (Coordinated Universal Time)