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.