Set Session in Database in PHP

set session in database in php

You would need to create an object like so:

class SessionHandler 
{ 
    private static $lifetime = 0; 

    private function __construct() //object constructor
    { 
       session_set_save_handler(
           array($this,'open'),
           array($this,'close'),
           array($this,'read'),
           array($this,'write'),
           array($this,'destroy'),
           array($this,'gc')
       );
    }

   public function start($session_name = null)
   {
       session_start($session_name); //Start it here
   }

    public static function open()
    {
        //Connect to mysql, if already connected, check the connection state here.

        return true;
    }

    public static function read($id)
    {
        //Get data from DB with id = $id;
    }

    public static function write($id, $data)
    {
        //insert data to DB, take note of serialize
    }

    public static function destroy($id)
    {
       //MySql delete sessions where ID = $id
    }

    public static function gc()
    {
        return true;
    }
    public static function close()
    {
        return true;
    }
    public function __destruct()
    {
        session_write_close();
    }
}

Then before session_start initiate this class!

include 'classes/sessionHandlerDB.php';

$session = new SessionHandler();

$session->start('userbase');

$_SESSION['name'] = 'Robert Pitt'; //This is sent to SessionHandler::write('my_id','Robert Pitt')
echo $_SESSION['name']; //This calls SessionHandler::read($id)//$id is Unique Identifier for that

http://php.net/manual/en/function.session-set-save-handler.php

http://php.net/manual/en/function.serialize.php

How do I save PHP session data to a database instead of in the file system?

I have found over the course of several hours debugging that the referenced articles found on numerous Google searches as well as a significant subset of Stack Overflow answers such as here, here and here all provide invalid or outdated information.

Things that can cause [critical] issues with saving session data to a database:

• While all the examples online state that you can "fill" the session_set_save_handler, none of them state that you must also set the register_shutdown_function('session_write_close') too (reference).

• Several (older) guides refer to an outdated SQL Database structure, and should not be used. The database structure that you need for saving session data to the database is: id/access/data. That's it. no need for various extra timestamp columns as I've seen on a few "guides" and examples.

  • Several of the older guides also have outdated MySQL syntax such as DELETE * FROM ...

• The class [made in my question] must implement the SessionHandlerInterface . I have seen guides (referenced above) that give the implementation of sessionHandler which is not a suitable interface. Perhaps previous versions of PHP had a slightly different method (probably <5.4).

• The session class methods must return the values set out by the PHP manual. Again, probably inherited from pre-5.4 PHP but two guides I read stated that class->open returns the row to be read, whereas the PHP manual states that it needs to return true or false only.

• This is the cause of my Original Issue: I was using custom session names (actually id's as session names and session id's are the same thing!) as per this very good StackOverflow post and this was generating a session name that was 128 characters long. As the session name is the sole key that is needed to be cracked to compromise a session and take over with a session hijacking then a longer name/id is a very good thing.

  • But, this caused an issue because MySQL was silently slicing the session id down to just 32 characters instead of 128, so it was never able to find the session data in the database. This was a completely silent issue (maybe due to my database connection class not throwing warnings of such things). But this is the one to watch out for. If you have any issues with retrieving sessions from a database first check is that the full session id can be stored in the field provided.

So with all that out of the way there are some extra details to add as well:

The PHP manual page (linked above) shows an unsuitable pile of lines for a class object:

$handler = new MySessionHandler();
session_set_save_handler($handler, true);
session_start();

Whereas it works just as well if you put this in the class constructor:

class MySessionHandler implements SessionHandlerInterface {

    private $database = null;

public function __construct(){

    $this->database = new Database(whatever);

    // Set handler to overide SESSION
    session_set_save_handler(
        array($this, "open"),
        array($this, "close"),
        array($this, "read"),
        array($this, "write"),
        array($this, "destroy"),
        array($this, "gc")
        );
    register_shutdown_function('session_write_close');
    session_start();
    }
...
}

This means that to then start a session on your output page all you need is:

<?php
require "path/to/sessionhandler.class.php"; 
new MySessionHandler();

//Bang session has been setup and started and works

---

For reference the complete Session communication class is as follows, this works with PHP 5.6 (and probably 7 but not tested on 7 yet)

<?php
/***
 * Created by PhpStorm.
 ***/
class MySessionHandler implements SessionHandlerInterface {
    private $database = null;

    public function __construct($sessionDBconnectionUrl){
        /***
         * Just setting up my own database connection. Use yours as you need.
         ***/ 

            require_once "class.database.include.php";
            $this->database = new DatabaseObject($sessionDBconnectionUrl);

        // Set handler to overide SESSION
        session_set_save_handler(
            array($this, "open"),
            array($this, "close"),
            array($this, "read"),
            array($this, "write"),
            array($this, "destroy"),
            array($this, "gc")
        );
        register_shutdown_function('session_write_close');
        session_start();
    }

    /**
     * Open
     */
    public function open($savepath, $id){
        // If successful
        $this->database->getSelect("SELECT `data` FROM sessions WHERE id = ? LIMIT 1",$id,TRUE);
        if($this->database->selectRowsFoundCounter() == 1){
            // Return True
            return true;
        }
        // Return False
        return false;
    }
    /**
     * Read
     */
    public function read($id)
    {
        // Set query
        $readRow = $this->database->getSelect('SELECT `data` FROM sessions WHERE id = ? LIMIT 1', $id,TRUE);
        if ($this->database->selectRowsFoundCounter() > 0) {
            return $readRow['data'];
        } else {
            return '';
        }
    }

    /**
     * Write
     */
    public function write($id, $data)
    {
        // Create time stamp
        $access = time();

        // Set query
        $dataReplace[0] = $id;
        $dataReplace[1] = $access;
        $dataReplace[2] = $data;
        if ($this->database->noReturnQuery('REPLACE INTO sessions(id,access,`data`) VALUES (?, ?, ?)', $dataReplace)) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * Destroy
     */
    public function destroy($id)
    {
        // Set query
        if ($this->database->noReturnQuery('DELETE FROM sessions WHERE id = ? LIMIT 1', $id)) {
            return true;
        } else {

            return false;
        }
    }
    /**
     * Close
     */
    public function close(){
        // Close the database connection
        if($this->database->dbiLink->close){
            // Return True
            return true;
        }
        // Return False
        return false;
    }

    /**
     * Garbage Collection
     */
    public function gc($max)
    {
        // Calculate what is to be deemed old
        $old = time() - $max;

        if ($this->database->noReturnQuery('DELETE FROM sessions WHERE access < ?', $old)) {
            return true;
        } else {
            return false;
        }
    }

    public function __destruct()
    {
        $this->close();
    }

}

Usage: As shown just above the class code text.

php store session variable in mysql database

You need to call session_start() at the beginning of your script (before using any $_SESSION variables). Also, you need quotes around the variable in you query:

$sql = 'INSERT INTO users 
VALUES ("'.$_SESSION['user_name'].'")';

Please note that this is not safe; you are wide open to SQL injection. Instead, you should use prepared statements:

<?php
$servername = "localhost";
$username = "user1";
$password = "user1";
$dbname = "payment";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = 'INSERT INTO users 
VALUES (?)';

$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $_SESSION['user_name']);

if ($stmt->execute()) {
    echo "New record created successfully";
} else {
     echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?> 

Getting id from database and put it in session id

else{
      if (mysqli_num_rows($EXEC) > 0) {
            while($row = mysqli_fetch_assoc($EXEC)) {
               $_SESSION['id'] = $row["id"];
            }
      }

      $_SESSION['name'] = $name;
      $_SESSION['pass'] = $pass;

if your query returns only one result then while loop will run only one time but if your query returns more than one record then the last record's id will be stored in your session variable

In $row["id"], id is the column name of the table, if you are selecting all columns from your table and if your users table has columns like name, username, password then you can access it using $row["name"], $row["username"], $row["password"]

Fetching data from mySql database for php user session

$rows is counter variable (having count of number of records) there that's why not working.

Do like below:-

....previous code as it is
$result = mysqli_query($con,$query) or die(mysql_error());
$row = mysqli_fetch_assoc($result); //fetch record
$rows = mysqli_num_rows($result);
if($rows==1){
        $_SESSION['email'] = $row['email'];
        header("Location: index.php");
    }...rest code as it is

Note:-

1.Don't use md5 password encryption, use password hashing technique.

2.Use prepared statements of mysqli_* to prevent your code from SQL Injection

Laravel how to set session from own table and display to view

I finally found the answer:

    public function auth(Request $r) {

        $username = $r->input('txt_username');
        $password = $r->input('txt_password');

        $data = DB::table('tbl_user')
                ->select('user_id', 'username', 'full_name')
                ->where('username', '=', $username)
                ->limit(1)
                ->get();
        
        if(Hash::check($password, $data[0]->password)) {
            Session::put('full_name', $data[0]->full_name);
            return redirect('home');
        }else{
            return redirect('/');
        } 
}

How I can set a session in codeigniter 3 database?

First of all CI3 session table and CI2 session table( Saving Session Data to a Database)structure is different

New session table structure

 CREATE TABLE IF NOT EXISTS `ci_sessions` (
    `id` varchar(40) NOT NULL,
    `ip_address` varchar(45) NOT NULL,
    `timestamp` int(10) unsigned DEFAULT 0 NOT NULL,
    `data` blob NOT NULL,
    PRIMARY KEY (id),
    KEY `ci_sessions_timestamp` (`timestamp`)
);

Second They support old configuration variables with new configuration but it is better to use new configuration

$config['sess_driver'] = 'database';
$config['sess_cookie_name'] = 'ci_sessions';
$config['sess_expiration'] = 7200;
$config['sess_save_path'] = 'ci_sessions';//its your table name name
$config['sess_match_ip'] = FALSE;
$config['sess_time_to_update'] = 300;

See more details at their docs

Few new feature(function) available for session library.

Remember Don't forget it to load via autoload.php or loading $this->load->library('session'); before use it.

Send session value in database

You have two ways of going about this. You can either create a hidden input field like so:

<input type="hidden" name="logged" value="<?php echo $_SESSION['logged']; ?>" />

or you can simply set a $_POST['logged'] = $_SESSION['logged'];

How to get a selected database value as a session variable in php?

Did you used session_start to start a session?

<?php
session_start();
?>

After this

$id = $jfeta['id'];

should work:

<?php
    $jsqla = mysql_query("select id, user_name, user_type from users where pw='".$pass."' and email='".$email."'") or die("Website under maintenance.");
    $jfeta = mysql_fetch_assoc($jsqla);

    session_start();
    $id = $jfeta['id'];
    $_SESSION['id'] = $id;
?>

---

your Selection could be the Problem too:

$jsqla = mysql_query("select id, user_name, user_type from users where pw='$pass' and email='$email'") or die("Website under maintenance.");

could work better with this:

$jsqla = mysql_query("select id, user_name, user_type from users where pw='".$pass."' and email='".$email."'") or die("Website under maintenance.");

---

The message "Undefined index" means that the one value you use isn't set.
you can prevent this by example:

if(isset($jfeta['id']))
{
    //do thing
}

---

Related Topics