[Kyek]

Users in MySQL aren't just a username and password like you'd probably expect -- they also include a source hostname.

I'm not sure how you're creating that user -- whether it's through cpanel or a GUI client or what. But if you're on the command line (meaning, the mysql client repl), you generally execute a line like this to give full access rights to a new user:

GRANT ALL ON myDB.* to myUser@localhost IDENTIFIED BY 'passHere';

That @localhost part is required, because it's a main part of the account definition. Not "localhost" specifically, of course, but the hostname in general. You could replace that with an IP address, or use the MySQL % wildcard like you would when matching strings in a WHERE clause. So this would allow a connection from the username from any IP address:

GRANT ALL ON myDB.* to someUser@'%' IDENTIFIED BY 'thePass';

The above isn't really recommended, though, and if you do that you must must must have strict firewall rules to prevent others from trying to break in. Another option might be to allow a certain IP block with something like

user@'38.82.%'

.

[Lemon]

Chances are MySQL is only setup to listen for local connections, which I believe is now the default behaviour. To be able to connect externally you need to specifically tell it to both listen on the network and also set its bind address to be non-loopback.
Also, the users you setup in MySQL may also only allow access to local hosts. If you have full access to your server then you can see your users and hosts in the users table of the mysql database and use the grant privileges and create user commands to give access to external users too.

[Sole_Wolf]

Kyek, on 28 February 2012 - 10:08 PM, said:

Users in MySQL aren't just a username and password like you'd probably expect -- they also include a source hostname.

I'm not sure how you're creating that user -- whether it's through cpanel or a GUI client or what. But if you're on the command line (meaning, the mysql client repl), you generally execute a line like this to give full access rights to a new user:

GRANT ALL ON myDB.* to myUser@localhost IDENTIFIED BY 'passHere';

That @localhost part is required, because it's a main part of the account definition. Not "localhost" specifically, of course, but the hostname in general. You could replace that with an IP address, or use the MySQL % wildcard like you would when matching strings in a WHERE clause. So this would allow a connection from the username from any IP address:

GRANT ALL ON myDB.* to someUser@'%' IDENTIFIED BY 'thePass';

The above isn't really recommended, though, and if you do that you must must must have strict firewall rules to prevent others from trying to break in. Another option might be to allow a certain IP block with something like

user@'38.82.%'

.

My website is hosted off of a VPN and I have a CPanel that I can configure MySQL databases with. I think that I can only configure users and not anything like source hostname.

I do have SSH access though, so it's possible I might have access to the MySQL command line. My account isn't a super user though so I doubt it.

Lemon, on 28 February 2012 - 10:10 PM, said:

Chances are MySQL is only setup to listen for local connections, which I believe is now the default behaviour. To be able to connect externally you need to specifically tell it to both listen on the network and also set its bind address to be non-loopback.
Also, the users you setup in MySQL may also only allow access to local hosts. If you have full access to your server then you can see your users and hosts in the users table of the mysql database and use the grant privileges and create user commands to give access to external users too.

To tell MySQL to allow external connections, I would need full permissions most likely right?

I think I'll just ask the VPN admin whether he can help me out.

Thanks for the replies.

[Lemon]

If you are using cPanel, look for the "Remote MySQL" button and you can enter your home IP as an allowed host. However, that button is not always available, depending on how your admin has it setup, so you may still need to talk to them.

[Sole_Wolf]

Lemon, on 28 February 2012 - 10:51 PM, said:

If you are using cPanel, look for the "Remote MySQL" button and you can enter your home IP as an allowed host. However, that button is not always available, depending on how your admin has it setup, so you may still need to talk to them.

It turns out that my cPanel does have that button.

But I added my IP Address to the list and the script still returns the same error.

I also added $_SERVER['REMOTE_ADDR'] to the top of my script to see what it returned.

It returned my IP address as

::1

That is localhost if I recall, but I still can't connect to the database.

Any ideas what I should try next?

[Daniel15]

Is MySQL running on the same server as your script? If so, try:

$db = new PDO("mysql:host=localhost;port=3306;dbname=dbname", "username", "password");

(that is, use "localhost" for the host name).

Using an actual host name or IP address connects via TCP, whereas using "localhost" connects via UNIX socket. Most likely, your MySQL server is blocking TCP connections (that's what the error is implying)

[fuegentech]

wow nice information here helped me a lot fixed the issues i was having ,thanks

[Sole_Wolf]

Daniel15, on 29 February 2012 - 02:08 AM, said:

Is MySQL running on the same server as your script? If so, try:

$db = new PDO("mysql:host=localhost;port=3306;dbname=dbname", "username", "password");

(that is, use "localhost" for the host name).

Using an actual host name or IP address connects via TCP, whereas using "localhost" connects via UNIX socket. Most likely, your MySQL server is blocking TCP connections (that's what the error is implying)

Nope, I am running the script from my computer which is attempting to connect to the mysql database on my website.

Also, do you know how I would go about unblocking TCP connections?

[Kyek]

It sounds like you need to SSH into your VPS and drop do root to do some config file editing. Your my.conf (location varies based on distro, but you can do a

find /etc -name 'my.conf'

to find it) should be set up to listen on TCP, bind to all IPs (not just localhost) and listen on the default port. Then it's VERY VERY VERY IMPORTANT that you firewall off everything but localhost and your home IP.

Once that's done, log into MySQL as the root MySQL user, and set up a user for an outside IP or IP range.

If you don't have the root VPS password or root MySQL password... it's probably time for a new VPS ;-). That would be little more than a hosting account.

[Sole_Wolf]

Ok, I can do that. I'll have to wait till my web tech class since my high school blocks FTP, SSH, and basically every port except HTTP over wifi.

Also, I know that it would be a security hole to not firewall everything off but I don't get why? Wouldn't an attacker need both my username and password or super user access to break anything in my databases?

EDIT: Nevermind, my school has apparently started to block SSH . I'll have to wait till I get home.

[Kyek]

Sole_Wolf, on 29 February 2012 - 10:41 AM, said:

Also, I know that it would be a security hole to not firewall everything off but I don't get why? Wouldn't an attacker need both my username and password or super user access to break anything in my databases?

There are three things you have to worry about with that:

• Brute-force attempts. MySQL doesn't have a delayed response time, so bots can (and absolutely will) be launched against your site to try to find those things.
  
• DoS attacks. In part, just having multiple bots flooding you with login attempts achieves this, but it also allows people to hold open idle connections and gives an outlet for packet fragments, SYN attacks, etc.
  
• You're open to future exploits. It's nice to think of MySQL as a perfectly secure service, but that's not the case and they're constantly needing to fix security holes. You're probably not updating your MySQL server from the nightly repo on a daily basis, so that means that if a big exploit comes out, your server is vulnerable and you're leaving that port wide open for anyone.

Some of those concepts carry over to other services, too -- if you're serious about security, it's always good to selectively block certain SYN requests, Xmas packets, and all of those fun things for every port on the machine. Whatever port you have SSH running on, you want the firewall set up with a rate limiter so that the same IP can't reconnect to the server within 5 seconds, which puts a ka'bosh on most brute-force attempts there (and is WAY safer for you than something like denyhosts). The general rule, though, is that the only ports you should have open to the outside are the ones necessary for the public to connect through, and SSH. Beyond that, you're leaving the door open to some really common and quite easy attacks.

Sole_Wolf, on 29 February 2012 - 10:41 AM, said:

EDIT: Nevermind, my school has apparently started to block SSH . I'll have to wait till I get home.

Protip: Take a port that your school does not block, and forward that port on your home router to your home computer's SSH port (assuming you have a non-windows machine at home). Bam, now you can SSH to your home computer and do anything you need to from there, even if your school blocks SSH. As an added benefit, you can also open an SSH tunnel through that port with a SOCKS4 interface on the school machine, and route your web traffic through it to avoid any blocks the school has in place.

For instance, if the port you're forwarding to SSH at home is '443' (the port used for https connections by default), this command will open up a local SOCKS4 proxy on port 1080 into a tunnel back home:

ssh -N -p 443 -c 3des -D 1080 username@your_home_ip

Now you just set your browser/computer's proxy settings to use SOCKS4 on localhost port 1080. Ta-da, no more blocks. Better than shitty web-based proxies any day.