Get your CodeRanch badge!*
The moose likes JDBC and the fly likes PreparedStatement v/s Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement v/s Statement" Watch "PreparedStatement v/s Statement" New topic
Author

PreparedStatement v/s Statement

Reema Patel
Ranch Hand

Joined: Jan 26, 2006
Posts: 169
I have a servlet wherein I'm just firing a SELECT Query which uses an INNER JOIN, and it wud b accessed my multiple clients concurrently. Should I use PreparedStatement instead of Statement to improve performance.

Or is it like on a select statement it hardly makes any difference?
Kj Reddy
Ranch Hand

Joined: Sep 20, 2003
Posts: 1704
I suggest you to use prepared statement.
Hemanth Pallavajula
Ranch Hand

Joined: Oct 07, 2004
Posts: 38
Hi Reema,

If you are having SQL queries, for which you don't pass parameter values dynamically, then you can for Statment itself.

Otherwise, PreparedStatement is preferable.

The reason is that the SQL queries being passed to the Oracle are parsed by the DB Engine. If you are using the Statement, everytime the parsing is done, and this impacts the performance. In case, you use PreparedStatement, the parsing is done only once and everytime you send a value it is attached to the query just before execution.


Cheers,<br />Hemanth...<br />(When opportunity doesn't knock, build a door.)
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8898

If you are having SQL queries, for which you don't pass parameter values dynamically, then you can for Statment itself.


FYI, Oracle caches the Statement query also.


Groovy
ak pillai
author
Ranch Hand

Joined: Feb 11, 2006
Posts: 288
-- Prepared statements offer better performance, as they are pre-compiled. Prepared statements reuse the same execution plan for different arguments rather than creating a new execution plan every time. Prepared statements use bind arguments, which are sent to the database engine. This allows mapping different requests with same prepared statement but different arguments to execute the same execution plan.

-- Prepared statements are more secure because they use bind variables, which can prevent SQL injection attack.

The most common type of SQL injection attack is SQL manipulation. The attacker attempts to modify the SQL statement by adding elements to the WHERE clause or extending the SQL with the set operators like UNION, INTERSECT etc.


java j2ee job interview questions with answers | Learn the core concepts and the key areas
Reema Patel
Ranch Hand

Joined: Jan 26, 2006
Posts: 169
Thanks! 4 the help...This has givem me enough food for thought!
R. M. Menon
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
Answer from ak pillai state the two major reasons why Prepared statements are almost always preferable when executing DML.
The first reason (reuse of an execution plan) shows even more in terms of performance improvement when you concurrent users (true for most real world OLTP applications.)

When executing DDL, you should use Statement since you cant bind in DDLs - but typically you dont use JDBC to do DDL statement (Example of DDL "create table" etc.) Again, you can check out my sample chapter 5 at http://www.bookpool.com/sm/159059407X where I go in more detail about this topic
Rajan Chinna
Ranch Hand

Joined: Jul 01, 2004
Posts: 320
-- Prepared statements are more secure because they use bind variables, which can prevent SQL injection attack.

The most common type of SQL injection attack is SQL manipulation. The attacker attempts to modify the SQL statement by adding elements to the WHERE clause or extending the SQL with the set operators like UNION, INTERSECT etc.


Pillai, don't you think we can attack the site though you use Prepared Statement?

Let's say i have a sql <select user from table where login=?>
And the user keyin a value "billgates or 1=1"
Isn't that valnarable to SQL injection attack though you use prepared stmt?

I feel this kind of attack is hard to control at DB level. However it can be controlled at presentation layer doing user input validation.
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
No, that attack shouldn't work. It isn't a text substitution, it is a typed value substitution.

Strictly speaking, the attack isn't based on whether you use Statement or PreparedStatement, it is based on how you build the SQL string used by either of them. If you concatenate fragments of SQL together based on some incoming request, or if you always specify every possible column in the SQL statement, either way you leave yourself open to hackers submitting requests to do things an application wasn't otherwise intended to do.


Reid - SCJP2 (April 2002)
R. M. Menon
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
No, that attack shouldn't work. It isn't a text substitution, it is a typed value substitution.


Correct - if you use PreparedStatement, the atatcker will fail as the SQL will fail.

Strictly speaking, the attack isn't based on whether you use Statement or PreparedStatement, it is based on how you build the SQL string used by either of them. If you concatenate fragments of SQL together based on some incoming request, or if you always specify every possible column in the SQL statement, either way you leave yourself open to hackers submitting requests to do things an application wasn't otherwise intended to do.


Well, if you use Statement, you CAN NOT use bind variables - which is the best technique to prevent SQL injection in almost all cases. So, I dont quite agree with that statement. Sure you can use PreparedStatement and still not use bind variables but I am assuming we are not discussin that possibility here...
You may be concatenating to form an SQL statement but if you took care to use bind variables, SQL injection is not an issue for you.
--------------------
Reid M. Pinchback
Ranch Hand

Joined: Jan 25, 2002
Posts: 775
Originally posted by R. M. Menon:
Sure you can use PreparedStatement and still not use bind variables but I am assuming we are not discussin that possibility here...


I'm discussing it. My point is that the class name of the object created and used has nothing to do with the problem, the programming practice is the problem. Security issues are almost always more an issue of programming practice than the technology artifacts linked to the code, or hackers would have a much harder time doing what they do. Personally, I'm remembering a project that had several hundred of lines of legacy JDBC code where dynamic sql with and without bind items was fed into prepared statements. :roll:
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PreparedStatement v/s Statement
 
Similar Threads
Apostrophe Problem in String
Statement and PreparedStatement
sql query error
PreparedStatement related query
benefit of Statement over PreparedStatement?