Switching databases in PHP

Question

I'm new to PHP, so this question might best be answered by a brief explanation of fundamentals of PHP rather than addressing what I'm trying to do (although that would be useful too).

I want to set up multiple databases for my application, based on user. When the user logs in, it would authenticate them against DB-1, and retrieve from there which database is to be used for everything else.

I define my login.php file as follows:

<?php
    $db_hostname = 'localhost';
    $db_database = 'testing';
    $db_username = 'someuser';
    $db_password = 'somepass';
?>

and then I have another PHP file defined as follows:

<%php
require_once 'login.php';

$db_server = mysql_connect($db_hostname, $db_username, $db_password);
if (!$db_server) die ("Unable to connect to MySQL: " . mysql_error());

mysql_select_db($db_database)
    or die ("Unable to select database: " . mysql_error());

function check_login($user, $password) {
    $user = mysql_entities_fix_string($user);
    $password = mysql_entities_fix_string($password);
    $password = secure_password($password);
    $query = "SELECT DB_NAME, DB_USER FROM USERS WHERE USER_NAME='$user' AND PASSWORD='$password'";
    $result = mysql_query($query);
    if (!$result) 0; //no access to the database
    elseif (mysql_num_rows($result)) {
        $row = mysql_fetch_row($result);
        $db_database = $row[0];
        $db_username = $row[1];
        $db_password = $password;
        return 1; //login succeeded
    } else {
        return 2; //login failure
    }
}
?>

What I am wondering is about the end of the function check_login(). Assume that the USERS table would return the name of the database (e.g. 'db123522') and the username for that database (e.g. 'jsmith'), and the password would be the same as their salted and hashed password. That is, each user would have their own database, with a generated user name and a password matching their salted+hashed password.

In this case, if my second PHP file had another function to access the database, how would I go about ensuring that it would use the new database definition, and not the database definition from login.php?

What is retained in memory between one call to the functions in this PHP file and the next call, and can this differentiate between users? If I have to put some of this information into the session so that I can load the appropriate database on the next call, what would be the minimal amount of information to let me do this, without compromising the security of the application (that is, I obviously don't want to put a password into the session)?

Is there a better way to do this (I'm sure there is, but can someone explain it to me or point me in the right direction)?

Answer 1

If you want to connect to multiple databases, be aware that mysql_connect() returns a resource for that database, which can be used as an optional parameter with mysql_select_db(), mysql_query() and so on.

If you don't specify the link to use, the last one opened will be used so:

mysql_connect($hostname, $username, $password);
mysql_select_db($database);
...
mysql_connect($host2, $user2, $pass2);
mysql_select_db($db2); // uses the database just opened
mysql_query('SELECT * FROM sometable'); // also uses the second database

or, more explicitly:

$res1 = mysql_connect($host1, $user1, $pass1);
$res2 = mysql_connect($host2, $user2, $pass2);
mysql_query('...', $res1); // use first database
mysql_query('...', $res2); // use second database

That all being said, rarely is it a good idea to different databases by user as a partitioning scheme. For one thing, most people tend to grossly overestimate the size or performance requirements of their databases or the likely usage or storage requirements. Secondly, it can greatly complicate things that you will probably be interested in doing, like finding all the user's (databases) that fit a particular profile. This will now involve connecting to N databases, running N queries and somehow aggregating the results rather than just running a simple (or even not-so-simple) SQL query.

Edit: You can use mysql_pconnect() if you want connections to be retained between requests. Nothing in PHP is retained between requests (barring storage to some persistence medium like the session or a database). But that's actually the beauty of PHP. Even using persistent connections involves a whole new set of problems like them going stale, needing to reconnect them and so on.

Keep it simple.

Also, storing in the session is a reasonable approach but be aware that anything in the session is readable by other sites in a shared hosting environment (although it can be hard, but not impossible, to figure out who it belongs to).