Javiigahe Posted August 16, 2023 Share Posted August 16, 2023 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. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted August 16, 2023 Share Posted August 16, 2023 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? Quote Link to comment Share on other sites More sharing options...
Javiigahe Posted August 16, 2023 Author Share Posted August 16, 2023 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! Quote Link to comment Share on other sites More sharing options...
ginerjm Posted August 16, 2023 Share Posted August 16, 2023 I have no idea what you are doing Quote Link to comment Share on other sites More sharing options...
Javiigahe Posted August 16, 2023 Author Share Posted August 16, 2023 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. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted August 16, 2023 Share Posted August 16, 2023 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. Quote Link to comment Share on other sites More sharing options...
Javiigahe Posted August 16, 2023 Author Share Posted August 16, 2023 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; } Quote Link to comment Share on other sites More sharing options...
ginerjm Posted August 16, 2023 Share Posted August 16, 2023 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! Quote Link to comment Share on other sites More sharing options...
Solution mac_gyver Posted August 16, 2023 Solution Share Posted August 16, 2023 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 - initialization post method form processing get method business logic - get/produce data needed to display the page 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. Quote Link to comment Share on other sites More sharing options...
ginerjm Posted August 16, 2023 Share Posted August 16, 2023 Do you have error checking enabled? Quote Link to comment Share on other sites More sharing options...
Javiigahe Posted August 16, 2023 Author Share Posted August 16, 2023 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 - initialization post method form processing get method business logic - get/produce data needed to display the page 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? Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted August 16, 2023 Share Posted August 16, 2023 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.