Server and Website Intercommunication menu

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    jinktreek's Avatar Member
    Reputation
    8
    Join Date
    Sep 2008
    Posts
    109
    Thanks G/R
    0/0
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Server and Website Intercommunication

    I have a web site created and a server compiled. The site is standardized with simple log-on and account creation functionality (done in PHP). Currently, when individuals create an account, the server writes to a database I have created on the site's MySQL server. Likewise, when the server requests log-in information, it reads from that database. Obviously however, the logon database on the site's MySQL server must be synchronized with Arcemu's logon database or else no one will actually be able to log in to the server.

    I am not exactly sure how to approach this linkage between the server's logon database and the website database that manages account creations.
    Is there any way to:

    A) Have the website read from and write to a MySQL database on my client computer from which the server is hosted.

    Or, the flip side:

    B) Have the server read from the database(s) I have hosted on my web server.

    I have looked into remote MySQL connections where client applications (the server modules in this case I guess) are granted access to the MySQL server on my web-server. However, I'm not exactly sure how to approach this problem and figured I'd ask before delving too deep into it.

    Thanks in advance.


    EDIT: Many thanks for the support all, but it seems as though my service provider is blocking outbound connections on port 3306. Unfortunately, it doesn't look like there is a way for me to get around that
    Last edited by jinktreek; 06-14-2011 at 04:44 PM. Reason: Grammar

    Server and Website Intercommunication
  2. #2
    myran2's Avatar Contributor

    Reputation
    130
    Join Date
    Dec 2008
    Posts
    475
    Thanks G/R
    3/4
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You'd probably be better off having the site read it's info from arcemu's logon database. (Requires less hackfixes in the core).
    You can choose between adding columns to the "account" table in arcemu's logon database or making a custom table. Either way, it's not going to affect Arcemu's performance.

    If you're using hashed passwords in arcemu, here's a function to hash the password's using arcemu's format:
    Code:
    function sha_password($user,$pass)
    {
        $user = strtoupper($user);
        $pass = strtoupper($pass);
        return SHA1($user.':'.$pass);
    }
    (Note: this is also TrinityCore's hash format if you ever decide to switch.)

  3. #3
    jinktreek's Avatar Member
    Reputation
    8
    Join Date
    Sep 2008
    Posts
    109
    Thanks G/R
    0/0
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally Posted by myran2 View Post
    You'd probably be better off having the site read it's info from arcemu's logon database.
    Thanks, that's what I figured. However, my knowledge of SQL internals is slim and I'm not exactly sure how to remotely access a client MySQL server from my site. I would very much appreciate an explanation or a referral to an external source for more information.
    Thanks.

  4. #4
    myran2's Avatar Contributor

    Reputation
    130
    Join Date
    Dec 2008
    Posts
    475
    Thanks G/R
    3/4
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you use a mysql front-end like heidisql, you can add an account specifically for your website.
    Then make a fancy mysql-connect function to switch between your site's DB and your server's DB.

    Edit: here's an example of the function:
    PHP Code:
    function connect($type) {
                switch(
    $type
                        {

                
    // site
                
    case "site":
                    @
    mysql_con mysql_pconnect(MYSQLSITEHOSTUSERPASS) or die(mysql_error());
                break;

                
    // server
                
    case "server":
                        @
    mysql_server_con mysql_pconnect(MYSQLSERVERHOSTUSERPASS) or die(mysql_error());
                break;
            }

        } 
    Last edited by myran2; 06-12-2011 at 05:11 PM.

  5. #5
    jinktreek's Avatar Member
    Reputation
    8
    Join Date
    Sep 2008
    Posts
    109
    Thanks G/R
    0/0
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally Posted by myran2 View Post
    make a fancy mysql-connect function to switch between your site's DB and your server's DB.
    Thanks a bunch once again. However, I had already implemented a function similar to your MySQL connection example function. I apologize for any ambiguity but the crux of the problem is actually connecting to the server's MySQL connection from my site. I was using
    PHP Code:
    mysql_connect() 
    as opposed to
    PHP Code:
    mysql_pconnect() 
    but the differences seem to be subtle.

    If your example function is executed when users create their accounts (i.e. from the site) then it will currently result in a connection failure when an attempt to connect to my Server's database (on my client) is made.

    Let me run through the problem using an attempt to create an account as an example:
    1) An individual goes to my site
    2) He or she enters their account information on the create account page (username and password)
    3) He or she clicks submit, sending the form and redirecting execution to my create_account.php file
    4) The account information (provided, of course, that it is unique) is written into the accounts table within the site's logon database
    5) An attempt to connect to the Server's logon database for additional writing is made and fails.

    I simply don't know how to enable my website to connect to my server's database.



    EDIT:
    In the MySQL command-line client (I'm running Windows XP by the way) I have granted my website's IP address permission, using the CREATE USER and GRANT commands, to connect to the server databases.

    However, I'm not sure what the first argument of the
    PHP Code:
    @mysql_server_con mysql_pconnect(MYSQLSERVERHOSTUSERPASS) or die(mysql_error()); 
    function call would be.
    Obviously it isn't localhost or 127.0.0.1 but is it my computer's IP address or some unique IP associated with my MySQL server?

    Thanks
    Last edited by jinktreek; 06-12-2011 at 06:22 PM.

  6. #6
    myran2's Avatar Contributor

    Reputation
    130
    Join Date
    Dec 2008
    Posts
    475
    Thanks G/R
    3/4
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, you're right.
    MYSQLSERVERHOST is the ip of the your arcemu logon database. (External, of course.)
    MYSQLSITEHOST would be your website's database IP.

    Essentially, you'd swap database connections as needed in your code using connect(site); or connect(server);

  7. #7
    Sonic Waffle's Avatar Contributor
    Reputation
    170
    Join Date
    Dec 2007
    Posts
    990
    Thanks G/R
    0/0
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you're new to PHP and MySQL, I recommend you read up on PHP/MySQL security. This will be vital, otherwise someone can come along and SQL inject into your site and make themselves administrators.
    PHP Freaks - PHP Help Tutorial: PHP Security
    PHP: Security - Manual


  8. #8
    jinktreek's Avatar Member
    Reputation
    8
    Join Date
    Sep 2008
    Posts
    109
    Thanks G/R
    0/0
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the recommendation Sonic Waffle. I'm still encountering issues when attempting to connect to my client MySQL from my website.

    First let me apologize in advance for the mass amount of text. I walk you through my process step-by-step just to make sure I have not done anything incorrectly or missed anything.

    In the MySQL command-line client (on the computer hosting the server) I have:
    1) Used
    Code:
    CREATE USER 'root'@'ip_address_of_my_website' IDENTIFIED BY 'password';
    2) Used
    Code:
    GRANT ALL ON *.* TO 'root'@'ip_address_of_my_website' IDENTIFIED BY 'password';
    To enable my website to remotely access my client MySQL server using username root and password password
    The ip_address_of_my_website was determined here by entering my website's url into the url field at the bottom. So my first question is: is this the right IP address for my website?

    Once that was complete I forwarded port 3306 onto my router using both UDP and TCP connections. Interestingly, many of the online port-checkers claim that port 3306 is still closed. However, the PFPortChecker application I downloaded from here claims that the port is open. From what I gathered, I think the PFPortChecker is more accurate than the online checkers because they look for an "application listening for the port test on your computer, and then assume that the test was OK simply because they were able to connect to your computer." Therefore I think it is safe to assume that port 3306 is open on my router, no?

    Once the port-forwarding and MySQL remote-access configurations were complete, I began scripting my PHP files to process the html forms for account creating and logging in. In this segment I will use my check_logon.php as an example.. Here is the segment of relevance of the check_logon.php file in my site's FTP directory:

    PHP Code:
    <?php
    $host
    ="my_remote_ip"// Host name
    $username="root"// Mysql username
    $password="password"// Mysql password
    $db_name="logon_database"// Database name
    $tbl_name="accounts"// Table name

    // Connect to server and select databse.
    mysql_connect("$host""$username""$password")or die("cannot connect");
    mysql_select_db("$db_name")or die("cannot select DB");
    /* ... */
    ?>
    Where my_remote_ip is the remote IP address of my computer, retrieved from here, root is the same username that I configured my client's MySQL server remote-access for my website (GRANT and CREATE USER) with, password is the name of the password that I configured my client's MySQL server remote-access for my website with, logon_database is logon the database within my client MySQL server and accounts is the table within the logon database of my client MySQL server that contains the account information.

    My second question is am I using the correct IP-address for my client MySQL server? Should it be the remote IP address of my computer (the one I am currently using), the IP-address I forwarded port 3306 with (my static IP) or some other IP address?

    However, upon logging in to my site, I get a "cannot connect" message, indicating that the website could not connect to the clien MySQL server and executed the die() function. Can anyone provide me with any insight as to what I may be doing incorrectly or missing?
    Last edited by jinktreek; 06-12-2011 at 09:26 PM.

  9. #9
    myran2's Avatar Contributor

    Reputation
    130
    Join Date
    Dec 2008
    Posts
    475
    Thanks G/R
    3/4
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    replace the two die functions you listed with
    Code:
    die(mysql_error());
    Having the actual SQL error might help quite a bit.

  10. #10
    Sonic Waffle's Avatar Contributor
    Reputation
    170
    Join Date
    Dec 2007
    Posts
    990
    Thanks G/R
    0/0
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For debugging, make a new PHP file, fill the info according and use this script:
    PHP Code:
    <?php
    $addr 
    "localhost"// MySQL server IP
    $port "3306"// MySQL Port (default)
    $sock fsockopen($addr$port$errno$errstr30);
    if(!
    $sock){
        echo 
    "$errstr ($errno)";
    }else{
       echo 
    "Connection OK!";
    }
    fclose($sock);
    ?>


  11. #11
    jinktreek's Avatar Member
    Reputation
    8
    Join Date
    Sep 2008
    Posts
    109
    Thanks G/R
    0/0
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the suggestions guys. When using the mysql_error() function, the output when trying to connect to my server (at my external IP address) is:
    Code:
    Can't connect to MySQL server on '24.151.113.249' (4)
    When using Sonic Waffle's socket debugging script, the connection times out and:
    Code:
    Connection timed out (110)
    is displayed. Port 3306 (the server port which I used to configure MySQL) is definitely forwarded so it must be the IP address that is causing the problem. I hope this information helps to further determine the root of the problem.
    Last edited by jinktreek; 06-13-2011 at 07:23 PM.

  12. #12
    jinktreek's Avatar Member
    Reputation
    8
    Join Date
    Sep 2008
    Posts
    109
    Thanks G/R
    0/0
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interestingly, I can connect via other computers (on different wireless networks) using the host's external IP, just not from my website.
    Last edited by jinktreek; 06-14-2011 at 04:43 PM.

  13. #13
    myran2's Avatar Contributor

    Reputation
    130
    Join Date
    Dec 2008
    Posts
    475
    Thanks G/R
    3/4
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does your website host allow external database connections?
    Also, are you sure the database account the site is using to connect the server is actually allowed to connect to the server? (Just for a moment, make the allowedIP %, which is a wildcard for anyone.

  14. #14
    jinktreek's Avatar Member
    Reputation
    8
    Join Date
    Sep 2008
    Posts
    109
    Thanks G/R
    0/0
    Trade Feedback
    0 (0%)
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally Posted by myran2 View Post
    Does your website host allow external database connections?
    After posting about the issue on the Stack Overflow forums, the general consensus was that my web-hosting service does not allow outbound requests/connections on port 3306. Since then, I have tried configuring MySQL to other ports and forwarding those however, I have had no luck. I guess I should switch hosts. Any recommendations?
    Thanks for all of your help, I really appreciate it.
    Last edited by jinktreek; 06-16-2011 at 05:28 PM.

  15. #15

Page 1 of 2 12 LastLast

Similar Threads

  1. [Guide] Creating a MANGoS server and website. (3.3.5)
    By TomJenkins in forum WoW EMU Guides & Tutorials
    Replies: 45
    Last Post: 11-01-2017, 12:40 PM
  2. [Selling] selling my finished wow server and website! msg me offer skype merlot996
    By Merlot96 in forum World of Warcraft Buy Sell Trade
    Replies: 0
    Last Post: 05-06-2014, 09:06 PM
  3. [Trinity] [TrinityCore] Need help setting up a trinity core based public server and website
    By Damnation92 in forum WoW EMU Questions & Requests
    Replies: 3
    Last Post: 10-18-2013, 03:03 PM
  4. [Request] Wotlk server and website
    By th3gam3 in forum WoW EMU Questions & Requests
    Replies: 2
    Last Post: 09-08-2008, 06:15 PM
  5. Herleybob - Your Server public without hamachi - and Website tutorial
    By Herleybob in forum WoW EMU Guides & Tutorials
    Replies: 161
    Last Post: 12-20-2007, 11:19 AM
All times are GMT -5. The time now is 01:51 PM. Powered by vBulletin® Version 4.2.3
Copyright © 2024 vBulletin Solutions, Inc. All rights reserved. User Alert System provided by Advanced User Tagging (Pro) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
Digital Point modules: Sphinx-based search