By krazykanuk on

Is it possible to do database switching in a validate or submit function? I ask because I try and switch in a validate function to check that the information entered in a form is correct, and it takes the site offline.

function mymyodule_validate(&$form, &$form_state) {
    db_set_active('whatever');
        db_query("SELECT * FROM {table} WHERE id = %d", 1);
    db_set_active('default');
}
⋅ Categories: Drupal 6.x

Comments

Anonymous’s picture

Anonymous (not verified) commented

I can't think of any reason why that wouldn't work. As long as you're not calling any functions that require access to the default database before you reset the active database it should work fine

krazykanuk’s picture

krazykanuk commented

OK because I have this at the very top of my php file.

global $db_url;
// Set up the new database value
$dbname = variable_get('dbname', 'dbname');
$dbuser= variable_get('dbuser', 'dbuser');
$dbpass = variable_get('dbpass', 'dbpass');
$dbhost = variable_get('dbhost', 'dbhost');

switch ($GLOBALS['db_type']) {
    // If the database type is mysql set it up to use the mysql driver
    case 'mysql':
        $dbtype = 'mysql';
        break;
    // If the database type is mysqli set it up to use the mysqli driver
    case 'mysqli':
        $dbtype = 'mysqli';
        break;
    // If the database type is pgsql set it up to use the pgsql driver
    case 'pgsql':
        $dbtype = 'pgsql';
        break;
    default;
    break;
}
$default_db = $db_url;
// Set the new databases to connect to with the information saved from the database settings page
$infoschema = ("$dbtype://$dbuser:$dbpass@$dbhost:/information_schema");
$freeradiusdb = ("$dbtype://$dbuser:$dbpass@$dbhost:/$dbname");
$db_url = array('default' => $default_db, 'schemadb' => $infoschema, 'freeradius' => $freeradiusdb);

I have an administration settings form to determine the database name, database user, database password and the database hostname.

$form['dbsettings'] = array(
        '#type' => 'fieldset',
        '#title' => 'Database Settings',
    );
    $form['dbsettings']['dbname'] = array(
        '#type' => 'textfield',
        '#title' => t('Database name'),
        '#description' => t('The database name that the FreeRadius will use. This database needs to already be created and set up by someone with permissions to do so.'),
        '#required' => TRUE,
        '#default_value' => variable_get('dbname',''), 
        '#size' => 25,
    );
    $form['dbsettings']['dbuser'] = array(
        '#type' => 'textfield',
        '#title' => t('Database username'),
        '#description' => t('The database username that is required to connect to the FreeRadius database.'),
        '#required' => TRUE,
         '#default_value' => variable_get('dbuser',''),
        '#size' => 16,
    );
    $form['dbsettings']['dbpass'] = array(
        '#type' => 'password',
        '#title' => t('Database password'),
        '#description' => t('The database password that is required to connect to the FreeRadius database.'),
        '#required' => TRUE,
        '#default_value' => variable_get('dbpass',''),
        '#size' => 16,
    );
    $form['dbsettings']['dbhost'] = array(
        '#type' => 'textfield',
        '#title' => t('Database host'),
        '#description' => t('The database hostname that is required to connect to the FreeRadius database. The hostname localhost will be assumed if left blank.'),
        '#default_value' => variable_get('dbhost','localhost'),
        '#size' => 50,
    );
    $form['submit'] = array(
        '#type' => 'submit',
        '#value' => 'Save',
    );
    return $form;
}

Now if I don't switch databases in the validate, it saves the information in the proper database as it should, but if I include a database switch it doesn't save the information and says site is off line. In the validate function after I validate that I am receiving characters when I should and numbers when I should, I check the information_schema to see if the supplied database exists. If I get an error back I could assume that 1 - database name is wrong 2 - the username is wrong 3 - password is wrong 4 - user doesn't have access to the information_schema database. Once I can do this I want to do the same for username and password.

function freeradius_dbsettings_form_validate(&$form, &$form_state) {
    global $user;
    global $db_url;
    // Set up variables for the values of are fields
    variable_set('dbname', 'dbname');
    variable_set('dbuser', 'dbuser');
    variable_set('dbpass', 'dbpass');
    variable_set('dbhost', 'dbhost');
    variable_set('enctype', 'enctype');
    variable_set('defaultop', 'defaultop');
    variable_set('usergroup', 'usergroup');
    
    if ($form['settings']['usergroup']['#options'][0] == 'Please create Role') {
        form_set_error('usergroup', 'Please visit the <a href="/admin/user/roles">Roles</a> page and add at least one role other then "anonymous user ,authenticated user or administrator".');
    }
    
    // If the value for dbname is empty give a error showwing it required
    if (is_null($form_state['values']['dbname'])) {
        form_set_error('dbname', t('The database name is a REQUIRED field.'));
    }
    
    // If the value for dbuser is empty give an error showing it is required
    if (is_null($form_state['values']['dbuser'])) {
        form_set_error('dbuser', t('The database username is a REQUIRED field.'));
    }
    
    // If the value of dbpass is empty give an error showing it is required
    if (is_null($form_state['values']['dbpass'])) {
        form_set_error('dbpass', t('The database password is a REQUIRED field.'));
    }
    // If the value of dbhost is not empty and it is numeric give an error saying it shouldn't be numeric
    if (!is_null($form_state['values']['dbhost']) && is_numeric($form_state['values']['dbhost'])) {
        form_set_error('dbhost', t('The value entered here should not be numeric.'));
    }
    
    // If the value of dbhost is left blank set it to localhost
    else {
        $form_state['values']['dbhost'] = 'localhost';
    }
    db_set_active('schemadb');
     $dbname = $form_state['values']['dbname'];
    $databasecheck = db_query("SELECT SCHEMA_NAME FROM {SCHEMATA} WHERE SCHEMA_NAME = '$dbname'");
    db_set_active('default');
}

This takes site offline but if I comment it out it works perfect without checking if database exists

WorldFallz’s picture

WorldFallz commented

might be obvious, but you don't mention it anywhere-- did you define and verify the db info in the settings.php file?

krazykanuk’s picture

krazykanuk commented

It not in the settings.php file it done on the fly. But if I take the switch to schemadb out it passes the verify and submits the information and the drupal id, username, password gets inserted to the freeradius database once a new user registers. The on the fly is explained at http://drupal.org/node/18429 as well as using the settings.php, I altered it a bit for my use. The post I have above with the code shows how I define the switching.

krazykanuk’s picture

krazykanuk commented

I have found if I enter the 3 databases into the sites settings.php file as:

$db_url['default'] = 'mysqli://username:passwprd@localhost/database';
$db_url['schemadb'] = 'mysqli://username:password@localhost/information_schema';
$db_url['freeradius'] = 'mysqli://username:password@localhost/database';

that I can get it to work after I comment out the respective sections. I am also assuming that once finished and released who ever installs the module will use a consistant database driver and not mix and match them, and also they use the ['default'],['schemadb'] and ['freeradius'] as above (but obviously substituting there database name, database username and database password) if they don't it would break the module.

If I put it back to how I had it before and not try and validate if the database exists or if username and password are correct, then it will work and when a new user registers it will do as expected and put the username and password into the second database. The problem arises when someone with administrater permissions goes to the form and uses either an incorrect database name or an incorrect username/password, it will save to the drupal database the information and when a new user tries to register it will say site is offline because something related to the new database is incorrect.

The other person testing this as I work on it (as well as me) is finding that it is more convienient and user friendly to enter the information in with the form but is also more prone to errors. Is there a way I can use the on the fly method of switching databases as described here http://drupal.org/node/18429
and also validate the information in the form to make sure it is correct? Below I will include what works as long as the form has the correct database information.

In the settings.php the $db_url set to the following:
$db_url = 'mysqli://username:password@localhost/database';

In the top of the freeradiu.module file just under the php tag:

global $db_url;
// Set up the new database value
$dbname = variable_get('dbname', 'dbname');
$dbuser= variable_get('dbuser', 'dbuser');
$dbpass = variable_get('dbpass', 'dbpass');
$dbhost = variable_get('dbhost', 'dbhost');

switch ($GLOBALS['db_type']) {
    // If the database type is mysql set it up to use the mysql driver
    case 'mysql':
        $dbtype = 'mysql';
        break;
    // If the database type is mysqli set it up to use the mysqli driver
    case 'mysqli':
        $dbtype = 'mysqli';
        break;
    // If the database type is pgsql set it up to use the pgsql driver
    case 'pgsql':
        $dbtype = 'pgsql';
        break;
    default;
    break;
}
$default_db = $db_url;
// Set the new databases to connect to with the information saved from the database settings page
$infoschema = ("$dbtype://$dbuser:$dbpass@$dbhost:/information_schema");
$freeradiusdb = ("$dbtype://$dbuser:$dbpass@$dbhost:/$dbname");
$db_url = array('default' => $default_db, 'schemadb' => $infoschema, 'freeradius' => $freeradiusdb);

The validate function that works as long as the database information is correct:

function freeradius_dbsettings_form_validate(&$form, &$form_state) {
    global $user;
    global $db_url;
    // Set up variables for the values of are fields
    variable_set('dbname', 'dbname');
    variable_set('dbuser', 'dbuser');
    variable_set('dbpass', 'dbpass');
    variable_set('dbhost', 'dbhost');
    variable_set('enctype', 'enctype');
    variable_set('defaultop', 'defaultop');
    variable_set('usergroup', 'usergroup');
    
    if ($form['settings']['usergroup']['#options'][0] == 'Please create Role') {
        form_set_error('usergroup', 'Please visit the <a href="/admin/user/roles">Roles</a> page and add at least one role other then "anonymous user ,authenticated user or administrator".');
    }
    
    // If the value for dbname is empty give a error showwing it required
    if (is_null($form_state['values']['dbname'])) {
        form_set_error('dbname', t('The database name is a REQUIRED field.'));
    }
    
    // If the value for dbuser is empty give an error showing it is required
    if (is_null($form_state['values']['dbuser'])) {
        form_set_error('dbuser', t('The database username is a REQUIRED field.'));
    }
    
    // If the value of dbpass is empty give an error showing it is required
    if (is_null($form_state['values']['dbpass'])) {
        form_set_error('dbpass', t('The database password is a REQUIRED field.'));
    }
    // If the value of dbhost is not empty and it is numeric give an error saying it shouldn't be numeric
    if (!is_null($form_state['values']['dbhost']) && is_numeric($form_state['values']['dbhost'])) {
        form_set_error('dbhost', t('The value entered here should not be numeric.'));
    }
    
    // If the value of dbhost is left blank set it to localhost
    else {
        $form_state['values']['dbhost'] = 'localhost';
    }

If I use the settings.php method adding the following to the bottom of the validate function works:

db_set_active('schemadb');
$databasename = $form_state['values']['dbname'];
$dataquery = db_query("SELECT SCHEMA_NAME FROM {SCHEMATA} WHERE SCHEMA_NAME = $databasename");
dsm($databasename);
dsm($dataquery);
db_set_active('default');
}

If I use the form method the above takes the site offline.