1

I developing something using PHP/MySQL. Architecture is such that each user will have own (different) DB name, inside that database, tables are called same for everyone. So basically if "bad" user can switch his DB name to someones else - he get access to someone's data means that security is breached.

  • What is best way to keep such user specific data such in my case his database name?
  • But I guess that answer will be $_SESSION superglobal ? If so how safe is it? Can user (httponly) change value of variable stored in $_SESSION? Particularly if I keep plain db_name in there? Or if I paranoid about it should I hash that db_name -> keep it in session -> read it -> unhash on server side -> give user his database? If so any good advice what encrypting/decrypting I could use? (I have no idea tbh)

Many questions, but they all pretty narrow - thank you in advance!

Edit: I'm afraid i didn't explain myself right:

By different database (or database name) i mean same MySQL instance, just different databases inside (like CREATE DATABASE "sec_login"; USE "sec_login"; in terms of SQL language) so for example I'll have following databases:

  • sec_login (with tables: members,loging_attempts, etc..)
    ...
  • db_user1
  • db_user2
    ...
  • db_user1001

So whenever e.g. user2 logins it gets his $db_name = db_user2; when user1001 comes $db_name = db_user1001; and then program will serve them with same logic regarding tables inside those databases.

So yes my question was where to keep in this case value of $db_name = db_user2;
Thank you @ Nathan Hazout I got your answer about where does $_SESSION being kept. And thanks for a good link! Useful and bookmarked for reading it second time :)

About safety: I totally agree on "...as safe as you make it." but just wanted to know what are the odds that "bad" user can get access to someone else's $_SESSION. Correct me please if I'm wrong - to steal others variables in $_SESSION is same as to steal others session_id, right?

halfer
  • 19,824
  • 17
  • 99
  • 186
user2997497
  • 159
  • 1
  • 2
  • 11
  • 3
    `each user will have own (different) DB name,` **A really bad design ! Change it !** – Shankar Narayana Damodaran Dec 31 '13 at 07:39
  • 1
    I think your question was understood fine the first time around. Have a single MySQL instance running just a single database. Put all the members that you would split across several databases in one `members` table, then add a foreign key to store ownership. The design you propose will get you into a right pickle, unfortunately! – halfer Dec 31 '13 at 09:57

3 Answers3

1

Data stored in the $_SESSION variable will stay on your server, not in the cookies if that's what you are worried about. What is stored on the client's side is simply a session ID, a kind of identifier so your server will know which $_SESSION data to load.

See for example Is my understanding of PHP sessions correct?

That being said, your code is only as safe as you make it.

I won't go over the whole "each user will have own (different) DB name", I simply don't understand your choice of architecture...

Community
  • 1
  • 1
Nathan H
  • 48,033
  • 60
  • 165
  • 247
  • Thank you for the answer. Please read my [EDIT] above hope I clarified it a bit. – user2997497 Dec 31 '13 at 09:44
  • 1
    I understood the first time. I still don't see why you would need this. It's not very standard. Usually you store everything in the same database and only serve what the user needs. As far as security, I think sessions are good enough. – Nathan H Dec 31 '13 at 10:01
  • Perhaps using different DBs gives way to SELECT things faster? May be keeping limits of data will be easier (when it comes an issue). I don't know, just finding advateges of already implemented approach. Designing it for some UNKNOWN reason I didn't take that approach as an option. But probably real reason is that I wanted to focus on users fuctionality NOW :) And didn't consider many options for big picture :) Thank you for replys - I'll go with SESSION as it keept on server side only. – user2997497 Dec 31 '13 at 10:26
1

If you add a user table, any entities that can be owned by a user can just get a user_id foreign key. I have heard of some financial companies insisting on separated databases (for paranoid security reasons) but in general having a single database is a much better idea. This will mean that extracting system-wide meta information (such as "which user is carrying out the highest number of transactions?") is much easier - if you have separate databases, you may not be able to make the necessary joins between tables.

In general, users do not have the ability to change the contents of the $_SESSION super-global, but you need to check your code doesn't give them that ability! Thus, I wouldn't bother with the hashing thing (and, technically, there's no such thing as unhashing - you may be thinking of encryption).

halfer
  • 19,824
  • 17
  • 99
  • 186
  • Thank you for the answer. In fact it is same DB - so I'm able to do the JOINs ets... Just ment different USE db_1 or USE db_2...
    And yes I was talking aparently about encryption. I edited post above.     – user2997497 Dec 31 '13 at 09:47
  • By "it is same DB", I think you mean "it is the same server" - if you are switching databases with `USE` then they must be different databases. So yes, my answer is have a single database, with just a single `USE` value. – halfer Dec 31 '13 at 09:51
  • Oki seems that terminology now fixed :) Can't really prove that my approach of using different user owned db's on one db server is better (perhaps that gives me ability to limit size of users db... if later it will be important) also (perhaps easier to drop user, that doesn't matter though) not sure. But functional logic of my program is built already and built that way that each new user gets new clean db and populates tables there with own data. Do you foresee some big problem about it? May be when I'll want to host it at some hosting? Security wise - a user shouldn't know name of his db... – user2997497 Dec 31 '13 at 10:20
  • The big problem is that it is way more complicated than it needs to be. It's true you can do cross-database joins in MySQL, but really, your approach offers you complexity with very little in the way of advantages. Personally, I would throw everything you have into version control, and then refactor so it works on just one database. – halfer Dec 31 '13 at 10:23
0

I guess you have your reasons for different db per user thought I never heard about this kind of architecture. Anyhow, don't store db name on session, store some hash that identify the user, hold on a general db a users table, when user logged in put his current session id in this users table, this way when user give you session id, you fetch db name from this table. It's hard to guess session id cause its random sequence of characters.

Chen Kinnrot
  • 20,609
  • 17
  • 79
  • 141