简单的PDO暴力查询不起作用

Question

I'm trying to check attempted logins by the user has committed. For some reason it skips even tough I have 7 entries in my database, +1 of reach try, with similar IP and user_id.

This is my query, full code can be found here.

// BRUTE FORCE CHECK
$remote_ip = $_SERVER['REMOTE_ADDR'];

$sql = "
SELECT  attempt_nr 
FROM    users_login_attempts 
WHERE   user_id = :userid 
AND     time > NOW() - INTERVAL 1 HOUR 
AND     user_ip = :userip
";

$results = $db_connect->prepare($sql);
if ($results->execute(array(':userid' => $user_id,':userip' => $remote_ip))){
    $count_tries = $results->rowCount();
    if ($count_tries < 5) {
        // DO SOMETHING IF LIMIT IS NOT REACHED
    }
    else { 
        // RETURN FAILURE 
    }

How come the user skips this part?

IMAGES: TABLE STRUCTURE

TABLE

MY CODE

THE VAR_DUMP RESULT

Answer 1

From phpdoc:

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Note the suggestion against using rowCount for select queries. Instead, I would change your code like this:

$sql = "
SELECT  count(*) AS attempt_nr
FROM    users_login_attempts 
WHERE   user_id = :userid 
AND     time > DATE_ADD(NOW(), INTERVAL -1 HOUR)
AND     user_ip = :userip
";

$results = $db_connect->prepare($sql);
if ($results->execute(array(':userid' => $user_id,':userip' => $remote_ip))) {
    $row = $results->fetch(PDO::FETCH_ASSOC);
    $count_tries = $row['attempt_nr'];
    if ($count_tries < 5) {
        // DO SOMETHING IF LIMIT IS NOT REACHED
    }
    else { 
        // RETURN FAILURE 
    }
}

In addition, note that with the code working correctly, you'll effectively lock your users out after 5 unsuccessful login attempts even if the user logged in successfully in between them, therefore you'll need to also ensure to clear the unsuccessful history on successful login or make your sql more complex to account for this.