Connecting to a MySQL database on my website from a computer program remotely?

Discussion in 'Web Design and Development' started by MythicFrost, Jun 12, 2010.

  1. MythicFrost macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #1
    Hi, I'm unsure if this is in the right section or not, I hope so.

    I've got a website www.example.com, I believe it's managed with CPanel and I've set up a MySql database and some tables with some info.
    And I've built myself a computer program (on the Windows platform) which needs to interact with that database from an end-users computer. In a nutshell, they create an account at my website, and they use those details to log in to my program.

    Thus, it needs to query the database for the username and password entered by the user, to make sure it exists in my database.
    I've been told this isn't possible, I've tried connecting and I get ~"access for user xxx denied" (I'm using the right address, database name, user and pass)

    Any thoughts? I've assumed connecting to the database would be simple :eek:.
    I have contacted the company that hosts my website, and I am awaiting an e-mail from them.
     
  2. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #2
    I can remotely connect to my database using Sequel Pro and even on my test Cocoa app.
    This database is on a VPS though with permissions set up accordingly, many web hosts will not allow you to do this.
     
  3. MythicFrost thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #3
    Thanks for your reply,

    I'm not sure that would be suitable for what I'm doing (not that I really know anyway), I'm hoping someone might have a simple solution to connect remotely, I thought it'd be easy. I've seen Trend Micro PC security do something similar, you register it with them and it saves it to their servers, so you can't for example install it on four computers, just three.

    I'm really stuck here, my program has to access the data somehow.
    (PS, is VPS virtual private server?)

    Kind Regards
     
  4. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #4
    Your web host may only allow connections from certain locations/IP addresses. Does your web host have a FAW or forum? Mine has a pretty comprehensive one that covers this type of question.
     
  5. MythicFrost thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #5
    I think that may be the case. No they do not have a forum, and I am unsure what a FAW is.

    http://www.crazydomains.com.au/help/adding_database_host_access.htm
    I think that might agree with what you said above, it only works from certain locations/IP addresses, it appears that link shows you how to add another one, is that right?

    Unfortunately, that doesn't suit me because I won't know the IP addresses of the users of my product. So, I may just have to find myself a web host that will allow me to connect remotely.

    EDIT: would it be a bad idea to enter an ip address of %%%.%%%.%.% (it says it allows wildcards), meaning my program could connect from any IP address? Is that a security risk? It's not like that'd get my database name, username or password, but I thought I should ask.

    Kind Regards
     
  6. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #6
    Typo, should have been FAQ, frequently asked questions.
    Even without the user name and password, a hacker could use brute force attacks on the server. The DB would only be as secure as the weakest account, and unless you're enforcing strong passwords a user could create a very easy password.

    Also, with accounts given out, make sure they only have capabilities they absolutely need. MySQL has a grant table that can determine what type of queries can be done such as DROP TABLE, DELETE, UPDATE, etc. That way if an account is compromised, it can only do so much damage.

    Another thing, unless the MySQL server is accessed via a secure protocol (SSH, TSL) then user names and passwords will be sent over the net in clear text, which could be intercepted by a third party. So there's a number of security concerns with this setup.
     
  7. MythicFrost thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #7
    Ah, yes they do, and they have not helped with this issue.
    So assuming I get my issue solved, and according to what Wikipedia says a brute force attack is, someone could write some kind of program to just guess a username that might exist and the password as well?

    I might add, this program does not offer the user the ability interact with the database, all it does is check that the username and password exist as an account created for my website (not for the database either).

    main_database -> accounts -> user, pass, email, dob, country, etc.,
    I think you've misunderstood me (unless I've misunderstood you haha lol), I'm not giving out MySql accounts to the database, I'm just checking some rows in my accounts table for a particular string.

    If we took the username and password equation out of it, I could say I just need to query the database of all accounts I have for their e-mail address.
    I thought as much, for now I'm only doing testing but I was going to invest in security after I get the basics working
     
  8. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #8
    Yes, you got it. Though it doesn't sound very effective, it gets used a bit still, and has been effective. Facebook had a few issues here. Since though, that it's only your MySQL account that can access it, that should greatly limit the chances of success of a brute force attack to succeed. As long as you're not using a common user name (admin, root, YourName, etc.) and have a strong password (8+ chars, numbers, letters, mixed case, and 1+ special chars).

    While your program may only make a specific query, a hacker could send whatever query they wanted to using the server IP, user name, and password.

    Just make sure the MySQL account that's used to login to the DB for the query only has the privileges it needs. It sounds like it only needs to use SELECT statements. A hacker (if they've figured out the login credentials) could still gain access to any data in the DB, but at least they couldn't modify or destroy it.

    MySQL allows creating grant rules that determine what databases, tables, and even columns an account can access. I haven't messed with this a whole lot, but it should limit the account's ability to gain access to the more sensitive user info like email and DOB while still being able to verify user name and password.

    I get that. I wish it was cheaper to do security the right way.
     
  9. MythicFrost thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #9
    How would they ever possibly get my login credentials?
    Yeah that's a good idea
    Is it expensive?
     
  10. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #10
    As I mentioned before, either brute force or intercepting your network traffic.
    Depends on the web host. Mine requires having a static IP, which costs an extra $100/year for it.
     
  11. MythicFrost thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #11
    I think a brute force attack is unlikely (right?), they'd have to get my database name, username, and password... the chances are impossible in mind, using a program to guess all three of those correctly at the same time?, if necessary I can increase the length of my username and password to 20 characters or so.

    I was unaware that they could get my login credentials by intercepting the network traffic, I'll definitely have to use SSH.
    Ah I see, tyvm.
     
  12. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #12
    Brute force attacks are used all the time. They wouldn't need to know the DB name, their scripts scan all IP addresses and looks for open ports that will tell them services like MySQL are being run on the server. If you've heard of bot nets, that's part of what they do. They get programmed to run these scripts, capable of trying millions of iterations a day. It may seem unlikely, but I promise they happen. It's why people have to defend themselves and why it's easy to find information about these attacks.

    Increasing the length and complexity of the user name and password definitely help, though after so many characters it doesn't really do anything more. That's partly because of how the password may be stored on the server (usually as a hash). The MD5 hash algorithm is a common used for passwords, but it's not considered as great as it use to be. As long as you're taking these other precautions it will improve your security much more than what most people do.

    Brute force attacks on passwords.
    A Study of Passwords and Methods Used in Brute-Force SSH Attacks

    Thread examples of people having their MySQL brute force attempted:
    http://serverfault.com/questions/67811/preventing-brute-force-attacks-on-mysql
    http://forums.mysql.com/read.php?30,205612,205612
    http://blog.taragana.com/index.php/...tempt-on-my-server-guess-who-was-responsible/
     
  13. designguy79 macrumors 6502

    Joined:
    Sep 24, 2009
    Location:
    Michigan
    #13
    Just a few additional thoughts (you are getting good advice on the security issues, BTW!)

    * If you program ends up being used by a malicious user, they could potentially reverse-engineer your code and find the username and password for the MySQL connection. Or use a packet-sniffer and grab it from an unencrypted connection

    * If you try using SSH somehow, I am still curious on how you will protect the SSH credentials in the compiled code

    * If they are behind a firewall that blocks the MySQL port, it won't work (a lot of networks block all ports that are deemed critical)

    My suggestion would be to write a API (of sorts) in PHP and send the requests to the server via HTTPS. I did this for a VB project awhile back.

    Make sure and get familiar with "salting" to help protect against someone brute-forcing (or maybe DDoS'ing?) your API.

    HTH

    ~ Jeremy
     
  14. MythicFrost thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #14
    I have a feeling this is going to be a headache. I will make sure my username and passwords are very random and very long (70+ characters) with upper and lower case, numbers, random letters, and potentially if possible symbols such as !@#$%^&*()_-+=.

    For the moment, my program runs only on my computer, is it possible for anyone to "intercept" the network traffic being sent to the server? Or is that something they have to do locally (I.E, monitoring my program running on their computer) or do they do it from the server?


    Thank you very much for your help, it's very much appreciated.
    Well, I will make sure I use SSH to encrypt the contents so they are safe. The reverse-engineering could be a real issue, since I'm using a high level language (Visual Basic .NET) for my program, although there is a powerful obfuscator I've found which is supposedly able to prevent any reverse-engineering, which I hope is true.
    :( Hopefully the obfuscator will work very well and prevent reverse engineering, otherwise I'm in trouble :p
    How do I check what port my MySQL database needs?
    How would I execute that PHP code from VB though?

    What is DDoS'ing?

    Thanks for all your help,
    Kind Regards
     
  15. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #15
    Yea, the more you learn about security, the more you realize there is no perfect solution that's airtight. You simply have to take the tactics that are available and don't take too much effort (costs) on your part. I mean if NASA's web sites can get hacked, what chance do most people have?

    70+ chars is probably overkill because of the hashing algorithms used on passwords. 32 chars is probably the most I'd recommend personally. I use 20 chars for a lot of my web ones.

    The default is 3306 for most installs, except MAMP, which uses 8889. The MySQL configuration files would be where the port is set otherwise.

    Distributed Denial of Service.
     
  16. MythicFrost thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #16
    -.- good to know
    I guess 150 is overkill too. lol. Is there any negative effect to using more? It can't hurt right?
    I thought so, thanks.
    Again, tyvm.
     
  17. designguy79 macrumors 6502

    Joined:
    Sep 24, 2009
    Location:
    Michigan
    #17
    * Obfuscating can help, but like angelwatt said, you can only do so much

    * You don't run the PHP code, it still runs on the web server. Your VB code would be making HTTPS requests to the web sever, kinda like the web browser does. But it would return XML (or any type of text you want to "decode" in your VB) with the results.
     
  18. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #18
    It won't hurt, but it won't improve the security either.
     
  19. sl1200mk2 macrumors 6502

    Joined:
    Oct 17, 2006
    #19
    While your initial attempt is the easiest method, it's also the worst. You've been given some good info so far. At the same time, don't over complicate things.

    The overall problem is fairly straight forward.

    Problem: Can't remotely connect to MySQL

    Answer: Many hosts won't allow remote MySQL connections from any port (encrypted or not). PCI compliance is increasingly dictating this direction. Your error is likely the result of your MySQL user only being allowed access from specific IP addresses (the web server) and not '%' (wildcard any IP address). As such, your access is blocked. Assuming this is case I'm surprised the host allows any external IP response from the MySQL server, but the DB and WEB servers might be the same machine.

    Resolution: There a few options, but a good one would be to write a web service that only exposes the specific functionality / results you need via HTTPS. The good thing about a web service is once it's implemented server side, it doesn't matter what language the client application is written in.

    As others have said, using MySQL user information (limited privileges or not) embedded within an application is a bad idea. I've seen this done many times over the years and it always ends up badly. A common result is that the application gets distributed to users and at some point the MySQL password changes in the future (any number of reasons why) and all of a sudden all the end user applications are broken. It's also usually the case the original developer has moved on, no longer supporting the app, or <insert reason here> and the clients are stuck.

    If you must use MySQL credentials for access you would implement a HTTP Tunnel for access. For the variety of security issues already discussed and more (possible AUP violation of your host) this would be a bad idea, but would expose the connectivity you are looking for.
     
  20. jaikob macrumors 6502

    jaikob

    Joined:
    Jul 1, 2008
    Location:
    Freeland, MI
    #20
    I've done this before.

    You need to download and install the mysql connector available on the mysql dev site: http://dev.mysql.com/usingmysql/dotnet/

    This is an API to allow your program to connect to a remote mysql database.

    Now in cPanel, there is an option to allow a remote IP to connect to your mysql database. I believe it's called something like Remote MySQL in cPanel. You need to allow all remote IP's you will be connecting with.

    Walla. :)
     
  21. MythicFrost thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #21
    I see. I managed to fix the "can't connect from my app problem" yesterday by adding %%%.%%%.%.% as the IP address.
    That sounds good, but it also sounds like what a leap ahead of what I'm capable of (I'm interested nonetheless).

    Are there any security risks to using a web service?

    I don't exactly understand several things such as, what language do I write it in? Is it just like a script or PHP script? I don't know how to post anything to HTTPS.

    Exactly how do I "access" this information from my program? Just navigate to mywebsite.com/webservice? And the webservice which is programmed to retrieve from my database the info I need, gives it the information somehow?
    I don't want to do it then, I'll try the web service.
    Ya thanks, I already had the MySQL connector downloaded so I was using the APIs in my program, and I added %%%.%%%.%.% in Remote MySQL yesterday to get it to work.
     
  22. designguy79 macrumors 6502

    Joined:
    Sep 24, 2009
    Location:
    Michigan
    #22
    There are always risks, but using HTTPS and good web programming is the key. PHP would be a good choice, as it will run on most web hosts and is pretty straight-forward to learn.

    The HTTPS requests come from your VB script. I can't remember the exact calls, but there are built-in controls for doing it. I am sure a Google search will provide plenty of options and examples.

    You have to write the PHP (or whatever), so how you access it will depend on your domain name, folder names and script filenames.

    The text that you output from PHP would be XML or just plain text that your VB script would parse and you would incorporate in to your code's logic.

    Sounds hard, but once you get going its not too bad!

    HTH,

    ~ Jeremy
     
  23. MythicFrost thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #23
    Ah good, I already know PHP.
    Ok I'll google for those.
    Ok, so the HTTPS request that is sent from my program sends the contents of two variables (username and password) which are then checked for in the database in PHP and then return to my program true or false (if they exist)... is that right and possible?
    I think it'll just be a true or false, whether the username and password passed into the script from my program exists in the database or not.

    Thanks everyone and thank you for your reply,
    Kind Regards
     
  24. sl1200mk2 macrumors 6502

    Joined:
    Oct 17, 2006
    #24
    What I indicated is how access is given to a mysql user (as defined in the mysql server). 'myuser@192.168.0.1' only allows access for that user from that specific IP address. 'myuser@%' allows access for that user from any IP address. You don't need to add % for every IP octet, but if you got it working that way, so be it.

    Probably not.

    If your web application is written in PHP, then the web service would be written in PHP. Web services will typically use data formatted in XML to overcome language differences. It doesn't matter what the interface on either end is written in. Take Twitter.com for example. They have API's (web services) that any developer can write applications against. It doesn't matter what language the developer writes it in; Objective C, php, asp.net, python, perl, etc -- all fair game. You pass the web service a formatted query, it sends you back a response.

    Posting to HTTPS is simply a matter of changing your application/script to access 'https://somesite.com/page.php' instead of 'http://somesite.com/page.php'. It generally doesn't involve any elaborate changes, but your website has to have an SSL certificate installed. These can be acquired for $10/yr or less. You can (depending on the host) do self-signed certificates for free if it's only your application accessing it.

    At a very high level, yes. You're simply posting to a PHP page that provides structured results.

    Example: http://mydomain.com/web-service.php?user=2&num=10


    I'm simplifying things, but if you do some Googling and reading you should pick it up. If you're already writing web applications using PHP that query MySQL and client applications involving VB I think it's well within your reach.
     
  25. MythicFrost thread starter macrumors 68040

    MythicFrost

    Joined:
    Mar 11, 2009
    Location:
    Australia
    #25
    The panel I used was under Remote MySQL in CPanel, not in the MySQL database section. Would adding myuser@% work? or should it just be %?
    The default one in there (which I used as an example of how to do it) was xxx.xxx.x.%.

    I'll try just % sometime, in fact, do I even need to do this if I'm using a web service? I don't think so.
    Ah, I see.
    Ok thanks, I get that.

    http://mydomain.com/web-service.php?user=myuser&pass=mypass

    So, how would I prevent users from navigating there? And just manipulating it via the URL to check if a username exists, that could be bad, very bad.

    Thanks again!

    EDIT:

    I've written my web service now, I'm just curious what I have to do to return the result?
    PHP:
    <?php 
    $user 
    mysql_real_escape_string($_POST["user"]); $pass mysql_real_escape_string($_POST["pass"]);
    $con mysql_connect("localhost""user""pass") or die("Unable to connect to MySQL");
    mysql_select_db("mydatabase"con);

    $sql "SELECT * FROM mytable WHERE user = ".$user." AND pass = ".$pass;

    $result mysql_query($sql$con);
    $i 0;
    while (
    $row mysql_fetch_array($result)) {
        
    $i+=1;
    }
    if (
    $i 0) {
        
    //return to my program true
    } else {
        
    //return to my program false
    }
    mysql_close($con);
    ?>
    As you can see, I want to return true or false. Do I just do echo("false") or is there a better way to do it?

    I'm using WebRequest in VB:
    Code:
            Dim wbRequest As WebRequest = HttpWebRequest.Create("http://www.mywebsite.com/web-service")
            wbRequest.Method = "POST"
            Using swWriter As New StreamWriter(wbRequest.GetRequestStream)
                swWriter.Write("user=myuser&pass=mypass")
                swWriter.Flush()
            End Using
            Dim wrResponse As WebResponse = wbRequest.GetResponse()
            Dim sText As String = ""
            Using sResponse As New StreamReader(wrResponse.GetResponseStream())
                sText = sResponse.ReadToEnd()
            End Using
    
    Is the line "sText = sResponse.ReadToEnd()" returning the HTML content output on the page? Or what? (I know this isn't a VB forum, but the code is fairly understandable I think)

    Kind Regards
     

Share This Page