Protecting MySQL Sessions With SSH Port Forwarding (Part 1)
by Mike Hillyer

This article was previously published by VBMySQL.com.

Introduction to SSH Port Forwarding

Security should be a major component of any application you develop. You should consider application security when designing and implementing your application. In a previous article I described how to avoid making your application vulnerable to SQL injection. In this article I will describe how to protect your application from outside interference between the client and server by employing the port forwarding capabilities of SSH.

When the mysql client communicates with the MySQL® server, all communication (with the exception of the user password) is done in plain text. The same holds true for the MyODBC driver and all software communicating through it. What this means is that if an unscrupulous individual gets between your client and the server, they can have full access to all information transmitted. In order to protect your information you need to encrypt communications between the MySQL server and the client application, whether it be the mysql client or a MyODBC application.

SSH can be used to encrypt communications between the client and server. This is known as SSH tunneling and is actually quite simple to use. One benefit of SSH tunneling is that it allows us to connect to a MySQL server from behind a firewall when the MySQL server port is blocked. Many hosting companies that provide MySQL hosting will block access to the MySQL server from outside the hosting company’s network, and only grant access to users connecting from localhost. As long as the company provides SSH access, you can still connect through port forwarding.

Requirements for SSH and MySQL

To perform port forwarding between a mysql client application and the MySQL server, you will need a working MySQL server. I will be showing all samples with the MySQL server running on RedHat Linux. It is my opinion that production MySQL servers should be installed on *NIX or *BSD servers. MySQL performance is better when run on Linux than on a Windows server system. The reasons for this include the overhead incurred by the Windows GUI, and the fact that many of the Linux and BSD variants have a better implementation of the TCP/IP stack. In should be noted that MySQL is developed on UNIX systems and then ported to Windows, therefore more manpower is directed to the UNIX versions of MySQL.

Another reason to show examples on Linux is the ready availability of an SSH server (usually OpenSSH), included by default on most Linux systems. For those who must use Windows for their MySQL server platform there are two options: Install an SSH server on Windows, or use a Linux system as a SSH proxy to the Windows server. The first option is covered will be covered in Part 2 of this article, and the second shall be described below.

You will also need a copy of Putty, an SSH client application written by Simon Tatham and available at http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html. You will also need a copy of Plink; a command-line version of the Putty client, one which I highly recommend. Plink is available at the same download link mentioned above for Putty. Both Putty and Plink are provided will full source code available under a GPL compatible license that permits unlimited use for both commercial and Open Source purposes.

What Is SSH?

SSH stands for Secure SHell and is typically used as an encrypted version of telnet. SSH allows you to access a remote server’s shell without compromising security. In a telnet session all communications, including username and password, are transmitted in plain-text, allowing anyone with adequate resources to listen-in on your session and steal passwords and other information. Such sessions are also susceptible to session hijacking, where a malicious user takes over your session once you have authenticated. SSH serves to prevent such vulnerabilities.

OpenSSH, the tool included with most Linux variants, is described as follows in the OpenSSH FAQ at http://www.openssh.org/faq.html#1.1:

“OpenSSH is a FREE version of the SSH suite of network connectivity tools that increasing numbers of people on the Internet are coming to rely on. Many users of telnet, rlogin, ftp, and other such programs might not realize that their password is transmitted across the Internet unencrypted, but it is. OpenSSH encrypts all traffic (including passwords) to effectively eliminate eavesdropping, connection hijacking, and other network-level attacks.”

Setting Up A Linux User Account

The first thing you will need in order to setup an encrypted session is a user account on the remote system. If you have already been provided with one then you are ready to go.

The following command will produce an appropriate user and must be run by root level user:

[root@polibase home]# useradd -s sshtunnel

The useradd command is used to add a new user to our system and sshtunnel is the new username. Once our user is created, we can assign a password. Once again we need to be a root-level user:

[root@polibase home]# passwd sshtunnel
  Changing password for user sshtunnel.
  New password: <your password>
  Retype new password: <your password>
  passwd: all authentication tokens updated successfully.

When typing your password the password will not echo to the screen for security purposes.

You will also need a user account for accessing MySQL, which I will assume you already posses.

Setting Up The SSH Tunnel

We will first need to configure a Putty session for port-forwarding. Our first step is to configure Putty to connect via the SSH protocol and specify the server address:

Once we have configured our host and protocol, we can move on to setting up the tunnel. To create a tunnel, we specify a destination host and port. SSH Tunneling has the unique ability to forward ports beyond the SSH server machine, useful for situations where perhaps the MySQL server does not have SSH installed:

Our remote system connects to the SSH host through the firewall and the SSH host then forwards the session on to the database. When specifying port forwards, we use a host:protocol syntax to specify where out traffic should end up. Assuming that you have SSH on the MySQL server, this would mean the use of localhost:3306 in most situations.

In this example, we are specifying that port 3306 on our client machine should be forwarded to port 3306 on the server. It should be noted that you can forward a local port to a different remote port number. For example, on my development machine I keep a local copy of MySQL running on port 3306. On my production server I also run MySQL on port 3306. I can activate a tunnel such as seen above, but port 3306 traffic will be intercepted and forwarded, making my local copy of MySQL unreachable. If I change the source port setting to 3307, I can access the remote server through port 3307 and my desktop server on port 3306.

Once we have added our port forwarding directives, we can then save the session to make it available for repeated use. Back on the session menu we can specify a saved session name and click save to add this to our list of saved sessions:

Once you have created and saved your session, it is easy to add a shortcut to your desktop to quickly access port forwarding. Right-click on your desktop and choose New> Shortcut. Configure the shortcut and assign the target as /path/to/putty/putty.exe -load sessionname. In the example above, with putty at c:\putty.exe, you would assign the shortcut target to be:

c:putty.exe -load mysqltunnel

Then you simply need to double-click the icon and provide a username and password when prompted. A shell session will open and your tunnel will be established. When you are finished using the tunnel you can close the Putty window to close the tunnel and shell session.

Using The SSH Tunnel with MySQL

Using your new SSH tunnel is as simple as pointing your application at the local port on the client side of the tunnel. Lets say the following is your connection string:

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
  & "SERVER=www.myhost.com;" _
  & "DATABASE=test;" _
  & "UID=testuser;" _
  & "PWD=12345;" _
  & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

We simply modify the SERVER section to point to localhost instead of www.myhost.com. Lets say we are forwarding port 3307 instead of 3306 in order to accommodate our local development MySQL server:

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
  & "SERVER=127.0.0.1;" _
  & "PORT=3307;" _
  & "DATABASE=test;" _
  & "UID=testuser;" _
  & "PWD=12345;" _
  & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

This change will allow our application to connect to the remote server through the tunnel, ensuring security and allowing us to connect through firewall protected connections where SSH connections are available. We can also reconfigure DSN based connections using the same approach in order to allow ODBC applications such as Microsoft Access and Crystal Reports to also connect to our remote database.

Automating The SSH Tunnel with Visual Basic

Sometimes we will want to activate the tunnel from within our VB application with minimal intervention by the user, other than perhaps to provide the username and password of the shell account on the remote server. This can be accomplished by using the ShellExecute API function. ShellExecute is often used to open documents with the document’s associated executable. Another powerful use of ShellExecute is the ability to open applications with parameters on the command line, and hide the window the application occupies. As such, we can use ShellExecute in combination with the Plink command-line utility to open our SSH tunnel automatically.

Create a new project and place this code on a blank form:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" 

  (ByVal hwnd As Long, _
  ByVal lpszOp As String, _
  ByVal lpszFile As String, _
  ByVal lpszParams As String, _
  ByVal lpszdir As String, _
  ByVal FsShowCmd As Long)
  As Long

The API declaration above allows us to access the ShellExecute API function as in the usage below. I am using form_load, but you could assign the tunneling code to a commandbutton if you so desired:

Private Sub Form_Load()
  ShellExecute Me.hwnd, "Open", "C:/plink.exe", "-ssh
  -l sshtunnel -pw 12345 -L 3306:127.0.0.1:3306 -batch www.myhost.com", "c:\",
  0
  MsgBox "Tunnel Open!"
  End Sub

The arguments for Plink can be found by typing Plink -h, or can be found at http://the.earth.li/~sgtatham/putty/0.53b/htmldoc/Chapter7.html#7.2 . The -ssh argument indicates that we wish to use the SSH protocol. The -l sshtunnel argument specifies that we wish to login as the sshtunnel user. The -pw 12345 argument specifies a password, in this case 12345, which is NOT recommended and is simple used as an example. The tunnel is established with the -L argument, which indicates that we want to take data directed at local port 3306 and forward it to port 3306 on 127.0.0.1 (relative to the remote server). We indicate that we wish to run in non-interactive mode with the -batch argument and finish the arguments with the address of the remote host.

Once ShellExecute is called the tunnel is opened and available to all applications in the system, and will remain open until either the system is shutdown or Plink is explicitly closed. You can also open the tunnel with the shellexecuteex API call, allowing you to close the tunnel within VB.

Conclusion

SSH port forwarding offers an easy way to boost the security of communications with the MySQL server. SSH port forwarding protects MySQL sessions from prying eyes and various attacks. By including Putty and/or Plink in your Visual Basic application you can secure your users data effectively.