Eliminate redundancy from session authentication

Question

I have a session authentication script and it works, but I can't find a way to eliminate the redundancy where the two database queries are. One checks if the cookie has a corresponding entry, and the other retrieves user information (if the user is logged on) based on the cookie.

$con = new Database();
if(isset($_COOKIE["session"])) {
 $stmt = $con->query("select null from `sessions` where session_id like :1 and ip_address like :2 and user_agent like :3",
 $_COOKIE["session"],
 $_SERVER["REMOTE_ADDR"],
 $_SERVER["HTTP_USER_AGENT"]
 );
 if($stmt->rowCount() === 1) { // Session is valid
 $stmt = $con->query(
 "select users.user_id, users.nickname, users.email, users.analytic_bool, users.chat_bool,".
 "users.news_bool, users.custom_bool, users.entry_time from `sessions`, `users` ".
 "where users.user_id like sessions.user_id and sessions.session_id like :1 and sessions.ip_address ".
 "like :2 and sessions.user_agent like :3",
 $_COOKIE["session"],
 $_SERVER["REMOTE_ADDR"],
 $_SERVER["HTTP_USER_AGENT"]
 ); // Set session data
 $_SESSION = $stmt->fetchAll()[0];
 $_SESSION["level_name"] = ucfirst(get_level_name($_SESSION["level"]));
 } else generate_new_session();
} else generate_new_session();

Note: The Database(), generate_new_session(), and get_level_name() have not been included because they are not relevant.

By the second query, I already know that the session is valid so there is no need to compare the values all over again. I just can't think of an alternative.

• forsvarir: Do you really need the first query? The second will fail if the session isn't valid, what are you actually gaining?
• Krii: The first query is needed in case the session cookie does not exist in the database. I need two queries because not every session has a user id associated with it (i.e. there are sessions where no one is logged in). See where users.user_id like sessions.user_id.
• forsvarir: Can't you just do an outer join to the users table? It will return those details if there is a user, or build if there isn't one.
• Krii: I was unfamiliar with outer join (or left join in my case), but it did the trick. Thanks. Post an answer and I'll accept it.
• forsvarir: If nobody gets there before me, I'll do it in the morning, when I have a keyboard. Answers from a phone take me forever, glad it helped

Answer 1

Your two queries are essentially the same, except the second query is also joining to the users table. This suggests that you might be able to remove the first query and simply execute the second query.

OUTER JOIN

Since you've said that it is possible for a client to have an active session, but no user associated with it, then you would need to to use an outer join, which mysql seems to support, to connect to the users table. The explanation in the documentation isn't overly easy to follow, but essentially, when you do a normal join results are only returned if all the join criteria are met. If you do an outer join, then results from the outer joined table (users) will be returned if present, otherwise columns from that table will be returned as null.

You can then determine if a user has a session (a row is returned) and if they're logged on (users columns have values) from a single query.