How to lock out login attempts with SQL

Question

I don't want by server to get brute forced but I don't want to use a CAPTCHA as they're not user friendly / I don't want to rely on another company - because I'm not going to implement my own captcha system.

I've thought of creating a attempts table with this structure:

________________________________________________________________
|         IP         |      ATTEMPTS      |        LOCK        |
|‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾|
|       1.1.1.1      |         0          |          2         |
|        ....        |        ....        |        ....        |
‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾          Yes I had fun making this

And let the user have 5-10 attempts, before resetting to 0 and increasing the lock time by a factor of 2 - it's in minutes.

Is this a good method to lock out a brute force?

I'd have logic like this on my server: - abstracted

....
* on request to server do below  *

db.query("SELECT * FROM attempts WHERE IP= *REQUEST.IP* ",function(result){
    if (result.rows.length > 0 && result.rows[0].attempts > 5) {
        if (CheckIfExpired(results.rows[0].lock)) {
            if (loginFail) MultplyLockBy2();
            else RemoveFromAttemptsTable();
        } else res.status("403").send("You're locked out");
    } else normalLoginAttempt().ifFal(add1toAttempts());
....

I have a feeling there's a more efficient way to do this natively in SQL, I'm a novice with SQL. (I'm using postgresql).

After looking at this I'm guessing I'd need a start-time field aswell, so I can calculate when the lock has expired.

EDIT: after doing research I've read / am thinking about implementing fail2ban which check's Apache/nginx logs and blocks users; a cookie that allows a user to connect back into their account without Captcha / proof of work, and then make others get captcha; or just a captcha in conjunction with a lockout;

Answer 1

There are some problems I see with your approach.

Firstly, it provides a route to a denial of service attack; someone just needs to keep supplying bad passwords from an ip address to lock it out. Assuming a 1:1 correlation between users and ip addresses is a bit silly.

Secondly, in order to reset an account you need to delete data which is useful in terms of an audit trail. Were you planning on reseting the account after a successful login?

Thirdly, it does not protect against a distributed attack - or one with spoofed addresses.

If it were me, I would go with....

SELECT SUM(score)
FROM (
   SELECT COUNT(*) * ipweight AS score
   FROM logins
   WHERE failed=true
   AND ip=INET_ATON(*client_address*) 
   AND ip <> *trusted_device*
   AND timestamp>NOW - INTERVAL *config_value* MINUTES
   UNION
   SELECT COUNT(*) * acctweight AS score
   FROM logins
   WHERE failed=true
   AND username=*supplied_username* 
   AND ip <> *trusted_device*
   AND timestamp>NOW - INTERVAL *config_value* MINUTES
   UNION
   SELECT COUNT(*) * allweight AS score
   FROM logins
   WHERE failed=true
   AND ip <> *trusted_device*
   AND timestamp>NOW - INTERVAL *config_value* MINUTES
)

(This can be extended with other atrributes such as user agent)

Set a lower threshold than the lockout at which you require the user to complete additional verification (such as a capcha).

Just log the attempt (ip address, username, timestamp, outcome) in the database (not the lockout time).

Note that representing the ip address in its native (integer) format in your database is almost always a good idea. Here it offers the opportunity of aggregating failures across an arbitrary netblock.

Answer 2

This method isn't very good.

Remember that I don't have to send you requests through your website. I can route my requests through a proxy and send you completely fake IPs. Moreover, the same applies to local storage, cookies (and sessions - which rely on cookies). I can send you requests that have absolutely no identifiying information.

So, this essentially leaves you with two options.

First, lock based on username. This option isn't good, because an anonymous attacker could block you from logging in without it being your fault.

Second, and the good one, is to apply a captcha. I think writing your own captcha is a waste of time and not efficien because today there are very good systems that can interpret written text on captchas and easily solve them. I recommend Google's ReCaptcha - it's proven and tested.