This week's book giveaway is in the Mac OS forum. We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line! See this thread for details.
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'?
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.
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.