Finding your SQL statements run on the database through JDBC

Note: The article was written back in 2009. I recommend log4jdbc instead of jdbclogger now a days.

Why is it of any interest at all?

The application may not log all the SQL statements it is executing. If you are using frameworks/tools that generate SQL statements, then it can be very handy to find out what the generated statements look like (specially if you looking forward to optimize you database performance using indexes and such).


Of-course, a lot of the frameworks (such as Hibernate, iBatis) out there do allow you to configure their logging so that you can see what SQLs are being generated. However for prepared statements, most of them fail to display the final SQL executed on your database.

Tools:

One interesting tool to get this job done is: JDBC Logger. Its easy to configure specially if you are already using a connection pool.

Configuration:

Follow the steps below to configure your connection pool (Tomcat 6.0) to use JDBC Logger:

• download the binaries and put the jar files in your classpath
• configure jdbc to use JDBC Logger's driver

  <Resource
        name="jdbc/mydb"
        auth="Container"
        type="javax.sql.DataSource"
        ...
        driverClassName="net.sourceforge.jdbclogger.JdbcLoggerDriver"
        url="jdbc:oracle:thin:@localhost:1521:orcl"
     />

• create  jdbclogger.properties in your application server classpath (on Tomcat 6, you can add it to your $CATALINA_HOME/lib directory)
• add your desired jdbc driver configuration into the file created above. Example entry: jdbclogger.driver=oracle.jdbc.driver.OracleDriver
• set logging level for JDBC Logger to DEBUG: log4j.logger.net.sourceforge.jdbclogger=DEBUG

Done. Note that, the amount of SQL statements logged may be a bit too much to have in one log file. In that case, you can configure log4j to let JDBC Logger to log the SQL statements into a separate file.

SSH Tunneling & Reverse Tunneling

SSH Tunneling:

In short, SSH tunneling allows unix users to connect to a remote computer (through ssh) and map a local port to the remote computer port.

SSH Reverse Tunneling:

SSH reverse tunneling allows a unix user to connect to a remote computer and map a port on the remote computer to a port in the local machine.

The Problem:

We need to test some kind of external service which redirects back to one of our provided service urls. We need a server with a public IP to do it. However, its a pain to always compile, deploy on the public server and test. 

Instead, it will be much easier to be able to map a remote server port to a port on my local machine so that I can deploy my services locally to which the external service will redirect to.

Assume, our external server hostname is mapped to: example.com and we will use sso.example.com to provide Single Sign On (sso) support. From the example site, when the user wants to log in using the SSO service provided by sso.example.com, the user gets redirected to sso.example.com with certain request parameters. Once the user has successfully logged into sso.example.com, he gets redirected back to example.com with some parameters which indicate that the user logged in successfully.

Now to test this, we need to re-deploy the services on to example.com every time we fix something. Of-course, we can mount a file system on the public server that reads the deployment files from a nfs share which can be mounted to some directory on your machine. 

But if the public server and your machine are in different networks and your machine do not have a public IP, then its slightly more tricky to achieve. SSH reverse tunneling will help us come to a solution. 

Assume that your machine host name is "developer" (it can of-course be an IP).

ssh -f user@example.com -R example.com:8081:developer:8080 -N

What this will do is it will log into the remote server "example.com", open a port "8081" and connect it to "developer:8080". So, when anyone logs into "example.com" and sends a request to "http://localhost:8081", the request comes to port 8080 on developer.

But this is no fun since to make the request, a user has to log into example.com. So to fix that, we can map another port on example.com to forward everything to port "8081" which is the reverse tunneling port. To do it, we have to first log into example.com and execute the following command:

ssh -f user@example.com -L example.com:8080:localhost:8081 -N

Once this command is executed, all requests send to example.com:8080 will be forwarded to example.com:8081 which in turn will forward everything to  "developer:8080".

Why not try this shortcut?

ssh -f user@example.com -R example.com:8080:developer:8080 -N 

This does log into example.com and map port 8080 to forward everything to developer:8080. However, it only works if someone logs into example.com and sends a request to "localhost:8080". It doesn't work if a user simply sends the request to "example.com:8080". The only way to make the shortcut mentioned above work is to configure the ssh daemon with the GatewayPorts option enabled. By default, sshd binds the reverse tunneling port to the loop back interface only, thus preventing remote hosts from using the reverse tunneling port. The default value for GatewayPorts is no .