What do I do next to block sign MySQL query attempts on the DB from the login form?

Question

I've managed to implement the code for connecting to the IP database, and register failed attempts, with the corresponding user_id from users table, and also the IP address & timestamp.

I then query the database and it checks with a 10-minute interval to the last login attempt.

The user can just attempt another sign in attempt, and it logs that to the database.

I'm not sure what I have to now to limit the database being queried again after the 3 failed times?

What should I be looking up?

Code is:

function timestampCount($ip,$id,$mysqli): int
    {

        mysqli_query($mysqli, "INSERT INTO `ip` (`id`, `address` ,`timestamp`)VALUES ('$id','$ip',CURRENT_TIMESTAMP)");

        $result = mysqli_query($mysqli, "SELECT COUNT(*) FROM `ip` WHERE `address` LIKE '$ip' AND `timestamp` > (now() - interval 10 minute)");
        $count = mysqli_fetch_array($result, MYSQLI_NUM);
        if($count[0] > 3){
            echo "Your are allowed 3 attempts in 10 minutes";
            return 1;
        }
        else {
            return 0;
        }
    }

I then call that with

$attempts = (new ip_request)->timestampCount($ip, $id,$mysqli);

on my login page.

As I thought I might get a 1 or a 0, and then depending on which one, set the $hideLogin = true;, and work with something like that.

But every time the user refreshes the page, the variable is reset.

So I'm not sure what next to do with the login page to stop the database from being queried again for subsequent login attempts.

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Jun 03 '21 at 13:40
"*But every time the user refreshes the page, the variable is reset."* That's how variables work and that's why you are using a database. What exactly do you mean here? — Dharman, Jun 03 '21 at 13:42
Ok i'll fix that, but that doesn't answer my original question, my login page still has no way to implement a block after 3 signups have been detected. — Spinstaz, Jun 03 '21 at 15:58

Spinstaz · Accepted Answer · 2021-06-04T13:44:08.943

I managed to get a working solution.

I split the timestampCount() into a getter and setter.

I then called the getTimestampCount() to check on the login page, if the user had 3 entries or more in the database.

If they did, it displays a message, and stops the rest of the code executing password calls to the database.

If there aren't more than 3 entries in the database, the wrong attempts are tracked and set by setTimestampCount()

function getTimestamp($email,$mysqli): bool
    {

        $result = $mysqli->prepare ("SELECT COUNT(*) FROM `ip` INNER JOIN users ON users.id = ip.id WHERE `email` LIKE ? AND `timestamp` > (now() - interval 10 minute)");
        $result ->bind_param("s", $email);
        $result->execute();
        $final = $result->get_result();
        $count = mysqli_fetch_array($final, MYSQLI_NUM);

        if($count[0] > 2){

            return true;
        }
        else {
            return false;
        }

    }

function setTimestampCount($ip,$id,$mysqli)
    {

        $query = $mysqli->prepare ("INSERT INTO `ip` (`id`, `address` ,`timestamp`)VALUES (?,?,CURRENT_TIMESTAMP)");
        $query ->bind_param("is", $id, $ip);
        $query->execute();

    }

Everything tested and working.

What do I do next to block sign MySQL query attempts on the DB from the login form?

1 Answers1