db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Why projects fail... wuz... Re: unable to execute procedure
Date Wed, 05 Apr 2006 23:11:22 GMT
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: JoeFarmer, cowid: 3
Owner: SecretFarmer, cowid: 5
Owner: JoeFarmer, cowid: 4

java CowTest "nonsense' OR TRUE --"
Input: nonsense' OR TRUE --
--- Using PreparedStatement
--- Using Statement
Owner: JoeFarmer, cowid: 1
Owner: JoeFarmer, cowid: 2
Owner: JoeFarmer, cowid: 3
Owner: SecretFarmer, cowid: 5
Owner: JoeFarmer, cowid: 4


I am aware of that my input should have been verified, but as can be 
observed, the query using the statement is a lot more "open for abuse" 
than when using a prepared statement and the setString method. You might 
need some knowledge about the database schema and such, but this can 
often be guessed or obtained in some way or another.

I'm not very experienced with SQL injection, so there might be ways to 
trick the application when using the prepared statement as well, but the 
most naive attempts are at least denied.



--
Kristian

>
>  
>
>>Dan.
>>    
>>
>
>  
>


Mime
View raw message