FuelPHP Forums

webdev25

How do you programmatically create and destroy database connections in the db.php config after the Database class has already initialized?

I am creating an app which will use 2 databases. The idea is to store each 'sites' data in it's own database, the lookup will be done based on what the url is via a 'global' database.

The app will check the 'global' database for sites using the domain 'http://www.example.com/', the record for this domain will contain connection information for the database that needs to be loaded for 'http://www.example.com/'s data.

The app will then set the default connection for the database to whatever it retrieves from the initial database lookup.

Then if possible, destroy or remove the connection information for the 'global' database, removing it from memory and stopping it being used in the application after that point.

I have run into some problems though, i have created two database connections in 'app/config/development/db.php', one called 'global' and one called 'default'. 'default' is an empty array, 'global' contains the connection information to the 'global' database for the initial lookup.

The problem is when i try to select data from the 'global' datasource and the database class initialises it tries to connect to the 'default' database connection first resulting in an error, even when i execute DBUtil::set_connection('global'); before anything else database related.

removing the key/value for 'default' in the db.php config file also causes the same error.

I have been reading through the documentation and i can't see a way to 'set' or 'update' or 'remove' the connection information for a database connection(s) programmatically, so even after i have the connection information from the first lookup, i don't know how to tell FuelPHP to connect to the database without putting it in config files.

After looking up the database connection details from the 'global' database, i would then like to destroy it and remove it from the config during run-time.

How would i go about getting the above to work?

Cheers :)

Harro

Whatever the default database is (the one used if you don't specify one) is determined by the configuration key 'active'. If not defined in your db.php, it defaults to:

    /*
    * If you don't specify a DB configuration name when you create a connection
    * the configuration to be used will be determined by the 'active' value
    */
    'active' => 'default',

If you haven't changed this to 'global', and something tries to connect to this database, then you have something in your app that does. DB sessions for example use the default database if you haven't specified a specific database to use in the session config,

You can use the profiler (enable both in the global config and for each of the database definitions in your db.php, even for your dummy default) to see which queries are executed by the DB class.

If you want to switch databases later on in your code, all you need to do is close the active connection

\Database_Connection::instance()->disconnect();

and than make another database active:

\Config::set('db.active', 'anotherDB');

before making new DB calls.

This will make the switch transparent for all DB operations in your application. Passing a database name on a DB statement is a lot less transparent, you need awareness of this name everywhere, and having to rely on global things is bad. It is more meant to allow you to access a second database concurrently.

webdev25

I have tried a few things to make this work, i have had success with a solution but it's not ideal as i can't replace the 'default' database connection.

I did notice the 'active' setting in the frameworks database config file, i tried using it to set 'global' as the default first, rather then default and i end up getting this error:

Database type not defined in "global" configuration or "global" configuration does not exist.

I am switching to the database early on in my code before any database calls are made, are session's enabled by default? The databases(s) i'm testing with are not setup to facilitate database-session data as they don't have the tables so i'm not sure if it's that. I'm also not loading any modules other then custom one's i've built for the purpose of testing things, and none of those do anything database related so i don't think it's those doing database stuff before i switch to the 'global database'. I am using the following code to do the switch to the 'global' database

\DBUtil::set_connection('global');

# Execute database query here

My Database config looks something like this:

return array(
	'global' => array(
		'connection'  => array(
			'dsn'        => 'mysql:host=localhost;dbname=database_name_global',
			'username'   => 'username_here',
			'password'   => 'password_here',
			'persistent'     => false,
		),
		
	),
	'default' => array(
		'connection'  => array(
			'dsn'        => 'mysql:host=localhost;dbname=database_name_global',
			'username'   => 'username_here',
			'password'   => 'password_here',
			'persistent'     => false,
		),
	),
);

I have both 'global' and 'default setup because if i remove default from the config, or leave it set to an empty array i get an error:

Fuel\Core\Database_Exception [ Error ]:
invalid data source name

It traces to the line of code where i try to execute a select statement, this is the line immediately after "\DBUtil::set_connection('global');" So i'm not sure if i'm doing this part right?

When i set the 'active' value in the database config this this i get the following error:

Database type not defined in "global" configuration or "global" configuration does not exist

This traces to the same line of code executing a select statement, right after "\DBUtil::set_connection('global');". This is how i'm setting 'active' in my database config

return array(
	'global' => array(
		'connection'  => array(
			'dsn'        => 'mysql:host=localhost;dbname=database_name_global',
			'username'   => 'username_here',
			'password'   => 'password_here',
			'persistent'     => false,
		),
		
	),
	'default' => array(
		'connection'  => array(
			'dsn'        => 'mysql:host=localhost;dbname=database_name_global',
			'username'   => 'username_here',
			'password'   => 'password_here',
			'persistent'     => false,
		),
	),
	'active' => 'global',
);

I have also tried the above with the 'default' entry completely removed and get the same error about 'global' not existing in the config

So i have attempted to do it without the 'global' and 'default' connections, and instead just try to replace default during run-time and re-connect to the new database afterwards. The db.php config looks like this:

return array(
	'default' => array(
		'connection'  => array(
			'dsn'        => 'mysql:host=localhost;dbname=database_name_global',
			'username'   => 'username_here',
			'password'   => 'password_here',
			'persistent'     => false,
		),
	)
);

The code i'm using looks something like this this:

$db_name = 'database_for_user_'.$site['site_slug'];
$db_pass = get_password_for_user($site); //this returns a string
$db_user = get_username_for_user($site); //this returns a string
$db_config = array(
	'type'=>'pdo',
	'identifier'=>'',
	'table_prefix'=>'',
	'charset'=>'utf8',
	'collation'=>'',
	'enable_cache'=>true,
	'profiling'=>false,
	'readonly'=>false,
	'connection'  => array(
		'dsn'        => 'mysql:host=localhost;dbname='.$db_name,
		'username'   => $db_user,
		'password'   => $db_pass,
		'persistent'     => false,
	),
);

# Disconnect from the database, not sure if this line is right?
\Database_Connection::instance()->disconnect();

# Replace the 'default' database connection in the config
\Config::set('db.default',$db_config);

# Set the current database to active to reload settings, i think these two lines do the same thing?
\Config::set('db.active', 'default');
\DBUtil::set_connection(null);

# Execute query on 'user-specific' database
$test = \DB::select('*')->from('test')->as_assoc()->execute();
$test = $test->as_array();
print_r($test);

I then get the following error:

Fuel\Core\Database_Exception [ 1146 ]:
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'database_name_global.test' doesn't exist with query: "SELECT * FROM `test`"

The error above traces to the line "$test = DB::select('*')->from('test')->as_assoc()->execute();"

I left the "'persistent' => false," line in the config files above in the hopes that it would help solve the error above, by needing to actually re-connect to the database server on the next query, rather then using the existing one, it makes no difference and i get the same error. If i change the above code to the following:

...
# Replace the 'default' database connection in the config
\Config::set('db.default_test',$db_config);

# Set the current database to active to reload settings, i think these two lines do the same thing?
\Config::set('db.active', 'default_test');
\DBUtil::set_connection('default_test');
...

Everything works fine, but it leaves the 'default' config in memory as a print_r of \Config::get('db'); show's, it also means i need to make sure i explicitly tell other modules to use the 'default_test' connection which seems more hastle then it needs to be when 'default' would be fine. The application does not need the 'global' connection to the database at any point after the switch-over too.

Doing a print_r of the database config shows the new connection settings have replaced the 'default' settings, but it seems as if the database module has a 'cached' version of these values that it holds, so when you change the config it's not reading the new settings and switching to the new database, because it's already been 'loaded' by the first database query.

I'm not sure if this is me doing something wrong or if it's a bug or something?

Harro

DBUtil contains schema manipulation methods, it has nothing to do with database selections whatsoever.

You are getting the 'data source' error because the active DB is still
set to 'default', so you can't remove the 'default' definition.

The only way so change the active database is the way I described it in my previous post. Why are you trying something completely diffferent? Please read it again.

webdev25

I did try the thing's you recommended and only had a partial success, using two different database connections 'default' and 'default_test' in my example worked but my goal was to replace the 'default' with the new database configuration, trying to do this using your examples did not work and still produced errors.

Replacing the 'default' configuration in the Config work's just fine, i can print_r the value of \Config::get('db.default'); before and after updating the 'default' database settings in the config and FuelPHP's config system registers the changes just fine

After looking through the Database_Connection and Database_PDO_Connection classes i've noticed that when constructed the class stores the config for it's database in the "protected $_config;" variable, this variable is set using

/**
 * Stores the database configuration locally and name the instance.
 *
 * [!!] This method cannot be accessed directly, you must use [static::instance].
 *
 * @param string $name
 * @param array  $config
 */
protected function __construct($name, array $config)
{
	// Set the instance name
	$this->_instance = $name;

	// Store the config locally
	$this->_config = $config;

	// Store the database instance
	static::$instances[$name] = $this;
}

The problem is that when you issue a disconnect() and connect() on the default database instance, it will disconnect and reconnect fine, but because the config information is stored in the local $_config variable, the updated connection info for 'default' in the configs \Config::get('db.default') is not reloaded, instead the old values in $_config are used which is why it re-connects back to the old, now incorrect database rather then the new database settings i specified, it also explains why using 'default' and 'default_test' in my examples did work ok.

The connect() and disconnect() methods do not reload the database configuration from the fuelphp config system, they only destroy and re-create the PDO object, but use the 'cached' config that was declared at startup to re-connect, rather then using the one now specified in the FuelPHP config.

If $_config in the Database_Connection class could be re-loaded from the config after dis-connecting i think this would work just fine, $_config is not public though and from what i can see (please correct me if i missed it) there's no method i can use to reload the config so accessing and resetting it manually from outside the Database_Connection class instance is not possible?

Harro

The problem is that you're still trying to do things the system is not designed for.

You should NOT alter database configurations at runtime! You can ADD new configurations as much as you like. The name of these configurations is not relevant, and it is not required to have a configuration called 'default'.

You must have at least one configuration defined when you do your first DB call, and you must have a config value called 'db.active' which must contain the name of a valid configuration.

So this works fine:

// db.php configuration file
return array(
    'active' => 'databaseA',
    'databaseA' => array(
        'connection' => array(
            'dsn'        => 'mysql:host=localhost;dbname=databaseA',
            'username'   => 'username_here',
            'password'   => 'password_here',
            'persistent'     => false,
        ),
    ),
    'databaseB' => array(
        'connection' => array(
            'dsn'        => 'mysql:host=localhost;dbname=databaseB',
            'username'   => 'username_here',
            'password'   => 'password_here',
            'persistent'     => false,
        ),
);

/**
* example code
*/

// run a query on the active DB: databaseA
$test = \DB::select('*')->from('test')->as_assoc()->execute();

// close the connection to databaseA
\Database_Connection::instance()->disconnect();

// switch to databaseB
\Config::set('db.active','databaseB');

// run a query on the active DB: databaseB
$test = \DB::select('*')->from('test')->as_assoc()->execute();

// close the connection to databaseB
\Database_Connection::instance()->disconnect();

// create a runtime definition for databaseC
$db_config = array(
    'type'=>'pdo',
    'identifier'=>'',
    'table_prefix'=>'',
    'charset'=>'utf8',
    'collation'=>'',
    'enable_cache'=>true,
    'profiling'=>false,
    'readonly'=>false,
    'connection' => array(
        'dsn'        => 'mysql:host=localhost;dbname='.$db_name,
        'username'   => $db_user,
        'password'   => $db_pass,
        'persistent'     => false,
    ),
);
\Config::set('db.databaseC',$db_config);

// make this config active
\Config::set('db.active','databaseC');

// run a query on the active DB: databaseC
$test = \DB::select('*')->from('test')->as_assoc()->execute();

webdev25

I understand you can create multiple database connections like in your example but this was not what i was trying to do.

I have implemented a work-around that reloads the configuration for the database connection with one i can specify, this works a charm. However you mentioned that i should not do this, can i ask why and what problems it could cause? Even though my work-around seems to work fine, what areas am i likely to run into trouble with?

webdev25

I put the following function inside the Database_PDO_Connection class in the core files and this allow's me to replace the 'cached' config for the database instance, then disconnect and reconnect to start talking to the new database. This works, and I can update the values in the config using \Config::set('db.default',$db_config); incase any modules from that point on in the code need to reference to the config and also removed it from memory too which is a bonus. The code i'm using to do this in the class is:

public function reload_config(array $config) {

		$this->_config = $config;

		$this->disconnect();
		$this->connect();

		return true;
		
	}

And in my application i just use:

...
\Database_Connection::instance('default')->reload_config($db_config);
\Config::set('db.default',$db_config);
...

Not the cleanest solution but it does meet all of my fairly simple requirements, the only problem is i do not want to modify the core files, i have had a quick go at extending the core Database_PDO_Connection class with no luck, i do not want to replace it completely, just add the new reload_config() method.

I was reading the documentation on this and at the top of the page it says

Extending but not replacing the core class
These are the easiest and will work like any other class you create; just make them extend the core class in the global namespace:
class MyConfig extends Config {}

I have tried creating a class in app/classes/database/pdo/connection.php and extending it like:

class MyDatabase_PDO_Connection extends Database_PDO_Connection {
...
code here
...
}

But it does not work, the documentation says i need to use the auto-loader if i'm replacing classes entirely, do i also need to use the autoloader to just extend the core classes, without replacing them? The documentation isn't entirely clear on that and i'm not sure if i'm putting the connection.php file in the right folder within my 'classes' directory

webdev25

Ah ok, i think i've figured it out, in my app/classes/database/pdo/connection.php file i have the following code:

class Database_PDO_Connection extends Fuel\Core\Database_PDO_Connection {

	public function reload_config(array $config) {

		$this->_config = $config;

		$this->disconnect();
		$this->connect();

		return true;
		
	}
	
}

And i have specified my replacement class using the autoloader in the bootstrap.php file

...
Autoloader::add_classes(array(
	// Add classes you want to override here
	'Database_PDO_Connection' => APPPATH.'classes/database/pdo/connection.php',
));
...

and removed my code from the core files, and everything works with no problems now, i'm guessing i can do a similar thing for the mysql and mysqli drivers too.

Harro

Why go through all this trouble if all you have to do is change the value of 'db.active' to switch to a different database (definition)?

I still struggle to understand why you want to make life so difficult for yourself.

Extending or modifying core code is something that should be used as a very last resort, it will make future upgrades difficult, not to mention changes in the core that can break your application.

webdev25

setting db.active did not seem to work when done via the config and resulted in errors, also the Database_connection class not reading the values from the Fuel Config and instead using the values which were present at start-up, even when issuing a disconnect and connect seems like a bug maybe?

I'm not sure if there's a reason behind this particular design choice i don't understand (e.g maybe a PHP Class can read a private variable quicker then it can retrieve that info from the Fuel Config class each time it needs it?), or if it was just simply built that way.

Also what make's me uncomfortable with leaving the 'global' database connection active and accessible via the Config class is it would have access to connection information for other sites, in the event a hacker manages to dump the contents for the Fuel Config at least they will likely only see connection information for the site-specific database, further more only one bit of user input data is handled in the code before the switch to the site-specific database, and it is checked thoroughly, it's very likely that any exploit will happen after the switch, unless it's an exploit in Fuel i guess then it may be more possible, but if the global database connection does not exist any more, and connection details have been removed.

This increases the cost for the attacker to gain access to that information via some routes, of course if they can read from the file system and see the source then it doesn't do much good however unless they can execute code remotely the global connection details in the config file will be difficult to do anything with.

Being able to just destroy and replace the global database connection with the site-specific one completely, rather then run them side-by-side as such, help's to mitigate the above scenario some-what so it was the approach i felt most comfortable with, at least that's my thinking behind it.

Harro

This is because you're changing existing configuration, which I told you you should not do. If you need to switch databases, change "db.active" to point to the new database definition. If you need to create that definition at runtime, you can just do so, before you switch. There is no need to change anything after the fact. It is only loaded when you open the connection to the database, and then you should not alter the configuration anyway!

You're trying to do something for which Fuel isn't designed to support, and I fail to see the reason why it should.

I you are worried that a hacker might be able to read data in memory (doesn't matter where it is stored), you should be worried about a lot of other things. More than about data in memory. You seem to forget that no matter what or how you do it, you need to store the credentials somewhere. And if a hacker is in, it won't matter if that is in a config file, in a Class instance in memory, or in some storage system. The hacker will have no trouble accessing it.

If this is a serious concern, you should not create an application that is and internet exposed, and contains sensitive data.

Instead, build a multi-tier application, in which you separate the html frontend from the backend using REST, have the backend in a secure datacenter, and use application level firewalls to protect the REST traffic. And design your interface so that some things are just not possible, for example don't expose a method that allows fetching all users. And don't return the users password on a fetch.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion