?
Solved

Return results from Oracle DB to command line using java

Posted on 2010-11-16
9
Medium Priority
?
543 Views
Last Modified: 2013-11-23
OK, so here's the problem:
Create a DB with transparent encryption - check.
populate it with "sensitive info" - check.
write a java program to access the db and return a query of said sensitive info - half check...

I've got the program.  I'm sure it can be done better (i'm working at my ability level as you can see).  I have hit a roadblock when it comes to printing out the results.  I've tested each sql statement (including the one that's an sqlj token) and they work in sqlplus and sqlDeveloper.  What the program is not doing however, is printing the results.  It runs up to the while loop and ends.  No printout whatsoever.  Line 86 is the last line printed than it returns to a prompt.  

Apart from developing th rest of the code to prevent sql injection and to also obscure the password entry input, I have no idea what to do as everything looks ok to me.  Would someone be so kind as to look over my work and tell me where I made a wrong turn (even if it's the whole thing)?

Thanks


 
/*
 * Glen Currier
 * Homework assignment 9
 * Prof. Jimmy Robertson
 * gcurrierhw9.sqlj
*/

import java.sql.SQLException;
import java.io.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.Scanner;
import oracle.sqlj.runtime.Oracle;

public class gcurrierhw9 {
    public static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
    public static final String URL = "jdbc:oracle:thin:@localhost:1521:academic";
    public static final String username = "cmis420";
    public static final String password = "cmis420";
    Connection con = null;
    ResultSet rs = null;
    Statement stmt = null;
    
    
    public static void main(String args[]) throws SQLException,ClassNotFoundException,
                            IOException {
        gcurrierhw9 myhw9 = new gcurrierhw9();
        myhw9.dbSYSLogin();
    }
    
    void  dbSYSLogin () throws SQLException, ClassNotFoundException,
                             IOException {
        gcurrierhw9 myhw9 = new gcurrierhw9();  
        try{
             Oracle.connect(gcurrierhw9.class, "connect.properties");
            
            #sql{alter system set wallet open identified by "Slap_U_S1lly"};
        }
        catch(SQLException e){
            System.err.println("Error while altering wallet"+e);
        }
        finally {              
            try{
                Oracle.close(); 
                myhw9.dbUserLogin();
            }
            catch (SQLException e){
                System.out.println("Error Closing first connection to the Database: " + e);
            }
        }
    }
    
    private void dbUserLogin() throws SQLException, ClassNotFoundException, IOException {
        boolean readOnly = true;
        try{
            Class.forName(DRIVER);
            con = DriverManager.getConnection(URL,username,password);
            try{
                con.setReadOnly(readOnly);
                stmt = con.createStatement();
                readOnly = con.isReadOnly();
               
                try{
                    if (!readOnly){
                        System.out.println("DB Connection is not read only. Terminating program.");
                        con.close();
                        
                    }
                        else if(readOnly){
                            Scanner input = new Scanner(System.in);
                            System.out.print("Please enter your username and password.\n");
                            System.out.println("User name :");
                            String uname = input.next();
                            System.out.println("Password :");
                            String pword = input.next();
                            String[] info={uname,pword};
                            //System.out.println(info[0]+" "+info[1]);
                            
                            rs = stmt.executeQuery("SELECT username,password,chargecardnumber" +
                                " FROM studentaccounts" +
                                " WHERE username=' "+info[0]+" ' AND password=' "+info[1]+" ' ");
                            System.out.println("Your Personal info:\n" +
                                                   "-------------------------------------");
                            while(rs.next()){
                                System.out.println("User Name:   " + rs.getString(1)+"\n");
                                System.out.println("Password:      " + rs.getString(2)+"\n");
                                System.out.println("Card Number:" + rs.getString(3));
                                
                            }
                            input.close();
                        }
                   
                }
                catch (Exception e){
                    System.out.println("Error in dbUserLogin Method: "+e);
                }
            }
            catch(Exception e){
                System.out.println(e);
            }
            stmt.close();
            rs.close();
            con.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
        stmt = null;
        rs = null;
        con = null;

    }
}

Open in new window

0
Comment
Question by:g_currier
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 92

Expert Comment

by:objects
ID: 34150437
>     public static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
>     public static final String URL = "jdbc:oracle:thin:@localhost:1521:academic";

driver does not match the connection string
0
 

Author Comment

by:g_currier
ID: 34150880
You are right about the driver but it still does the same thing.  I altered the code to obscure the password input with console.readPassword.  THAT result makes me enter the password twice before it continues and then doesn't display query reslts...

/*
 * Glen Currier
 * Homework assignment 9
 * Prof. Jimmy Robertson
 * gcurrierhw9.sqlj
*/

import java.sql.SQLException;
import java.io.*;
import java.io.Console;
import java.util.Arrays;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.Scanner;
import java.util.logging.ConsoleHandler;

import oracle.sqlj.runtime.Oracle;

public class gcurrierhw9 {
    public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    public static final String URL = "jdbc:oracle:thin:@localhost:1521:academic";
    public static final String username = "cmis420";
    public static final String password = "cmis420";
    Connection con = null;
    ResultSet rs = null;
    Statement stmt = null;
    
    
    public static void main(String args[]) throws SQLException,ClassNotFoundException,
                            IOException {
        gcurrierhw9 myhw9 = new gcurrierhw9();
        myhw9.dbSYSLogin();
    }
    
    void  dbSYSLogin () throws SQLException, ClassNotFoundException,
                             IOException {
        gcurrierhw9 myhw9 = new gcurrierhw9();  
        try{
             Oracle.connect(gcurrierhw9.class, "connect.properties");
            
            #sql{alter system set wallet open identified by "Slap_U_S1lly"};
        }
        catch(SQLException e){
            System.err.println("Error while altering wallet"+e);
        }
        finally {              
            try{
                Oracle.close(); 
                myhw9.dbUserLogin();
            }
            catch (SQLException e){
                System.out.println("Error Closing first connection to the Database: " + e);
            }
        }
    }
    
    private void dbUserLogin() throws SQLException, ClassNotFoundException, IOException {
        boolean readOnly = true;
        try{
            Class.forName(DRIVER);
            con = DriverManager.getConnection(URL,username,password);
           
                        
            try{
                con.setReadOnly(readOnly);
                stmt = con.createStatement();
                readOnly = con.isReadOnly();
               
                try{
                    if (!readOnly){
                        System.out.println("DB Connection is not read only. Terminating program.");
                        con.close();
                     }
                        else if(readOnly){
                            System.out.print("Please enter your username and password.\n");
                            Console console = System.console();
                            String uname = console.readLine("User Name? ");
                            char[] pword = console.readPassword("Password? ");
                            try{
                                if ((console.readLine()) != null && (console.readPassword()) != null) {
                                    Arrays.fill(pword, ' ');
                                    rs = stmt.executeQuery("SELECT username,password,chargecardnumber" +
                                                           " FROM studentaccounts" +
                                                           " WHERE username=' "+uname+" ' AND password=' "+pword+" ' ");
                                    System.out.println("Your Personal info:\n" +
                                                       "-------------------------------------");
                                    while(rs.next()){
                                        System.out.println("User Name:   " + rs.getString(1)+"\n");
                                        System.out.println("Password:      " + rs.getString(2)+"\n");
                                        System.out.println("Card Number:" + rs.getString(3));
                                        }    
                                }
                            }
                            catch(Exception e){
                                System.out.println(e);
                            }
                        }
                   
                }
                catch (Exception e){
                    System.out.println("Error in dbUserLogin Method: "+e);
                }
            }
            catch(Exception e){
                System.out.println(e);
            }
            stmt.close();
            rs.close();
            con.close();
        }
        catch(Exception e){
            System.out.println(e);
        }
        stmt = null;
        rs = null;
        con = null;

    }
}

Open in new window

0
 
LVL 92

Expert Comment

by:objects
ID: 34150994
>                                                            " WHERE username=' "+uname+" ' AND password=' "+pword+" ' ");

if you're using a character array for password then that also needs to change to


                                                           " WHERE username=' "+uname+" ' AND password=' "+new String(pword)+" ' ");


(And will only find a record if one exists with a blank password)
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:g_currier
ID: 34151054
This is nuts...what you're saying makes sense once I see it, but it is simply not wanting to work.  I get the same result:  Enter the password once, twice (nothing) and on the third it prints the title line.  I am going to try a DSN to the local DB (that connection I KNOW works).
 
try{
                con.setReadOnly(readOnly);
                stmt = con.createStatement();
                readOnly = con.isReadOnly();

               
                try{
                    if (!readOnly){
                        System.out.println("DB Connection is non-existent. Terminating program.");
                        con.close();
                     }
                        else if(readOnly){
                            System.out.print("Please enter your username and password.\n");
                            Console console = System.console();
                            String uname = console.readLine("User Name? ");
                            char[] pword = console.readPassword("Password? ");
                            try{
                                if ((console.readLine()) != null && (console.readPassword()) != null) {
                                    Arrays.fill(pword, ' ');
                                    rs = stmt.executeQuery("SELECT username,password,chargecardnumber" +
                                                           " FROM studentaccounts" +
                                                           " WHERE username=' "+uname+" ' AND password=' "+new String(pword)+" ' ");
//                                  " WHERE username='jsmith ' AND password='ABC123** ' ");
                                    System.out.println("Your Personal info:\n" +
                                                       "-------------------------------------");
                                    while(rs.next()){
                                        System.out.println("User Name:   " + rs.getString(1)+"\n");
                                        System.out.println("Password:      " + rs.getString(2)+"\n");
                                        System.out.println("Card Number:" + rs.getString(3));
                                        }    
                                }
                            }
                            catch(Exception e){
                                System.out.println(e);
                           }
                        }
                   
                }
                catch (Exception e){
                    System.out.println("Error in dbUserLogin Method: "+e);
                }

Open in new window

0
 
LVL 92

Accepted Solution

by:
objects earned 1600 total points
ID: 34151083
yes, make sure you are hitting the right database

and remove the where from your query as a test to see all the rows returned
0
 

Author Comment

by:g_currier
ID: 34151121
I think I just saw two parts to the problem.  First the DSN connection worked, which means my oracle drivers aren't where my machine believes them to be (env vars).  No matter, i can run with the DSN connection for now.
THe second is this error I get after the db accepts the three password inputs (for each result set presumably):
java.sql.SQLException: ResultSet is closed

...after the title line with no line number to indicate.
0
 

Author Comment

by:g_currier
ID: 34151146
returns all rows without the user name and password (or rather, with, but just as junk data to get past the conditions.

http://www.screencast.com/users/gcurrier/folders/Jing/media/31ace149-ff75-423d-9478-32de434fcd18
0
 
LVL 92

Expert Comment

by:objects
ID: 34151200
                           char[] pword = console.readPassword("Password? ");
                            try{
                                if ((console.readLine()) != null && (console.readPassword()) != null) {


you call readPassword() twice
0
 

Author Comment

by:g_currier
ID: 34151263
This after making the changes:

http://www.screencast.com/users/gcurrier/folders/Jing/media/65fa423d-60ed-4917-af0e-b803f4ad7509
 I added a line to output the vars (to see what would be returned)  Pass word is only entered twice this time...
else if(readOnly){
                            System.out.print("Please enter your username and password.\n");
                            Console console = System.console();
                            String uname = console.readLine("User Name? ");
                            char[] pword = console.readPassword("Password? ");
                            try{
                                if ((console.readLine()) != null) {
                                    Arrays.fill(pword,' ');
                                    rs = stmt.executeQuery("SELECT username,password,chargecardnumber" +
                                                           " FROM studentaccounts");
                                    System.out.println(uname+" "+new String(pword));
                                                           //" WHERE username=' "+uname+" ' AND password=' "+new String(pword)+" ' ");
//                                  " WHERE username='jsmith ' AND password='ABC123** ' ");
                                    System.out.println("Your Personal info:\n" +
                                                       "-------------------------------------");
                                    while(rs.next()){
                                        System.out.println("User Name:   " + rs.getString(1)+"\n");
                                        System.out.println("Password:      " + rs.getString(2)+"\n");
                                        System.out.println("Card Number:" + rs.getString(3)+"\n");
                                        System.out.println("-------------------------------------");
                                        }
                                        
                                }
                            }
                            catch(Exception e){
                                System.out.println(e);
                           }

Open in new window

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month12 days, 6 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question