php8 sqlite checking if user exists always returns true

Question

Trying to check if the user exists

$db = new PDO('sqlite:practice.sqlite');
$query = $db->prepare("SELECT EXISTS (SELECT 1 FROM users WHERE name = :name AND password = :password)");
$query->bindValue(':name', $_POST['name']);
$query->bindValue(':password', $password);
$result = $query->execute();
echo gettype($result);
echo $result;

Will always output a boolean with the value of 1. I have checked with wrong password, wrong username or both. Always the same. Is there something obvious I'm missing here?

edit: maybe its useful to note that I've also attempted this method

$db = new PDO('sqlite:practice.sqlite');
$query = $db->prepare("SELECT EXISTS (SELECT 1 FROM users WHERE name = ? AND password = ?)");
$query->execute([$_POST['name'], $password]);
$result = $query->fetch();
print_r($result);

which results in me getting

Array ( [EXISTS (SELECT 1 FROM users WHERE name = ? AND password = ?)] => 0 [0] => 0 )

every time whether the user exists or not. I have also tested this by adding an account with an unencrypted password. And then running the check with the raw $_POST['password'] value passed on. Which results in the same 0 in the $result array.

edit 2: after another suggestion I have tried out this answer, which yielded the same result where I get "doesn't exist" for freshly created users. This is the case with and without password encryption. The result of count($result) is always higher than 0.

`$query->execute()` returns whether the query succeeded. You need to get the first result row with something like [fetch](https://www.php.net/manual/en/pdostatement.fetch.php) — apokryfos, Jun 04 '22 at 20:35
@apokryfos Yes, thank you for reminding me. I have also attempted this but with the result being a 0 every time interestingly enough. I've put more details in the original post. And thank you for the clarification on execute(). I seem to have misunderstood what type of output its supposed to give me. — a.anev, Jun 04 '22 at 20:52
Can you use some [earlier code](https://stackoverflow.com/questions/33844957/checking-if-a-record-exists-with-php-and-sqlite) ? — MyICQ, Jun 04 '22 at 22:31
@MyICQ tried it, same result. With or without password encryption on freshly created users count($result) is always higher than 0. — a.anev, Jun 04 '22 at 22:46
**You should never use the password** in the query in the first place — Your Common Sense, Jun 05 '22 at 05:24
I would expect the `SELECT EXISTS` result to be a boolean 0 or 1 depending on whether the subquery result has any rows. You might want to alias that selection to get a more sensible name e.g. `SELECT EXISTS (...) as userExists` — apokryfos, Jun 05 '22 at 10:31

score -1 · Answer 1 · answered Jun 05 '22 at 02:27

$db = new PDO('sqlite:practice.sqlite');
$query = $db->prepare("SELECT EXISTS (SELECT * FROM users WHERE name = :name AND password = :password)");
$query->bindValue(':name', $_POST['name']);
$query->bindValue(':password', $password);
$result = $query->execute();
echo gettype($result);
echo $result;

Or

$db = new PDO('sqlite:practice.sqlite');
$query = $db->prepare("SELECT * FROM users WHERE name = ? AND password = ?");
$query->execute([$_POST['name'], $password]);

$result = $query->fetch();
print_r($result);
if(count($query->fetch()) > 0){
echo "exist";
}else{
echo "not exist";
}

Try This

php8 sqlite checking if user exists always returns true

1 Answers1