Using a single database connection script for multiple domains

[fjm]

Hello everyone and happy Sunday. :)

Can anyone advise if using a single database class for a single domain and 1 subdomain is the correct way to go? I have:

mydomain.com (and)
sub.mydomain.co m

I would like to use a single script to connect to the database. Is this advisable? The subdomain is the private side and the domain is the public side.

Thanks,

Frank

[fjm]

bump.

Should have started a new post.. I'm losing my mind. :)

[whiteyoh]

im guessing that your pages are protected in some way?

if you do wouldn't it just require you to use the include statement? using it after the page security would mean access control would have to be passed before being able to see that page

[ak1dnar]

Could you please tell us more on your public side and private side. basically your domain and subdomain is available for public access right? In other words can anyone access http://sub.yourdomain.com. ?

In generally subdomain is nothing but its again another dir in public_html dir (I'm talking about apache on linux). if you really want to put your secure files out side the public_html folder, you can store them in a top level derectory of your server. then public users or programs can't access those files. but Php-Engine will be able to read the top level dir s on the server.

-Thanks

[fjm]

Could you please tell us more on your public side and private side. basically your domain and subdomain is available for public access right? In other words can anyone access http://sub.yourdomain.com. ?

In generally subdomain is nothing but its again another dir in public_html dir (I'm talking about apache on linux). if you really want to put your secure files out side the public_html folder, you can store them in a top level derectory of your server. then public users or programs can't access those files. but Php-Engine will be able to read the top level dir s on the server.

-Thanks

Hi and thanks everyone for the responses. I will explain what I have done and if someone sees a security issue, please let me know.

mydomain.com is the public access side and then I have admin.mydomain. com which is the private side. The public side uses a database connection script as well as the private side.

I have a login box on the public side and have decided to log the user into the private side using the public side database script. Once the user is on the private side, the private side database script takes over. I just wanted to keep all the queries on the public side seperate from the private side. Is this a good practice?

There are no real "files" to move, it is just a private site with normal html however, only people that are authorized to be there should have access.

[pbmods]

Heya, Frank.

Generally, you should be able to re-use the same class because if you designed your DBAL properly, it won't have any domain-specific code in it.

The trick comes in when you want to use different login credentials (perhaps the private domain has INSERT and DELETE privileges on certain tables that you don't want the public domain to be able to run). But all you need to do there is supply a relative path to the credentials.

E.g.,:

[code=php]
$dbCredentials = require_once "{$_SERVER['DOCUMENT_ROOT']}/../includes/dbCredentials.p hp";

$db = new database($dbCre dentials);
[/code]

Or something to that effect. Since $_SERVER['DOCUMENT_ROOT'] will be different depending on the subdomain, you can use the same code, but it will generate different results per subdomain.

[fjm]

Hey Pbmods,

Thanks for the response. Please picture what I am trying to do here. I have a main domain and a sub domain. The main domain is public and the sub is private and is where my app will reside.

I have a login box on the PUBLIC side which used the DBAL script that we were discussing the other day. I have all of my calls to the dbal class in my process script as you and Atli suggested I do. Works great by the way (Thank you! :) )

The problem arose the other day when I implemented a session store. My process script is the default page which starts the initial session.

I quickly learned that the session that is initiated is the session used for the duration of the visit. That being said, this login box is the authentication method into the sub domain.

Now, the public side uses a dbal also and I was thinking that I should probably only be using 1 dbal script for both sides, then that didn't seem like it would be secure plus why would I want queries executed from the app whilst the public queries were running off the same script. For some reason it just doesn't sit well with me. I am really concerned about security on this one.

What I did was use the dbal on the app (private) side to log the user into the subdomain from the public side.

So to recap, user logs into private side from public side using dbal from Private side. dbal on public side starts the session store and runs the queries for the public side.

If you say I should be using 1 script, I will take your suggestion and rework it to use 1 dbal. What are your feelings?

Thanks for the help and direction Pbmods!

BTW: Sorry I didn't have a chance to check back sooner, I kind of got tied up and it slipped my mind.

Frank

[pbmods]

When you are require()'ing files in PHP, PHP doesn't particularly care where the files are located. You can put a file in one location and access it from a completely different one (technically, you could even host the file on a totally separate server, but let's not go into orbit here).

As long as your DBAL script is generic enough that it could run on *any* domain, you only need one. The instant you start putting domain-specific code in there, you lose that flexibility.

So for example, if you are including queries or credentials in your DBAL script, you'll need a separate one for each domain.

If, on the other hand, you created a DBAL script in a common location that *only* implemented (rough example here:) a connect(), disconnect() and query() method and then stored the queries themselves in separate file on each domain, you could make this setup work quite nicely and only have to maintain a single DBAL script.

So you might have a setup something like this:

• /webroot/common/includes/dbal.php
• /webroot/common/includes/session.php
• /webroot/subdomains/public/includes/sess_public.php
• /webroot/subdomains/private/includes/sess_private.ph p

dbal.php would contain your generic DBAL:
[code=php]
class DBAL
{
public function __construct( $hostname, $username, $password, $database ) { ... }
public function connect( ) { ... }
public function disconnect( ) { ... }
public function query( $sql ) { ... }
}
[/code]

session.php would probably be abstract or an interface:
[code=php]
interface Session
{
public static function start( );
public static function stop( );
}
[/code]

And then you could put your subdomain-specific code in the session.php file for each subdomain:

[code=php]
require_once '/webroot/common/includes/session.php';

class Session_Public implements Session
{
public static function start( ) { ... /* Login stuff for public subdomain. */ ... }
public static function stop( ) { ... }
}
[/code]

And a similar file for your private subdomain.

This way you can consolidate all your generic code into a single file while still retaining the flexibility to customize your app's behavior based on the subdomain.

[fjm]

If, on the other hand, you created a DBAL script in a common location that *only* implemented (rough example here:) a connect(), disconnect() and query() method and then stored the queries themselves in separate file on each domain, you could make this setup work quite nicely and only have to maintain a single DBAL script.

Ok, this is exactly what I currently have. I am using two completely seperate databases though. One database is for the public side and the other is for the private side.

Actually.. you've inspired me. I am going to put the "common" classes in a common location and try and follow your map above. I will post back with any questions. :) Thanks Pbmods!

[pbmods]

There's two ways that I would approach this situation.

You could create a factory class (I'm a particular fan of this one, especially combined with a singleton DBAL connection):

[code=php]
class DB
{
const
DB_PUBLIC = 'publicDBName',
DB_PRIVATE = 'privateDBName' ;

public static function getDB( $database = self::DB_PUBLIC )
{
switch( $database )
{
case self::DB_PUBLIC :
case self::DB_PRIVAT E:
return new DBAL('hostname' , 'username', 'password', $database);
break;

default:
/** Error condition. */
break;
}

/* Or:
switch( $database )
{
case self::DB_PUBLIC :
$username = '...';
$password = '...';
break;

case self::DB_PRIVAT E:
$username = '...';
$password = '...';
break;
}

if( isset($username ) and isset($password ) )
{
return new DBAL('hostname' , $username, $password, $database);
}
*/
}
}
[/code]

And then simply define your DB_NAME on a per-subdomain basis:

On the public subdomain, you might do something like this:
[code=php]
define('DB_NAME ', DB::DB_PUBLIC);
[/code]

And on the private subdomain, you might do something like this:
[code=php]
define('DB_NAME ', DB::DB_PRIVATE) ;
[/code]

Anywhere you need to create a new DB connection, you could then simply use this code:
[code=php]
$db = DB::getDB(DB_NA ME);
[/code]

Now this works reasonably well, but you would be putting domain-specific code in a common file, which might not be desirable depending on how you want to be able to scale your application.

An alternative approach would be to simply create a credentials file on each subdomain. For example, the public one might contain a getDB.php that looks like this:

[code=php]
return
new
DBAL
(
'public_hostnam e'
, 'public_usernam e'
, 'public_passwor d'
, 'public_databas e'
);
[/code]

You might also want to consider making DBAL into a singleton, but that is beyond the scope of this thread.

[fjm]

Hmm.. Ok, I see what you are doing I think. I never thought about using a switch for that. Oh... your good Mr. Pbmods. :)

What I did since my last post was put the DBAL and session scripts into a common dir under my docroot and changed the inc path, then deleted the second DBAL script I was using. I could not connect afterwards, so I modified the DBAL constructor to accept the DBName of the script calling it then it worked.

Let me ask you.. What would the advantage be of using a switch and DEFINE in the scripts to call the DBAL over the way I have it now?

Thanks PBmods!

EDIT:
I think I can answer my own question. Correct me if I am wrong, but I could just define the DBAL at the top of the script and go. Instead of changing the DBName (the way I currently have it) wherever and however many times I am instantiating the object. Right?

[pbmods]

This is correct, Mr. Fjm.

Incidentally, you might also find this useful if you want to have a separate development database. This way you can test features and fix bugs without affecting your production data.

You might end up with something like this:
[code=php]
switch( $database )
{
/** Most common one goes first b/c switch stops as soon as it hits a match. */
case self::DB_PUBLIC :
break;

case self::DB_PRIVAT E:
break;

case self::DB_PUBLIC _DEBUG:
break;

case self::DB_PRIVAT E_DEBUG:
break;
}
[/code]

It's a lot easier to keep track of your DB credentials when you only have to maintain them in one location.

[fjm]

I think I may have hit a bug that I don't know how to fix. I don't know where or why but after I consolidated the DBAL scripts, I now have a mysql db error at the bottom of the page. "table site.sessions doesn't exist". The sessions table resides on the "app" database and not on the "site db.

I have this call at the top of my script for the session store:
[PHP]require_once(". ./common/inc/db.php");
$db = new Database('app') ;
[/PHP]
Then I have this below in a switch for the public website:
[PHP]$db = new Database('site' );
$id = (int) $db->QuoteSmart($id );[/PHP]
The "app" and "site" are the dbs that are being passed to the constructor.
These are the only two calls in the script and the session table is being updated so I am not quite sure what is happening here. Anyone care to take a stab?
Here are the relavent lines in the db class:
[PHP]
private $_LinkID;
public $Query;
public $Row;
public $NRows;
public $Record;

public function __construct($db Name)
{
$this->LinkID = null;
$this->Query = "";
$this->Row = 0;
$this->NRows = 0;
$this->Record = array();
$this->Connect('local host','dev','pa ssword');
$this->SelectDB($dbNa me);
}

public function Connect($host,$ user,$pass)
{
$this->_LinkID = mysql_connect($ host,$user,$pas s) or die(mysql_error ());
return $this->_LinkID;
}

public function SelectDB($dbNam e)
{
mysql_select_db ($dbName);
return true;
}[/PHP]

Thanks,

Frank

[pbmods]

Are you using a custom session handler (via session_set_sav e_handler() (http://php.net/session_set_save_handler))?

Somewhere in your 'site' code, there's a query run on the session table (perhaps in a common include file somewhere? The login/logout script, perhaps?).

[fjm]

Are you using a custom session handler (via session_set_sav e_handler() (http://php.net/session_set_save_handler))?

Somewhere in your 'site' code, there's a query run on the session table (perhaps in a common include file somewhere? The login/logout script, perhaps?).

Yes Pbmods, I am using session_set_sav e_handler.

I have looked and looked. I cannot find anything where the session store is using the "site" DBAL.

Is there a debugger or something I can use? Maybe something that will step me through what is happening so I can where it is messing up?