Oracle Java SQL Exception Error: ORA-0094

Question

I am trying to write a function for this button. I want to be able to pass it a textfield value and be able to go into my database to retrieve some information.....

Can somebody explain to me what is going on and provide me a solution to this madness?

Thank you all xD

I keep running into this stupid problem:

 ACTION1 createdoracle.jdbc.driver.T4CConnection@484845aa

 Exception:java.sql.SQLSyntaxErrorException: ORA-00904: "ART": invalid identifier

Code:

 private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
    // TODO add your handling code here:
    //CLASS TYPE 
    //LIST ALL OFFERED CLASSES AND REVENUE


    try{
        String classtype = jTextField1.getText().trim();
        if(classtype.equals("")){
            JOptionPane.showMessageDialog(this, "Sorry Wrong input.... Please try again....");
        }
        else if(classtype != ""){
            try
            {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    Connection conn=DriverManager.getConnection(
                    "jdbc:oracle:thin:@fourier.cs.iit.edu:1521:orcl",
                    "usr","pwd");
                    Statement stmt = conn.createStatement();
                    System.out.println("ACTION1 created"+conn+"\n\n");


                    String ct = jTextField1.getText().trim();
                    //String aa = "SELECT * FROM CLASS WHERE TYPE="+classtype;
                    //System.out.println(aa);

                    ResultSet rset = stmt.executeQuery("SELECT * FROM CLASS WHERE TYPE="+ct);
                    while (rset.next()) { 
                        System.out.println(rset.getString("TITLE") + " ");
                    }

                    JOptionPane.showMessageDialog(this, "Class Type: "+classtype);


                    stmt.close();
                    conn.close();
                    System.out.println("Connection Closed");
            }
            catch(Exception sqle){
                    System.out.println("\nException:"+sqle);
            }
        }
    }
    catch(Exception e){
        JOptionPane.showMessageDialog(this, "Please Retry input....", "Error", JOptionPane.ERROR_MESSAGE);
    }

}

You add the content of the textfield to you SQL String (ABSOLUTE NOGO! SQL injection vulnerability!), so what is the content of ct when the exception is thrown? — Andreas Dolk
– Andreas Dolk, Commented Mar 1, 2012 at 9:04
as @Andreas_D said you're not using the prepared statement right. But anyway if you concatenate a string it needs to be within two ', ie- "SELECT * FROM CLASS WHERE TYPE='"+ct+"'" (but better solution is to add params to the prepared statement — A.B.Cade
– A.B.Cade, Commented Mar 1, 2012 at 9:14

Stephen C · Accepted Answer · 2012-03-01 09:13:17Z

3

Let me guess ... does the ct String start with "ART" (or some variation)?

If so, the problem is that SQL requires quotes around string literals. Your query probably looks to Oracle something like this:

    SELECT * FROM CLASS WHERE TYPE=Art of War

but it should look like

    SELECT * FROM CLASS WHERE TYPE='Art of War'

There are two ways to fix this:

Assemble the query with quote characters around ct.
Write the query as "SELECT * FROM CLASS WHERE TYPE=?", use a PreparedStatement instead of a Statement and use the setString method to supply the parameter value.

If done properly, the second approach is both more secure and more efficient. (The problem with string-bashing the query and using Statement is that you are potentially making yourself vulnerable to SQL injection attacks.)

answered Mar 1, 2012 at 9:13

Stephen C

723k95 gold badges849 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2012-03-01 09:15:39Z

2

You're passing the value as part of the query, and the string concatenation you're doing makes the SQL into:

SELECT * FROM CLASS WHERE TYPE=ART

(where ART is the value of ct from the textfield) so it's trying to find a column on the table called ART. At an absolute minimum you need to quote the string:

ResultSet rset = stmt.executeQuery("SELECT * FROM CLASS WHERE TYPE='" + ct + "'");

But really don't do this; as @Andreas_D says you're leaving yourself open to SQL injection. Always use prepared statements and bind variables:

String sql = "SELECT * FROM CLASS WHERE TYPE=?";
PrepareStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, ct);
ResultSet rset = stmt.executeQuery();

answered Mar 1, 2012 at 9:15

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Collectives™ on Stack Overflow

Oracle Java SQL Exception Error: ORA-0094

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related