PHP, MySQL and SSH Tunneling (Port Forwarding)

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to FurlAdd to Newsvine

[Follow our blog posts, obsession with data, and original articles on Twitter @RJMetrics]

Welcome to another exciting edition of “way-too-detailed explanations of obscure data-related topics.”  I’m your host, Robert J. Moore, and today we’ll be exploring the exciting world of SSH Tunneling with MySQL and PHP.

Background

As you can imagine, a big part of our job here at RJMetrics is making sure that data gets from a client’s database at Point A to our systems at Point B securely, reliably, and automatically.  Depending on your database platform and other system characteristics, there are a number of ways to approach to this challenge.  For customers running Linux and MySQL, our preferred method is called SSH Tunneling.As anyone who has even brushed up against Linux knows, SSH is the “Secure Shell.”  SSH uses public-key cryptography to securely exchange data between two networked devices over an insecure channel (such as the Internet).  If you are in a local location and there is a Linux machine at a remote location containing your sensitive information, you can use SSH establish a secure connection between the two locations and interface with the remote machine via the local machine.

SSH has an exciting and often overlooked feature called Port Forwarding (also known as SSH Tunneling).   SSH Tunneling allows you to send information to a specific TCP port on your local machine and have it be transferred to a port on the remote machine securely over SSH (and vice versa).  This allows for the encrypted transfer of information from programs and services that might not otherwise communicate securely.

Just imagine the possibilities.  You could create a secure tunnel to encrypt your FTP uploads.  You could use an SSH tunnel to download your POP mail without worrying about your password or e-mails being sniffed along the way.  When you consider the benefits, it should come as no surprise that SSH Tunneling is a key underlying methodology in many Virtual Private Networking (VPN) systems.

As an added bonus, SSH tunneling allows you to avoid the problems associated with penetrating your remote server’s firewall.  When receiving information sent via an SSH Tunnel, a remote program is receiving data sent directly from a shell running on their own machine.

Here at RJMetrics, however, we care about things like ecommerce analytics.  That means it’s all about the database.  We use SSH Tunneling to port-forward MySQL queries from our servers to customer databases and receive the results securely via SSH.  SSH tunneling is attractive for this purpose for a few key reasons:

  • Data security is our top priority, and SSH Tunneling offers mature and secure solution to the data transfer problem.  (We are also excited about the secure MySQL connections via SSL that now exist in nonstandard MySQL releases.  This may prove to be a less complex solution as adoption picks up.)
  • SSH tunneling is extremely easy for customers to configure and for us to automate (as explained below), and can often be set up in just a few minutes.

Configuration and Tunnel Creation

For our clients using MySQL and Linux, setting up SSH Tunneling couldn’t be easier.  New customers receive detailed tutorials, of course, but here are the basics.  It’s a three-step process:

  1. Create a Linux user that RJMetrics will use to SSH into a server that holds an instance of your database.
  2. Create a MySQL user that RJMetrics will use to run SELECT queries on that database.
  3. Use our secure dashboard portal (credentials are provided after signup) to provide us with the server’s address and the credentials for these two accounts.

Security can be further enhanced by restricting our Linux user’s rights within the shell (using methods like rbash, chroot, etc) and our MySQL user’s access to only the databases, tables, and columns you choose (using GRANT and REVOKE statements within MySQL).

Next, on our side, the real fun begins.  For those of you who might be interested in creating your own SSH Tunnels for MySQL, what follows is a simplified version of what happens on our end.

It only takes one line of code to establish an SSH tunnel:

ssh -f -L local-port:remote-address:remote-port login-name [command] >> logfile

Let’s break this down:

  • ssh: this is the call to the ssh command line program, which establishes the SSH connection.
  • -f: this option “forks” the new SSH connection, sending it to the background and leaving control with the local shell.  For port forwarding, this is necessary because we’ll be using local programs to communicate with the remote machine.
  • -L local-port:remote-address:remote-port: The -L option is for “local forwarding.”  It means we want SSH to tunnel commands sent via a port on our local machine to a specific port on the remote machine; the colon-separated parameters that follow are the ports and address in question. (There is a -R option that does the opposite — “remote forwarding” — we don’t use it here but can be quite useful in other applications.)
  • login-name: this is the login name used to establish an SSH connection to the remote host.
  • [command]: this optional parameter is a command you would like to execute remotely as soon as the connection is established.
  • >> logfile: As we will explain in a moment, we want this tunnel to close itself after we are done using it.  Redirecting standard output to a log file prevents the program from “hanging” while stdout awaits an end-of-stream that won’t arrive unless the process is manually killed.

To provide a bit more color on some of the nuances and complications involved, here is a version of the command with actual values included:

ssh -f -L 3307:127.0.0.1:3306 user@remote.rjmetrics.com sleep 60 >> logfile
  • The Port Numbers: notice that the local port is set to 3307, while the remote port is set to 3306.  Port 3306 is the default port for MySQL, so that’s the port where we want our queries being forwarded on the remote system.  On the local system, however, we already have our own instance of MySQL server occupying port 3306.  As such, we must use a different port to disambiguate the local and remote MySQL servers.Using this technique (and more port numbers), it is actually possible for us to establish multiple MySQL connections to multiple clients at the same time.
  • The Remote Address: You’ve probably noticed that 127.0.0.1 (the number between the port numbers) isn’t a public IP address, and as such the term “remote address” is a bit of a misnomer.  This is a spot where many people get stuck when setting up MySQL tunneling.  The value here is not necessarily the web server’s public IP address, but the IP address to which MySQL server is bound. The value we are looking for is stored in the file /etc/my.cnf (or wherever your MySQL configuration file is located).  The line “bind-address=xxx.xx.xx.xx” binds the MySQL server to a specific IP address.  Depending on whether or not you want to allow users to connect to MySQL server directly from remote locations (this should be avoided if you’re not sure), the bind-address can be set to the web server’s actual public IP address (which allows these outside connections) or to 127.0.0.1 (which only allows local connections). 127.0.0.1 is the loopback IP address, which is functionally equivalent to “localhost” but serves as a valid, necessary bind-address value for our tunneling commands.In some cases, the “bind-address” line may be commented out of my.cnf (since it’s not really needed if remote connections aren’t allowed).  In these cases, however, you must uncomment the line and make sure that it reads “bind-address=127.0.0.1” — this is just as secure, but is necessary for tunneling to work.  Also, if the line “skip-networking” appears, be sure that it is commented out.
  • sleep 60: When it comes to tunnel connections, we basically have two options: leave the connection open all the time or open it and close it as needed.  We prefer the latter, and as such we don’t specify the “-N” option when establishing a tunnel, which would leave it open until the process is manually killed (bad for automation).  Since “-N” is not specified, our tunnel will close itself as soon as its SSH session isn’t being used for anything. This is ideal behavior, except for the few seconds between when we create the tunnel and when we get a MySQL connection up and running via the tunnel.  To buy us some time during this period, we issue the harmless “sleep 60” command when the tunnel is created, which basically buys us 60 seconds to get something else going through the tunnel before it closes itself.  As long as a MySQL connection is established in that timeframe, we are all set.
  • Avoiding a password prompt: Normally, after issuing a command like the one above, you will receive a password prompt to validate the user logging into the remote machine.  Again, this is bad for automation, since it’s never a good idea to have an application interacting with a command line prompt (or storing plain-text passwords). Public key encryption comes to the rescue again in this case.  SSH won’t prompt for a password if the public certificate of remote user is stored in the ~/.ssh/authorized_keys file of the remote user account being logged into.  We simply append our public key to this file and password prompts are no longer an issue.

Connecting via MySQL

It’s time to see all of our hard work pay off.  From our local machine, we simply issue the following command:

mysql -u sqluser -p -h 127.0.0.1 -P 3307

Notice that the MySQL host is 127.0.0.1, which is the same as the bind-address value on the remote server’s my.cnf file.  It’s important that we use this value and not localhost, since we are ultimately accessing a forwarded TCP port, and specifying localhost causes MySQL to ignore TCP altogether and simply connect to the local server via a local socket.  Accordingly, notice that we have specified port 3307 to make the connection; this is the TCP port we are forwarding.

Integrating with PHP CLI

So, now we have all these interesting tools doing our bidding at the command line, but how do we use them to automate data replication and analysis?  How do we go from a cool technique to a hosted business intelligence application?  While we use a number of technologies and methodologies to conduct this process here at RJMetrics, I thought I would share a simple way one could automate this using PHP CLI (the often-overlooked PHP command line interface).

Here’s how to securely establish a remote database connection in just 2 lines of PHP code:

shell_exec(“ssh -f -L 3307:127.0.0.1:3306 user@remote.rjmetrics.com sleep 60 >> logfile”);
$db = mysqli_connect(‘127.0.0.1’, ‘sqluser’, ‘sqlpassword’, ‘rjmadmin’, 3307);

We use the shell_exec function to create the tunnel with a 60 second opening window, and then use the mysqli_connect function to open a database connection using the forwarded port.  Note that we must use the “mysqli” library here because mysql_connect does not allow us to specify a port.

Of course, you should be careful using functions like shell_exec that make system level calls.  They should never include user-specified data and should generally be avoided by code that serves webpages.  We get away with it here because this particular PHP file is being used via CLI and can not be triggered via the web.

A final note on PHP: there is a very poorly documented PHP extension called SSH2 that provides an interface for making SSH connections via PHP functions (eliminating the need for shell_exec and its brethren).  Unfortunately, however, the current version of this library does not allow you to specify a local port when opening an SSH tunnel.  This renders the library rather useless for the MySQL application, since the port used by the remote application is almost always in use by the local server.  For other applications of SSH tunneling, however, you may find it to be a more secure bet.

Thanks for reading and I hope you enjoyed this peek at just one of the many interesting ways we’re dealing with data here at RJMetrics.  If you have a chance, check out our site to learn more about our ecommerce analytics and hosted business intelligence products.  See you next time!

23 Comments

  1. Alex
    Posted March 9, 2009 at 5:14 am | Permalink

    Hello,

    I like your howto and it already helped me oneday.
    Right now I can’t access the MySQL database. I used the command line with the parameter -v (=verbose) and it tells me that I’m proper connected and even the port 3306/3007 would be forwarded.
    How comes that I can’t find the open port with netstat?

    Thanks, Alex

  2. Posted March 9, 2009 at 10:21 am | Permalink

    Make sure you are forwarding local port 3307 to remote port 3306 and that you’ve got the bind-address on your remote MySQL server set to 127.0.0.1 and not localhost. Also, make sure the skip-networking line is commented out of your my.cnf file. Finally, make sure that you have rebooted MySQL server since making the changes to my.cnf. As for the netstat issue, you might check and see if any local settings or applications are preventing you from using port forwarding.

  3. Posted March 16, 2009 at 9:58 am | Permalink

    One big question I have — how do you keep an SSH tunnel alive on a workstation for weeks at a time, without it being closed on either end?

    No one has addressed this thorny issue.

    Thanks,

    Matthew

  4. Posted March 16, 2009 at 5:32 pm | Permalink

    Matthew,

    Thanks for the question. I would consider creating a cron job to run a small script that ensures the connection gets reconnected if severed.

    You might find this helpful: http://www.brandonhutchinson.com/ssh_tunnelling.html

  5. Philipp.nasri@google
    Posted June 4, 2009 at 7:20 am | Permalink

    Thanks for the tutorial, thats what I was looking for. But how to send the password via shell_exec? Can´t establish a connection without a ssh password.

    Thanks,
    Philipp

  6. Posted June 4, 2009 at 9:27 am | Permalink

    Philipp,

    Good question. In short, you should really use a passwordless authentication method. Here’s why:

    Since SSH doesn’t allow you to specify the password in plaintext as part of the initial command, the only way to send the password using shell_exec would be to simulate the interactivity of a command-line session (in which a password prompt follows an ssh call requiring a password). Basically, that would involve sleeping for a few seconds and then sending another shell_exec command with just the password (we assume the password prompt has shown up during the sleeping period). However, this is really dicey and not something I would depend upon in PHP. I wouldn’t recommend it– you really need to use passwordless authentication for this method to work correctly.

    Good luck! -RJM

  7. Philipp
    Posted June 4, 2009 at 9:58 am | Permalink

    Robert,

    Thank you for your answer. Than I will use passwordless auth.

    Philipp

  8. Jemmy Lai
    Posted August 18, 2009 at 10:07 pm | Permalink

    FYI, on Windows, PuTTY’s plink.exe could use the -pw switch to include the SSH password.
    Just in case anybody’s doing this on Win32.

  9. Posted August 23, 2009 at 11:15 pm | Permalink

    Just a quick point on this:

    “Note that we must use the “mysqli” library here because mysql_connect does not allow us to specify a port.”

    mysql_connect accepts host:port for the server.

  10. hui
    Posted September 21, 2009 at 1:15 am | Permalink

    So if my web site is running as user “apache”. I need to log in as user apache to generate keys and paste the key to the remote system authorized_keys file?

    And if user apache’s home directory is in the var/www. I need to go to there to mkdir .ssh and then run the key generation command as user apache?

    I don’t even remember that user apache has a password, I think the user apache is auto generated when I install the linux OS.

  11. hui
    Posted September 21, 2009 at 1:17 am | Permalink

    So I need to use sudo? Something like

    mkdir /var/www/.ssh
    chown -R apache:apache /var/www/.ssh
    sudo -u apache ssh-keygen -t dsa

  12. Posted September 21, 2009 at 8:38 am | Permalink

    Hui, I’m not sure why your apache user would need to establish an SSH tunnel directly (I’ve typically established it via a normal Linux user). Even if a web application needs to make use of an SSH Tunnel, you shouldn’t have to establish it as the apache user.

  13. hui
    Posted September 21, 2009 at 11:09 am | Permalink

    I thought due to you are running this script from a php page.

    shell_exec(”ssh -f -L 3307:127.0.0.1:3306 user@remote.rjmetrics.com sleep 60 >> logfile”);
    $db = mysqli_connect(’127.0.0.1′, ’sqluser’, ’sqlpassword’, ‘rjmadmin’, 3307);

    And to be able to do the passwordless authentication from php page as your above code, you need to generate key as apache user, and paste that key to authorized_keys file in the remote machine .ssh folder.

    Or do you mean that as long as php page is in my account root, I can execute ‘shell_exec(”ssh -f -L 3307:127.0.0.1:3306 user@remote.rjmetrics.com sleep 60 >> logfile”); ‘ as long as I have set up my account key pairs and paste my public key to the remote machine key file?

    Thanks!

  14. Posted September 21, 2009 at 12:25 pm | Permalink

    try this:
    print shell_exec(“whoami”);

    It should output the linux user that is actually executing commands when using the shell_exec() function. Configure passwordless authentication for this user. Also, as a general rule, I would make sure everything is working from the command line first before doing it from PHP.

    Good luck.

  15. hui
    Posted September 21, 2009 at 12:52 pm | Permalink

    I got the user as ‘apache”, so I think I am out of luck and I have to use apache account to generate the key and set up the authorized_key files.

    One more question:

    I have set up my account keys pair and paste my public key to one remote account, and the passwordless authentication is working fine.

    But on another remote account, I did the same thing, but I cannot get the passwordless access to that account. I double checked the authorized_keys file, ssh folder and their permissions. They are all set up right. But somehow it works on one machine but not on another.

    Both remote accounts have open ssh, and I can generate keys at both remote accounts.

    You have to access many remote accounts from your clients. Any issues that you think may stop me to passwordless access to that remote account (it always ask for the password.)

  16. hui
    Posted September 21, 2009 at 1:53 pm | Permalink

    I checked the sshd_config and I see the following line
    RSAAuthentication yes

    But I didn’t see the following line
    PubkeyAuthentication yes

    Will this line be required “PubkeyAuthentication yes” for the passwordless to work.

    Thanks!

  17. hui
    Posted September 23, 2009 at 1:27 am | Permalink

    Robert, I was struggling with using ssh2_tunnel php function to build a ssh tunnel to access remote mysql. Like you pointed out, ssh2_tunnel won’t work due to they would allow specifying the local port. (Googled, many people fell in this ssh2_tunnel trap.)

    So use php to connect mysql through ssh that is the only way, right?

    thanks for this instruction! Due to I will be doing many of same kind works, get data from remote mysql, like you did, a few quick questions.

    1) so you run this php script through command line not web page to get the remote database data, due to it is command line, there are several types scripts you can use, why use php, any other programming scripts will do the command line mysql job better? php is on my top to use list too, only because it is the script I am more familiar with than other scripts.

    2) if you already have mysql access through ssh to remote host, why not just run one mysqldump command to get the mysql data from remote host? will that be simpler and safer? (or usually you would not be granted the ‘lock” rights?

    3) about the ssh sleep 60, when the ssh tunnel will be closed, after the php script executed. Then if I am running second php script right after the first, will that port 3307 be already released after first page finished, or will the 3307 still in use, and my second php page will get error?

    Thanks again!

  18. hui
    Posted September 23, 2009 at 1:29 am | Permalink

    I made a mistake in my previous post. correction: “Like you pointed out, ssh2_tunnel won’t work due to they WOULDN’T allow specifying the local port.”

  19. Posted September 23, 2009 at 3:40 pm | Permalink

    Hui, #1 and #2 have to do with your own application and its needs– my notes in this blog post were only made for simplicity’s sake. For #3, you might consider writing a script that uses netstat to detect if the tunnel is still open and respond accordingly. Good luck.

  20. hui
    Posted September 24, 2009 at 1:46 pm | Permalink

    Robert, following your instruction.

    ssh -f -L 3307:127.0.0.1:3306 user@remotehost sleep 60
    mysql -u mysqluser -p -h 127.0.0.1 -P 3307

    Works fine! I think that I am in the right direction.

    But I wrote test.php with following php codes:

    shell_exec(”ssh -f -L 3307:127.0.0.1:3306 user@remotehost sleep 60”);
    $db = mysqli_connect(’127.0.0.1′, ’sqluser’, ’sqlpassword’, ‘sqldatabase’, 3307);

    I then run command line
    php test.php

    It gave me Error: Can’t connect to MySQL server on ‘127.0.0.1’ (111)

    So I change test.php to the following line:

    shell_exec(‘ssh -f -L 3307:127.0.0.1:3306 user@remotehost sleep 60’);
    shell_exec(‘mysql -u mysqluser -p -h 127.0.0.1 -P 3307’);

    Run command line
    php test.php

    I would be prompted for password, but after I keyed in password, I still got Error: Can’t connect to MySQL server on ‘127.0.0.1’ (111)

    Any one has any advices, some set ups I would need? Still thanks for your help, at least I am close to get it working.

  21. hui
    Posted September 24, 2009 at 2:47 pm | Permalink

    It works!

    Thanks Robert. I got it works now!

    A few points to answer my own questions:

    1) >> logfile is needed. (as robert’s post)
    2) I use regular mysql_connect and set up the host to be “127.0.0.1:3307”, so I get the localport too, but somehow “localhost:3307” is not working, it has to be “127.0.0.1:3307”.
    3) and if I run web page due to user is “apache”, I will need to set key pairs for user apache. But I will run my php from shell command, and the “user” will be just my user account.

    Thanks again!

  22. hui
    Posted September 24, 2009 at 7:02 pm | Permalink

    Robert, now I have connected to the remote database through ssh tunnel in php script by following your solution.

    Now I need to select from remote database and insert the result into local database. I can do it in two steps, select first, and then insert second. But it seems to be slow for large set of records.

    I would like to use subquery, something like

    //get the data from remotedatabase
    $sql_select_records_from_remotedatabse=”SELECT field_1, field_2 FROM remotedatabase.table1″;

    //insert into localsdatabase using subquery

    $sql_insert_records_to_localdatabase=”INSERT INTO table_a (field_a, field_b) $sql_select_records_from_remotedatabse”;

    some threads suggest to make it work, I need to use sp_addlinkedserver.

    solution for remote server
    Submitted by Anonymous (not verified) on Tue, 06/02/2009 – 11:56.

    1. first add the server to your local sql server (from where you want to run the query)

    exec sp_addlinkedserver [192.168.1.130]

    2. now run the query as

    select * from [192.168.1.130].[pubs].[dbo].[authors]

    Did you run into the same situation, if so, what is your solution?

    Thanks!

  23. Posted January 7, 2010 at 3:52 pm | Permalink

    Thanks for sharing your solution. I was having a heck of a time connecting to mysql via ssh. The examples most readily found on the net suggest using the remote ip between the local and remote ports. Changing it to 127.0.0.1 fixed the problem. Good thing I kept googling the various error terms, else I would still be puzzling.


Post a Comment

Required fields are marked *
*
*

%d bloggers like this: