CODEWITHSUNDEEP
phpmysqlsqlmysqli
user3297181
user3297181

Reputation: 32

Database doesn't update second time with mysqli

I´m currently creating a login script in PHP. When a user tries to login <5 times in a short period of time the account get´s temporary locked and the user get´s an email in order to reactivate the account instantly. This is all working fine, the user fails to login, the database is updated and the email is sent. Then when I try to do this very same thing a second time with the second or third email, the database simply doesn't update. The only way to get it to work for the next email is to remove all values created from the first email in the database.

Here is the relevant code for the login php file. 

<?php
include_once 'db_connect.php';
include_once 'functions.php';

...

}   elseif (login($email, $password, $mysqli) == 2)
        $theid = getId($email, $mysqli); /// this is a function that get´s id based on email located in functions.php
        if ($stmt = $mysqli->prepare("SELECT emailSent FROM login_attempts WHERE user_id = ? LIMIT 1")) 
        { 
            $stmt->bind_param('i', $theid);                 
            $stmt->execute();       
            $stmt->bind_result($skickat); 
            $stmt->fetch();
            $stmt->close();
            if ($skickat == '0') /// email sent
            {
                $thehash = md5( rand(0,10000) ); /// Just a random md5 hash for reactivation
                $emailsent = 1;
                if ($insert_stmt = $mysqli->prepare("UPDATE `secure_login`.`login_attempts` SET `ResetHash` = ?, `emailSent` = ? WHERE `login_attempts`.`user_id` = ?")) {
                    $insert_stmt->bind_param('sss', $thehash, $emailsent, $theid);
                    if (! $insert_stmt->execute()) {
                        header('Location: ../error.php?err=Registration failure: INSERT');
                        exit();
                    }
                }
                else {
                    header('Location: ../error.php?err=Registration failure: INSERT');
                    exit();
                }
            else /// alread sent,  we already sent email. Show error that decribe email is sent and it´s locked
            {           
                $_SESSION['lgnerror']= '<p class="error">Error 2</p>';
                header('Location: ../login.php');
                exit();
            }  
/// Email code starts from here
$_SESSION['lgnerror']= '<p class="error">Error 1</p>';

Here is the getId function located in functions.php

function getId($email, $mysqli) 
{   
    if ($stmt = $mysqli->prepare("SELECT id FROM members WHERE email = ? LIMIT 1")) 
    { 
        $stmt->bind_param('i', $email);                
        $stmt->execute();       
        $stmt->bind_result($resultat); 
        $stmt->fetch();
        $stmt->close();
        return $resultat; 
    } 
    else 
    {        
        // Could not create a prepared statement        
        header("Location: ../error.php?err=Database error: cannot prepare statement");        
        exit();    
    }
}

Why does the code only work for the first email and not when you try it a second time? I think the error lies somewhere in the code provided above, if needed just comment and I will provide more code.

The code on tests shows $_SESSION['lgnerror'] "error 2" on the 5th+ attempt when it schould show "error 1" on the 5th attemt. This indicates that $skickat != '0', even though the databse indicates that emailSent == '0' for the second emails id. I think the error probably is that that the user id is not updated so it just checks for the first email id which == '1'. I´m still a beginner at PHP and Mysqli so that might not be the error so please take a look and let me know what you think. What do you think is the error and how could I fix this so I could use this for more accounts than one, preferably with the security of prepared statements?

Upvotes: 1

Views: 360

Answers (1)

Tom&#225;s
Tom&#225;s

Reputation: 3561

Im not sure but I think you got a mistake here.

In the function getId you do a bind_param of the email as an integer where it should be a string.

It should be like this:

function getId($email, $mysqli) 
{   
    if ($stmt = $mysqli->prepare("SELECT id FROM members WHERE email = ? LIMIT 1")) 
    { 
        $stmt->bind_param('s', $email);                
        $stmt->execute();       
        $stmt->bind_result($resultat); 
        $stmt->fetch();
        $stmt->close();
        return $resultat; 
    } 
    else 
    {        
        // Could not create a prepared statement        
        header("Location: ../error.php?err=Database error: cannot prepare statement");        
        exit();    
    }
}

Upvotes: 1

Related Questions