**Why is my MySQL login script not working?**

What is the issue with my MySQL connection in my login script?

In the provided code fragments, there seem to be several areas that could be causing issues with the login form. Let's go through them one by one:

1. Database Connection:

In the PHP file login.php, you attempt to establish a connection to the database using the following code:

<code class="php">// Pretend the following is locked in a vault and loaded but hard coded here
$hostname = "localhost";
$database = "boost";
$username = "root";
$password = "";
$localhost = mysqli_connect($hostname, $username, $password, $database);

if (mysqli_connect_errno()) {
    die("Connection Failed" . mysqli_error());
}</code>

However, you have hard-coded the database credentials. It's not a good practice to hard-code these details in the code, as they may change in the future. Instead, it's recommended to separate the database credentials in a configuration file and read them from there to ensure better security and flexibility.

2. Prepared Statement:

In both register.php and login.php, you prepare the SQL statements using the mysqli_prepare function. However, you don't execute them afterwards using mysqli_stmt_execute. This will prevent the query from being executed and result in a failed login or registration.

3. Binding Parameters:

When using the mysqli_stmt_bind_param function, you should provide the types of the bound parameters. In both register.php and login.php, you bind the parameters using the "s" type, which is for strings. However, if you're binding integer values, you should use the appropriate type specifier, such as "i" for integers.

4. User Authentication:

In login.php, when verifying the user's password, you compare the cleartext password ($ctPassword) directly to the hashed password stored in the database ($dbHashedPassword). This is a security risk, as it allows the attacker to gain access to the user's password if they can intercept the request. Instead, you should use the password_verify function to compare the hashed passwords securely.

5. Session Handling:

You use the $_SESSION superglobal to store the user's ID upon successful login. However, you don't start the session using session_start() in login.php. This will prevent the session from being properly initialized, and the session data will not be available.

6. SQL Injection:

You're not using prepared statements when executing your SQL queries, which makes your code vulnerable to SQL injection attacks. You should always use prepared statements to prevent this type of attack.

Here are some updated code fragments that address these issues:

register.php:

<code class="php">session_start();

if (isset($_POST['register'])) {
    $email = $_POST['email'];
    $ctPassword = $_POST['password'];   // Cleartext password from user

    // Pretend the following is locked in a vault and loaded but hard coded here
    $host = "yourhostname";
    $dbname = "dbname";
    $user = "dbuser";
    $pwd = "password";
    $port = 3306; // Comes along for the ride so I don't need to look up param order below

    try {
        $mysqli = new mysqli($host, $user, $pwd, $dbname, $port);

        if ($mysqli->connect_error) {
            die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
        }

        $query = "INSERT INTO user_accounts2(email, password) VALUES (?, ?)";
        $stmt = $mysqli->prepare($query);

        $hp = password_hash($ctPassword, PASSWORD_DEFAULT); // Hashed password using cleartext one

        $stmt->bind_param("ss", $email, $hp);
        $stmt->execute();

        $iLastInsertId = $mysqli->insert_id;
        $stmt->close();
        $mysqli->close();
    } catch (mysqli_sql_exception $e) {
        throw $e;
    }
}</code>

login.php:

<code class="php">session_start();

if (isset($_POST['login'])) {
    $email = $_POST['email'];
    $ctPassword = $_POST['password'];   // Cleartext password from user

    // Pretend the following is locked in a vault and loaded but hard coded here
    $host = "yourhostname";
    $dbname = "dbname";
    $user = "dbuser";
    $pwd = "password";
    $port = 3306;

    try {
        $mysqli = new mysqli($host, $user, $pwd, $dbname, $port);

        if ($mysqli->connect_error) {
            die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
        }

        $query = "SELECT id, email, password FROM user_accounts2 WHERE email = ?";
        $stmt = $mysqli->prepare($query);

        $stmt->bind_param("s", $email);
        $stmt->execute();

        $result = $stmt->get_result();

        if ($row = $result->fetch_array(MYSQLI_ASSOC)) {
            $dbHashedPassword = $row['password'];
            if (password_verify($ctPassword, $dbHashedPassword)) {
                echo "Right, userId=";
                $_SESSION['userid'] = $row['id'];
                echo $_SESSION['userid'];
            } else {
                echo "Wrong";
            }
        } else {
            echo 'No such record';
        }

        $stmt->close();
        $mysqli->close();
    } catch (mysqli_sql_exception $e) {
        throw $e;
    }
}</code>

Additional Notes:

• It's highly recommended to use a database abstraction layer (DAL) like PDO or Doctrine to simplify your database interactions.
• Never store passwords in plaintext in your database. Always hash them securely using a strong hashing algorithm like bcrypt or Argon2.
• Use prepared statements to prevent SQL injection attacks.
• Validate user input before submitting it to the database to prevent malicious attacks.
• Consider using a more robust security framework to protect your application from various types of attacks.

The above is the detailed content of **Why is my MySQL login script not working?**. For more information, please follow other related articles on the PHP Chinese website!