Verified Emails Query

Question

I have the following table

Login

IdUser (int)

Username(varchar)

Password(varchar)

Email(varchar)

Active(int)

Active is either 0 or 1 depending on if the users email is verified or not. If an account is verified the active row in the table is updated with a 1. If an account is not verified the active row in the table remains a 0.

Users should only be able to login if their account is verified.

So far my login works like this:

//login API
function login($user, $pass) {

// try to match a row in the "login" table for the given username and password
$result = query("SELECT IdUser, username FROM login WHERE username='%s' AND pass='%s' limit 1", $user, $pass);

if (count($result['result'])>0) {
    // a row was found in the database for username/pass combination
    // save a simple flag in the user session, so the server remembers that the user is authorized
    $_SESSION['IdUser'] = $result['result'][0]['IdUser'];
    // print out the JSON of the user data to the iPhone app; it looks like this:
    // {IdUser:1, username: "Name"}
    print json_encode($result);
} else {
    // no matching username/password was found in the login table
    errorJson('Authorization failed');
}
}

How would I give only users that are verified the ability to login?

Answer 1

Well, unless I'm missing something in your description, it seems that you simply have to add a AND active=1 to your WHERE clause. So you would end up with:

SELECT IdUser, username FROM login WHERE username='%s' AND pass='%s' AND active=1  limit 1

Updated

//login API
function login($user, $pass) {

    // try to match a row in the "login" table for the given username and password
    $result = query("SELECT IdUser, username, active, email FROM login WHERE username='%s' AND pass='%s' limit 1", $user, $pass);

    if (count($result['result'])>0) {
        // a row was found in the database for username/pass combination
        if (!$result['result'][0]['active']) {
            // not activated yet
            errorJson('Not activated yet: ' + $result['result'][0]['email']);

        } else {
            // save a simple flag in the user session, so the server remembers that the user is authorized
            $_SESSION['IdUser'] = $result['result'][0]['IdUser'];
            // print out the JSON of the user data to the iPhone app; it looks like this:
            // {IdUser:1, username: "Name"}
            print json_encode($result);
        }
    } else {
        // no matching username/password was found in the login table
        errorJson('Authorization failed');
    }
}

And by the way, as other mentionned, your code appears to be sensible to SQL injection, and you appear to store password in raw text in the database, which is a very bad practice. You should consider using mysqli + placeholders for your queries. You should also hash the passwords, at any point in the process. A simple way (though not the best) might be to use MySQL's password function. So your query would simply be changed to:

$result = query("SELECT IdUser, username, active, email FROM login WHERE username=? AND password=PASSWORD(?) limit 1", $user, $pass);

Answer 2

Simply add AND active = 1 before limit 1 in your query.

As an aside there are some broader issues with your code:

• Avoid storing passwords directly in the database, use bcrypt instead, for example
• Use mysqli or another database interface with prepared statements to avoid SQL injection

Answer 3

here:

$result = query("SELECT IdUser, username FROM login WHERE username='%s' AND pass='%s' AND emailVerified='1' limit 1", $user, $pass);

where there emailVerified is your email verified status field name, replace that to your own

Answer 4

first you have to check user is active or not

select active from login where username='%s';//execute this query and check result and store in active..

if(!$active)
{
errorJson("your account is not activated yet!);
}
else
{
//login code
}