CODEWITHSUNDEEP
javamysqlsshjsch
TrackerSB
TrackerSB

Reputation: 317

Java - ssh-tunneling to mysqldatabase with JSch

I already tried to search for solutions through Google and Stackoverflow. There´re plenty of topics about this but nothing helped me out, yet.

import java.util.logging.Level;
import java.util.logging.Logger;

public class GTEVDatabaseConnection {

private Connection connection = null;
private static final int DATABASE_PORT = 3306,
        LOCAL_PORT = 1234,
        SSH_PORT = 22;
private static final String DRIVER = "com.mysql.jdbc.Driver",
        DATABASE = "myDatabase",
        DATABASE_USER = "dbuser",
        DATABASE_PASSWORD = "dbpasswd",
        DATABASE_HOST = "example.de.mysql", 
        SSH_HOST = "ssh.example.de",
        LOCAL_DATABASE_URL = "jdbc:mysql://localhost:" + LOCAL_PORT + "/" + DATABASE;

public GTEVDatabaseConnection(String sshUsername, String sshPassword) throws SQLException {
    Session sshSession = null;
    try {
        sshSession = new JSch().getSession(sshUsername, SSH_HOST, SSH_PORT);
        sshSession.setPassword(sshPassword);
        sshSession.setConfig("StrictHostKeyChecking", "no");
        sshSession.connect();
        sshSession.setPortForwardingL(LOCAL_PORT, DATABASE_HOST, DATABASE_PORT);
        //sshSession.setPortForwardingR(DATABASE_PORT, "localhost", LOCAL_PORT);

        Class.forName(DRIVER);
        this.connection =     DriverManager.getConnection(LOCAL_DATABASE_URL, DATABASE_USER, DATABASE_PASSWORD); //TODO com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
    } catch (ClassNotFoundException | JSchException ex) {
Logger.getLogger(GTEVDatabaseConnection.class.getName()).log(Level.SEVERE,null, ex);
        } finally {
            if(this.connection != null){
                connection.close();
            }
            if(sshSession != null && sshSession.isConnected()){
                sshSession.disconnect();
            }
        }
    }

    public ResultSet execQuery(String query) throws SQLException {
        return this.connection.createStatement().executeQuery(query);
    }
}

What the code should do exactly:
You should be able to create an object of GTEVDatabaseConnection. The params of the constructor are username and password for ssh-login. You need this login in order to be able to connect to my MySql-database because you´re only able to reach it over ssh.
So, when a new object gets created it creates a new Session over jsch.getSession(..), connects and sets PortForwarding to localhost.
The error occurs when I try to execute DriverManager.getConnection(...). Here is the error:

SCHWERWIEGEND: null
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:389)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1038)
at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:627)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1013)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2239)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2270)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2069)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:389)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at connection.GTEVDatabaseConnection.<init>(GTEVDatabaseConnection.java:44)
at gui.DatabaseConnector.createConnection(DatabaseConnector.java:102)
at gui.DatabaseConnector.lambda$start$0(DatabaseConnector.java:50)
at gui.DatabaseConnector$$Lambda$67/2106702206.handle(Unknown Source)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Node.fireEvent(Node.java:8390)
at javafx.scene.control.Button.fire(Button.java:185)
at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
at javafx.event.Event.fireEvent(Event.java:198)
at javafx.scene.Scene$MouseHandler.process(Scene.java:3758)
at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3486)
at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2495)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:350)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:275)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$350(GlassViewEventHandler.java:385)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$$Lambda$140/973574965.get(Unknown Source)
at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:404)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:384)
at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
at com.sun.glass.ui.View.notifyMouse(View.java:927)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$145(WinApplication.java:101)
at com.sun.glass.ui.win.WinApplication$$Lambda$36/1963387170.run(Unknown Source)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2914)
at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:559)
... 68 more

Thank you for your help. Ask for more details when I missed something. Greetings Tracker

Upvotes: 0

Views: 3344

Answers (1)

TrackerSB
TrackerSB

Reputation: 317

I found the error. The firewall of the server is blocking ssh-tunneling. So I created following code, which works.

package connection;
import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelExec;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.logging.Level;
import java.util.logging.Logger;

public class GTEVDatabaseConnection {

private final Channel channel;
private static final int SSH_PORT = 22;
private static final String DATABASE = "databasename",
        DATABASE_USER = "databaseuser",
        DATABASE_PASSWORD = "databasepasswd",
        DATABASE_HOST = "databasehost",
        SSH_HOST = "sshhost";

public GTEVDatabaseConnection(String sshUsername, String sshPassword) {
    try {
        Session sshSession = createSshSession(sshUsername, sshPassword);
        this.channel = sshSession.openChannel("exec");
        ((ChannelExec) channel).setErrStream(System.err);
    } catch (JSchException ex) {
        Logger.getLogger(GTEVDatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        throw new SQLException("username or password didn´t work");
    }
}

/**
 * F&uuml;hrt eine SQL-Abfrage aus und liefert eine Tabelle der Ergebnisse
 * @param query Der SQL-Code
 * @return Tabelle der Ergebnisse. Erste Dimension = Zeile; zweite Dimension = Spalte
 * @throws SQLException Tritt auf, wenn der SQL-Befehl eine Fehler ausl&ouml;ste. (Fehler wird per System.err.println(...) ausgegeben)
 */
public ArrayList<ArrayList<String>> execQuery(String query) throws SQLException {
    ArrayList<ArrayList<String>> formattedResult = null;
    try {
        ((ChannelExec) channel).setCommand("mysql -u" + DATABASE_USER + " -p" + DATABASE_PASSWORD + " -h" + DATABASE_HOST + " -e'" + query + "' " + DATABASE);
        InputStream in = channel.getInputStream();
        channel.connect();
        //TODO check whether sqlcode is correct

        String result = readResult(in);
        if(result == null){
            throw new SQLException("Invalid SQL-Code");
        }
        formattedResult = new ArrayList<>();
        for(String row: result.split("\n")){
            ArrayList<String> fields = new ArrayList<>();
            fields.addAll(Arrays.asList(row.split("\t")));
            formattedResult.add(fields);
        }

        channel.disconnect();
    } catch (JSchException | IOException ex) {
        Logger.getLogger(GTEVDatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
    }

    return formattedResult;
}

private String readResult(InputStream in) throws IOException {
    if (in.available() <= 0) {
        return null;
    }

    StringBuilder output = new StringBuilder();
    int nextByte;
    do {
        nextByte = in.read();
        output.append((char) nextByte);
    } while (nextByte != -1);

    return output.toString();
}

private Session createSshSession(String sshUsername, String sshPassword) throws JSchException {
    Session session = new JSch().getSession(sshUsername, SSH_HOST, SSH_PORT);
    session.setPassword(sshPassword);
    session.setConfig("StrictHostKeyChecking", "no");
    session.connect();
    return session;
}
}

Now the program connects over ssh, execute a mysql-command and reads out the result.

It may be less proffessional or "nice" because I can´t use ResultSet to iterate my result but it works.

I tried different things in order to connect over telnet.

  • When I don´t run the program there´s connection error as expected.
  • When I run the program and pause it in order to execute telnet localhost 1234 then the cmd is cleared and there´s no response.
  • When I try to type something the cmd-prompt appears again. So I only think that there´s a connection...

Upvotes: 1

Related Questions