Another horrific Java gem



  •   /** 
       * Executes a sql string passed by the calling method
       * Basically, bypasses the Object Oriented way of doing business and
       * allows direct database queries. Only put in for outside process who
       * decide to access the database without using <SYSTEM> objects. Only allows select
       * statements to be executed except for update of <OTHER organization> tables.
       * @return Collection containing the ResultSet
       * of the query and the Connection so that
       * it may be returned when the ResultSet has been parsed
       * @param sql String containing the SQL statement to be executed
       * @throws SQLException passes the SQLException though if one occurs
       * or will throw one if an attempt to insert or update an inappropriate 
       * table is made
       * @throws OutOfConnectionsException If all the connections in the Pooler are currently
       * in use.
       */
    
       public Collection executeSQLString(String sql) throws SQLException, OutOfConnectionsException {
         boolean gotCon = false;
         Collection rtn = new ArrayList();
         Connection conn = null;
         ResultSet rset = null;
         Statement stmt = null;
    
         if (!(sql.toUpperCase().startsWith("SELECT") ||
               sql.toUpperCase().startsWith("INSERT INTO <OTHER TABLE ORG>") ||
               sql.toUpperCase().startsWith("UPDATE <OTHER TABLE ORG>") ||
               sql.toUpperCase().startsWith("DELETE FROM <OTHER TABLE ORG>") ||
               sql.toUpperCase().startsWith("INSERT INTO <SCHEMA>" + getSchemaId() + ".<OTHER TABLE ORG>") ||
               sql.toUpperCase().startsWith("UPDATE <SCHEMA>" + getSchemaId() + ".<OTHER TABLE ORG>") || 
               sql.toUpperCase().startsWith("DELETE FROM <SCHEMA>" + getSchemaId() + ".<OTHER TABLE ORG>") ) {
           throw new SQLException("String passed must begin with 'SELECT' or you can only update or insert into the <OTHER ORG> Tables.");
         }
    
         while(!gotCon && !shutdown) {
           try {
             conn = pool.getConnection();
             gotCon = true;
           } catch (OutOfConnectionsException e) {
             System.err.println("No available connections... trying again...");
           } catch (Exception e) {
             handleUnanticipatedOccurance("DB_DOWN", e);
             return null;
           }
         }
    
         stmt = conn.createStatement();
         stmt.setMaxRows(MAX_ROWS);
         stmt.setFetchSize(FETCH_SIZE);
    
         if (sql.toUpperCase().startsWith("SELECT") {
           rset = stmt.executeQuery(sql);
         } else {
           stmt.executeUpdate(sql);
           conn.commit();
         }
    
         rtn.add(rset);
         rtn.add(conn);
         rtn.add(stmt);
    
         return rtn;
       }
    

    Typical usage example:

    List results = (ArrayList) executeSQLString("select first_name, last_name, postal_code from users");
    

    ResultSet rs = (ResultSet) results.get(0);
    Connection conn = (Connection) results.get(1);
    Statement stmt = (Statement) results.get(2);

    .
    .
    .

    rs.close();
    stmt.close();
    conn.close();

    Whew! Thank goodness for the Collections API ... how else would we return three values to the caller? o.0  (And, yes, they used "Collection" and cast it to "ArrayList" ... really.)



  • Haha, pretty bad. Might be front page worthy, but others probably know that better than I do.



  • Is the function really named "handleUnanticipatedOccurance"? Because that's awesome.



  • @blakeyrat said:

    Is the function really named "handleUnanticipatedOccurance"? Because that's awesome.

    Yeah, I think the OP explained its existence before in another thread. I'm pretty sure it just prints it to std err or something.



  • @Power Troll said:

    @blakeyrat said:
    Is the function really named "handleUnanticipatedOccurance"? Because that's awesome.
    Yeah, I think the OP explained its existence before in another thread. I'm pretty sure it just prints it to std err or something.

    Yeah, from the same folk(s) who brought you http://forums.thedailywtf.com/forums/t/24894.aspx


  • Discourse touched me in a no-no place

    TRWTF is not going for "what I tell you three times is true," right?

    sql.toUpperCase().startsWith("INSERT INTO ") ||

    sql.toUpperCase().startsWith("INSERT INTO " + getSchemaId() + ".") ||



    sql.toUpperCase().startsWith("UPDATE ") ||

    sql.toUpperCase().startsWith("UPDATE " + getSchemaId() + ".") ||



    sql.toUpperCase().startsWith("DELETE FROM ") ||

    sql.toUpperCase().startsWith("DELETE FROM " + getSchemaId() + ".") ) {



  • I don't even know Java and already see lots of things that are wrong with it. I particularly like their way of getting a connection from the pool.



  • @blakeyrat said:

    Is the function really named "handleUnanticipatedOccurance"? Because that's awesome.
    I agree(btw, you'll want to click that link to see its WTFish implementation)

    But this is awesomer:

    while(!gotCon && !shutdown) {
           try {
             // Try to get a connection
           } catch (OutOfConnectionsException e) {
             // Don't give up! You'll do better next time!
           }
    }
    


  •  Can the SQL passed in be semi-colon delimited? For example, "SELECT * FROM users WHERE 1=0; DROP TABLE users;"?



  • @Schlagwerk said:

     Can the SQL passed in be semi-colon delimited? For example, "SELECT * FROM users WHERE 1=0; DROP TABLE users;"?

    TRWTF for me is that this method disallows "INSERT INTO" and "DELETE INTO" but will happily run "INSERT table_name" and "DELETE table_name" just fine.



  • @The_Assimilator said:

    TRWTF for me is that this method disallows "INSERT INTO" and "DELETE INTO" but will happily run "INSERT table_name" and "DELETE table_name" just fine.

    Well, I'm perfectly happy with disallowing "DELETE INTO". However, I don't think you've read the OP correctly. Queries are rejected unless they start with one of SELECT, INSERT INTO, UPDATE, DELETE FROM. SELECT statements are passed to stmt.executeQuery(sql) and all other statements are passed to stmt.executeUpdate(sql).



  • oops nothing to see here ...



  • Oh, crap:

    private Connection getConnection() {
      Connection rtn = null;
    

    while(rtn == null) {
    try {
    rtn = super.getConnection();
    } catch (Exception e) {
    e.printStackTrace();
    break;
    }
    }

    return rtn;
    }

    I mean ... seriously?



  • @derula said:

    I don't even know Java and already see lots of things that are wrong with it. I particularly like their way of getting a connection from the pool.

    Not knowing Java usually helps bashing it. And the way he gets connections has nothing to do with Java.



  • @veggen said:

    @derula said:
    I don't even know Java and already see lots of things that are wrong with it. I particularly like their way of getting a connection from the pool.
    Not knowing Java usually helps bashing it. And the way he gets connections has nothing to do with Java.
    I think you got that completely wrong. The point isn't that Java sux (which derula may or may not believe), but that the code is so wrong, it's obvious even if you don't know the language.



  • @veggen said:

    @derula said:
    I don't even know Java and already see lots of things that are wrong with it. I particularly like their way of getting a connection from the pool.

    Not knowing Java usually helps bashing it. And the way he gets connections has nothing to do with Java.

    As a person who doesn't know Java, is that code necessary? Connection pooling in .net is automagic.



  • @blakeyrat said:

    @veggen said:
    @derula said:
    I don't even know Java and already see lots of things that are wrong with it. I particularly like their way of getting a connection from the pool.
    Not knowing Java usually helps bashing it. And the way he gets connections has nothing to do with Java.
    As a person who doesn't know Java, is that code necessary? Connection pooling in .net is automagic.

    I may misunderstand this, but not really. There are hooks in the language for driver implementors to make connection pools available. This is true as of v1.6; I have yet to look at 1.7.

    Remember, however, that this code that I'm spelunking through was developed prior to and during the Java 1.1/1.2 days. Which in terms of the Internet, is really friggin' old. The guys who built this, built their own pooling (or copied/gleaned it from somewhere else and gave it their own spin.) The Oracle JDBC driver of the day didn't support connection pools.

     


Log in to reply