db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <de...@segel.com>
Subject Why projects fail... wuz... Re: unable to execute procedure
Date Wed, 05 Apr 2006 04:42:33 GMT
On Tuesday 04 April 2006 6:23 pm, Daniel John Debrunner wrote:
> Daniel John Debrunner wrote:
> > May I suggest that if you are writing samples for others to use that you
> > demonstrate use of parameter markers in PreparedStatements. This will
> > perfom better on Derby and all other relational database engines.
>
> Another reason to use PreparedStatements is to avoid all the security
> concerns that come with SQL injection issues.
>
[SNIP]

Uhm, I'm afraid this has nothing to do with the issue at hand, along with the 
fact that its not exactly true....

While its never a good idea to accept SQL statements from a web interface, it 
has nothing to do with the decision to use a Prepared Statement vs a regular 
Statement. 

Here's a better example. 
In the code below we have a head to head comparison of a prepared statement vs 
a regular statement. Note: This code was written on the fly so I'm sure I 
made a gramatical mistake or two...

Note that both foo() and bar() are functionally equivalent. That is, they both 
take the same input, and the output in the System.out.println() will be the 
same.

So... Daniel's "security" concern is moot and we can focus on the differences 
between the prepare statement and the statement classes. To show the benefit, 
I prepare the preparedStatement in a separate function. So that if the class 
is called multiple times its only prepared once.

Now, does this make sense?

Preparing a statement carries a bit more overhead than just creating a 
Statement. The benefit is that if you're going to use that prepared statement 
multiple times, you'll start to see cost savings and it becomes more 
efficient than a statement.

The point of this is to help people understand how to write efficient and 
*secure* code.  Why prepare a statement if its only going to be used once or 
only a handfull of times? 

Now here's the code...


public class Retz {
    // Class variables here...
    Connection con = null;
    PreparedStatement pstmt = null;

    public void Retz(){
        // Init background stuff
       getCon(); // Get the connection.
    }

    public void foo(int colID){
        // This is the method that uses a Statement
        String s = " SELECT * FROM herd WHERE cowID = \""+colID+"\"";
        try{
             Statement stmt = con.createStatement();
             stmt.executeQuery(s);
             rs.next();
             String cowBreed = rs.getString(breed);
             System.out.println("Cow #"+colID+" is of type :"+cowBreed);
        } catch( Exception e){
             e.printStackTrace();
        }
    }

   public void bar(int colID) {
       //  This is the method that uses a PreparedStatement

      if (pstmt == null){
           buildPStmt(); // Assume that it goes well...
      }
      
        try{
             
             pstmt.setInt(1,colID);
             pstmt.executeQuery();
             rs.next();
             String cowBreed = rs.getString(breed);
             System.out.println("Cow #"+colID+" is of type :"+cowBreed);
        } catch( Exception e){
             e.printStackTrace();
        }
   }

   private void buildPStmt(){
       String s = " SELECT * FROM herd WHERE cowID = ?";
       try{
            pstmt = con.preparedStatement(s);
       } catch (Exception e){
            e.printStackTrace();
       }
   }
   private void  getCon() {
      // Ok, you know how to build a connection ;-)
      try{
       Con = DriverManager.getConnection("Blah, blah, blah...");
      } catch (Exception e){
         e.printStackTrace();
      }
   }
   
   public  static void main(String argv[]){
   
      /*
       * Ok, so you know what to do....
       */
   }

}

-G
-- 
--
Michael Segel
Principal 
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]

Mime
View raw message