How to Check Username and Password Matches in a Database
Validating user credentials against a database is a fundamental task for any web application, but the implementation details often reveal critical security flaws. The core challenge lies not just in retrieving data, but in comparing input strings with stored values while preventing malicious manipulation of those queries.
In many introductory scenarios, developers attempt to construct SQL queries by directly interpolating user input from $_POST variables into the query string. While this might appear to work for a simple demonstration, it introduces severe vulnerabilities and logical errors that prevent robust application development.
The Problem with Direct String Interpolation
Consider a scenario where you retrieve the username and password via PHP's $_POST superglobal array. A common mistake is constructing the query like this:
$query = "SELECT log_username, log_password FROM login WHERE log_username='$un' AND log_password='$pw'";
This approach fails immediately when a user inputs special characters such as quotes or semicolons into their username or password fields. If a user enters ' OR '1'='1 as their username, the resulting SQL query effectively becomes:
SELECT ... WHERE log_username='' OR '1'='1' AND log_password=...
The condition '1'='1' is always true, causing the database to return rows regardless of the actual password. This is the mechanism behind SQL injection attacks. Even if you are building a proof-of-concept or a demo for friends as mentioned in the source material, understanding this behavior is essential before moving to production code.
Furthermore, comparing plain-text passwords ($pw == $row['password']) is fundamentally insecure. Passwords should never be stored as plain text because a database breach would expose every user's credentials immediately. Modern standards require hashing algorithms like bcrypt or Argon2id. You must compare the input against the hash of the input, not the raw string itself.
Implementing Secure Authentication Logic
To correctly check if a username and password match the database values, you must adopt parameterized queries. This ensures that user input is treated strictly as data, never as executable code. In MySQLi, this is achieved using prepared statements with bound parameters.
Here is how the logic should be structured:
$conn = new mysqli($servername, $username, $password, $databaseName);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$stmt = $conn->prepare("SELECT log_password FROM login WHERE log_username = ?");
$un = $_POST['username'];
$pw = $_POST['password'];
$stmt->bind_param("s", $un); // 's' denotes a string type for the first parameter
$stmt->execute();
$result = $stmt->get_result();
Once you retrieve the stored hash from the database, you cannot simply compare $pw to $row['log_password']. Instead, you need to verify if the input password generates the same hash as the one stored in the database. PHP's password_verify() function handles this complexity internally, checking the algorithm type and automatically hashing the input for comparison.
$row = $result->fetch_assoc();
if ($row) {
// Verify the password against the stored hash
if (password_verify($pw, $row['log_password'])) {
echo "<script>alert('Login Successful');</script>";
header("Location: Doctors.php");
} else {
echo "<script>alert('Invalid Password');</script>";
}
} else {
echo "<script>alert('User not found');</script>";
}
$stmt->close();
$conn->close();
Why This Approach Matters
The distinction between string interpolation and prepared statements is the difference between a functional demo and a secure application. The source material highlights a common frustration where code appears to fail or behave unexpectedly; in many cases, this is due to subtle character encoding issues or SQL injection attempts altering the query logic before execution.
By switching to password_verify() and prepared statements, you eliminate both the SQL injection risk and the security flaw of storing plain-text passwords. These are not optional improvements but mandatory standards for any authentication system. While the original example focused on a basic check, professional development requires adhering to these practices from day one to ensure that user data remains protected against evolving threats.
For those looking to streamline this process further within a larger Laravel application, resources like https://laravelcompany.com provide insights into how framework-specific helpers and Eloquent ORM simplify secure authentication without reinventing the wheel.
