SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Red face Detect number of concurrent, failed, login attempts

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

    Code MySQL:
    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

    Code MySQL:
    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.
    Last edited by AnthonySterling; Oct 20, 2009 at 11:18. Reason: Tiredness, and crmalibu was surely s******ing at my poor english.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did you mean consecutive instead of concurrent?

  3. #3
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    *whistles* Yup, sorry, I'll edit the post.

    Thanks Chris.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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!

    Code MySQL:
    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?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This would give you zero if they should be blocked
    Code SQL:
    SELECT COALESCE(SUM(was_successful), 1) AS not_blocked
      FROM user_login_log
     WHERE supplied_username = ?
     ORDER
        BY created_on DESC
     LIMIT 3

  7. #7
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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...

    Code MySQL:
    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!

    Code MySQL:
    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
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •