How to insert into a table after a successful session login

Question

I'm making a login script with a simple manage session feature like Facebook

As a start I have created a table called "users_sessions"

This is the code :

$database->query("
    CREATE TABLE `users_sessions` (
        `session_id` int(10) UNSIGNED NOT NULL,
        `session_date` datetime NOT NULL,
        `user_id` int(10) UNSIGNED NOT NULL,
        `user_browser` varchar(64) COLLATE utf8mb4_bin NOT NULL,
        `user_os` varchar(64) COLLATE utf8mb4_bin NOT NULL,
        `user_ip` varchar(64) COLLATE utf8mb4_bin NOT NULL,
         PRIMARY KEY (`session_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
");

So what I want to do is when users fill in the box the right username and password automatically the script will insert into this table the right information of the successful user log.

and this is the index.php :

 $_POST['password'] = User::hash_password($_POST['password']);
if(!empty($_POST) && empty($_SESSION['error'])) {
    /* If remember me is checked, log the user with cookies for 30 days else, remember just with a session */
    if(isset($_POST['rememberme'])) {
        setcookie('username', $_POST['username'], time()+60*60*24*30);
        setcookie('password', $_POST['password'], time()+60*60*24*30);
        setcookie('user_id', User::login($_POST['username'], $_POST['password']), time()+60*60*24*30);


    } else {
        $_SESSION['user_id'] = User::login($_POST['username'], $_POST['password']);
    }
    redirect();
}

And this is my database config :

<?php

/* CONNECTION */
$database_connection = new StdClass();
$database_connection->server = 'localhost';
$database_connection->username = 'root';
$database_connection->password = '';
$database_connection->name = 'wearesocio';
$database = new mysqli($database_connection->server, $database_connection->username, $database_connection->password, $database_connection->name);
if($database->connect_error) {

    header('Location: '.$_SERVER['PHP_URL']."install");
}

/* DB CLASS */
Database::$database = $database;
/* DEBUGGING */
define('DEBUGGING', true);

?>

Don't store the password in a cookie! You can use the existence of a valid session as proof of authentication. — Alex Howansky, Jan 04 '19 at 21:03
@AlexHowansky don't worry Iwill delete the remember me, so where should i add the insert form and what is the right insert form ? — MimoudiX, Jan 04 '19 at 21:05
See [this](http://php.net/manual/en/class.sessionhandler.php) and [this](https://stackoverflow.com/questions/36753513/how-do-i-save-session-data-to-a-database-instead-of-in-the-file-system/36753514). — Alex Howansky, Jan 04 '19 at 21:09
Also note that an INT type column is not sufficient for a session id field. You'll want a string. (Not sure what PHP's default session id length is, but let's be safe and say at least 64 chars.) — Alex Howansky, Jan 04 '19 at 21:10
@MohcineMimoudi Please [edit] your question to include a [mcve] of your problem, which can be tested by others, which shows how you try to create a new row in the database/table. Currently you are not showing the code which sends the `INSERT INTO` SQL query. — Progman, Jan 04 '19 at 21:23

score 2 · Answer 1 · answered Jan 23 '19 at 11:15

this is my solution

I have created some functions for getting user browser or user ip etc Then creating this function below to add it after the $_SESSION['user_id']

    function insert_into_users_sessions($user_id){
    // here add your database class name
    global $database;

    $user = $_SESSION['user_id'];
    $date = new DateTime();
    $date = $date->format('Y-m-d H:i:s');
    $user_browser = get_user_browser();
    $user_ip = get_user_ip();
    $user_os = get_user_os();

    @$database->query("INSERT INTO `user_sessions` (`user_id`, `session_ip`, `session_os`, `session_browser`, `session_date`) VALUES ('$user', '$user_ip', '$user_os', '$user_browser', '$date')");

}

Vörös Imi · Accepted Answer · 2019-01-05T18:07:10.053

if(isset($_POST['rememberme'])) {
    setcookie('username', $_POST['username'], time()+60*60*24*30);
    setcookie('password', $_POST['password'], time()+60*60*24*30);
    setcookie('user_id', User::login($_POST['username'], $_POST['password']), time()+60*60*24*30);
    insert_into_db($params);
} else {
    $_SESSION['user_id'] = User::login($_POST['username'], $_POST['password']);
    insert_into_db($params);
}

function insert_into_db($params){
    // write insert query
}

In an ideal world, I'd use PDO with bound parameters in the insert function, have every table with PRIMARY_KEYs set, and user inputs sanitized. And of course, hash passwords before storage as per How to use password_hash

UPDATE

if(!empty($_POST) && empty($_SESSION['error'])) {
    $username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING);
    $password_raw = $_POST['password'];

    // Get these data
    $session_params = array();
    $session_id = 1;
    $session_date = time();
    $user_id = User::login($username, $password_raw);
    $user_browser = '';
    $user_os = '';
    $user_ip = '';
    array_push($session_params, $session_id, $session_date, $user_id, $user_browser, $user_os, $user_ip);

    if(isset($_POST['rememberme'])) {
        setcookie('username', $username, time()+60*60*24*30);
        //setcookie('password', $_POST['password'], time()+60*60*24*30);
        setcookie('user_id', $user_id, time()+60*60*24*30);
        insert_session_into_db($session_params);
    } else {
        $_SESSION['user_id'] = $user_id;
        insert_session_into_db($session_params);
    }
    redirect();
}

function insert_session_into_db($session_params)
{
    // include database config file, or instantiate your Database class here

    extract($session_params);

    $sql = "INSERT INTO users_sessions (session_id, session_date, user_id, user_browser, user_os, user_ip)
            VALUES ('".$session_id."', '".$session_date."', '".$user_id."', '".user_os."', '".$user_ip."')";
    $res = $database->query($sql);
    if ($res === true) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $database->error;
    }
    $database->close();
}

How to insert into a table after a successful session login

2 Answers2