php - 无法从数据库中选择

Question

我尝试在登录函数上使用准备好的语句来防止 SQL 注入(inject)，代码如下:

    function login($email, $password, $mysqli)
        {
            // Using prepared statements means that SQL injection is not possible. 
            if ($stmt = $mysqli->prepare("SELECT id, username, password, salt, phnumber, realname, age, sex FROM members WHERE email = ? LIMIT 1")) {

                $stmt->bind_param('s', $email);  // Bind "$email" to parameter.
                $stmt->execute();    // Execute the prepared query.
                $stmt->store_result();

                // get variables from result.
                $stmt->bind_result($user_id, $username, $db_password, $salt, $phnumber, $realname, $age, $sex);
                $stmt->fetch();
                 }
         }

然后是密码部分，工作正常。然后我创建了一个 test.php 页面并将以下代码放入其中:

print_r($_SESSION);

它只打印$user_id、$username和password。我记不起 $email 或其他数据，我做错了什么？

Answer 1

从函数变量中删除 mysqli 变量并仅使用全局 $mysqli。

此外，您不要将查询返回的值保存到 session 变量或数组中。

session 变量的结果:

$mysqli = new MySQLi("$dbhost", "$dbuser", "$dbpass", "$db");

function login($email, $password){
    global $mysqli;
    if ($stmt = $mysqli->prepare("SELECT id, username, password, salt, phnumber, realname, age, sex FROM members WHERE email = ? LIMIT 1")) {
        $stmt->bind_param('s', $email);  // Bind "$email" to parameter.
        $stmt->execute();    // Execute the prepared query.
        $stmt->store_result()
        $stmt->bind_result($user_id, $username, $db_password, $salt, $phnumber, $realname, $age, $sex);
        $stmt->fetch()
        $_SESSION['user_id'] = $user_id;
        $_SESSION['username'] = $username;
        $_SESSION['db_password'] = $db_password;
        $_SESSION['salt'] = $salt;
        $_SESSION['phnumber'] = $phnumber;
        $_SESSION['realname'] = $realname;
        $_SESSION['age'] = $age;
        $_SESSION['sex'] = $sex;

    }
}

结果到数组:

$mysqli = new MySQLi("$dbhost", "$dbuser", "$dbpass", "$db");

function login($email, $password){
    global $mysqli;
    if ($stmt = $mysqli->prepare("SELECT id, username, password, salt, phnumber, realname, age, sex FROM members WHERE email = ? LIMIT 1")) {
         $stmt->bind_param('s', $email);  // Bind "email" to parameter.
        $stmt->execute();    // Execute the prepared query.
        $stmt->store_result()
        $stmt->bind_result($user_id, $username, $db_password, $salt, $phnumber, $realname, $age, $sex);
        $stmt->fetch()
        $userdata['user_id'] = $user_id;
        $userdata['username'] = $username;
        $userdata['db_password'] = $db_password;
        $userdata['salt'] = $salt;
        $userdata['phnumber'] = $phnumber;
        $userdata['realname'] = $realname;
        $userdata['age'] = $age;
        $userdata['sex'] = $sex;

    }
    return $userdata;
}

如果您的电子邮件值为空，则查询将永远无法工作，因此首先您需要查看电子邮件变量是否已正确传递给函数。暂时用此代码替换该函数(在您的 test.php 页面中)

function login($email, $password){
    echo $email;
    return $email;
}
echo login($email, $password);

如果没有显示任何结果，您应该首先找到原因。如果确实显示结果，请直接测试 SQL 查询(使用 PHPMyAdmin 等...)或 MySQL CLI。