Forum Discussion

chiragverma123's avatar
chiragverma123
Contributor
9 years ago

Tunnel via ssh(port 22) then port forward 3306 to access the database - SOAP UI groovy

Here is what i have so far. But the problem is first I need to tunnel via ssh(port 22) then port forward 3306 to access the database over the ssh connection. I was able to get this far but no luck, can someone please look into it?

 

import groovy.sql.Sql
package mypackage
import java.sql.*
import com.jcraft.jsch.JSch
import com.jcraft.jsch.Session


// ssh login
def sshHost = ''
def sshUser = ''
def sshPass = ''
def sshPort = 22

// database login
def targetHost = '127.0.0.1'
def targetUser = ''
def targetPass = ''
def targetPort = '3306'

JSch jsch = new JSch();
Session session = jsch.getSession(sshUser, sshHost, sshPort);
session.setPassword(sshPass);
session.setConfig("StrictHostKeyChecking", "no");
System.out.println("Establishing Connection...");
session.connect();
int assinged_port=session.setPortForwardingL(0, targetHost, targetPort);

Connection con = null;
def driver = 'org.mariadb.jdbc.Driver'
def connectionString = 'jdbc:mariadb://localhost:3306/db'
con = DriverManager.getConnection(connectionString, targetUser, targetPass);
Statement st = con.createStatement();
String sql = "select * company "
st.execute(sql);

  • I just implemented a function in groovy for my team project to test data in AWS RDS with ssh tunnel. And when I googled it, there was no much results about how to make ssh tunnel via jsch. I spent quite a long time to figure it out, and saw your question post, so want to share here and hope it would be helpful.

     

      import groovy.sql.Sql
      import com.jcraft.jsch.*
    
    def resultSet = queryDatabase(sql)
    // do verification with resultSet
    
    // make an ssh tunnel to database in AWS, execute query with specific sql and // return result set as a list.
    def queryDatabase(String sql){
    	def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
    	def projectPath = groovyUtils.projectPath //gets the path of the project root
    	def sshKeyPath = projectPath+"/../key"
    	
    	JSch jsch = new JSch()
    	jsch.addIdentity(sshKeyPath)
    	
    	Session session = jsch.getSession(sshUser, sshHost, sshPort);
    	session.setConfig("StrictHostKeyChecking", "no")
    	
    	try{
    	  session.connect()
    	  session.setPortForwardingL(1521, targetHost, targetPort);
    
    	  // Query database and process the result.
    	  sql = Sql.newInstance(
    	  'jdbc:oracle:thin:@localhost:1521'
    	  + testRunner.testCase.testSuite.project.getPropertyValue("DATABASE_SERVICE_NAME"),
    	  testRunner.testCase.testSuite.project.getPropertyValue("DATABASE_USERNAME"),
    	  testRunner.testCase.testSuite.project.getPropertyValue("DATABASE_PASSWORD"),
    	  'oracle.jdbc.driver.OracleDriver'
    	  )
    		
    	  def resultset = sql.rows(sql)
    	resultset
    	}finally{
    	  session.disconnect()
    	  null
    	}
    }

    NOTE:

    1. Download jsch-0.1.54.jar from https://mvnrepository.com/artifact/com.jcraft/jsch/0.1.54 and put in <soapui-dir>/bin/ext directory.

    2. Restart soapui.

    3. Remember to disconnect the session when exception occurs, otherwise the connection would be hang, then the port cannot be bound to.

  • ginnyd's avatar
    ginnyd
    New Contributor

    I just implemented a function in groovy for my team project to test data in AWS RDS with ssh tunnel. And when I googled it, there was no much results about how to make ssh tunnel via jsch. I spent quite a long time to figure it out, and saw your question post, so want to share here and hope it would be helpful.

     

      import groovy.sql.Sql
      import com.jcraft.jsch.*
    
    def resultSet = queryDatabase(sql)
    // do verification with resultSet
    
    // make an ssh tunnel to database in AWS, execute query with specific sql and // return result set as a list.
    def queryDatabase(String sql){
    	def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
    	def projectPath = groovyUtils.projectPath //gets the path of the project root
    	def sshKeyPath = projectPath+"/../key"
    	
    	JSch jsch = new JSch()
    	jsch.addIdentity(sshKeyPath)
    	
    	Session session = jsch.getSession(sshUser, sshHost, sshPort);
    	session.setConfig("StrictHostKeyChecking", "no")
    	
    	try{
    	  session.connect()
    	  session.setPortForwardingL(1521, targetHost, targetPort);
    
    	  // Query database and process the result.
    	  sql = Sql.newInstance(
    	  'jdbc:oracle:thin:@localhost:1521'
    	  + testRunner.testCase.testSuite.project.getPropertyValue("DATABASE_SERVICE_NAME"),
    	  testRunner.testCase.testSuite.project.getPropertyValue("DATABASE_USERNAME"),
    	  testRunner.testCase.testSuite.project.getPropertyValue("DATABASE_PASSWORD"),
    	  'oracle.jdbc.driver.OracleDriver'
    	  )
    		
    	  def resultset = sql.rows(sql)
    	resultset
    	}finally{
    	  session.disconnect()
    	  null
    	}
    }

    NOTE:

    1. Download jsch-0.1.54.jar from https://mvnrepository.com/artifact/com.jcraft/jsch/0.1.54 and put in <soapui-dir>/bin/ext directory.

    2. Restart soapui.

    3. Remember to disconnect the session when exception occurs, otherwise the connection would be hang, then the port cannot be bound to.