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.
[EMAIL PROTECTED]
(312) 952-8175 [mobile]
