db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <mse...@segel.com>
Subject Re: Why projects fail... wuz... Re: unable to execute procedure
Date Wed, 05 Apr 2006 18:41:02 GMT
On Wednesday 05 April 2006 12:33 pm, Daniel John Debrunner wrote:
> Michael Segel wrote:
> > 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....
>
> Could you expand on what is "not exactly true"?
>
Sure.

Your said "Another reason to use PreparedStatements is to avoid all the 
security concerns that come with SQL injection issues." as a benefit of a 
prepared statement over a regular statement.

This is not 100% true.  You're implying that using a Statement has some stigma 
of security concerns. You can effectively use a Statement in a manner that 
does not create a potential of SQL injection issues. Thus no benefit.

In addition, its possible to create a SQL injection that passes in an SQL 
string, and an array[] of values. ("SELECT * FROM foo where id =? and 
DATE>?", "15", "12/10/2004") as an example.  I can then create a prepared 
statement and then set the variables and execute it. Is this good code? 
Clearly not! But that doesn't mean that it isn't possible. (See below...)

The point being that SQL Injection is more of an overall design issue and has 
little to do with the actual java class structure itself.

> > 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.
>
> Any time you build the text of a SQL statement from user supplied values
> you run the risk that a rogue parameter value could change the intended
> behaviour of the SQL statement. This is simply not an issue with
> PreparedStatements and parameter markers. That in my mind makes it a
> factor in deciding what to use.
>
Sure, there is no argument there. This is why you build business logic to 
validate the user input before using it.

However, this is true for the PreparedStatement as well. You're still taking 
user input.

Again looking at the code :

String s1 = "SELECT * FROM employee WHERE emp_id = "+empID+";";
String s2 = "SELECT * FROM employee WHERE emp_id = ?";

Now in both statements, you are passing in the emp_id, presumably from the end 
user's input.

The difference is that in using S1

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(s1);

vs

PreparedStatement pstmt = con.preparedStatement(s2);
pstmt.setInt(1,empID);
ResultSet rs = pstmt.executeQuery();

Then the rest of the code is the same.

Now... Note that there is no chance of SQL Injection, hence your argument of 
an advantage of using PreparedStatement is moot.

> http://www.governmentsecurity.org/articles/SQLInjectionModesofAttackDefence
>andWhyItMatters.php
>
> Dan.

Thats nice, but here's a method that blows your point away...

    public void fubar(String stmt, String[] values){
        // Assume that you already have established a connection.
       try{
       PreparedStatement pstmt = con.preparedStatement(stmt);
       for(int i=0; i<values.length; i++){
            pstmt.setString((i+1), values[i]);
       }
       ResultSet rs = pstmt.executeQuery(); 
       }catch(Exception e){
           System.exit(-1);
      }
   }

And voila! A birth of a bad idea. ;-)
But it goes to show you that the issue is not a matter of the java construct, 
just the improper use of it in a bad design.


Mime
View raw message