MYSQL rows are not incrementing properly

Question

I am creating a piece of code that blocks a user a for 48 hours after attempting to login 5 times with the wrong password, within a 24hour period. If the user logs in successful within the 24hr and, it should reset the attempt count.

The issue I'm having ATM is that with the attempt count, It is only updating the first row of that user, if i attempt more times. Here is an example of whats going on:

User - Time - Attempt- count()
User 1 10:00pm Attempt 1 (5)
User 1 10:02pm Attempt 2 (4)
User 1 10:04pm Attempt 3 (3)
User 1 10:06pm Attempt 4 (2)
User 1 10:07pm Attempt 5 (1)
User 2 10:15pm Attempt 1 (2)
User 2 10:20pm Attempt 2 (1)

As you can see, all the attempts will increment (the numbers in the bracket) but the latest attempt will be set to one. How do I get it so that all the attempts are incremented so it looks like this.

User - Time - Attempt- count()
User 1 10:00pm Attempt 1 (5)
User 1 10:02pm Attempt 2 (5)
User 1 10:04pm Attempt 3 (5)
User 1 10:06pm Attempt 4 (5)
User 1 10:07pm Attempt 5 (5)
User 2 10:15pm Attempt 1 (2)
User 2 10:20pm Attempt 2 (2)

Here is a snippet of my code:

if (!$pw_ok)    {
            if (isset($_SERVER["REMOTE_ADDR"])) {
                    $str_RemoteHost = $_SERVER["REMOTE_ADDR"];
                } else {
                    $str_RemoteHost = '';
                }
        $qry_WriteToDatabase = "INSERT INTO cms_user_login_attempts
                                (
                                    cula_user_id,
                                    cula_date_time,
                                    cula_remote_host,
                                    cula_attempt_count
                                )
                        VALUES      (
                                    " . $db->SQLString($row->user_id) . ",
                                    Now(),
                                    " . $db->SQLString($str_RemoteHost, true) . ",
                                    'cula_attempt_count'
                                    )";
        $db->query($qry_WriteToDatabase);

        $qry_UpdateCount = "UPDATE 
                                cms_user_login_attempts
                            SET 
                                cula_attempt_count = cula_attempt_count + 1
                            WHERE 
                                cula_user_id = " . $db->SQLString($row->user_id) . " ";
        $db->query($qry_UpdateCount);                           

        $qry_CheckDatabase = "  SELECT 
                                    CASE WHEN count(*) >= 5 THEN 0 ELSE 1 END as allowed_login 
                                FROM
                                    cms_user_login_attempts
                                WHERE
                                    cula_date_time >= DATE_SUB(CURRENT_TIMESTAMP, interval 48 hour) 
                                AND 
                                    cula_user_id = " . $db->SQLString($row->user_id) . "";
            $rs_CheckDatabase = $db->query($qry_CheckDatabase);

            if (! (isset($qry_CheckDatabase) && $qry_CheckDatabase)) {
            $errors->defineError("invalid_user_pass", "Too many attempts, account locked for 48hours.", array("username","password"));
            }
    }

I'm not understanding - what do the numbers in the parentheses represent in your example? They are clearly decrementing, not incrementing..? — cbreezier, Jan 08 '15 at 23:31
The numbers in the parentheses is the total amount of failed attempts by that user.Edit: I have written the table out in the same way my mysql table displays the information. — PHP Learner, Jan 08 '15 at 23:32
Failed attempts or attempts left? `As you can see, all the attempts will increment (the numbers in the bracket)` I'm not seeing this. Also in your examples, can you tell us whether they were successful or unsuccessful attempts? — cbreezier, Jan 08 '15 at 23:35
So you want the number in the parentheses to be the `SUM` of the attempts, not a count down of attempts left? — Sean, Jan 08 '15 at 23:41

Sean · Accepted Answer · 2015-01-09T19:07:50.130

To set the cula_attempt_count to sum of the attempts, you will need to do a subquery to get the sum, ie.

UPDATE cms_user_login_attempts 
SET cula_attempt_count = (
    SELECT COUNT(*)
    FROM cms_user_login_attempts
    WHERE cula_user_id = $db->SQLString($row->user_id)
    AND cula_date_time > DATE_SUB(NOW(), INTERVAL 24 HOUR))
WHERE  cula_user_id = $db->SQLString($row->user_id)
AND cula_date_time > DATE_SUB(NOW(), INTERVAL 24 HOUR)

you need to nest the inner query, so it is treated as a tmp table

UPDATE cms_user_login_attempts 
SET cula_attempt_count = (
  SELECT * FROM (
    SELECT COUNT(*) as `sum`
    FROM cms_user_login_attempts
    WHERE cula_user_id = $db->SQLString($row->user_id)
    AND cula_date_time > DATE_SUB(NOW(), INTERVAL 24 HOUR)
  )
)
WHERE  cula_user_id = $db->SQLString($row->user_id)
AND cula_date_time > DATE_SUB(NOW(), INTERVAL 24 HOUR)

Hello, I am getting the error: You can't specify target table 'cms_user_login_attempts' for update in FROM clause — PHP Learner, Jan 09 '15 at 10:06
Apparently MySQL does not allow you to `SELECT` from the same table that you want to `UPDATE`. I have updated with the fix. see also http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause — Sean, Jan 09 '15 at 19:05

score 0 · Answer 2 · answered Jan 08 '15 at 23:53

In your insert statement for the new record you are trying to set the value of cula_attempt_count to a string: 'cula_attempt_count'.

This is an invalid value for a numeric field, thus MySQL is setting the value of the field to 0, which is then incremented to 1 by the next query.

To do what your are trying to do you will need to add a subquery to your insert something like this:

"INSERT INTO cms_user_login_attempts (ula_user_id, cula_date_time, cula_remote_host, cula_attempt_count )
VALUES (" . $db->SQLString($row->user_id) . ",
        Now(),
        " . $db->SQLString($str_RemoteHost, true) . ",
        IFNULL((SELECT cula_attempt_count FROM cms_login_attempts
            WHERE ula_user_id=" . $db->SQLString($row->user_id) . "
            AND cula_date_time >= DATE_SUB(CURRENT_TIMESTAMP, interval 48 hour)
            ORDER BY cula_date_time DESC LIMIT 1), 0))"

This subquery will return the cula_attempt_count from the user's last login attempt within 48 hours, and the IFNULL is there to handle cases where the user has not logged in during the previous 48 hours, so it returns 0.

Sorry, I put this question up before I realised how tired I was and went to sleep, thanks for this. — PHP Learner, Jan 09 '15 at 09:11
Hi im getting this error: You can't specify target table 'cms_user_login_attempts' for update in FROM clause (1093) — PHP Learner, Jan 09 '15 at 10:36

MYSQL rows are not incrementing properly

2 Answers2