A Java/PL/SQL Port Scanner II In a post last week, I detailed my efforts in trying to use Java and the UTL_TCP to determine open ports on my DVR.

From that I learned a valuable lesson in infinite loops and also that you could DELETE from dba_jobs.

Since I was too lazy to diagnose the TNS errors I was receiving from using UTL_TCP and I knew that the Java class was working, I decided to combine the 2 approaches on Friday night.

There are 5 cable boxes in the house which means 5 IPs to check. Looking more closely at the MAC addresses, I realize 4 are the same (or roughly the same). That led me to believe that the 5th was the DVR. But I'm going to check them all for fun.

Start with the table to capture those open ports:

CREATE TABLE open_ports
(
  ip VARCHAR2(20),
  port NUMBER(5,0)
);

Then I take the java class (with the Main procedure) and convert it into a standard Java class that can be loaded into the database:

CREATE OR REPLACE AND COMPILE 
JAVA SOURCE NAMED "CheckPorts"
AS
import java.io.IOException;
import java.net.Socket;
import java.net.UnknownHostException;

public class CheckPorts
{
  public static String CheckPorts( String ip, String port ) throws UnknownHostException, IOException
  {
    try
    {
      Socket socket = new Socket( ip, Integer.parseInt( port ) );
      return "Y";
    }
    catch ( UnknownHostException e ){ return "N"; }
    catch ( IOException e ) { return "N"; }
    finally { }
  }
}
/

Then I create a PL/SQL wrapper around the Java class:

CREATE OR REPLACE
FUNCTION check_port( p_ip IN VARCHAR2, p_port IN VARCHAR2 ) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'CheckPorts.CheckPorts( java.lang.String, java.lang.String ) return String';
/
show errors

Then I wrap that call up in a standalone procedure (remember, this is just for fun).

CREATE OR REPLACE
PROCEDURE check_port_insert
  ( p_ip IN VARCHAR2,
    p_port IN INTEGER )
IS
  l_port VARCHAR2(1);
BEGIN
  l_port := check_port( p_ip, TO_CHAR( p_port ) );

  IF l_port = 'Y' THEN
    INSERT INTO open_ports( ip, port )
    VALUES( p_ip, p_port );
  END IF;  
EXCEPTION
  WHEN others THEN
    NULL;
END check_port_insert;
/

No, I didn't re-raise after the WHEN OTHERS, had I been doing this seriously, I would have trapped it or better yet, would have caught the specific exception.

Finally my anonymous block to call check_port_insert and submit as a job (for threading). I picked 30 processes at a time so as not to overload my laptop...on the first go anyway.

DECLARE
  TYPE r_record IS TABLE OF OPEN_PORTS.IP%TYPE;
  l_table R_RECORD := R_RECORD();
  job_count PLS_INTEGER;
  job_number PLS_INTEGER;
BEGIN
  l_table.EXTEND;
  l_table(1) := '192.168.1.102';
  l_table.EXTEND;
  l_table(2) := '192.168.1.100';
  l_table.EXTEND;
  l_table(3) := '192.168.1.101';
  l_table.EXTEND;
  l_table(4) := '192.168.1.103';
  l_table.EXTEND;
  l_table(5) := '192.168.1.104';
  <>
  FOR i IN 1..l_table.COUNT LOOP
    <>
    FOR j IN 1..10000 LOOP

      SELECT COUNT(*)
      INTO job_count
      FROM user_jobs;

      IF job_count < 30 THEN
        dbms_job.submit
          ( job => job_number,
            what => 'BEGIN ' || 
                    '  check_port_insert( ''' || l_table(i) || ''',' || j || '); ' ||
                    '  COMMIT; ' ||
                    'END;',
            next_date => SYSDATE );

        COMMIT;
      ELSE
        dbms_lock.sleep( 5 );
      END IF;
    END LOOP;    
  END LOOP;
END;
/

A table of IP addresses to check each of the 5 known IPs. I used 10,000 as the number of ports to check (I think it goes higher...again, just being a lot lazy). Then a check against USER_JOBS to see if there were 30 jobs or not, if so, wait 5 seconds (DBMS_LOCK.SLEEP) otherwise submit another job.

I probably could have sped things up and not brought down my system by submitting 100 jobs...I'll probably try that tonight.

So here are the results:

PL/SQL procedure successfully completed.

Elapsed: 02:33:18.98
CJUSTICE@ELEVEN>SELECT * FROM open_ports;

IP                         PORT
-------------------- ----------
192.168.1.102              7501
192.168.1.102              8082

2 rows selected.

50K ports in 2 hours and 33 minutes. Is that good? I never let the free port scanner I downloaded go this far so I have no idea. 2 ports open out of 50K. Not a lot. Then I used Telnet to connect to those ports:

Microsoft Telnet> o 192.168.1.102 7501
Connecting To 192.168.1.102...

No go. How about 8082?

Microsoft Telnet> o 192.168.1.102 8082
Connecting To 192.168.1.102...
HTTP/1.1 200 OK
Server: gSOAP/2.7
Content-Type: text/xml; charset=utf-8
Content-Length: 0
Connection: close

Connection to host lost.

Press any key to continue...

OK, so that's probably the port used to pull down the schedule/user guide.

Anyway, I got to try something new and learn a little bit about ports/sockets. I'm not done yet. Next I need to figure out how many possible ports there are...well, this tells me. So I guess I'll have to expand my search. At least now I can concentrate on the DVR (.102) but I still have to check out about 50K ports it looks like.

Labels: java, utl_tcp

  ¶ 11/23/2008 07:24:00 PM   0 Comments Links to this post

  A Java PL/SQL Port Scanner Saturday night I started to wonder how I could get the movies of my DVR. Recently Verizon FIOS added a boatload of HD stations so I've naturally begun to record those. Problem is, the 50 hours or so I have available are quickly eaten up by the HD versions.

We have the multi-room DVR setup, which means we have one DVR but we can view those movies from any TV (with a cable box). Naturally all these boxes are on my internal network (router provided by Verizon).

I understand that with some boxes you can go out and buy additional storage, but I wanted a DIY solution. So, if I could find an open port on one of these boxes, then perhaps I could figure out a way to get the movies onto my computer.

Java to the rescue. Well, not really, but I have always wanted to learn more about that kind of lower level programming (sockets/ports/TCP/whatever). See, I don't really know what I'm talking about which is why I did it.

Since I use JDeveloper for writing SQL and PL/SQL, creating a java class (with the Main method) is easy.

import java.io.IOException;
import java.net.Socket;
import java.net.UnknownHostException;
import java.util.Date;

public class Class1
{
  private static void p ( String text )
  {
    System.out.println( text );
  }

  public static void main(String[] args) throws UnknownHostException, IOException
  {
    Date startDate = new Date();
    String machine = "192.168.1.102";
    Thread thread = new Thread();

    Socket socket;
    int j = 0;
    int x = 0;
    p( "start date/time:  " + startDate.toString() );
    for ( int i = 1000; i < 10000; i++ )
    {
      x++;
      p( "checking port:  " + machine + ":" + i );
      try
      {
        socket = new Socket( machine, i );
        p( "Port open:  " + machine + ":" + i );
        j++;
      }
      catch (UnknownHostException e) { }
      catch (IOException e) { }
      finally
      {
      }
    }
    p( "ports checked:  " + x );
    p( "ports open:  " + j );
    p( "start date/time:  " + startDate.toString() );
    p( "end date/time:    " + new Date().toString() );
  }
}

As LC would say, "That's not so bad."

Only it was slow, 1 port at a time. I did learn that trying to open up a port connection is s l o w. How can I speed this up?

I could make it threaded right? No. That would take too much time.

But hey, I know how to do it in PL/SQL (threaded that is). UTL_TCP should work nicely. I've never had to use it, so a great opportunity to familiarize myself with it (not that it's needed very often).

So I start Oracle XE and initially just try it out to see how it works:

DECLARE
  c UTL_TCP.CONNECTION;
BEGIN
  c := utl_tcp.open_connection
        ( remote_host => '192.168.1.2',
          remote_port => 1,
          tx_timeout => 1 );
  
  utl_tcp.close_connection( c );
EXCEPTION
  WHEN others THEN
    utl_tcp.close_connection( c );
END check_port;
/

Easy enough. Now let's start use DBMS_JOB to "thread" this.

DECLARE
  TYPE r_record IS TABLE OF OPEN_PORTS.IP%TYPE INDEX BY BINARY_INTEGER;
  l_table R_RECORD;
  job_count PLS_INTEGER;
  job_number PLS_INTEGER;
BEGIN
  l_table(1) := '192.168.1.102';
  
  FOR i IN 1..l_table.COUNT LOOP
    FOR j IN 1..10000 LOOP
      SELECT COUNT(*)
      INTO job_count
      FROM user_jobs;

      WHILE job_count < 20 LOOP
      
        dbms_job.submit
          ( job => job_number,
            what => 'BEGIN check_port( ''' || l_table(i) || ''',' || j || '); COMMIT; END;' );

        COMMIT;
      END LOOP;
    END LOOP;    
  END LOOP;
END;
/

So it's running, and running, and running...I opened up another session and did a count on user_jobs. 10000. 12000. 20000. Uh, I think there's something wrong here.

I finally kill the session and there are 36,446 jobs in the queue. All checking the same port.

Then I can't even issue a simple SELECT statement. Maximum number of sessions reached or some such rubbish. Just a reminder, it was Saturday night and I was just trying to see if I could do this quickly.

Since I couldn't connect, I tried bouncing the database (a loud gasp from the DBAs out there). Come on! It's a local XE instance...there's nothing important there.

Start it back up, connect as SYS and still can't issue any queries. I do notice as I hit the / sign and the Enter key a bunch of times that once in a while it will let me do a query. So I create a quick PL/SQL block to remove all the jobs from the queue.

BEGIN
  FOR i IN ( SELECT job FROM user_jobs ) LOOP
    dbms_job.remove( i.job );
  END LOOP;
END;
/

I go through the / + Enter key about 50 times until it finally runs. Success!

Or so I think. Still receiving the max sessions error. There are still 36,446 jobs in user_jobs. I can't remove them anymore as I get "job doesn't exist" when I try to dbms_job.remove it again. Hmmm...How about?

DELETE FROM dba_jobs;

36,446 rows deleted.

What?

How can that be? I didn't expect that to work at all...but it did. I have yet to look up the particulars of my actions (deleting from dba_jobs) but it did the job.

And yes, I'm still planning on writing this (correctly) in the near future...I want more movies!

Labels: dbms_job, funny, humility, utl_tcp, wtf

  ¶ 11/18/2008 10:12:00 PM   1 Comments Links to this post