pdo - The username is already, but it isn't

Question

Function

  public function isUsernameAvailable($username) {
        $sql = "SELECT COUNT(*) FROM users WHERE username = '$username'";
        $sql = $this->db->quote($sql);

        if ($sth = $this->db->query($sql)) {
            if ($sth->fetchColumn() > 0) {
                return false;
            } else {
                return true;
            }
        }
    }


if (!$user->isUsernameAvailable($_POST['username'])) {
        echo 'The username is already taken!';
        $error = 1;
    }

I get the output "The username is already taken!" even though It isn't.

Then do some debugging. The comparison on `fetchColumn()` probably doesn't work that way. Try outputting what it results in using `var_dump()` — Pekka, Nov 19 '11 at 10:18
If you're using `PDO`, you're misusing prepared statements/escaping. — deceze, Nov 19 '11 at 10:21

ajreal · Accepted Answer · 2011-11-19T11:07:42.677

3

select count(*)... will ALWAYS return a record, even nothing is matched

so, you can try this :

select 1 from ...;

between, a proper method to check any record return should be using rowCount

as indicate by the rest :-

you should make use of bind rather than using quote
function should always return a value

edited Nov 19 '11 at 11:07

answered Nov 19 '11 at 10:20

ajreal

46,720
11
89
119

That SQL will not return a record if the query results in an error. – hakre Nov 19 '11 at 10:59
is true, the OP having few problem rather that just sql logic – ajreal Nov 19 '11 at 11:05

score 1 · Answer 2 · edited May 23 '17 at 12:05

Your isUsernameAvailable function will return NULL (equals to FALSE) when the query fails. This will trigger the The username is already taken! message.

Looks like your SQL query always fails, which I think is no wonder because you blindly apply quotes onto it.

After running a SQL query, you must more properly deal with the case that the query failed, e.g. display that an internal SQL error occured and log it, so you can look into the log and locate the cause of the error.

Additionally, you should use the PDO more properly, specifically when you want to prevent SQL injection (which you must care about). See Best way to stop SQL Injection in PHP.

Adithya Surampudi · Answer 3 · 2011-11-19T10:30:42.467

1

Use rowCount() instead of fetchColumn()

  if ($sth->rowCount() > 0) {

edited Nov 19 '11 at 10:30

answered Nov 19 '11 at 10:21

Adithya Surampudi

4,354
1
17
17

ajreal is right about using `SELECT count(*)`, you're right about using `rowCount()`, except you forgot the `()`. – Yes Barry Nov 19 '11 at 10:29
edited the () i actually didnt look at the sql part, though thats the major bug – Adithya Surampudi Nov 19 '11 at 10:31
yeah it happens. Cheers! – Yes Barry Nov 19 '11 at 10:32
I don't think that part of the function is actually executed. – hakre Nov 19 '11 at 11:00

score 0 · Answer 4 · answered Nov 19 '11 at 10:22

0

fetchColumn returns false if there is no column and also see ajreal answer.

    if ($sth = $this->db->query($sql)) {
        if ($sth->fetchColumn() == false) {
            return true;
        } else {
            return false;
        }
    }

answered Nov 19 '11 at 10:22

Joost

1,426
2
16
39

score 0 · Answer 5 · edited Nov 19 '11 at 11:03

0

Can you try using the following code?

$st->rowCount() > 0

edited Nov 19 '11 at 11:03

apaderno

28,547
16
75
90

answered Nov 19 '11 at 10:22

Sudhir Bastakoti

99,167
15
158
162

knittl · Answer 6 · 2011-11-20T08:47:43.483

$sql = "SELECT COUNT(*) FROM users WHERE username = '$username'";
$sql = $this->db->quote($sql);

You are quoting the whole query, this does not make any sense and is outright dangerous from a security standpoint – and probably results in an invalid query anyway.

Here's a more proper code:

// return TRUE if user is available
public function isUsernameAvailable($username) {
    $sql = "SELECT id FROM users WHERE username = :username";
    $stmt = $this->db->prepare($sql);
    $stmt->execute(array(':username' => $username));

    return $stmt->fetch() === FALSE;
}


if (!$user->isUsernameAvailable($_POST['username'])) {
    echo 'The username is already taken!';
    $error = 1;
}

pdo - The username is already, but it isn't

6 Answers6