How come my script can't check the database for similar usernames?

Question

I'm trying to check the database for a taken username when the user signs up. The connection to the database works fine as a similar password will be added to the table.

$username = $_POST['user'];
$password = $_POST['password'];
$hash = password_hash($password, PASSWORD_DEFAULT);

$s = 'SELECT * FROM users WHERE username = "$username"';

$result = mysqli_query($con, $s);

$num = mysqli_num_rows($result);

if ($num == 1) {
    echo "Username is taken";
}else {

table for users

It goes to the else and adds the username to the database anyways. I have checked to make sure there isn't more than one username, although a greater than sign would work better anyway. any ideas?

Answer 1

Your code must be using parameter binding to send the value of $username to the database, otherwise "$username" is treated as a literal string. It will also protect your from SQL injections.

It would probably be better to create a UNIQUE key on that column instead. If you want to do it in the application layer for whatever reason, you can fetch the result and use that.

$stmt = $con->prepare('SELECT * FROM users WHERE username = ?');
$stmt->bind_param('s', $username);
$stmt->execute();
$result = $stmt->get_result()->fetch_all();

if ($result) {
    echo "Username is taken";
} else {
    // No such username in the database yet
}

This is not going to be very efficient, so we can simplify it using COUNT(1). It will return a single value containing the number of matching rows.

$stmt = $con->prepare('SELECT COUNT(1) FROM users WHERE username = ?');
$stmt->bind_param('s', $username);
$stmt->execute();
$usernameTaken = $stmt->get_result()->fetch_row()[0];

if ($usernameTaken) {
    echo "Username is taken";
} else {
    // No such username in the database yet
}

For more explanation see https://phpdelusions.net/mysqli/check_value

Answer 2

$s = 'SELECT * FROM users WHERE username = "$username"';

You are using double quote inside single quote so there is no interpolation happening. Change the order to

$s = "SELECT * FROM users WHERE username = '{$username}'";