Can't check if user exists in database PHP

Question

I have a situation where I have to check if user exists in database, I try this:

$username = htmlspecialchars($_POST['userName']);
/* Check if username is free*/
if(!isset($error_message)) {
if(!isset($_POST["userName"])) {
$error_message = " All Fields are required";
} else {
    $db_handle = new mysqli("localhost", "root", "pass", "database");
    $query = 'SELECT * FROM naudotojai WHERE username = "$username"';
    $result = $db_handle->query($query);
    if($result->num_rows == 0) {
        $error_message = "Do not exist";
}
}
}

But It doesn't work. It always returns 0 rows even if I enter a valid username that exists in database.

mysqli_result Object ( [current_field] => 0 [field_count] => 7 [lengths] => [num_rows] => 0 [type] => 0 )

Then I have second script, where I check if username is free, that works just fine:

 $username = htmlspecialchars($_POST['userName']);
    /* Check ir username is free*/
    if(!isset($error_message)) {
    if(!isset($_POST["userName"])) {
    $error_message = " All Fields are required";
    } else {
        $db_handle = new mysqli("localhost", "root", "pass", "database");
        $query = 'SELECT * FROM naudotojai where username = "$username"';
        $result = $db_handle->query($query);
        if(!empty($result)) {
            $error_message = "Exists";
    }
    }
    }

Could you help me out with this? Can't figure it out on my own.

EDIT: When I enter the username manually it just works fine. So the problem is with the variable in the query. But I don't get It. Why It works on one query but not the other....

$query = 'SELECT * FROM naudotojai WHERE username = admin';

So, in your query you have the variable `$username`, but where is that being set? Because before that you are checking the variable `$_POST['userName']` which wouldn't be the same. — Jonathan Kuhn, May 23 '17 at 17:50
Your code is vulnerable to SQL injections. Please learn to use [prepared statements](https://www.youtube.com/watch?v=nLinqtCfhKY). — tereško, May 23 '17 at 17:52
OK, next, you do know the difference between single quote and double quote in php? https://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php — Jonathan Kuhn, May 23 '17 at 17:53
Also, if you just need to check for the user, I would recommend `SELECT 1 FROM naudotojai WHERE username = :username` — tereško, May 23 '17 at 17:53
If you have not stored the username in the database using `htmlspecialchars()`, you will not want to use that function to retrieve. — Rasclatt, May 23 '17 at 17:54
@tereško Yes, I know, thanks. It's just a test on my localhost. — Dr. Docttor, May 23 '17 at 17:54
When I enter the username manually It works just fine... What's wrong with the variable in this query? `$query = 'SELECT * FROM naudotojai WHERE username = admin';` — Dr. Docttor, May 23 '17 at 18:02

score 0 · Answer 1 · answered May 23 '17 at 18:03

0

You are using a single quote string to build your query, so you string $username is not replaced.

Try to use

"SELECT * FROM naudotojai where username = '$username'"

But a better approach would be to use a prepared statement to avoid SQL injection.

answered May 23 '17 at 18:03

sleblanc

38
5

Yes, it worked. But why the variable is replaced, and works fine, in my second example? – Dr. Docttor May 23 '17 at 18:05
It's the same for your second example. It's just because your logic is inverted. I'm pretty sure that you will receive your error message only if you try to create a user that have a user name equal to "$username". – sleblanc Feb 11 '18 at 22:14

score 0 · Answer 2 · answered May 23 '17 at 18:23

You can use PDO with PHP.

}else{
    $dbh = new PDO('mysql:host=localhost;dbname=database', "root", "pass");
    $sth = $dbh->prepare("SELECT * FROM naudotojai where username = :username");
    $sth->execute(":username" => $username);
    $result = $sth->fetchAll(PDO::FETCH_ASSOC);

    if($result){
       // do something
   }

}

Can't check if user exists in database PHP

2 Answers2