Use PHP to authenticate information in a MySQL database from an external server

I'm not extremely familiar with how PHP's mysql_connect works, but can program very basic scripts. I joined up at a forum for forum administrators and we have been offered a partnership by a hosting company.

This partnership involves our 开发者_C百科active members receiving discounts for hosting packages from that service. The hosting owner suggested that he would have a "hidden URL" for this particular package, but I felt that this could be abused by non-members of our forum if they found out about the URL.

So I suggested that we instead install a script that takes credentials input by the user (username and password) on the hosting website, and cross-references that information with our database (obviously on a separate server) to look for a match, as well as check if the user is activated and has at least 100 posts, and only if all the if statements return true, the user is then authenticated and granted access permission for the hosting package.

I know how to validate the data, but I do not know how to access a database external to the server, and I did a few Google searches and the information I found did nothing but confuse me.

---

MySQL can connect to an external server in the same manner as it connects to the localhost server:

$conn = mysql_connect('external-server.hostname.example.com', $user, $password);
mysql_select_db($database_name);

The external server must allow MySQL connections through its firewall from the web server making the requests, and it must have a user webserveruser@webserver-hostname with access to the database that needs to be queried.

On the external server, assuming you need only read (SELECT) access to a table:

GRANT SELECT ON databasename.tablename TO `webserveruser`@`webserver-hostname` IDENTIFIED BY 'thepassword';

---

It seems like the easiest solution would be for the hosting company to individually email each one of you a long, unique URL like

http://www.myhostingservice.com/refer/80ddca50-f41e-11e0-be50-0800200c9a66/

Clicking this link would take you to their signup/purchase page where you would receive a special discount and then after the purchase is made, the link becomes dead or invalid so no one else could use it. It is extremely unlikely that someone could guess the url randomly.

That would be a lot easier than trying to ask sysadmins for direct access to their databases. The only other alternative would be for the database/forum sysadmin to write a PHP API script that the hosting service can safely use to check the database for credentials without the database sysadmin needing to reveal important security details about the database.

---

You should be able to access any mysql database remotely from host to host. I know personally with mine I asked them to make remote connecting available, and they asked me for the specific IP address that they would allow to access it.

I've knocked together a bit of code also in case you're confused about how you would go about achieving the desired form/validation of user that you're after - as you stated that you have limited PHP knowledge.

Presuming that you have a login form similar to this one

<form action="post" method="checklogin.php">
<input type="username" name="username_input" />
<input type="password" name="password_input" />
<input type="submit" value="Validate Login" name="do_validation" />
</form>

Here's some PHP code for checklogin.php that I've thrown together with //comments to guide you:

// Get the form data
if ($_POST['do_validation'])
{
    // The correct form was posted, get the form data
    $username = mysql_real_escape_string($_POST['username_input']);
    $password = mysql_real_escape_string($_POST['password_input']);

    // Connect to the database
    mysql_connect ("http://91.0.0.1", "myUsername", "myPassword");

    // Select database
    mysql_select_db('remote_table_name');

    // Check the username and password against database
    $check_credentials = mysql_query("SELECT user_id FROM users WHERE username='$username' AND password='$password'");

    // Check that user exists
    if (mysql_num_rows($check_credentials) == 1)
    {
        $existing_user = mysql_fetch_assoc($check_credentials);
        $existing_user_id = $existing_user_id['user_id'];

        // The user exists, now get ID of posts from another table, using their 'user_id'
        $check_posts = mysql_query("SELECT post_id FROM posts WHERE user_id='$existing_user_id'");

        // Check the number of posts is at least 100
        if ($check_posts >= 100)
        {
            // The user has 100 posts OR MORE
            echo "You are a valid user";

            // Here you could start a session for the user (logging them in), and then show the data that you want

            session_start();
            // Store the user_id of the user in a SESSION
            $_SESSION['user_id'] = $existing_user_id;
        }
    }
}

The code above is untested, and contains hardly any safety mechanisms and no validation.

But it would fulfil the criteria that you have suggested to protect your form.