Bypass ExternalDB for WordPress Admin

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 🙂

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

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 🙂