Re: Why projects fail... wuz... Re: unable to execute procedure
Michael Segel wrote: On Wednesday 05 April 2006 2:16 pm, Daniel John Debrunner wrote: Michael Segel wrote: 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. Any time you build the SQL text from user input you run the risk of SQL injection, your example below even shows that. I was assuming the typical model with PreparedStatements that the SQL text is fixed by the application and parameter subsitution is performed using parameter markers and not modifying the SQL text. Ok, Look at it this way. You're going to be using user input if you use either a Prepared Statement or just a regular statement. If you don't bother to perform business logic checks and validate that input, you're going to run in to trouble. Period. The point being is that a prepared statement offers no more inherit protection that just a regular statement. 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. Now... Note that there is no chance of SQL Injection, hence your argument of an advantage of using PreparedStatement is moot. With the statement case there is every chance of SQL injection. If empID is a String (or becomes a String later when someone modifies the application) then SQL injection is possible. With the PreparedStatement it isn't. Really? So I prepare a statement and I say pstmt.setString(n, empID) and its going to automatically validate the input? I really don't think so. setString public void setString(int parameterIndex, String x) throws SQLException Sets the designated parameter to the given Java String value. The driver converts this to an SQL VARCHAR or LONGVARCHAR value (depending on the argument's size relative to the driver's limits on VARCHAR values) when it sends it to the database. Parameters: parameterIndex - the first parameter is 1, the second is 2, ... x - the parameter value Throws: SQLException - if a database access error occurs Nothing here about validating the data. I agree no validation of the data will be performed when using a prepared statement, but since the input is/should be handled correctly by the driver, you won't be able to use the comment trick with a prepared statement, right? So I agree with Dan that a prepared statement removes *some* of the common SQL injection tricks, and thus can be seen to be more secure than using a regular statement. But, you are absolutely correct in claiming that the input data should be validated by the application before being passed to the database! Just to illustrate, I wrote a small cow database, storing owner, cowid and milkrating. Consider the following statements: PreparedStatement: "SELECT owner, cowid FROM herd WHERE cowrating LIKE ? AND owner LIKE 'JoeFarmer'", followed by a pStmt.setString(1, args[0]) Statement: "SELECT owner, cowid FROM herd WHERE cowrating LIKE '" + args[0] + "' AND owner LIKE 'JoeFarmer'" For the common comment trick, I am not able to get a list over the cows for other farmers than JoeFarmer with the prepared statement. A few examples: java CowTest "good" Input: good --- Using PreparedStatement Owner: JoeFarmer, cowid: 3 Owner: JoeFarmer, cowid: 4 --- Using Statement Owner: JoeFarmer, cowid: 3 Owner: JoeFarmer, cowid: 4 java -classpath CowTest "good' --" Input: good' -- --- Using PreparedStatement --- Using Statement Owner: JoeFarmer, cowid: 3 Owner: SecretFarmer, cowid: 5 Owner: JoeFarmer, cowid: 4 java CowTest "good' OR milkrating LIKE 'bad" Input: good' OR milkrating LIKE 'bad --- Using PreparedStatement --- Using Statement Owner: JoeFarmer, cowid: 1 Owner: JoeFarm
Re: Why projects fail... wuz... Re: unable to execute procedure
Michael Segel wrote: > On Wednesday 05 April 2006 2:16 pm, Daniel John Debrunner wrote: > >>Michael Segel wrote: >>>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. >>> >>> >>> >>>Now... Note that there is no chance of SQL Injection, hence your argument >>>of an advantage of using PreparedStatement is moot. >> >>With the statement case there is every chance of SQL injection. If empID >>is a String (or becomes a String later when someone modifies the >>application) then SQL injection is possible. With the PreparedStatement >>it isn't. >> > > Really? > So I prepare a statement and I say pstmt.setString(n, empID) and its going to > automatically validate the input? Of course setString isn't going to validate the input. But no matter what value you set using setString it can't change the plan of the SQL statement when it's executed using a PreparedStatement. On the other hand modifying the text of the SQL statement with the value of a parameter can change the behaviour of the SQL statement. To make it clear, if the user supplies empID as a String with the value 13 OR TRUE then with the Statement approach the intent of the statement has been changed from return a single row to return all the rows. Thus allowing potentially someone to find out all employees. If the same string is set using PreparedStatement.setString() then some error will occur. Much more secure than leaking all employees. It's a fail safe system. Of course this is a simple SQL statement with a simple example, but SQL injection is a real problem. I'm not saying that PreparedStatement is the only solution, but the established pattern of a fixed SQL string and parameters set using the setXXX calls removes a whole set of potential SQL injections. Dan.
Re: Why projects fail... wuz... Re: unable to execute procedure
On Wednesday 05 April 2006 2:16 pm, Daniel John Debrunner wrote: > Michael Segel wrote: > > 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. > > Any time you build the SQL text from user input you run the risk of SQL > injection, your example below even shows that. I was assuming the > typical model with PreparedStatements that the SQL text is fixed by the > application and parameter subsitution is performed using parameter > markers and not modifying the SQL text. > Ok, Look at it this way. You're going to be using user input if you use either a Prepared Statement or just a regular statement. If you don't bother to perform business logic checks and validate that input, you're going to run in to trouble. Period. The point being is that a prepared statement offers no more inherit protection that just a regular statement. > > 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. > > > > > > > > Now... Note that there is no chance of SQL Injection, hence your argument > > of an advantage of using PreparedStatement is moot. > > With the statement case there is every chance of SQL injection. If empID > is a String (or becomes a String later when someone modifies the > application) then SQL injection is possible. With the PreparedStatement > it isn't. > Really? So I prepare a statement and I say pstmt.setString(n, empID) and its going to automatically validate the input? I really don't think so. setString public void setString(int parameterIndex, String x) throws SQLException Sets the designated parameter to the given Java String value. The driver converts this to an SQL VARCHAR or LONGVARCHAR value (depending on the argument's size relative to the driver's limits on VARCHAR values) when it sends it to the database. Parameters: parameterIndex - the first parameter is 1, the second is 2, ... x - the parameter value Throws: SQLException - if a database access error occurs Nothing here about validating the data. > Dan. -- -- Michael Segel Principal Michael Segel Consulting Corp. [EMAIL PROTECTED] (312) 952-8175 [mobile]
Re: Why projects fail... wuz... Re: unable to execute procedure
Michael Segel wrote: > 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. Any time you build the SQL text from user input you run the risk of SQL injection, your example below even shows that. I was assuming the typical model with PreparedStatements that the SQL text is fixed by the application and parameter subsitution is performed using parameter markers and not modifying the SQL text. > 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. > > > Now... Note that there is no chance of SQL Injection, hence your argument of > an advantage of using PreparedStatement is moot. With the statement case there is every chance of SQL injection. If empID is a String (or becomes a String later when someone modifies the application) then SQL injection is possible. With the PreparedStatement it isn't. Dan.
Re: Why projects fail... wuz... Re: unable to execute procedure
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
Re: Why projects fail... wuz... Re: unable to execute procedure
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"? > 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. http://www.governmentsecurity.org/articles/SQLInjectionModesofAttackDefenceandWhyItMatters.php Dan.
Re: Why projects fail... wuz... Re: unable to execute procedure
[SNIP] I could be wrong, and please correct me if I am... But I thought a PreparedStatement just precompiled the statement on the database when the statement is prepared. How is that any more overhead than just using a normal Statement? When you use a normal statement and you execute the statement, it still has to be compiled on the database. So, either way, the statement is being compiled, it's just a matter of when. djm > 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. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
