• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

PreparedStatement v/s Statement

 
Ranch Hand
Posts: 169
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 1704
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I suggest you to use prepared statement.
 
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
author
Posts: 288
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
-- 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.
 
Reema Patel
Ranch Hand
Posts: 169
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks! 4 the help...This has givem me enough food for thought!
 
Author
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 320
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

-- 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.
 
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
R. M. Menon
Author
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 775
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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:
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic