php - 已验证的电子邮件查询

Question

我有下表

登录

IdUser(整数)

用户名(varchar)

密码(varchar)

电子邮件(varchar)

事件(整数)

Active 为 0 或 1，具体取决于用户电子邮件是否经过验证。如果帐户已验证，表中的事件行将更新为 1。如果帐户未验证，表中的事件行仍为 0。

用户只有在帐户经过验证后才能够登录。

到目前为止，我的登录方式是这样的:

//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');
}
}

如何只为经过验证的用户提供登录能力？

Answer 1

好吧，除非我在您的描述中遗漏了某些内容，否则您似乎只需在 WHERE 子句中添加 AND active=1 即可。所以你最终会得到:

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

已更新

//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');
    }
}

顺便说一句，正如其他人提到的，您的代码似乎对 SQL 注入(inject)很敏感，并且您似乎将密码以原始文本形式存储在数据库中，这是一种非常糟糕的做法。您应该考虑使用 mysqli + 占位符进行查询。您还应该在此过程中的任何时刻对密码进行哈希处理。一个简单的方法(虽然不是最好的)可能是使用 MySQL 的密码功能。因此您的查询只需更改为:

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