2

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 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.

Improve this question

3 Answers 3

Reset to default
1

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';
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

And that GRANT would just be a regular SQL query entered directly into the database?
Thank you very much then, Michael. Your post was of great help!
0

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.

Improve this answer

4 Comments

Thank you for this reply. The database information on the forum is stored in variables so the hosting company would not be able to see this information in any case, unless they rewrote a script to fetch this information, but I suppose there's an easily available workaround fix for this. I'm making note of your unique URL idea though, I will propose that as well.
Keep in mind that if the hosting company is directly accessing the database and making direct database calls then you are revealing your table names and table layouts, which is a big potential security problem.
Would you by any chance know of any ways to mask this information in such a way that it is inaccessible to the hosting company, then? I've been doing some research on the matter and had no luck so far
Coulton's solution is what I would go with. It's a basic PHP script. You give it a username and password, and in turn, it tells you whether or not that's a valid username/password. All of the important/secret database information is hidden in the script. All the script needs to do is say "valid" or "invalid" at the end. There are some slight security concerns with this, with regards to lack of login attempt throttling due to brute force attacks, but for the most part the script is there.
0

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.

Improve this answer

5 Comments

haha thanks for basically coding the entire script for me even though I only asked how to connect to an external database :P Your post was still of help to me even though I had half of it coded already. I'll edit my code a bit to include some of this and add some safety mechanisms. Thanks very much :)
@Schwing I realised at the last second that you only really wanted to know about remote mysql... but shhh keep it quiet! haha. How depressing after all of that coding :(. If you use any of the code, that a reason it itself to vote up :).
I'm quite new to stackoverflow so I wouldn't know how to vote up, unless it has any relation to "Was this post useful to you? Yes / No" then this has already been done. :)
Yup that's the one, but it's the number and up/down arrow that's right next to my specific answer. Best of luck anyway!
ah well in that case I don't have enough reputation to do so as I'm newly signed up :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.