Bypass ExternalDB for WordPress Admin

  • Resolved stezter

    (@stezter)


    4 years, 1 month ago

    This an excellent plug-in that has invaluable potential for me.

    I haven’t activated it permanently yet due to a simple concern …

    My main WordPress Admin is not a user in my external database. If the external database is not available for some reasons, how can admin log-in to WordPress? How can I ensure Administrators will always have access to the site irrespective of externalDB connection?

    Thanks
    Steve

Viewing 9 replies - 16 through 24 (of 24 total)
  • Plugin Author tbenyon

    (@tbenyon)

    AHA!

    This was really helpful. It appears you must be using MSSQL?

    I have found a logic error that effects only this database type. Working on a fix now.

    If you don’t mind, once complete, I would love it if you could test this before I make a release. I’ll give you the code for both of your issues.

    To do this, I can either give you the Github link so you can download the whole project directory and replace the plugin with that OR like we did above, I can send you code snippets in here to replace certain files with?

    Let me know your preference while I get back to resolving the issue ??

    Thread Starter stezter

    (@stezter)

    Ah yes, MS SQL. I should have said.

    Of course I’ll test it.

    If you send me the codes snippets here, as you did before, that works fine for me.

    Thanks Tom
    Steve

    Plugin Author tbenyon

    (@tbenyon)

    Hey Steve,

    I’ve got a proposed fix but am struggling to add plugins to my local WordPress docker image to be able to use “sqlsrv_query” and test it.

    It is painful, infrastructure is not my strength.

    Would you like to test it in the mean time. These two file replacements should fix both your issues but before I release I’ll want to get my automated tests setup.

    Don’t forget you’ll also need to add the hook code mentioned in your issue to functions.php.

    login/dp.php

    
<?php

function exlog_get_external_db_instance_and_fields($dbType) {
	try {
		$host = exlog_get_option("external_login_option_db_host");
		$port = exlog_get_option("external_login_option_db_port");
		$user = exlog_get_option("external_login_option_db_username");
		$password = exlog_get_option("external_login_option_db_password");
		$dbname = exlog_get_option("external_login_option_db_name");

		$db_instance = null;
		$mySqlHost = null;
		if ($dbType == "mssql") {
			$connectionOptions = array(
                "Database" => exlog_get_option("external_login_option_db_name"),
                "UID" => exlog_get_option("external_login_option_db_username"),
                "PWD" => exlog_get_option("external_login_option_db_password"),
                "APP" => "WordPressExternalLogin",
                "ApplicationIntent" => "ReadOnly"
            );
			$db_instance = sqlsrv_connect( exlog_get_option("external_login_option_db_host"), $connectionOptions);
			if( $db_instance === false ) {
				error_log('EXLOG:');
				error_log(FormatErrors(sqlsrv_errors()));
				return false;
			}
		}
		else if ($dbType == "postgresql") {
			$postgresConnectionString = "";
			if ($host) {
				$postgresConnectionString .= " host=" . $host;
			}

			if ($port) {
				$postgresConnectionString .= " port=" . $port;
			}

			if ($user) {
				$postgresConnectionString .= " user=" . $user;
			}

			if ($password) {
				$postgresConnectionString .= " password=" . $password;
			}

			if ($dbname) {
				$postgresConnectionString .= " dbname=" . $dbname;
			}

			$db_instance = pg_connect($postgresConnectionString) or error_log("EXLOG: Cannot connect to the external database.");
		} else {
			$mySqlHost = $host;

			$hostIncludesPort = strpos($host, ':') !== false;

			if ($port && !$hostIncludesPort) { //If port is included in $host, don't add the $port variable as well
				$mySqlHost .= ":" . $port;
			}

			$db_instance = new wpdb(
				$user,
				$password,
				$dbname,
				$mySqlHost
			);
		}

		$data = array(
			"db_instance" => $db_instance,
			"dbstructure_table" => exlog_get_option('exlog_dbstructure_table'),
			"dbstructure_username" => exlog_get_option('exlog_dbstructure_username'),
			"dbstructure_password" => exlog_get_option('exlog_dbstructure_password'),
			"dbstructure_first_name" => exlog_get_option('exlog_dbstructure_first_name'),
			"dbstructure_last_name" => exlog_get_option('exlog_dbstructure_last_name'),
			"dbstructure_role" => exlog_get_option('exlog_dbstructure_role'),
			"dbstructure_email" => exlog_get_option('exlog_dbstructure_email'),
		);

		if (exlog_get_option('external_login_option_db_salting_method') == 'all') {
			$data['dbstructure_salt'] = exlog_get_option('exlog_dbstructure_salt');
		}

		return $data;
	}
	catch (Exception $ex) {
		error_log('EXLOG: Unable to create database connection:');
		error_log(var_export($ex, true));
		return false;
	}
};

function exlog_build_wp_user_data($db_data, $userData) {
    return array(
        "username" => $userData[$db_data["dbstructure_username"]],
        "password" => $userData[$db_data["dbstructure_password"]],
        "first_name" => $userData[$db_data["dbstructure_first_name"]],
        "last_name" => $userData[$db_data["dbstructure_last_name"]],
        "role" => $userData[$db_data["dbstructure_role"]],
        "email" => $userData[$db_data["dbstructure_email"]],
    );
}

function exlog_build_query_response($userData, $username, $password, $db_data) {
    if (!$userData) {
        return false;
    }

    $query_response = array(
        "authenticated" => false,
        "raw_response" => $userData,
        "wp_user_data" => null
    );

    if(exlogCustomShouldExcludeUser($userData) || exlogShouldExcludeUserBasedOnSettingsPageExcludeUsersSettings($userData)) {
        return $query_response;
    }

    $user_specific_salt = false;

    if (exlog_get_option('external_login_option_db_salting_method') == 'all') {
        $user_specific_salt =  $userData[$db_data["dbstructure_salt"]];
    }

    $hashFromDatabase = $userData[$db_data["dbstructure_password"]];
    if (has_filter(EXLOG_HOOK_FILTER_AUTHENTICATE_HASH)) {
        $valid_credentials = apply_filters(
            EXLOG_HOOK_FILTER_AUTHENTICATE_HASH,
            $password,
            $hashFromDatabase,
            $username,
            $userData
        );
    } else {
        $valid_credentials = exlog_validate_password($password, $hashFromDatabase, $user_specific_salt);
    }

    if ($valid_credentials) {
        $query_response["wp_user_data"] = exlog_build_wp_user_data($db_data, $userData);
        $query_response["authenticated"] = true;
    }
    return $query_response;
}

function exlog_auth_query($username, $password) {
	try {
		$dbType = exlog_get_option('external_login_option_db_type');

		$db_data = exlog_get_external_db_instance_and_fields($dbType);

		if ($db_data == false) {
            return false;
        }

		$userData = null;

		if ($dbType == "mssql") {
			$query_string =
			'SELECT *' .
			' FROM ' . esc_sql($db_data["dbstructure_table"]) .
			' WHERE ' . esc_sql($db_data["dbstructure_username"]) . '=\'' . esc_sql($username) . '\'';

			$stmt = sqlsrv_query($db_data["db_instance"], $query_string);

			$userData = sqlsrv_fetch_array($stmt);

		} else if ($dbType == "postgresql") {
			$query_string =
				'SELECT *' .
				' FROM "' . esc_sql($db_data["dbstructure_table"]) . '"' .
				' WHERE "' . esc_sql($db_data["dbstructure_username"]) . '" ILIKE \'' . esc_sql($username) . '\'';

			$rows = pg_query($query_string) or error_log("EXLOG: External DB query failed.");

			$userData = pg_fetch_array($rows, null, PGSQL_ASSOC); //Gets the first row

			pg_close($db_data["db_instance"]);

		} else {
			$query_string =
				'SELECT *' .
				' FROM ' . esc_sql($db_data["dbstructure_table"]) .
				' WHERE (' . esc_sql($db_data["dbstructure_username"]) . '="' . esc_sql($username) . '"';

			if ($db_data["dbstructure_email"]) {
				$query_string .= ' OR ' . esc_sql($db_data["dbstructure_email"]) . '="' . esc_sql($username) . '")';
			} else {
                $query_string .= ')';
            }

			$rows = $db_data["db_instance"]->get_results($query_string, ARRAY_A);

			if (sizeof($rows) > 0) {
				$userData = $rows[0];
			}
		}

        return exlog_build_query_response($userData, $username, $password, $db_data);
	}
	catch (Exception $ex) {
        error_log('EXLOG: Unable to complete database query:');
        error_log(var_export($ex, true));
        return false;
	}
}

function exlog_test_query($limit = false) {
	try {
		$dbType = exlog_get_option('external_login_option_db_type');

		$db_data = exlog_get_external_db_instance_and_fields($dbType);

        if ($db_data == false) {
            return false;
        }

		if($dbType == "mssql") {
			$query_string = "";

			if ($limit && is_int($limit)) {
				$query_string .= 'SELECT TOP ' . $limit . ' *';
			} else {
				$query_string .= 'SELECT *';
			}

			$query_string .= ' FROM ' . esc_sql($db_data["dbstructure_table"]);

			$stmt = sqlsrv_query( $db_data["db_instance"], $query_string);

			if (sqlsrv_has_rows( $stmt ) != true) {
				error_log("EXLOG: No rows returned from test query.");
				return false;
			}

			$users = array();

			while( $user_data = sqlsrv_fetch_array( $stmt))
			{
				array_push($users, exlog_build_wp_user_data($db_data, $user_data));
			}

			return $users;
		}
		else if ($dbType == "postgresql") {
			$query_string =
				'SELECT *' .
				' FROM "' . esc_sql($db_data["dbstructure_table"]) . '"';

			if ($limit && is_int($limit)) {
				$query_string .= ' LIMIT ' . $limit;
			}

			$rows = pg_query($query_string) or die('Query failed: ' . pg_last_error());

			$users = array();
			if (sizeof($rows) > 0) {
				while ($x = pg_fetch_array($rows, null, PGSQL_ASSOC)) {
					array_push($users, $x); //Gets the first row
				};
				pg_close($db_data["db_instance"]);
				return $users;
			}

		} else {
			$query_string =
				'SELECT *' .
				' FROM ' . esc_sql($db_data["dbstructure_table"]) . '';

			if ($limit && is_int($limit)) {
				$query_string .= ' LIMIT ' . $limit;
			}

			$rows = $db_data["db_instance"]->get_results($query_string, ARRAY_A);

			$users = array();
			if (sizeof($rows) > 0) {
				foreach ($rows as $user_data) {
					array_push($users, exlog_build_wp_user_data($db_data, $user_data));
				};
				return $users;
			}
		}

		//If got this far, query failed
		error_log("EXLOG: No rows returned from test query.");
		return false;
	}
	catch (Exception $ex) {
        error_log('EXLOG: Unable to make test query:');
        error_log(var_export($ex, true));
        return false;
	}
}

function exlog_check_if_field_exists($db_data, $field) {
    $dbType = exlog_get_option('external_login_option_db_type');
    if ($dbType == "mssql") {
		$query_string = "SELECT 1 FROM sys.columns WHERE Name = \'" . $field . "\' AND Object_ID = Object_ID(\'". esc_sql($db_data["dbstructure_table"]) ."\'";
        $result = $db_data["db_instance"]->get_results($query_string, ARRAY_A);
        return !empty($result);
	}
	else if ($dbType == "mysql") {
        $query_string = "SHOW COLUMNS FROM <code>&quot; . esc_sql($db_data[&quot;dbstructure_table&quot;]) . &quot;</code> LIKE '" . $field . "';";
        $result = $db_data["db_instance"]->get_results($query_string, ARRAY_A);
        return !empty($result);
    } else {
        $query_string = "SELECT column_name FROM information_schema.columns WHERE table_name='" . $db_data["dbstructure_table"] ."' and column_name='" . $field . "';";
        $query_results = pg_query($query_string) or error_log("EXLOG: External DB query failed when checking if Field Exists");
        $result = pg_fetch_array($query_results, null, PGSQL_ASSOC);
        return is_array($result);
    }
}

    login/authenticate.php

    
<?php
function exlog_auth( $user, $username, $password ){
    $migration_mode = exlog_get_option('external_login_option_migration_mode') == "on";

    // If not in migration mode or you are in migration mode and the username isn't in the WordPress DB
    if (!$migration_mode || ($migration_mode && !username_exists($username))) {

        // Make sure a username and password are present for us to work with
        if ($username == '' || $password == '') return;

        $response = exlog_auth_query($username, $password);

        $roles = exlog_map_role($response['wp_user_data']['role']);

        $block_access_due_to_role = true;
        foreach ($roles as $role) {
            if ($role != EXLOG_ROLE_BLOCK_VALUE) {
                $block_access_due_to_role = false;
            }
        }

        // If a user was found
        if ($response) {

            // If role is blocking user access
            if ($block_access_due_to_role) {
                $user = new WP_Error('denied', __("You are not allowed access"));

                // If user was NOT authenticated
            } else if (!$response["authenticated"]) {
                // User does not exist, send back an error message
                $user = new WP_Error('denied', __("Invalid username or password"));

                // If user was authenticated
            } else if ($response["authenticated"]) {
                // External user exists, try to load the user info from the WordPress user table
                $userobj = new WP_User();
                $user = $userobj->get_data_by('login', $response['wp_user_data']['username']); // Does not return a WP_User object ??
                $user = new WP_User($user->ID); // Attempt to load up the user with that ID

                $exlog_userdata = array(
                    'user_login' => $response['wp_user_data']['username'],
                    'first_name' => $response['wp_user_data']['first_name'],
                    'last_name'  => $response['wp_user_data']['last_name'],
                    'user_pass'  => $password,
                    'role'       => $roles[0],
                    'user_email' => $response['wp_user_data']['email'],
                );

                // If user does not exist
                if ($user->ID == 0) {
                    // Setup the minimum required user information

                    $new_user_id = wp_insert_user( $exlog_userdata ); // A new user has been created

                    // Load the new user info
                    $user = new WP_User ($new_user_id);
                } else {
                    $exlog_userdata['ID'] = $user->ID;

                    add_filter('send_password_change_email', '__return_false'); // Prevent password update e-mail

                    wp_update_user($exlog_userdata);
                }

                $user->set_role($roles[0]); // Wipe out old roles

                // Add roles to user if more than one
                foreach ($roles as $role) {
                    $user->add_role($role);
                }

                // Hook that passes user data on successful login
                do_action('exlog_hook_action_authenticated', $user, $exlog_userdata, $response['raw_response']);
            }
        }

        // Whether to disable login fallback with the local WordPress version of the username and password
        // Prevents local login if:
        // - Disable local login  is set in the admin area
        // - OR
        // - The user was found but the password was rejected
        if (exlog_get_option('external_login_option_disable_local_login') == "on" || is_wp_error($user)) {
            remove_action('authenticate', 'wp_authenticate_username_password', 20);
        }
    }

    return $user;
}

if (exlog_get_option("external_login_option_enable_external_login") == "on") {
    add_filter('authenticate', 'exlog_auth', 10, 3);
}

    Let me know how you get on ??

    Thanks,

    Tom

    Thread Starter stezter

    (@stezter)

    Eeeuch!!

    My site’s not happy with you.

    New db.php causes “There has been a critical error on your website”.
    New autheticate.php prevents admin login whether user exists on external db or not.

    Had to revert. ??

    Steve

    Thread Starter stezter

    (@stezter)

    Tom

    This is probably a result of your infrastructure challenges. Drink more heavily and it’ll all come good. I’ve sent you beers.

    A pint of Guinness for me when you’re next up lad.

    Regards
    Steve

    Plugin Author tbenyon

    (@tbenyon)

    Hey Steve,

    That is VERY kind of you! It is greatly appreciated.

    I’ve been on this since 5am and have just got to a win ??

    I’ve resolved my infrastructure issues, tested this and it appears to be working.

    This then made me concerned that it wasn’t working for you. I copied and pasted the code I sent you in to my copy to check again and it seems that upon pasting, certain speech mark characters " were getting HTML encoded to &quot;.

    Lesson learnt for me – be careful with asking users to paste in code from code blocks on here.

    To better test now, can I ask you to:

    1. Go to the Github page for the plugin
    2. Click the green “Code” button
    3. Click “Download ZIP”
    4. Open up the zipped folder
    5. Inside open the directory “plugin-files”
    6. Copy all of those files over the top of the ones in the external login plugin

    I think you could get away with just replacing these two:

    • /login/authenticate.php
    • /login/db.php

    Let me know how you get on. Thanks for your patience, your beers and the communication. You’ve been a pleasure to work with on this.

    Thanks,

    Tom ??

    Thread Starter stezter

    (@stezter)

    And ….

    Bingo! He’s nailed it.

    Thank you Tom. Everything working.

    Fabulous Support!!!

    Best
    Steve

    Plugin Author tbenyon

    (@tbenyon)

    That’s great news. I’ll try and get a release out this evening.

    Will keep you posted!

    Plugin Author tbenyon

    (@tbenyon)

    Hey Steve,

    Just letting you know that this was released in version 1.10.0 just now.

    If you have any further questions, please don’t hesitate to get back in contact but for now I’ll mark this ticket as resolved.

    Thanks again for the beers.

    Tom ??

Viewing 9 replies - 16 through 24 (of 24 total)
  • The topic ‘Bypass ExternalDB for WordPress Admin’ is closed to new replies.