[TYPO3-dev] Tangled-up user authentication

[Christopher Lörken]

Hello everybody,

I've got some questions regarding the Typo3 4.2 code for authenticating 
FE sessions and logging in users.

In short, the background is that we have experienced some performance 
issues on our site. We are running a browser-game which uses a Java 
backend application (same physical server) that causes quite some load 
on the database leaving us with a mere 20% of SELECT queries in total. I 
tried to profile our DB and why it was running slow using Jet Profiler 
(if anyone has tips for better programs, please tell me!!). To my 
surprise, Jet Profiler showed that roughly 60% of the time the database 
needed was spend in fe_users and especially in fe_sessions tables....

60% of MySQL time spend in those two tables? That puzzled me so I 
XCLASSed t3lib_db to see what queries are executed and I found, that:

- for not logged in users, each page hit causes (summed)
	- 2 SELECT call on fe_session_data
	- 1 SELECT call on fe_sessions
	- 2 SELECT calls on fe_sessions joined with fe_users
	- 2 DELETE calls on fe_sessions
- for a logged in user with a valid cookie:
	- 1 SELECT call on fe_sessions
	- 1 SELECT call on fe_session_data
	- 1 DELETE call on fe_session_data (every 100th call)
	- 2 SELECT calls on fe_sessions joined with fe_users
	- 2 UPDATE calls on fe_sessions
	- 1 Update on fe_users if "is_online" it at least 60 seconds old.


So in total there are 7 DB calls for not logged in users with every page 
hit and 6-8 DB calls for logged in users with every page hit. This seems 
to me like an unnecessarily high number...

Wouldn't the following steps suffice:

1) SELECT from fe_sessions the row with the set ses_id
    a) if IPlock, ses_tstamp, and so on are correct: Proceed with 2
    b) if they are not correct, DELETE the row from fe_sessions (maybe 
fe_session_data?), keep the session ID (or assign new?) and assume this 
is a guest user. Done.
    c) if there is no such record, check the session_data table if there 
are extensions using that table (would be a nice config flag I 
guess...). Keep the session ID. Done.
2) Session data is valid, so select the fe_user and (if necessary) the 
corresponding session_data.
    a) If the user is invalid (deleted, etc.) void the session, assume 
Guest user, done.
3) Every 60 seconds, update fe_sessions.tstamp
4) Every 60 seconds, update fe_user.is_online

In other words, what I see necessary are 1-2 calls for not logged in 
users and 2-5 calls for logged in users... That is a difference (current 
worst case against possible best case) of 12 database queries...

Please, do tell me if I miss something here...




Some words about the actual implementation:

**1. Calls to fetchUserSession**
The really redundant calls that are simply executed twice in a row are 
caused by fetchUserSession(). fetchUserSession tries to get the session 
and the corresponding user with a joined SELECT over fe_sessions and 
fe_user. If it has found the row, it will (always) update the tstamp in 
fe_sessions. (There is actually a comment in the 4.2 source that it is 
probably not necessary to _always_ do that.)
That method is first called in authenticateUser() which simply does not 
use the result but merely sets $this->loginFailure to false... And what 
we find as code in t3lib_userauth is:

	$this->checkAuthentication();
	unset($this->user);
	$this->user = $this->fetchUserSession();

So as just said: checkAuthentication calls fetchUserSession and 
afterwards it is fetched right again, duplicating the JOIN SELECT and 
the fe_sessions tstamp UPDATE. While the SELECT is probably still in the 
MySQL query cache, the UPDATE will probably at least cause some load 
(although the second time at least nothing needs to be physically written).


**2. isExistingSessionRecord**
As I understand the Hotfix against session fixation... It is responsible 
for one of the SELECTs on fe_sessions in both, logged in and logged out 
cases and, when logged out, as well for 1 SELECT on session_data. The 
result of that call is again only true or false. The call only selects a 
count(*) instead of reading that row for later usage.


**3. isExistingSessionRecords influence on the session ID and the MySQL 
query cache**
When a user is not logged in, the Method results in a change of the 
fe_typo_user session ID with every page a user loads. This effectively 
voids any possible positive effect of MySQL query cache since the looked 
for key is never the same. Remember here that there are two selects, one 
on fe_sessions, one on fe_session_data wich use that key. Changing the 
sessionID every time does not only make no use of the MySQL query cache, 
it also spams it with queries which are never again to be experienced...


**4. fe_session_data**
We are not using that many extensions, but I did not yet come along one 
which actually uses the fe_session_data table. I know there are some 
basket case implementations and such, but I'd guess that there are many 
Typo3 installations who do not actually need that table. Here it would 
be nice to get rid of the overload of checking that table with every 
pagevisit (up to 2 times!) and from time to time issueing DELETE queries 
on an anyway emtpy table. I'd suggest a config flag which disables that 
table and skips those unnecessary database calls.


**5. JOINED select on fe_sessions and fe_users**
I'm very unsure about this last point, as I'm not an expert when it 
comes to databases, but I kind of wonder, if there may be performance 
problems and slow queries by having fe_users a MyISAM table... The table 
is read heavily but is also frequently updated by changing is_online 
tstamps... Since those updates lock the whole table they probably would 
block reads and joined reads even back in the fe_sessions table... My 
proposal above would split the calls to fe_sessions and fe_users, mainly 
to be able to know that there is a session record for an ID even by 
ignoring an IP lock or ses_id of a disabled user.

Well... I Don't really know where I'm aiming at here but I've got an 
unsure feeling ;)



So... I'm about to XCLASS tslib_feuserauth to rewrite necessary parts 
for a "slim" authentication. What I would really like to know is if I 
have overlooked some crucial part in my simple setup... I

especially do not understand the famous session fixation problem and why 
the ID always changes...

All comments on any of this are highly appreciated.

Have a nice Sunday,
Christopher