Function runs great when called isolated but only that way

[Javiigahe]

I have a function in a php file that checks the attempts done to verify the email and in case it does not exceed certain limits, it sends a new verification mail. The function works great if I call it from the same file, but as soon as I require it from the login file I get a:

 

"Connection failed: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES)"

 

error. I am completely lost, I have tried integrate the function with the rest of the login.php and any possible solution that came to my mind but I have no idea about what could be the issue. I would appreciate if any of you can see what I am missing.

login.php:

 

<?php
require '../vendor/autoload.php';
require_once __DIR__ . '/../mentaltimecfg.php';
require_once __DIR__ . '/get_attempts.php';
require __DIR__ . '/../jwt.php';
require __DIR__ . '/send_otp_mail.php';
require_once __DIR__ . '/../resources/elasticmail2.php';

// if (isset($_POST['device_id'])) {
    // $device_id = filter_var($_POST['device_id'], FILTER_SANITIZE_STRING);
// } else {
    // header('Content-Type: application/json');
    // http_response_code(400);
    // $response = ["status" => "error", "message" => "Invalid request"];
    // die(json_encode($response));
//}

if (!$_POST["email"] OR !$_POST["password"]) {
    header('Content-Type: application/json');
    http_response_code(400);
    $response = ["status" => "error", "message" => "Bad request."];
    die(json_encode($response));
}

$ip = filter_var($_SERVER['REMOTE_ADDR'], FILTER_SANITIZE_STRING);

// Sanitize the IP address using PHP's FILTER_VALIDATE_IP filter
if (filter_var($ip, FILTER_VALIDATE_IP)) {
    $ip_status = checkIP_login($ip);
    
    if ($ip_status != 'ok') {
    header('Content-Type: application/json');
    http_response_code(429);
    $response = ["status" => "error", "message" => $ip_status];
    die(json_encode($response));
    }
} else {
    header('Content-Type: application/json');
    http_response_code(403);
    $response = ["status" => "error", "message" => "Invalid Ip"];
    die(json_encode($response));
}


//Connecto to DB
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (PDOException $e) {
    // Login failed due to an error
    http_response_code(504);
    $response = ["status" => "error", "message" => "Login failed. Please try again."];
    //echo json_encode($e->getMessage());
    // Send the response as JSON
    header('Content-Type: application/json');
    echo json_encode($response);
}


//Get and sanitize data
    
    if ($_SERVER["REQUEST_METHOD"] == "POST") {
        if (filter_var($_POST["email"], FILTER_VALIDATE_EMAIL)) {
            $email = $_POST["email"];
        } else {
            header('Content-Type: application/json');
            http_response_code(403);
            $response = ["status" => "error", "message" => "Invalid email"];
            die(json_encode($response));
        }
        
        $password = filter_var($_POST["password"], FILTER_SANITIZE_STRING);

        // User SQL Query
        $stmt = $conn->prepare("SELECT id, name, surname, password, salt, 2FA, email_verified FROM clients WHERE email = :email");
        $stmt->bindParam(':email', $email);
        $stmt->execute();
        $userData = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($stmt->rowCount() > 0) {
            $Profile = "User";
        }
        
        // Psychologists SQL query
        $stmt = $conn->prepare("SELECT Id, name, surname, password, salt, 2FA, email_verified FROM psychologist WHERE email = :email");
        $stmt->bindParam(':email', $email);
        $stmt->execute();
        $PsychData = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($stmt->rowCount() > 0) {
            $Profile = "Psychologist";
        }
        

        // Check if the user exists wither in clients or psychologist database
        
        if (!$userData AND !$PsychData) {
            header('Content-Type: application/json');
            http_response_code(404);
            $response = ["status" => "error", "message" => "User not found."];
            die(json_encode($response));
        }
        
        // if (isset($userData) && isset($PsychData)) {
            // header('Content-Type: application/json');
            // http_response_code(409);
            // $response = ["status" => "error", "message" => "User not found."];
            // die(json_encode($response));
        // }
            
        if (isset($userData) AND $Profile == "User") {
            // Verify the password
            $isValidPassword = password_verify($userData['salt'] . $password, $userData['password']);
            
            if ($isValidPassword) {             
                
                $userId = $userData['id'];
                //Check if user has authenticator app or if 2fa is default
                if ($userData['2FA'] == "Authenticator") {
                    //Do nothing, 
                }
                if ($userData['2FA'] == "Default") {
                    //Generate 2FA code and send email (Maybe the best is to create a function in another file and call it from here just in case user wants to ask to resend an email
                    //Send email
                    
                    echo "ip: " . $ip . "userId: " . $userId;
                }
                $pretoken = simple_generateToken($jwt, $userId);
                $response = ["status" => "success", "message" => "Login correct", "PreAuth" => $pretoken];
                http_response_code(200);
            } else {
                
                $response = ["status" => "error", "message" => "Invalid credentials."];
                http_response_code(401);
            }
        }
        
                
        if (isset($PsychData) AND $Profile == "Psychologist") {
                // Verify the password
                $isValidPassword = password_verify($PsychData['salt'] . $password, $PsychData['password']);
                
                if ($isValidPassword) {
                    //Psychologists can only enable authenticator app 2 steps verification
                                                            
                    $pretoken = simple_generatePToken($jwt, $PsychData['Id']);
                    $response = array("status" => "success", "message" => "Login correct", "PreAuth" => $pretoken);
                    http_response_code(200);
                } else {
                    
                    $response = array("status" => "error", "message" => "Invalid credentials.");
                    http_response_code(401);
                }               
        }
        
    // Close the database connection
    $conn = null;
    // Send the response as JSON
    header('Content-Type: application/json');
    $sendOTP = sendOTPmail($jwt, $ip, $userId);
    echo json_encode($response);
    }
?>

send_otp_mail.php:

<?php

require_once __DIR__ . '/../jwt.php';
require_once __DIR__ . '/../mentaltimecfg.php';
require_once __DIR__ . '/get_attempts.php';
require_once __DIR__ . '/../resources/elasticmail2.php';



function sendOTPmail($jwt, $ip, $uid) {
    global $servername, $dbname, $username, $password;
    //Connect to database
    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // Set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch(PDOException $e) {
        die("Connection failed: " . $e->getMessage());
    }
    
    //Verify ip is not abusing the server

    $ip = $ip;
    

    //Prevent user abusing email verification system. Check if has requested an email in the last minute or if has requested more than 10 validations in the last hour.
    $checkVmailsent = checkOTPmailsent($uid);
    if ($checkVmailsent != "ok") {
        $checkVmailsent;
        return $checkVmailsent;
    }

    //Check if there is a valid code already or generate new random code for email verification and verification url and insert generated code and validity to the database

    //Delete codes generated more than 15 minutes ago
    $ft_mins_ago = date('Y-m-d H:i:s', strtotime('-15 minutes'));
    $delete_old_codes = "DELETE FROM v_attempts WHERE type = 'mailOTP' AND timestamp < :ft_mins_ago";
    //$doldcodes_query = "SELECT email FROM clients WHERE Id = :id";
    $stmt = $conn->prepare($delete_old_codes);
    $stmt->bindParam(':ft_mins_ago', $ft_mins_ago);
    $stmt->execute();

    //Check if there is any code for user id AQUÍ HE AÑADIDO LO DE OBTENER EL EMAIL
    $get_email_query = "SELECT Code, timestamp FROM v_attempts WHERE type = 'mailOTP' AND UserId = :id";
    $stmt = $conn->prepare($get_email_query);
    $stmt->bindParam(':id', $uid);
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($row) {
        //If code validity is less than 3 minutes generate a new one before resending
        $inthreemin = date('Y-m-d H:i:s', strtotime('+3 minutes'));
        $timestamp = $row['timestamp'];
        $codevalidity = date('Y-m-d H:i:s', strtotime($timestamp . ' +15 minutes'));
        if ($inthreemin > $codevalidity) {
            $code = str_pad(rand(0, 9999), 4, '0', STR_PAD_LEFT);
        } else {
            $old_code = $row['Code'];
            $code = $old_code;          // Assign 'Code' column to $code variable
        }

    } else {
        $code = str_pad(rand(0, 9999), 4, '0', STR_PAD_LEFT);
        //echo $code . '</br>';
        //If there are no codes, generate new random code, otherwise select the sent code to send it again
    }


    //Check the email of Id

    $get_email_query = "SELECT email FROM clients WHERE Id = :id";
    $stmt = $conn->prepare($get_email_query);
    $stmt->bindParam(':id', $uid);
    $stmt->execute();
    $to = $stmt->fetchColumn();

    //Send email and insert attempt to the database

    $vToken = generate_emailToken($jwt, $uid, $to);
    $nowtime = date('Y-m-d H:i:s');
    $Vmail = sendVmail($code, $to, $vToken);

    if ($Vmail != "ok") {
        $response = array("Status" => "error", "message" => $Vmail);
        return $response;
    } else {
        $insert_t_Vmail_query = "INSERT INTO t_v_attempts (UserId, email, Code, timestamp, type) VALUES (:Userid, :email, :Code, :timestamp, 'mailOTP')";
        $stmt = $conn->prepare($insert_t_Vmail_query);
        $stmt->bindParam(':Userid', $uid);
        $stmt->bindParam(':email', $to);
        $stmt->bindParam(':Code', $code);
        $stmt->bindParam(':timestamp', $nowtime);
        $stmt->execute();
        
        if (!empty($old_code) && $old_code = $code) {
            return "Email sent.";
        }
            
        $insert_Vmail_query = "START TRANSACTION; SELECT COUNT(*) INTO @row_count FROM v_attempts WHERE UserId = :Userid AND type = 'mailOTP'; IF @row_count > 0 THEN UPDATE v_attempts SET email = :email, Code = :Code, timestamp = :timestamp, type = 'mailOTP' WHERE UserId = :Userid AND 'type' = 'mailOTP'; ELSE INSERT INTO v_attempts (UserId, email, Code, timestamp, type) VALUES (:Userid, :email, :Code, :timestamp, 'mailOTP'); END IF; COMMIT;";
        //$insert_Vmail_query = "START TRANSACTION; SELECT COUNT(*) INTO @row_count FROM v_attempts WHERE UserId = :Userid; IF @row_count > 0 THEN UPDATE v_attempts SET email = :email, Code = :Code, timestamp = :timestamp, type = 'mail' WHERE UserId = :Userid; ELSE INSERT INTO v_attempts (UserId, email, Code, timestamp, type) VALUES (:Userid, :email, :Code, :timestamp, 'mail'); END IF; COMMIT;";
        $stmt = $conn->prepare($insert_Vmail_query);
        $stmt->bindParam(':Userid', $uid);
        $stmt->bindParam(':email', $to);
        $stmt->bindParam(':Code', $code);
        $stmt->bindParam(':timestamp', $nowtime);
        $stmt->execute();

        return "Email sent.";
    }
}


//$sendmail = sendOTPmail($jwt, '0', $_POST['uid']);
//echo json_encode($sendmail);

?>

 

Thank you all in advance for taking the time to read this.

[ginerjm]

Is the problem IN the function?  If not where in this bunch of code do you think the issue is?

If it is in the function - how about showing us?

[Javiigahe]

 

2 minutes ago, ginerjm said:

Is the problem IN the function?  If not where in this bunch of code do you think the issue is?

If it is in the function - how about showing us?

I don’t think the problem is in the function but at this point I am not sure. All I know is that if I open “send_otp_mail.php” and call the function uncommenting the lines at the end of the code I sent, everything works great but if I require the file to the login.php and call the function, it does indeed call the function and starts to run and executes until:

$checkVmailsent = checkOTPmailsent($uid);
    if ($checkVmailsent != "ok") {
        $checkVmailsent;
        return $checkVmailsent;
    }

However, I have tried to comment all those lines just to see if the issue was there but still I am getting the same error, also I’ve printed an if ($conn) at the beggining of the function and apparently it does indeed connect to the db at the beggining of the function but the error arises later on.

 

Regarding the required php files, sure, let me know which one you’d like to see that I haven’t added and I’ll upload it right away!

 

Thanks again for your time!

[ginerjm]

I have no idea what you are doing 

[Javiigahe]

7 minutes ago, ginerjm said:

I have no idea what you are doing 

What do you mean?

function sentOTPmail(), is in file send_otp_mail.php

 

When I do $sendmail = sendOTPmail($jwt, '0', $_POST['uid']) in the “send_otp_mail.php” file and then I open it, the function works, the db queries work great and the email is sent.

 

However, when I call the function on the “login.php” file requiring it from the “send_otp_mail.php”, I get the error I shared before.

I hope I’ve been clear enough this time.

Thanks again.

[ginerjm]

Still have no idea what you are doing.  And the fact that you don't know what is wrong says you need to re-think what you are doing so that you can do some debugging.  Start with adding some echoes to tell you if things are actually happening as you think they are. Show some values.  Echo out the line numbers that you are getting to.  Follow the process by showing the process, step by step.

[Javiigahe]

1 minute ago, ginerjm said:

Still have no idea what you are doing.  And the fact that you don't know what is wrong says you need to re-think what you are doing so that you can do some debugging.  Start with adding some echoes to tell you if things are actually happening as you think they are. Show some values.  Echo out the line numbers that you are getting to.  Follow the process by showing the process, step by step.

Yes, that’s the first thing I tried and echoes work until:

$checkVmailsent = checkOTPmailsent($uid);
    if ($checkVmailsent != "ok") {
        $checkVmailsent;
        return $checkVmailsent;
    }

 

[ginerjm]

what do you mean they work until.....   What happens then?  What are you showing to find out what is wrong?  Anything?  Which line does it not get to here?  Ask yourself some questions and get some answers by doing something with your code.  Act like a programmer should!

[mac_gyver]

the problem is you are using the $password variable for two different things.

the code for any page should be laid out in this general order -

1. initialization
2. post method form processing
3. get method business logic - get/produce data needed to display the page
4. html document

you should have one user database table with both client and psychologist registration/login data and a 'type' column holding a value that indicates which type of user they are. Don't Repeat Yourself (DRY.)

it is not the responsibility of the sendOTPmail() function to create a database connection. you already have a database connection in the application, supply it to any function that needs it as a call-time parameter.

[ginerjm]

Do you have error checking enabled?  

[Javiigahe]

1 hour ago, mac_gyver said:

the problem is you are using the $password variable for two different things.

the code for any page should be laid out in this general order -

1. initialization
2. post method form processing
3. get method business logic - get/produce data needed to display the page
4. html document

you should have one user database table with both client and psychologist registration/login data and a 'type' column holding a value that indicates which type of user they are. Don't Repeat Yourself (DRY.)

it is not the responsibility of the sendOTPmail() function to create a database connection. you already have a database connection in the application, supply it to any function that needs it as a call-time parameter.

That was the issue, defining the password to times. Thank you so much, you just made my day!

The reason why I have two different tables is because there is so many information that psychologists have and users not and viceversa. Do you think it is that bad idea to organize it that way?

I will definitely from now on only define $conn once and send the var to the functions when called, I hadn’t thought of that option.

Overall do you think the code is secure?

[mac_gyver]

the user table would hold the common one-time information. you would have a second table holding the unique information for the different types of users, with a separate row for each piece of information, related back to the user table through the user id.