Archive for the ‘Databases’ Category

HOWTO: JDBC over an SSH Tunnel

First, credit where credit is due. Most of this code came from here (I just modified it a bit):

You’ll also need JSch (a java implementation of SSH):

The major function of SSH tunnels are to secure what would otherwise be an unsecure client/server connection. But another awfully handy use of SSH tunnels are accessing remote resources that are not normally exposed. Databases, for example.

I recently needed to access a MySQL database on a remote server from some local Java code. This database is off on a third party hosting server that does not allow outside access to MySQL, but I can login via SSH.

So, here goes:

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;

public class CTestDriver
  private static void doSshTunnel( String strSshUser, String strSshPassword, String strSshHost, int nSshPort, String strRemoteHost, int nLocalPort, int nRemotePort ) throws JSchException
    final JSch jsch = new JSch();
    Session session = jsch.getSession( strSshUser, strSshHost, 22 );
    session.setPassword( strSshPassword );
    final Properties config = new Properties();
    config.put( "StrictHostKeyChecking", "no" );
    session.setConfig( config );
    session.setPortForwardingL(nLocalPort, strRemoteHost, nRemotePort);
  public static void main(String[] args)
      String strSshUser = "ssh_user_name";                  // SSH loging username
      String strSshPassword = "abcd1234";                   // SSH login password
      String strSshHost = "";          // hostname or ip or SSH server
      int nSshPort = 22;                                    // remote SSH host port number
      String strRemoteHost = "";  // hostname or ip of your database server
      int nLocalPort = 3366;                                // local port number use to bind SSH tunnel
      int nRemotePort = 3306;                               // remote port number of your database 
      String strDbUser = "db_user_name";                    // database loging username
      String strDbPassword = "4321dcba";                    // database login password
      CTestDriver.doSshTunnel(strSshUser, strSshPassword, strSshHost, nSshPort, strRemoteHost, nLocalPort, nRemotePort);
      Connection con = DriverManager.getConnection("jdbc:mysql://localhost:"+nLocalPort, strDbUser, strDbPassword);
    catch( Exception e )

So, now I can access the remote database and the traffic is encrypted on top of that!

Same disclaimer as always, this IS NOT production worthy code as is. The exception handling is crap and there’s lots more paranoia to be had. Please follow your own coding best-practices.

ah oh…

How did I miss THIS yesterday?

Seems the web developers at MySQL skipped out on their best practices 101 class … you know, the day they talked about binding parameters.

Of course, that’s every developer’s knee-jerk reaction.  I’m sure if you searched all of our code long enough, you’d see some improperly escaped SQL in there somewhere.

Although, I’m puzzled as to how this would have gotten through a formal code review.  Interesting, no doubt.

Now, I’m off to change my account passwords…