Struggling with user authentication in PHP and MySQL

Data and Databases
1

Hey everyone! I’m having a hard time with my PHP login system. Right now, I’m using SELECT * FROM User in my SQL query, but it only works for the first user in the database. I want to fetch the row where the username matches the input.

I tried something like this:

"SELECT * FROM User WHERE username = '$_post['username_input']'"

But I’m getting an error. Can anyone help me figure out the right way to do this? Here’s my current code:

$link = mysqli_connect("localhost", "db_user", "db_pass", "db_name");
$sql_result = $link->query("SELECT * FROM User");
$row = mysqli_fetch_array($sql_result);

db_username = $row['username'];
$db_password = $row['password'];

if (isset($_POST['submit'])) {
    if ($_POST['username_input'] == $db_username && md5($_POST['password_input']) == $db_password) {
        $_SESSION['log_in'] = true;
        header("Location: dashboard.php");
    } else {
        echo "Wrong username or password. Please try again.";
    }
}
mysqli_close($link);

Thanks in advance for any help!

2

hey, have you tried using pdo? it’s flexible with multiple db. im curious why you’re using md5, given bcrypt or argon2 are safer. what type of app are you building? also, how do you plan to manage user sessions?

3

hey luke, ur code needs work. try using prepared statements to avoid sql injection. also, md5 isn’t secure for passwords anymore. use password_hash() and password_verify() instead. here’s a quick example:

$stmt = $link->prepare("SELECT * FROM User WHERE username = ?");
$stmt->bind_param("s", $_POST['username_input']);
$stmt->execute();
$user = $stmt->get_result()->fetch_assoc();

hope this helps!

4

I’ve encountered similar issues before, and I can offer some advice to improve your authentication system. First, your SQL query approach is vulnerable to SQL injection attacks. Instead, use prepared statements with parameterized queries for better security. Here’s an example:

$stmt = $link->prepare("SELECT * FROM User WHERE username = ?");
$stmt->bind_param("s", $_POST['username_input']);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();

This method is safer and will fetch the correct user based on the input username. Also, consider using password_hash() and password_verify() functions for password handling instead of md5(), which is no longer considered secure for password storage. These changes will significantly enhance your authentication system’s security and functionality.