Persistent Login PHP and SQL

Question

I'm a little unsure on how to connect all of my files together for this project. Basically I have a simple login screen which pulls the username and password from my registration table. Once they log in, I want them to be able to see a few random research papers that were assigned to them. I'll add the link in my database to the paper and then assign 2-3 papers for 2-3 users. However, currently when I log in, and click on the papers page, I'm getting the echo message that I'm not signed in. Here are my files. The first one is the login screen which then gives them 2 options. The first is to go back to the home page and the second is their page with the assigned research papers.

<?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);
}

Then when they click "assigned papers" i want it to at least say their name for now and then eventually pull the name of each paper, but its' not even doing that. Here is that php file:

<?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.";
}
?>

Lastly, here is the user sql table if that helps with anything. Also, please let me know if I can add any more info. I'm still learning all of this so anything I can do to clarify is no problem. Thank you very much for your help!

-- 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

Based on what I see, the variable $_SESSION['verified_user'] does not exist. So your if always fails.

Also, I think it's a bad idea to store the users password in the session. I can see no reason to do so and it's a really bad practice.

Switch to a variable you have defined:

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

And then do this:

<?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.";
}
?>

Also, you will want to read up on SQL Injection. This code is vulnerable to it and you DB could get hosed.

Answer 2

First, create a new file named constants.php.

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

You should define a new column named id which has int type and it's auto_increment so it will be your primary key. Primary keys have to be unique and your table lacks such a column. So, in your phpMyAdmin write in SQL tab:

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

Then, in your login file you can use PDO as mentioned above from the other users (if you are not ready for this you can have a look here and here).

<?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();
}
?>

Finally, in your file assigned_papers.php you can access the $_SESSION variables you already have stored and then fetch all the assigned papers for the user that just logged in.

<?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 3

In your first file on line 24, do like this:

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

And the papers (protected file):

<?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.: In your previous files, you haven't set $_SESSION["verified_user"] and same with variable $firstname.