58

I have seen that codeigniter have facility to save session values in database.
It says saving session in database is good security practice.

But I think saving session information in the database helps improve performance.
They save only a few elements of the session, such as:

CREATE TABLE IF NOT EXISTS  'ci_sessions' (
  session_id varchar(40) DEFAULT '0' NOT NULL,
  ip_address varchar(16) DEFAULT '0' NOT NULL,
  user_agent varchar(50) NOT NULL,
  last_activity int(10) unsigned DEFAULT 0 NOT NULL,
  user_data text NOT NULL,
  PRIMARY KEY (session_id)
);

But if a site uses more session variables such as username, last log in time, etc, I can save them in database and use them in the program.

Do I have to add these columns to the same table? I think saving session information in the database only helps reduce web servers' memory usage (RAM). Can anybody explain in what sense does it improve security.

Josep Valls
  • 5,483
  • 2
  • 33
  • 67
kiriappa
  • 822
  • 2
  • 7
  • 14

6 Answers6

120

It doesn't improve security in any way.

The most common and reasonable pattern to store sessions in database is when you have several frontend servers, so you need a shared session storage for them.

For downvoters: a file in filesystem isn't less secured than a record in database.

zerkms
  • 249,484
  • 69
  • 436
  • 539
  • Oh, even 2 downvotes. Any comments on that action? I'm curious ) – zerkms Dec 17 '12 at 09:19
  • 1
    As Tim said, IP match can be a relevant security method. – Aurélien Grimpard Dec 17 '12 at 09:19
  • I was refering to his very last sentence. – Aurélien Grimpard Dec 17 '12 at 09:22
  • 1
    @Aurélien Grimpard: and how is it relevant to the question? – zerkms Dec 17 '12 at 09:23
  • 1
    Just read zerkms, i was refering to his last sentence "And can anybody explain what causes to improve security". As you can see i didn't answered, just commented. He wants to know how DB storage of session can improve security ... sounds relevant to me, no ? – Aurélien Grimpard Dec 17 '12 at 09:28
  • @zerkms I didn't downvoted your answer and I do agree with your point with complex server setups. So how do you usually tackle session hijacking (without assuming all the HTTPS ONLY flags and other browser-specific stuff ?) – AKS Dec 17 '12 at 09:29
  • 1
    @Ayesh K: there are various techniques, depends on the requirements and your skills. One of the basic and good enough for most projects - is to keep useragent + IP in the session and compare it each request. – zerkms Dec 17 '12 at 09:31
  • I dont know how you say exactly file system is more secure than database record but you bring a good point. thanks. – kiriappa Dec 17 '12 at 09:31
  • 4
    @kiriappa: I didn't say it's more secure either. Nothing of them is more or less secure, because "it depends" – zerkms Dec 17 '12 at 09:32
  • 1
    but nobody says about performance? doesnt it causes to performance? – kiriappa Dec 17 '12 at 09:38
  • @kiriappa: "performance" term makes only sense in a particular case. If different 2 solutions solve the issue - you can and need to compare their performance (there may be a lot of criterias for that). If solutions are incomparable - then you just have no choice :-) – zerkms Dec 17 '12 at 09:40
  • 1
    what I mean is, if 10000000 users are logged in to my site at once, then I need a big memory to handle their session. what if we save them in database? doesnt it cause to improve performance in that case? – kiriappa Dec 17 '12 at 10:43
  • 7
    @kiriappa: what do you mean when say "memory"? What kind of memory? In neither cases the session data is stored in RAM. "doesnt it cause to improve performance in that case" --- this question makes no sense. You cannot say if something would improve performance without *detailed investigation*. Performance optimization process are barely unified. So in each particular case you need to perform different optimizations. That is. – zerkms Dec 17 '12 at 10:48
  • 6
    is it a good idea to hit database on every page hit that require a session? – slier Feb 12 '15 at 18:58
  • 2
    `a file in filesystem isn't less secured than a record in database.` but this is what I find at http://www.hackingwithphp.com/10/3/7/files-vs-databases : " If session data is stored in files, the files would need to be in a shared location somewhere - not ideal for performance or locking reasons." – Istiaque Ahmed Oct 13 '17 at 09:29
  • @IstiaqueAhmed how your quote is relevant to security? There is literally not even a single word about security in the piece you quoted. – zerkms Oct 13 '17 at 09:52
  • @zerkms, if in a shared hosting environment, admins of other sites with the same hosting can play with the session files, right ? I am just trying to understand . – Istiaque Ahmed Oct 13 '17 at 10:02
  • @IstiaqueAhmed nope. I highly doubt you can find any hosting configured like that. Or please provide a particular example. – zerkms Oct 14 '17 at 04:32
  • @zerkms Can we talk sir, I had some doubts regarding sessions – Suraj Jain Dec 30 '17 at 06:13
  • @SurajJain ask a question and post a link to it here – zerkms Dec 30 '17 at 07:35
  • 1
    @zerkms Sir, i no longer ask questions here, they only get down voted – Suraj Jain Dec 30 '17 at 11:04
29

The idea is that sessions can't be hijacked.

A session ID is stored in a cookie. If a hacker can steal that ID, he can pretend to be someone else, because a session is identified by... it's ID.

By saving a user's session ID, IP and agent server-side (your database for example) you can compare the data saved in the database with the client. If a hacker steals someone's session ID, the hacker just might not have a matching IP and/or user-agent, making the users not match which allows you to show or hide certain content.

You have to compare the data manually though.

Tim S.
  • 13,597
  • 7
  • 46
  • 72
  • 4
    How is it related to the question? Regardless of where you store the sessions - you still need to persist SID somewhere on the client. – zerkms Dec 17 '12 at 09:18
  • We stre the IP/ UserAgent String in the session database row so at least we can prevent someone hijacking same session from a different IP or software. – AKS Dec 17 '12 at 09:20
  • 10
    @Ayesh K: session storage has **nothing** to do with hijacking. – zerkms Dec 17 '12 at 09:21
  • 4
    A malicious user can fake both SESSID and IP address, this check doesn't make your application safer. – Neils Sep 21 '15 at 14:04
  • If you can save IP and user-agent into database you can also save this data into that session too! – Ali.Sh Feb 09 '21 at 17:30
9

A common security faux-pas with file-based sessions is to store them in /tmp or another shared directory where the data may be accessible to 3rd parties; especially on shared hosts this can be a problem. This can be prevented with proper file permissions though.

Storing them in a database means you have all the access restrictions of the database in place, though that also means you need to configure them correctly and set up the physical storage of the database securely.

It improves performance insofar as the database server has more layers to improve performance through caching and in-memory storage, whereas file based sessions always incur a disk access. Concurrent access can be improved since you can choose other concurrency mechanisms than file locking. If your database server is already busy with regular database work though, additionally throwing session handling at it may or may not be a good idea.

deceze
  • 510,633
  • 85
  • 743
  • 889
  • 2
    "Concurrent access is also better since no file-locking is necessary." --- you still need to maintain row locks – zerkms Dec 17 '12 at 09:21
  • @zerkms But you can *read* from the database while someone else is writing, file based sessions lock each other out entirely. – deceze Dec 17 '12 at 09:22
  • 1
    reading when someone else is writing will cause the session data loss. (in case if one thread has written after you've read the data, you'll owerwrite it with obsolete data) – zerkms Dec 17 '12 at 09:23
  • 1
    @zerkms OK, true. There are of course many ways to deal with this, as in regular database work, and it depends on what you need to do. If you seldom write to the session but need to read it on every page load, optimistic locking is probably a real advantage over file-based sessions. – deceze Dec 17 '12 at 09:26
  • well, it's a bit offtopic indeed, but I see the only real reason: to have a shared storage. – zerkms Dec 17 '12 at 09:27
  • does it keep session value in file system or ram(memory)? – kiriappa Dec 17 '12 at 10:04
  • @kiriappa Databases you mean? It depends on your database. Typically all data in a database is ultimately written to disk, but recently used data is also typically stored in memory for faster access. – deceze Dec 17 '12 at 10:06
4

This is to answer: can I save them in the database and utilize them in the program?

Yes, you can save them in the database,but there is no need to create a separate column for that. It goes into the userdata column.

You can set the username with $this->session->set_userdata('sessionname',session value);

You can retrieve it with $var=$this->session->userdata('sessionname');

carla
  • 1,970
  • 1
  • 31
  • 44
Ganesh RJ
  • 942
  • 2
  • 19
  • 31
4

You don't mention if you use PHP or MYSQL, but saving your session in a database does not give you better performance, in fact quite the opposite.

The default file based session in PHP is much faster than retrieving session values from the database, however you won't really notice the difference until you're processing hundreds of queries per second.

Vincent
  • 1,741
  • 23
  • 35
2

  • The application needs to be able to run on multiple servers without server affinity (methods that direct requests from the same client to the same server). An easy way to make sure that sessions continue to work properly is to store sessions in a central database that is common to all servers.

  • The application needs to be able to run on a shared host, where there are significant security concerns associated with storing session data in the filesystem.

  • The performance needs of the application are very demanding and require a more sophisticated storage solution for session data. There are many existing ideas and methodologies that address database performance issues, and these can be used when sessions are stored in a database.

Reference

Chris, S (2004, 14 Dec). Storing Sessions in a Database. Chris Shiflett Blog. https://shiflett.org/articles/storing-sessions-in-a-database

Salih Kavaf
  • 897
  • 9
  • 17
Nikunj K.
  • 8,779
  • 4
  • 43
  • 53