PHP: Secure Member-Only Pages with a Login System

Challenges with the Provided Code

The provided PHP code encounters several issues that hinder its functionality:

1. Retrieval of Query Results: Instead of using $data1 = $conn->query($sql1);, the correct approach is to use $data = mysqli_fetch_array($conn->query($sql1)); or $data = $conn->query($sql1)->fetch_array(); to fetch the query results.
2. Database Connection and Execution: The connection to the database should be established using $total = $data = 0; before executing any queries.
3. MySQLi Syntax: The query for inserting the token should use backticks () around table and column names (INSERT INTO tokens (tk, gauth) VALUES (?,?)`) instead of single quotes.
4. User Authentication: The authentication logic should return the result of the query instead of relying on boolean values ($result = $conn->query($sql3)->fetch_array();).
5. Token Generation: The existing token generation method is not secure as it uses openssl_random_pseudo_bytes(). Instead, the code should use a cryptographically secure random number generator (CSPRNG) like random_bytes().

Proposed Solution

1. Simplify Database Operations: Use a single query to retrieve user information and check credentials.
2. Use Prepared Statements: Bind parameters to prevent SQL injection vulnerabilities.
3. Generate Tokens Securely: Employ random_bytes() or a similar function for secure token generation.
4. Store Authentication Data in Session: Save the authentication token in a session variable instead of a cookie.
5. Validate Tokens: Perform a database query against the tokens table to validate the provided token.

Improved Code

The following revised code addresses the identified issues and provides a more secure member-only page login system:

connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    // Prepare statement for user authentication
    $sql_auth = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
    $sql_auth->bind_param("ss", $_POST['uname'], $_POST['pss']);
    $sql_auth->execute();
    $result_auth = $sql_auth->get_result();

    // Authenticate user
    if ($result_auth->num_rows > 0) {
        $user = $result_auth->fetch_assoc();
        $correct = TRUE;
    } else {
        $correct = FALSE;
    }

    // Generate token
    if ($correct === TRUE) {
        $hex = bin2hex(random_bytes(3));
        $_SESSION['auth'] = $hex;
        $_SESSION['logstat'] = TRUE;
    }

    // Close connection
    $conn->close();
?>