Wednesday, May 30, 2007

The best reasons for using PreparedStatements are these:

(1) Executing the same query multiple times in loop, binding different parameter values each time, and
(2) Using the setDate()/setString() methods to escape dates and strings properly, in a database-independent way.
(3) SQL injection attacks on a system are virtually impossible when using Prepared Statements.

SQL injection:

Suppose your web application asks the user for their ID number. They type it into a box and click submit. This ends up calling the following method:

public List processUserID(String idNumber)
throws SQLException
{
String query = "SELECT role FROM roles WHERE id = '" + idNumber + "'";
ResultSet rs = this.connection.executeQuery(query);
// ... process results ...
}

If out of a sense of informed malice, your user enters the following text into the ID number field:

12345'; TRUNCATE role; SELECT '

They may be able to drop the contents of your role table, because the string that ends up in "query" will be:

SELECT role FROM roles WHERE id = '12345'; TRUNCATE role; SELECT ''

They have successfully injected SQL into your application that wasn't there before, hence the name. The specifics of this depend to some extent on your database, but there's some pretty portable SQL you can use to achieve this.

On the other hand, if you use a prepared statement:

public List processUserID(String idNumber)
throws SQLException
{
String query = "SELECT role FROM roles WHERE id = ?";
PreparedStatement statement = this.connection.prepare(query);
statement.setString(id,idNumber);

ResultSet rs = this.connection.executeQuery(query);
// ... process results ...
}
The database is told to compile the SQL in query first. The parameter is then submitted - so whatever you put into it will never get executed as SQL (well, ok, it's possible if you're passing it as a parameter to a stored proc, but it's very unlikely) - it will just return no matching records (because there won't be any users with id "12345'; TRUNCATE role; SELECT '"

Source>>http://forum.java.sun.com/thread.jspa?tstart=0&forumID=48&threadID=538747&trange=15

1 comment:

Anonymous said...

Thanks for writing this.