aspose file tools*
The moose likes JDBC and the fly likes prepared statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "prepared statement" Watch "prepared statement" New topic
Author

prepared statement

Mary Wallace
Ranch Hand

Joined: Aug 25, 2003
Posts: 138
How to write a prepared statement for a select statement like this

select wba
from wages_wba
where 3123 //a dynamic varible passed from form
between earnings_start_range and earnings_end_range;


thanks in advance:
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
From the Javadoc for PreparedStatement (which you should always read when having problems):

See if you can adapt that to your example.

Jules
Mary Wallace
Ranch Hand

Joined: Aug 25, 2003
Posts: 138
Julian,
I have read all those and know some basics about prepared statements. As you can see the select statement its little different from typical select and update statements ie why i asked for help.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874


it may help
i just edited it. now it will work. and remind you this is not the actual string. this is the way we tell how to write queries
[ September 06, 2004: Message edited by: adeel ansari ]
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Mary,

Agreed, but the principle doesn't change. Just substitute the number for a question mark and set it with the appropriate setXxx() method. I don't think Adeel's suggestion will do exactly what you want in this case.

Jules
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

Mary,
In a "PreparedStatement", a question-mark ("?") place-holder can be put anywhere that a literal value can be placed. Therefore, if I understand your question correctly, the code you require should be something like this:
[NOTE: Uncompiled and untested.]

Good Luck,
Avi.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30502
    
150

Avi,
That example won't work because it replaces more than just a value. You can't replace a full where clause with a question mark.

I agree that you can do select * from table where id = ? and field between ? and ?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Jeanne,

Avi's suggestion (and mine, although I was trying not to spell it out) does work. It's not a very intuitive way of using BETWEEN, hence Mary's confusion I expect, but the whole expression "? BETWEEN earnings_start_range AND earnings_end_range" is a single predicate in the WHERE clause. The question mark is substituted for a single value, 3123 in this case.

Jules
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30502
    
150

Jules,
I agree 100% with what you said. Maybe Avi's post has a typo in it that threw me.

Legal:
String sql = "select wba from wages_wba where id = ? " +
"between earnings_start_range and earnings_end_range";
String sql = "select wba from wages_wba where " +
"between ? and ?";

Not legal:
String sql = "select wba from wages_wba where ? " +
"between earnings_start_range and earnings_end_range";
String sql = "select wba from wages_wba where " +
"between ?";
tring sql = "select wba from wages_wba where ?";

As long as the ? replaces a value rather than the whole where clause it is fine.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

Hi Jeanne,
Are you familiar with this example:

Allow me to suggest you do an Internet search for the terms
"sql injection attack" -- you may find it interesting (assuming
you are unfamiliar with the phenomenon).

Good Luck,
Avi.
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hmmm, the code in Avi's post works perfectly as is (at least in MySQL); check it out. I'm pretty sure I've also used the same syntax in both Oracle and SQL Server in the past.

From your post (slightly edited for added clarity, hopefully), only the statement in bold is legal, ignoring my additions in italics:


I'm not sure how the reference to SQL injections is relevant to this discussion.

Jules
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

Julian,

I'm not sure how the reference to SQL injections is relevant to this discussion.

I just thought people would be interested in seeing the clever things that hackers can do with SQL, and to help them realize (as you have tried to do, also) that Jeanne has provided misleading examples of (what she claims is) "legal" SQL.
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
I see. I think the general concensus is that using PreparedStatement rather than plain old Statement will protect your app from SQL injection attacks, notwithstanding buggy JDBC driver implementations. PreparedStatement should be used as well as, not instead of, adequate validation of user input.

Jules
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30502
    
150

Avi,
If that is the case, why can't you do:


I know for a fact that doesn't work (in db2 at least) because I tried it a while back.

As for SQL injection attacks, you can't be a victim when using a prepared statement (as Jules pointed out.) SQL injection is more for when you do:
"select * from table where field = '" + value + "'" and value contains "5 and 1=1".
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30502
    
150

I reread my post and realized I had a typo in the two "legal" queries. It should have read:

Legal:
String sql = "select wba from wages_wba where id = ? and column " +
"between earnings_start_range and earnings_end_range";
String sql = "select wba from wages_wba where column " +
"between ? and ?";

Sorry about that!
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Originally posted by Jeanne Boyarsky:
If that is the case, why can't you do:


Because the ? would have to be substituted for the brackets and/or comma separators as well as the values. The following would be valid, as I'm sure you know:

The simple rule, as Avi stated in his first post, is that if it's a value it can be substituted for a ?

With regard to your SQL injection illustration, you'd have to do something funky with the single quotes in the injected string otherwise it won't affect the query.

Jules
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

Jeanne,

If that is the case

If what is the case?

why can't you do


Actually, you can. The following was tested using J2SE 1.4.2_05 and Oracle's
"ojdbc14.jar" (thin) JDBC driver on Windows XP against an
Oracle 9i (9.2.0.4) database running on SUN [sparc] Solaris 9.

Now regarding your comment on "SQL injection attack"...

SQL injection is more for when you do:


Actually, that should be "OR 1 = 1" [instead of "AND 1 = 1"] because then it
would retrieve _all_ the rows in the table. Adding "AND 1 = 1" does not
affect the query criteria at all.

Allow me to suggest Joe Celko's SQL for Smarties.

Good Luck,
Avi.
[ September 06, 2004: Message edited by: Avi Abrami ]
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Jules i did the same previously with little typo mistake, wht you and avi suggested afterwards.

now i edited it.

thanx
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by Avi Abrami:
Actually, you can.
Not in general you can't. Some drivers are happy with it. Others choke. I wouldn't use this technique.

- Peter
Mary Wallace
Ranch Hand

Joined: Aug 25, 2003
Posts: 138
Thanks for everybodys reply. My table is like this

Earnins_start_range Earnings_end_start wba

3000 4000 30


So if a person earned between 3000 and 4000 the wba is 30.

so in my prepared statement i cant say

where colum_name is ?





This sql is working for me but want to know how to write prepared statement for a scenario like this





Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
... where ? between earnings_start_range and earnings_end_range

Will work fine for a PreparedStatement. It's not where you usually find parameters in a BETWEEN clause, but there's nothing dodgy or strange about it.

- Peter
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30502
    
150

Avi,
Thanks for the details on SQL injection. I will take a look at that book for more details. It's interesting, but it's good to know that prepared statements save you anyway

It's also interesting that some drivers let you do in(?). I never tried it on Oracle. I tried it on db2 and it didn't work, so I assumed it didn't work across the board. I know not to rely on it in case I have to support db2 as well. But it is very interesting!
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
I've actually got that book - borrowed it from a mate a couple of years ago...ooops! Now, I like my SQL but that book's rather heavy going. It's also quite evident from Joe's style of writing that he fancies himself as a Smartie, and not the tasty, chocolatey kind! [I hope that reference travels!]

Jules
Giuoco Krag
Greenhorn

Joined: Mar 23, 2004
Posts: 10
Thanks a bunch for this thread (very insightful).

I really don't want to beat a dead horse (might be too late), but what is a work around for passing "(1, 2, 3, 4)" into 'select * from table where field in ? ' Peter? you say you wouldn't use this technique? what technique would you use to accomplish this?



Originally posted by Peter den Haan:
Actually, you can.<hr></blockquote>Not in general you can't. Some drivers are happy with it. Others choke. I wouldn't use this technique.

- Peter[/QB]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30502
    
150

Scot,
Assuming there are a finite number of items in your in clause, I would use:

This works well if you typically have the same number of items repeated. For example, I often do queries for 2, 3 or 4 items. That way there are only three prepared statements and they can be reused.
Giuoco Krag
Greenhorn

Joined: Mar 23, 2004
Posts: 10
Using (?,?,?) is a very clean way of handling 3 arguments. But when your arguments vary in number (like from 2 to 300), this doesn't really work that well.

Currently, I am being forced (because of this problem), to build my PreparedStatement with a StringBuffer (or something similar) so that I can query.append(" (1, 2, 3, 4, 5) "); in the middle of the query string... very ugly becuase every time I call the prepared statement, I have to "rebuild" the query string from scratch... Yuck.

Now that I look at it, I don't even think using PreparedStatment in this situation adds any value to the process... Probably just bloats the database with extra overhead...

Any other thoughts?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30502
    
150

Scot,
Regardless of my approach, I build the SQL dynamically using the StringBuffer. If you only have a finite set of choices, you can let the database optimize.

When I have many choices (like the range you described), I batch my statements. For example: two batches of 100 and threee single statements. This allows the database to give you some benefits of prepared statements. I described this approach in more detail in this thread.
Giuoco Krag
Greenhorn

Joined: Mar 23, 2004
Posts: 10
Thanks for the advice (and sorry for hijacking this thread). I saw a document on Oracle's sight that suggested that a performance gain is not realized with PreparedStatment until you reach about 500 uses of the same select. My application (a batch application) will never reach that level before completing it's tasks... It looks like my best bet is to use Statement and just create a StringBuffer to managed the variations of parameters.

Thanks again!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: prepared statement