Login Authentication Query in MySQL

Logging a user in

A login query checks a username and password against the database. Done wrong, it is one of the biggest security holes in an app. The safe way rests on two rules: never store plain passwords, and never put user input directly in SQL.

The dangerous way

-- NEVER do this
SELECT * FROM users
WHERE username = '$user' AND password = '$pass';

This has two fatal flaws: the password is compared in plain text (so it must be stored unhashed), and the user input is pasted into the SQL, allowing SQL injection. A crafted username could bypass the login entirely.

Never store plain passwords

// when the user registers (PHP)
$hash = password_hash($password, PASSWORD_DEFAULT);
// store $hash in the password column, not $password

Store a strong one-way hash, not the real password. password_hash uses bcrypt, so even if the database leaks, attackers cannot read the passwords.

Use prepared statements

// PHP with PDO
$stmt = $pdo->prepare('SELECT id, password FROM users WHERE username = ?');
$stmt->execute([$username]);
$user = $stmt->fetch();

A prepared statement sends the query and the value separately, so the username can never alter the SQL. This closes the SQL injection hole completely. Notice we fetch by username only — not the password.

Verifying the password

if ($user && password_verify($password, $user['password'])) {
    // login success
} else {
    // invalid username or password
}

password_verify compares the typed password with the stored hash and returns true if they match. The password is checked in code, never in the SQL.

The full safe flow

1. User submits username and password.
2. Fetch the row by username using a prepared statement.
3. If a row exists, password_verify the password against its hash.
4. On match, start the session; otherwise show a generic "invalid login" message.

Use the same generic error for wrong username and wrong password, so attackers cannot tell which was wrong.

Common mistakes

• Comparing passwords in SQL. Fetch by username, verify in code.
• Storing plain passwords. Always hash with password_hash.
• Building SQL with user input. Use prepared statements.
• Revealing which field was wrong. Use one generic error message.

Summary

• Never store plain passwords — store a bcrypt hash (password_hash).
• Never put user input directly in SQL — use prepared statements.
• Fetch the user by username, not by password.
• Verify the password in code with password_verify.
• Show one generic error for any failed login.

Note: this is a sensitive security topic; always follow current best practices. Next, guard queries in safe UPDATE and DELETE, or filter rows in SELECT and WHERE.