How to save and manage the Session ID and a User ID in PHP and MySql

I need clarification on the doubts I have about PHP sessions.

I'm creating an Android app, in some activities I need to make queries to extract user data.

At the moment to do so, I send the user id to the PHP file via the hidden EditText from the Android app.

In the Android app the user id is not saved in shared preferences, but I get it through a request to Facebook Account Kit (for authentication use Facebook Account kit).

So when I need the user id, I request it from Facebook Account Kit, I get it and through a hidden field I send it to the PHP file, which I will use later in the WHERE clause of the Query.

Now, however, for security reasons, I would prefer to use the sessions to save the user id and then keep the user id in the session.

A friend of mine told me that if a hacker gets the id of the session, in which the user id is saved, it can happen that it has expired and that he can't get anything.

The problem is that I don't understand how to save the session ID and the userID and how to manage them at the database table architecture level.

I have to save the session ID in the database, I create a table called "Sessions" with 3 fields:

  • Session_ID,
  • ID_User,
  • Data_access.

To insert the user ID on the database I should always pass it from the app to the PHP file, and so if a hacker could discover the user id, I think he could very well get information about sessions with a WHERE clause ID_user = ID_user.

Quite right?

If I'm right, then what's safer than what I do?

Then if the ID of the Session changes with each access, to change it in the Sessions table in the MySql database I should switch from the Android app to PHP again the user id and through a query Update I should change the session id and date of access with ID_User = ID_User in the WHERE clause.

Exact?

If anyone has any advice for me or a criticism of how I handled the situation and has a better solution than mine, I listen to it willingly.

If I didn't understand anything about how the sessions work, then excuse me in advance for the time I stole from you.

Thanks anyway.

1 answer

  • answered 2019-04-21 11:34 Ashwani Agarwal

    PHP sessions are based on Cookies. When a user opens a webpage, PHP set a cookie in response. Browser automatically ensures that in subsequent request, that cookie is also sent in requests, hence $_SESSION variables works. In my opinion, they don't provide much of the security. Instead of User Id, now a malicious user has to get his hands on the Cookies. Always use HTTPS for securing the Requests.

    With Android app, you'll be making a custom request, so you'll have to explicitely set the cookie, once received from the very first call (You'll have to persist it on the client-side; Not a good approach).

    The usual way of working of session is as following -

    1. When $_SESSION is used to set a value for the very first time, PHP sets a Session cookie, which essentially is a random String.
    2. Behind the scene (in Backend), PHP is maintaining an Object (key-value pair) against this string value. (SessionKey1 = { key: value, key2: value2 }; this could be on Disk File system or on a Cache layer (e.g Redis), depending on your server configuration)
    3. When you set/updated/delete a key in session, SessionKey1 is modified.

    Now you can create a similar behaviour for yourself. When userId is found on Android App side, send it to backend, create a row in session table (Session Id as a Random String and UserId as UserId). With each request send this SessionId alongwith the request (in the body or headers), on the backend check if sessionId received exit in Sessions table. If yes, get user Id and process.

    Here is an alternative approach (probably a-bit more secure)

    When Access Token is found after Account Kit verification

    • send it backend
    • validate the Token from Facebook APIs (Link)
    • Validation API also return user info, map it to your User's Data (from your DB Structure).

    (This will ensure that Token is always Valid, user won't be able to put a random token in order to attack as that will fail the verification from Facebook APIs).

    Generate a UUID, create a row with

    • UUID as sessionId,
    • Your userId
    • Access Data
    • Created At (when row is created)
    • Expiry (Current Time + X days)

    send this UUID in response.

    Now on Android side, save this UUID somewhere. Create a request Intercepter, for all the request set this UUID in a custom header (e.g X-<application_name>-Auth). On backend side for each request, access this Header, check if it's expired, get the User Id from your session table, and proceed.

    I think he could very well get information about sessions with a WHERE clause ID_user = ID_user.

    Quite right?

    You are absolutely right. But, securing the DB layer should be a separate task from the Application development. How do you think a Hacker would be able to execute queries in the first place? If one finds a way to run raw queries on DB, he can do a lot more damage than just extracting the data.

    If I'm right, then what's safer than what I do?

    There is not right answer, you just have to do your best to secure the application. Ensure that your application is following best security practices. For example - Use HTTPS, preventing SQL Injection.s (Search for OWASP Vulnerabilities)