2

I was currently developing a module and I want to display the names of all the databases present in my Postgres server through a query. I want also to query and list the names of schemas, tables, field and field type of each databases. How can I do that? I want to do this because I was making an interface about database connections and configuration. Is that possible?

1
  • 1
    Drupal should only touch the databases that are configured in its settings.php file. Being able to read all databases on the server would be an escalation vector for hackers. Commented Jan 28, 2016 at 16:08

1 Answer 1

3

One solution can be configurate the external database as a second database in settings.php.

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'standard_db',
      'username' => 'username',
      'password' => 'password',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'postgres',
      'prefix' => '',
    ),
  ),
  'external' => 
  array (
    'default' => 
    array (
      'database' => 'external',
      'username' => 'username',
      'password' => 'password',
      'host' => 'db.external.com',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

Then before you do your database queries you need to set the active database. In this example, to switch to external you need to execute db_set_active('external');

When you want to switch back to the default database you execute db_set_active();

Example code to switch to and query the external database:

db_set_active('external');
$result = db_query('SELECT something FROM {sometable}');
while ($row = db_fetch_array($result)) {
  drupal_set_message($row['something']);
}

db_set_active();

And here are the queries that I have used for a similar project.

Getting the Databases:

SELECT datname 
FROM pg_database 
WHERE datallowconn = true AND NOT datname = 'postgres' AND NOT datname = 'template1'
ORDER BY datname

Getting the Schemas:

SELECT schema_name
FROM information_schema.schemata 
WHERE catalog_name = 'DATABASE_NAME' AND schema_name NOT LIKE 'pg_%' AND schema_name <> 'information_schema'
ORDER BY schema_name

Getting the Tables:

SELECT table_name
FROM information_schema.tables 
WHERE table_catalog = 'DATABASE_NAME' AND table_schema NOT LIKE 'pg_%' AND table_schema <> 'information_schema' and table_type = 'BASE TABLE'

Getting Fields and Field types

SELECT ordinal_position, column_name, 
CASE 
WHEN character_maximum_length > 0 THEN concat(data_type, ' (',  character_maximum_length, ')')
ELSE data_type
END
AS data_type, is_nullable, column_default
FROM information_schema.columns
WHERE
table_schema = 'SCHEMA' and table_name = 'TABLE_NAME'

Reference

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.