Securing login PHP script

Question

I have made a login system where the inserted_id and inserted_password are sent to the login.inc.php via the XMLHttpRequest. I'm not sure if my PHP script is secure. I need some securing advice for my script.

login.inc.php:

<?php
    session_start();
    $conn = mysqli_connect("localhost", "root", "", "users");
    $params = json_decode(file_get_contents('php://input'), true);
    $inserted_id = $params['inserted_id'];
    $inserted_password = $params['inserted_password'];

    $stmt = mysqli_stmt_init($conn);
    if (mysqli_stmt_prepare($stmt, "SELECT * FROM user WHERE account_name=? OR email=?;")) {
        mysqli_stmt_bind_param($stmt, "ss", $inserted_id, $inserted_id);
        mysqli_stmt_execute($stmt);
        $row = mysqli_fetch_assoc(mysqli_stmt_get_result($stmt));
        if ($row == null) {
            echo ("DOESNT EXISTS");
        } else {
            if (password_verify($inserted_password, $row['password'])) {
                $_SESSION['user_id'] = $row['id'];
                echo("SUCCESS");
            } else {
                echo("PASSWORD_FAIL");
            }
        }
    }
?>

signup.inc.php:

<?php
    $conn = mysqli_connect("localhost", "root", "", "users");
    $params = json_decode(file_get_contents('php://input'), true);
    $inserted_first_name = $params['first_name'];
    $inserted_last_name = $params['last_name'];
    $inserted_dob = $params['dob'];
    $inserted_email = $params['email'];
    $inserted_account_name = $params['account_name'];
    $inserted_password = $params['password'];

    $stmt = mysqli_stmt_init($conn);
    if (mysqli_stmt_prepare($stmt, "SELECT * FROM user WHERE email=?;")) {
        mysqli_stmt_bind_param($stmt, "s", $inserted_email);
        mysqli_stmt_execute($stmt);
        if (mysqli_num_rows(mysqli_stmt_get_result($stmt)) > 0) {
            echo("EMAIL_TAKEN");
        } else {
            $hashed_password = password_hash($inserted_password, PASSWORD_DEFAULT);
            $created_id = rand(111111111, 999999999);
            $stmt = mysqli_stmt_init($conn);
            if (mysqli_stmt_prepare($stmt, "INSERT INTO user(id, first_name, last_name, dob, email, account_name, password) VALUES (?, ?, ?, ?, ?, ?, ?);")) {
                mysqli_stmt_bind_param($stmt, "issssss", $created_id, $inserted_first_name, $inserted_last_name, $inserted_dob, $inserted_email, $inserted_account_name, $hashed_password);
                $result = mysqli_stmt_execute($stmt);
                echo ($result ? "SUCCESS" : "FAIL");
            }
        }
        mysqli_stmt_close($stmt);
    }
?>

Answer 1

The database access information (server address, user name, password, database name) are repeatedly hard coded in each PHP file instead of having them in one common place, where they can be easily changed and configured for each run time environment (development, production, etc.)

Additionally a (production) database should not be accessed using a root user with a blank password.

Personally I never accessed a database in PHP using the built-in low level functions. I found using a database access library lead to less boilerplate code, they had a better readable API, and most importantly built-in security measures, that you can't forget to use. At the very least I would outsource database access into separate repository classes/modules/services.

A common security measure for logins is to have the script return the same result for both unknown account name and wrong password, so that an attacker can't find out if a specific user has an account or not.

Answer 2

    if (mysqli_stmt_prepare($stmt, "SELECT * FROM user WHERE account_name=? OR email=?;")) {

So if I know that someone has an account with e-mail address [email protected], I can register with

{
  "first_name": "",
  "last_name": "",
  "dob": "1900-01-01",
  "email": "[email protected]",
  "account_name": "[email protected]",
  "password": "password"
}

and have a 50/50 chance of gaining control of their account?

---

Why does the signup ask for an e-mail address? If it's used to send e-mail, the process seems to be missing a verification step. If it isn't used, you shouldn't ask it.

Similarly, is there a good reason to ask for date of birth? If not, collecting it would be a violation of GDPR.

---

            $created_id = rand(111111111, 999999999);

If you scale to 30k users (including bots, tests, etc) then you can expect to see a collision. Why not rely on the database's autoincrement?