File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Statement and PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Statement and PreparedStatement" Watch "Statement and PreparedStatement" New topic
Author

Statement and PreparedStatement

Arvind Mahendra
Ranch Hand

Joined: Jul 14, 2007
Posts: 1162
I am trying to learn about the difference between Statement and PreparedStatement and came across this jdbc tutorial.
Scrolling down to the part labeled 'Three kinds of Statements' it says "Statement: Execute simple sql queries without parameters."

My question is what does this mean? I can execute queries with parameters using statement for ex:

Above 'username' is a parameter. So I'm confused as to what does that tutorial mean by 'parameters'?


I want to be like marc
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19655
    
  18

PreparedStatement allows you to specify the values you fill in by using question marks. You can then set them using the value you need - int, String, InputStream, Blob, etc.

Now you can pass ints and Strings using Statement as well like you did it, but the difference is that PreparedStatement escapes the value, making it safe(r) against people trying to pass nasty values. Search a bit for "SQL injection" and you'll get the point.

Here's how your code works with a PreparedStatement:

As you'll see the first question mark will be replaced with userName. Note that, much like other JDBC classes, counting starts at 1, not 0.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Arvind Mahendra
Ranch Hand

Joined: Jul 14, 2007
Posts: 1162
So can we say that we can use parameters with Statement but they must be hardcoded, whereas using parameters with PreparedStatement they can change at runtime?
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19655
    
  18

Well, your own code has shown that you can use a statement with parameters that can change during runtime.

The thing is, if you use Statement, you will have to check your parameters yourself. Especially with strings that can be hard, and PreparedStatement takes that difficulty away from you.

Now for numbers there is not much chance a user inputs an incorrect value that can corrupt your data, but with strings the chance is that some day it WILL occur.

Simple example:
Query: "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'"

Now what will happen if someone enters the following values:
username: ' OR '1'='1
password: ' OR '1'='1

The result:
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1'

Now that will surely get him in without a problem, no matter the operator precedence.


If you use PreparedStatement however, the full username and password will be just the values to look for; it will only return those rows for which the username = "' OR '1'='1" and the password is "' OR '1'='1". Not likely there will be any.
 
wood burning stoves
 
subject: Statement and PreparedStatement
 
Similar Threads
how to execute complex queries
Prepared Statement vs Callable Statement
How to make INSERT and UPDATE with the ' character in the String?
does Hibernate supports Stored Procedures?
Are there any problems using the same statement for multiple queries?