Expert: Brian Ngure - 4/16/2007

Question
QUESTION: hello Brian. i need some help. now i'm develop a monitoring system with java. i want make a filtering to user accessibility, where user will be block to enter the url that has been blocked by admin. the list of url that has been blocked will be save at database. so the system should be refer to database when user make a request, and filter the request either it valid or not. so i need some idea, how to make the query that will be filter user request? i got some idea here where we select database and compare user request with the database. if the url exist in database, so the user connection will be reset. but i dont know how to build the query. another one, how to make connection reset with java? i hope u can help me..this is my fully coding for my system. but i cant upload my database for your referrence.. :-)


-----------------------------------------------------------
SYSTEM CODING.....


import java.net.*;
import sun.net.NetworkServer;
import java.io.*;
import java.util.*;
import java.awt.*;


public class FTPMonitoring extends NetworkServer
{
   public static String name;
   public Socket clientsocket;
   public DataOutputStream outbound1;
   public BufferedReader inbound1;
   public BufferedReader inbound2;
   
   public static void main(String args[])
  {
     name="";
       int port=8080;
       try
       {
         port=Integer.parseInt(args[0]);
       }
       
       catch(Exception e){}
     
     try
     {
         FTPMonitoring es=new FTPMonitoring();
         es.startServer(port);
         System.out.println("FTP monitoring start at " +new Date().toString()+" with port "+ port);
       }

       catch(IOException e)
       {
         System.err.println("fail to start");
       }
   }
  
  public void serviceRequest() throws IOException
  {
      String test1="",urlstring="",req,req1="";
      int j=0;
      
      DB db=new DB();

      try
      {
         db.connect();
         DataInputStream msg =new DataInputStream(clientSocket.getInputStream());
         String test=msg.readLine();
         byte b[]=new byte[test.indexOf("HTTP/")];
       
         if(test.charAt(0)=='G')
        {
         test.getBytes(4,b.length,b,0);
         req="get";
         req1="GET ";
         }
       
         else
        {
         test.getBytes(5,b.length,b,0);
         req="post";
         req1="POST ";
         }
       
         int i=0,cnt=0;
       
         for(i=0;i<b.length-4;i++)
        {
         urlstring+=(char)b[i];
         }

         URL ur=new URL(urlstring);

         clientsocket= new Socket(InetAddress.getByName(ur.getHost()),21);
         System.out.println("New Connection");
         
         InetAddress addr = InetAddress.getByName(ur.getHost());
         InetAddress host = InetAddress.getLocalHost();
         
         outbound1=new DataOutputStream(clientsocket.getOutputStream());
       
         if(req.equals("post")==false)
        {
         outbound1.writeBytes(req1+ur.getFile()+"\r\n");
         
         while((test1=msg.readLine())!=null)
         {
         if(test1.equals("") || test1.equals(" ") || test1.equals("\r\n\r\n"))
         {
         outbound1.writeBytes("\r\n\r\n");
         
         System.out.println("\nRequest to  " + ur.getHost() +" has been sent\n");
         System.out.println("URL : " + ur.toString()+"\n");
         System.out.println("Site Name : "+ addr.getHostName()+"\n");
         System.out.println("Site Address : "+ addr.getHostAddress()+"\n");
         System.out.println("Client Name : "+ host.getHostName()+"\n");
         System.out.println("Client Address : "+ host.getHostAddress()+"\n");
         System.out.println("Protocol : "+ ur.getProtocol()+"\n");
         System.out.println("Port : "+ ur.getPort()+"\n");
         System.out.println("File name : " + ur.getFile()+"\n");
         System.out.println("Time : " + new Date().toString() + "\n");
         
         db.addData(ur.toString(),addr.getHostName(), addr.getHostAddress(), host.getHostName(), host.getHostAddress(), ur.getProtocol(), ur.getPort(), ur.getFile(), new Date().toString());

         break;
         }
         
         outbound1.writeBytes(test1+"\r\n");
         }
         
         int ch1;
         
         inbound1=new BufferedReader(new InputStreamReader(clientsocket.getInputStream()));
         
         while((ch1=inbound1.read())!=-1)
         {
         clientOutput.write(ch1);
         clientOutput.flush();
         }
         }
       
         else
        {
         outbound1.writeBytes(req1+ur.getFile()+"\r\n");
         
         while((test1=msg.readLine())!=null)
         {
         if(test1.equals("") || test1.equals(" ") || test1.equals("\r\n\r\n"))
         {
         outbound1.writeBytes("\r\n");
         int ch3;
         int total=msg.available();
         
         for(int l=0;l<total;l++)
         {
         ch3=msg.read();
         outbound1.write(ch3);
         outbound1.flush();
         }
         
         outbound1.writeBytes("\r\n");
         outbound1.flush();
         System.out.println("Get Request to Host " + ur.getHost() +" has been sent");
         break;
         
         }
         
         outbound1.writeBytes(test1+"\r\n");
         
         }
         
         inbound1=new BufferedReader(new InputStreamReader(clientsocket.getInputStream()));
         int fin;
         
         while((fin=inbound1.read())!=-1)
         {
         clientOutput.write(fin);
         clientOutput.flush();
         }
         }
         
         inbound1.close();
         outbound1.close();
         clientsocket.close();
     }
   
      catch(IOException e)
     {
        System.out.println(e);
         db.disconnect();
       }
  }
}



-----------------------------------------------------------
DATABASE CODING.....



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class DB
{
  
  protected Connection con;
  protected Statement stmt;
  protected ResultSet rs;   
  String query;
  
  public static void main (String [] args) throws Exception
  {
  }
     
  public void connect()
  {
     try
     {
        Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
        con = DriverManager.getConnection ("jdbc:odbc:ftp");
        stmt = con.createStatement ();
         
        System.out.println("Connection to database succesfully");
     }

     catch(Exception e)
     {
        System.out.println("Connection to database fail");
     }
  }
     
  
  public void addData(String url,String sitename, String siteaddress, String clientname, String clientaddress, String protocol, Integer port, String filename, String date)
  {
     try
     {          
        query = "insert into main(url,sitename,siteaddress,clientname,clientaddress,protocol,port,filename,date) values('"+url+"','"+sitename+"','"+siteaddress+"','"+clientname+"','"+clientaddress+"','"+protocol+"','"+port+"','"+filename+"','"+date+"')";
        stmt.executeUpdate(query);


        System.out.println("Data has been succesfully add to database");         
     }
     catch(Exception e)
     {
        System.out.println("error add data");
     }   
  }

  public void disconnect()
  {
     try
     {          
        con.close ();   
        System.out.println(" Connection has been disconnect");
     }

     catch(Exception e)
     {
        System.out.println("fail to disconnect");
     }
  }
}
ANSWER: Hi,

Try something like this to query for a url that is in the database:

boolean inDatabase = stmt.execute("SELECT URL FROM tablename WHERE URL = http://www.something.com");

If inDatabase is true, then the URL is in the database and you reset.

Now, what do you mean by reset? Do you mean disconnect? You could simply display a warning message and close the socket connection.


Brian

---------- FOLLOW-UP ----------

QUESTION: thanks for your help brian..but i think the query that u give for only one site name. how about if my database has a 10 list of site name? how can i make a comparison between user request with database?
ANSWER: Hi,

All site names will names will be stored in the table. The example query I gave you will check a given URL against the database.

You will have to modify the example statement e.g.

boolean inDatabase = stmt.execute("SELECT URL FROM tablename WHERE URL = ?);

String url = "http://www.something.com");// Get the URL from the entered URL that the user is trying to access
stmt.setString(1, url); // Use parameterised queries


Brian

---------- FOLLOW-UP ----------

QUESTION: i try to use the query that u given..but nothing happen. i dont know why..this is my coding that i made...


  public void checkData(String SiteName)
  {
     try
     {   
        boolean ftp = stmt.execute("SELECT word FROM keyword WHERE word = ?");
        
        
        String word = SiteName;
        stmt.setString(1, word);
        
        if(true)
        {
         System.out.println("The address has been blocked by administrator!!");
        }
        
        else
        {
         System.out.println("Connection succesfully");
        }
     }
     
     catch(Exception e)
     {
     }
  }


the other thing, when i use stmt.setString(1,word) this coding cant be run. there are a problem with the coding. where i should declare the setString variable?
the error like this..

cannot find symbol method setString(int,java.lang.String)

why this happen?


ANSWER: Hi,

You need to use prepared statements in your code (you should always try to use prepared statements). Try something like this:

public void checkData(String SiteName) {
   try {
       PreparedStatement stmt = con.prepareStatement("SELECT word FROM keyword WHERE word = ?");

       stmt.setString(1, SiteName); // Just use the parameter, no need to create a new String

       if(stmt.execute()) { // You can use stmt.execute directly
         System.out.println("The address has been blocked by administrator!!");
       }
       else {
         System.out.println("Connection succesfully");
       }
   }
   catch(Exception ex) {
   }
}


Brian

P.S.

In your code you should always clean up resouces, e.g set stms to null in a finally block.

---------- FOLLOW-UP ----------

QUESTION: i try build some code like this..

public void checkData(String SiteName)
  {
     try
     {
        query2 = "SELECT word FROM keyword WHERE word = ?;";
        PreparedStatement stmt = con.prepareStatement(query2);
        stmt.setString(1, SiteName);
        ResultSet rs = stmt.executeQuery();
        String word = rs.getString("word");
        while(rs.next())
        {
         String word = rs.getString("word");
         if (SiteName == word)
         {
         System.out.println("The address has been blocked by administrator!!\n");
         }
         
         else
         {
         System.out.println("Connection succesfully\n");
         }
        }
     }
     
     catch(Exception ex)
        {
        }
  }


but nothing happen..there are nothing output..i dont know why..
ANSWER: Hi,

The code you have posted should automatically throw an exception an not compile. You have declared "String word" twice.

Change the first statement from:

   String word = rs.getString("word");

to:

   String word = "";

And change the second one (inside the while loop) to:

   word = rs.getString("word");

Also, is the column in the database called "word". Make sure that it is. On Linux thye case matters.

Also, in the statement:

   query2 = "SELECT word FROM keyword WHERE word = ?;";

you can remove the semi-colon from the query. It is not needed. i.e.

   query2 = "SELECT word FROM keyword WHERE word = ?";

Make sure that the parameter "SiteName" is properly excaped or converted to a proper String.

Other than this you should get results.


Brian

---------- FOLLOW-UP ----------

QUESTION: hello brian..thanks a lot for your help..finally i can make the filterring. i can make the comparison between user request with the list of site name in database..thanks so much man..but i got new problem here, i cant disconnect the user connection. i try to use socket connection, but i dont really know how. it suppose to be when the filterring done, if the site is blocked, client socket be close. that what i cant do. i have try some code, but nothing happen. have you any idea?

Answer
Hi,

You should dispose of the PreparedStatement, ResultSet and if necessary the Connection by setting them to null. The garbage collector will then be able to clean up these resources.

Use Connection.close() to close the database.
Use Socket.close() to close the socket.


Brian