CODEWITHSUNDEEP
phpmysqlsqlpdoregistration
BorisTB
BorisTB

Reputation: 1806

Check if username/email available method

I try to make a secure method that checks if a username or email is not taken and I'm not sure if this is the right way. How can I do this better?

private function checkAvailability() {
        try {
            $conn = new PDO(DB_SERVER, DB_USER, DB_PASS);
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $sql = ("SELECT COUNT(*) FROM users WHERE username = :username OR email = :email");
            $st = $conn->prepare($sql);
            $st->bindValue(":username", $_POST["username"], PDO::PARAM_STR);
            $st->bindValue(":email", $_POST["email"], PDO::PARAM_STR);
            $st->execute();
            if($st->fetchColumn() > 0) {
                $sql = ("SELECT COUNT(*) FROM users WHERE username = :username");
                $st = $conn->prepare($sql);
                $st->bindValue(":username", $_POST["username"], PDO::PARAM_STR);
                $st->execute();
                if($st->fetchColumn() > 0) {
                    throw new Exception("That username is already taken");
                } else {
                    throw new Exception("That e-mail is already registered.")
                }
                return 0;
            } else {
                return 1;
            }
            $conn = null;
        } catch (PDOException $e) {
            echo "Database error: " . $e->geMessage();
        } catch (Exception $e) {
            echo "Registration failed: " . $e->geMessage();
        }
    }

    public function registerUser() {
        if(self::checkAvailability) {
            // register user
        }
    }

Upvotes: 0

Views: 686

Answers (1)

colburton
colburton

Reputation: 4715

You are already on the right path here.

The query SELECT COUNT(*) FROM users WHERE username = :username OR email = :email can be a performance problem, because the db can't use any index here. So you might want to split that in two statements one for username and one for email. That would also help you determining which error occured.

Also you don't need every hit in the db just the first, so a limit 1 is also usefull.

Upvotes: 2

Related Questions