db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: Why projects fail... wuz... Re: unable to execute procedure
Date Wed, 05 Apr 2006 22:31:10 GMT
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.


Mime
View raw message