持久登录PHP和SQL

Question

我有点不确定如何连接我所有的文件为这个项目。基本上，我有一个简单的登录屏幕，从我的登记表中提取用户名和密码。一旦他们登录，我希望他们能够看到一些随机的研究论文分配给他们。我将把我的数据库中的链接添加到论文中，然后为2-3个用户分配2-3篇论文。但是，当前，当我登录并单击文件页时，我将收到未登录的回显消息。这是我的档案。第一个是登录屏幕，然后给他们两个选项。第一个是回到主页，第二个是他们指定的研究论文的页面。

<?php

define('DB_NAME', 'conference');
define('DB_USER', 'root');
define('DB_PASSWORD', 'password');
define('DB_HOST', 'localhost');

$link = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
mysqli_set_charset($link, 'utf8');
if (!$link) {
    die("Database connection failed: " . mysqli_error($link));
}

$username = mysqli_real_escape_string($link, $_POST['username']);
$password = mysqli_real_escape_string($link, $_POST['password']);

function SignIn($link) {

    session_start(); 
    if (!empty($_POST['username'])) {
        $query = mysqli_query($link, "SELECT * FROM users where username = '$_POST[username]' AND password = '$_POST[password]'")or die(mysqli_error($link));
        $row = mysqli_fetch_array($query) or die(mysqli_error($link));
        if (!empty($row['username']) && !empty($row['password'])) {
            $_SESSION['username'] = $row['password']; 
            echo "Welcome to your User Account for CSIT Conference. Click to go home: ";
            echo '<a href="index.html"> Home Page </a>. ';
            echo "Or here to go to your assigned papers: ";
            echo '<a href="assigned.php"> Assigned Papers </a>. ';
        } else {
            echo "SORRY... YOU ENTERD WRONG ID AND PASSWORD... PLEASE RETRY...";
        }
    }
}

if (isset($_POST['submit'])) {
    SignIn($link);
}

然后，当他们点击“分配的论文”，我希望它至少说出他们的名字暂时，然后最终拉出每一篇论文的名称，但它‘甚至没有这样做。下面是这个php文件：

<?php
session_start();

if (isset($_SESSION['verified_user'])) {
echo "Hello, '$firstname', Here are your assigned papers: ";

$paper1;
$paper2;
}

else {
echo "You are not logged in and cannot see this page.";
}
?>

最后，这是用户sql表，如果有任何帮助的话。此外，请让我知道，如果我可以添加任何更多的信息。我仍然在学习这一切，所以我能做的任何事情都是没有问题的。非常感谢您的帮助！

-- phpMyAdmin SQL Dump
-- version 4.4.14
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Oct 26, 2015 at 02:49 AM
-- Server version: 5.6.26
-- PHP Version: 5.6.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `conference`
--

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `firstname` varchar(40) NOT NULL,
  `lastname` varchar(40) NOT NULL,
  `username` varchar(40) NOT NULL,
  `password` varchar(40) NOT NULL,
  `state` varchar(40) NOT NULL,
  `city` varchar(40) NOT NULL,
  `streetaddress` varchar(40) NOT NULL,
  `zipcode` varchar(40) NOT NULL,
  `phonenumber` varchar(40) NOT NULL,
  `emailaddress` varchar(40) NOT NULL,
  `email` tinyint(1) NOT NULL,
  `help` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`firstname`, `lastname`, `username`, `password`, `state`, `city`, `streetaddress`, `zipcode`, `phonenumber`, `emailaddress`, `email`, `help`) VALUES
('Steve', 'Paul', 'root', 'root', 'ny', 'new york', '20 ridge road', '10990', '98493938383939', 'loucolu@gmail.com', 0, 0),
('test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 0, 0),
('gsdfgsdfgqsdfgsdfg', 'sdfasdf', 'asdfasdfsadf', 'asdfasdfasdfasdf', 'asdfasdfasdfsadfsadfsdf', 'asdfasdfasdfasdfsadf', 'asdfasdf', 'asdfasfsfsfsdfasdfsdf', 'asdfasdfasdfsd', 'asdfasdfsadf', 0, 0);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Answer 1

首先，创建一个名为constants.php的新文件。

<?php
//This is constants.php file
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASSWORD', 'password');
define('DB_NAME', 'conference');
?>

您应该定义一个名为id的新列，它具有int type，并且它是auto_increment，因此它将成为您的主键。主键必须是唯一的，而且您的表缺少这样的列。因此，在phpMyAdmin中写入SQL选项卡：

ALTER TABLE `users` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

然后，在您的登录文件中，您可以使用上面提到的来自其他用户的PDO (如果您还没有做好准备，可以查看这里和这里)。

<?php
function SignIn() {
    require_once("constants.php"); //Now constants will be accessible
    session_start(); 
    try {
        $link = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USER, DB_PASSWORD);
        $username = $_POST['username']; //no need to esaping as we will use prepared statements
        $password = $_POST['password'];
        if (!empty($username) && !empty($password)) {
            //You need to define a new column named "id" which will be int auto_increment and it will be your primary key

            $sql = "SELECT id, username, password FROM users where username = :username AND password = :password";
            //Prepare your query
            $stmt = $link->prepare($sql);
            //Execute your query binding variables values
            $stmt->execute(array(':username'=>$username, ':password'=>$password));
            //Fetch the row that match the criteria
            $row = $stmt->fetch();

            if (!empty($row['username']) && !empty($row['password'])) {
                $_SESSION['is_logged'] = true; //Now user is considered logged in
                $_SESSION['username'] = $row['username'];
                $_SESSION['id'] = $row['id'];

                //Never store passwords in $_SESSION

                echo "Welcome to your User Account for CSIT Conference. Click to go home: ";
                echo '<a href="index.html"> Home Page </a>. ';
                echo "Or here to go to your assigned papers: ";
                echo '<a href="assigned.php"> Assigned Papers </a>. ';
            } else {
                echo "SORRY... YOU ENTERED WRONG ID AND PASSWORD... PLEASE RETRY...";
            }

            $link = null;
        } else {
            echo 'Please enter username and password.';
        }
    } catch(PDOException $e) {
        echo $e->getMessage();
    }
}

if (isset($_POST['submit'])) {
    SignIn();
}
?>

最后，在您的文件assigned_papers.php中，您可以访问已经存储的$_SESSION变量，然后为刚刚登录的用户获取所有分配的文档。

<?php
//assigned_papers
session_start();
require_once("constants.php"); //Now constants will be accessible
if (!empty($_SESSION['is_logged'])) {
    echo 'Hello, '.$_SESSION['username'].'! Here are your assigned papers: ';

    try {
        $link = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USER, DB_PASSWORD);
        $sql = "SELECT * FROM assigned_papers where users_id = :users_id";
        $stmt = $link->prepare($sql);
        //We want all assigned papers for the particular user
        $stmt->execute(array(':users_id'=>$_SESSION['id']));
        $result = $stmt->fetchAll();
        foreach ($result as $row) {
            //You can echo what you want from table assigned_papers
            //echo '<p>'.$row['paper_name'].'</p>';
        }
    } catch(PDOException $e) {
        echo $e->getMessage();
    }

} else
    header("Location: login.php"); //If user isn't logged in then redirect him to login page
    die();
}
?>

Answer 2

根据我所看到的，变量$_SESSION['verified_user']不存在。所以你的if总是失败。

另外，我认为在会话中存储用户密码是个坏主意。我看不出有什么理由这么做，这是一个非常糟糕的做法。

切换到您定义的变量：

$_SESSION['username'] = $row['username']; 
$_SESSION['firstname'] = $row['firstname']; 

然后这样做：

<?php
session_start();

if (isset($_SESSION['username'])) {
     echo "Hello, '{$_SESSION['firstname']}', Here are your assigned papers:  $paper1, $paper2";
} else {
     echo "You are not logged in and cannot see this page.";
}
?>

此外，您还需要阅读SQL注入。此代码易受攻击，您的DB可能会被冲洗。

Answer 3

在第24行的第一个文件中，这样做：

$_SESSION['username'] = $row['username'];

和文件(受保护的档案)：

<?php
session_start();

if (isset($_SESSION['username'])) {
echo "Hello, $_SESSION['username'], Here are your assigned papers: ";

$paper1;
$paper2;
}

else {
echo "You are not logged in and cannot see this page.";
}
?>

P.S.：在以前的文件中，您没有设置$_SESSION["verified_user"]，也没有对变量$firstname设置相同的设置。