Detect number of concurrent, failed, login attempts

[AnthonySterling]

Hi there SQL folk,

I'm currently logging all login activity into a table, what I'd like to do is have a flag returned if a certain user has had a certain number of consecutivefailed attempts in a given period, say 24 hours?

My rather poor SQL skills have this as a skeleton:-

SELECT CASE WHEN (SELECT COUNT(was_successful) WHERE was_successful = 0 AND SOME_CONSECUTIVE IDENTIFIER AND supplied_username = 'silverbulletuk' AND INTERVAL 24 HOURS) > 0
THEN 1
ELSE 0
END AS lock_account

CREATE TABLE IF NOT EXISTS `user_login_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `supplied_username` varchar(50) COLLATE utf8_bin NOT NULL,
  `ip_address` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `user_agent` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `created_on` datetime NOT NULL,
  `was_successful` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=11 ;

I'd really appreciate some help here.

Thanks,


Anthony.

[crmalibu]

Did you mean consecutive instead of concurrent?

[AnthonySterling]

*whistles* Yup, sorry, I'll edit the post.

Thanks Chris.

[crmalibu]

Could this be refined into:
find out if the X most recent entries in a given time period, contain a successful login?

I would imagine that's what you probably want.

[AnthonySterling]

No, that won't do it I'm afraid. Although, maybe I am approaching this in the wrong way...

Yes, yes I am.

Regardless of the time frame, I just need to get the last x logins for the chosen user and see if they are all marked as failed.

Thanks!

SELECT CASE WHEN 1
IN (
    SELECT was_successful
    FROM user_login_log
    WHERE supplied_username = 'anthony.sterling'
    ORDER BY created_on DESC
    LIMIT 3
)
THEN 0
ELSE 1
END AS lock_account

Oh, "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery".

Thoughts?

[crmalibu]

This would give you zero if they should be blocked

SELECT COALESCE(SUM(was_successful), 1) AS not_blocked
  FROM user_login_log
 WHERE supplied_username = ?
 ORDER
    BY created_on DESC
 LIMIT 3

[AnthonySterling]

That doesn't work for me crmalibu.

I'm using this to determine whether or not to lock the account, so maybe that factors in. I seem to be getting strange results though...

SELECT was_successful AS lock_account
FROM user_login_log
WHERE supplied_username =  ?
ORDER  BY created_on DESC 
LIMIT 0 , 3

...gives the expected...

lock_account
-------------
0
0
0

...but the following seems strange to me!

SELECT  DISTINCT was_successful AS lock_account
FROM user_login_log
WHERE supplied_username =  ?
ORDER  BY created_on DESC 
LIMIT 0 , 3

lock_account
-------------
0
1

[crmalibu]

SELECT COALESCE(SUM(was_successful), 1) AS not_blocked
  FROM ( SELECT was_successful
           FROM user_login_log
          WHERE supplied_username = ?
          ORDER
             BY created_on DESC
          LIMIT 3 ) t