How to make sure only unused login names work in PHP / mySQL?

Question

I am trying to build a database for a website that allows users to login if they have already created an account, or create an account then login. The problem I am currently running into is that I want only unused user names to be added to the database and used ones to be rejected. My mySQL query seems to work, either putting back an empty set if the userInput isn't in the set. Currently, if you input any username, regardless of it being taken or not, it will not let you in. Below is my code:

    if($firstName != null && $address != null) 
 {
    $userCheck =$dbh->query("SELECT userID FROM user WHERE userID='$userInput'"); 
            if(!$userCheck)
            { 
                $sql = "INSERT INTO user(firstName, userID, password, address) VALUES ('$firstName', '$userInput', '$password', '$address')"; 
                $dbh->exec($sql);   
                is_password_correct($userInput, $password);
            }   

  }

Essentially... if the first two fields are filled our (or not null) then the query wil run, and if that is empty then server should recieve the information and allow the user to access the page.

  function is_password_correct($userInput, $password)
{ 
    $dbh = new PDO
    $userInput = $_POST["userID"]; 
    $password = $_POST["password"]; 
    $firstName = $_POST["first"]; 
    $rows =$dbh->query("SELECT password FROM user WHERE userID='$userInput' "); 
    if($rows)
        { 
            foreach ($rows as $row)
                {
                    if($password == $row["password"])
                    { 
                        return TRUE;
                    } 
                }
        }
    return FALSE; 
}

`$dbh->query()` returns a Boolean false if the query failed..... but a failed query means __failed to execute__, not simply __failed to return any records__. The latter is a 100% valid query return, and returns an empty result set, not a Boolean false.... what you need to check then is the number of rows returned — Mark Baker, Dec 04 '15 at 17:15
and you really should be using a prepared statement here. Also hoping you're using a good hashing function for passwords. — Funk Forty Niner, Dec 04 '15 at 17:16
possible duplicate of http://stackoverflow.com/questions/22252904/check-if-row-exists-with-mysqli — Funk Forty Niner, Dec 04 '15 at 17:17
anyone posting answers below, the OP's using `mysqli_` and not `mysql_`. — Funk Forty Niner, Dec 04 '15 at 17:23
we also have no idea where your inputs and variables are coming, nor do we know what your `is_password_correct()` function does. Too much guesswork here. Check for errors on both PHP and MySQL side of things. Nor do we know if you are using `mysqli_` or PDO here. — Funk Forty Niner, Dec 04 '15 at 17:25
You really should use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). — Jay Blanchard, Dec 04 '15 at 17:29

Manoj Salvi · Accepted Answer · 2015-12-04T17:58:44.347

you should use num_rows, here is its Documentation also you should change the way using quotes('$userInput') in your queries -

  if($firstName != null && $address != null) 
 {
    $userCheck =$dbh->query("SELECT userID FROM user WHERE userID='".$userInput."'"); 
            if($userCheck->num_rows == 0)
            { 
                $sql = "INSERT INTO user(firstName, userID, password, address) VALUES ('".$firstName."', '".$userInput."', '".$password."', '".$address."')"; 
                $dbh->exec($sql);   
                is_password_correct($userInput, $password);
            }   

  }

you mentioned that you are using pdo so the rowCount() is the right thing to use with pdo like this -

    if($firstName != null && $address != null) 
     {
        $userCheck =$dbh->query("SELECT userID FROM user WHERE userID='".$userInput."'"); 
        $count = $userCheck->rowCount();
                if($count == 0)
                { 
                    $sql = "INSERT INTO user(firstName, userID, password, address) VALUES ('".$firstName."', '".$userInput."', '".$password."', '".$address."')"; 
                    $dbh->exec($sql);   
                    is_password_correct($userInput, $password);
                }   

      }

S. M. Shahinul Islam · Answer 2 · 2015-12-04T17:33:04.180

$dhb->query doesn't return the row count just the resource or boolean false. so you have to count the rows in the returned resourse using fetchColumn.Try this.

{
    $userCheck =$dbh->query("SELECT userID FROM user WHERE userID='".$userInput."'");
    $num_rows = $userCheck->fetchColumn; 
    if($num_rows == 0)
    { 
        $sql = "INSERT INTO user(firstName, userID, password, address) VALUES ('$firstName', '$userInput', '$password', '$address')"; 
        $dbh->exec($sql);   
        is_password_correct($userInput, $password);
    }   

}

score -1 · Answer 3 · answered Dec 04 '15 at 17:17

-1

As Mark Baker says, try something like this:

 if($firstName != null && $address != null) 
 {
    $userCheck =$dbh->query("SELECT userID FROM user WHERE userID='$userInput'"); 
            if(!mysql_num_rows($userCheck))
            { 
                $sql = "INSERT INTO user(firstName, userID, password, address) VALUES ('$firstName', '$userInput', '$password', '$address')"; 
                $dbh->exec($sql);   
                is_password_correct($userInput, $password);
            }   

  }

answered Dec 04 '15 at 17:17

Facundo Fasciolo

452
4
15

1

this will fail `mysql_num_rows` you can't mix different MySQL APIs. – Funk Forty Niner Dec 04 '15 at 17:18
Now every user can register and login, regardless of if their $userInput is the same as someone's who is already in the database – NewtoCompSci Dec 04 '15 at 17:21
...so that `mysql_num_rows` needs to be changed, in order to accommodate for the `mysqli_` API. – Funk Forty Niner Dec 04 '15 at 17:22
Why should the OP "try this"? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO. – Jay Blanchard Dec 04 '15 at 17:29
*"I am using PDO.. I will post the is_password_correct() now – NewtoCompSci 14 secs ago"*. All the more reasons why `mysql_num_rows()`. will fail. – Funk Forty Niner Dec 04 '15 at 17:33

How to make sure only unused login names work in PHP / mySQL?

3 Answers3