tbenyon

That is a good idea! 😊

I could check to see if a string was returned and display that as an error message.

I’ve got a busy few days but I’ll try and get this done for you next week.

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 🙂

Sounds awesome. I’m going to mark this as resolved but I’m happy to discuss things further with you if you require a ‘rubber duck’.

And yes, that would be great if you find a solution to post it back here. I’d be interested to know and I’m sure someone in the future will find it useful!

Good luck with your project 🙂

Ok, sounds good.

Are these the same errors as before? I expect those errors aren’t actually breaking things but I’d like to check and see if they can be cleaned up all the same.

I’m getting a release out later this evening with other features. I’ll have a quick look to see if it’s a quickie to tidy them up.

Tom

Hey @danilocubrovic,

Just acknowledging your support request. Definitely one I’ve not seen before. Not sure how I’m going to replicate but will certainly do my best. If you think of anything else special about your setup please let me know.

Hoping to have a look at this tonight or tomorrow.

Thanks,

Tom

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

Thanks Steve,

This is all ready to be deployed but I’m just seeing if any other changes are needed from your other thread so I don’t make multiple releases 🙂

Thanks,

Tom

Hey Steve,

I’ve had a look into this to try and replicate. I switched my version of PHP and WordPress to the versions you mentioned and am still finding the functionality working.

To test I’m stopping my docker container of the external database and then checking that I can no longer login with new external users that have not yet been copied to the local WordPress database.

In this situation I can still login with my local admin user.

NEW THOUGHT!

There are two states that block local login as you can see from this code snippet:

        // 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)) {

The first is if disable local login setting is checked, but you’ve checked that you haven’t got that active.

The second is if an external user was found in your external database but the user does not match.

The reason this logic is here is because if your user updates their password in the external database, and that user does exist when we come to try and authenticate in WordPress, we should prevent local login if their password was not validated.

I wonder if you are connecting to the external database and the admin user that you’re trying to login with locally also exists in your external database with the same username?

If this is not the case then the next step is for you to add some error logs that I provide throughout this file and you send me the result of what happens when you login so that we can start debugging what stage is not working for you.

Let me know,

Thanks,

Tom