Setting up multiple mysql databases

Question

I have a Mysql/ PHP application. I want to sell it SAS. I am thinking of a hosting company that has multiple databases available. I want a page were they login and I take them to their database. I will have many databases with identical structure. I need a way to switch them into that folder( I may modify some code for them like css) and operate their database. I could use a master table for the logins but how to connect to their database? I am concerned about security. Example: Lets say I have 3 customers. A B C I set up 3 folders on an apache server, copy code there, set unique database names like new_db_a, new_db_b, new_db_c. Now if I do it in a memory variable when a logs in it will take him to his spot. He can see the url and could easily substitute another letter and run another companies database.

I am willing to use Perl, PHP or whatever to do this. Whats the best way to do this?

Answer 1

This is a classic abstraction problem. Basically, you need to abstract the database selection process from not only the application, but also the selection screen.

Think of it terms of 'problem space'. Each application has their own (i.e. their database) and then you have one more, which is the customer login system. There is nothing wrong with using another, different database for this. Associated with each login will be database connection information. This means you can store the database information in the PHP session alongside the fact that the user is logged in (and who they are logged in as).

Now, your other application needs its database layer[1] to be taught how to look for the database information in the session. In fact, if you store just not just the database name, but the server name, a login and password, you have freedom to put some customers on the same database server, some together on another, etc. You just have get the MySQL permissions correct (not hard).

Probably the biggest fly in this ointment, though, is that if the application being selected decides to take control of the PHP session information itself. There are several ways to solve this. One is for it to copy across to its own session what it needs to connect to the correct database. A second is for the selection session to do its session management separately and possibly manually. But don't store the database information in a cookie. Not only can this can be changed by a determined user but you are ceding security to an insecure location - the browser.

[1] You do have a proper database layer, don't you? And by that I mean you have all your mysql_query() calls behind a handler, either bespoke or something like PDO.