Error in executeUpdate

[Keshini Weerasuriya]

st.executeUpdate("UPDATE UserAccount" + " SET password ="+newPass + "WHERE UserName = "+UName);

SQL command not properly ended

Hi.. I can't seem to find the error in the above statement.. Can anyone help?? Pls..

[seby mathew]

Pls modify your code as follows and check the print line

String query = "UPDATE UserAccount SET password ='" +newPass+ "' WHERE UserName = '" +UName+ "'";

System.out.println(query );

st.executeUpdate(query);

[Keshini Weerasuriya]

Thanks Mathew.. It works..

[stu derby]

It will work until someone tries to put a single-quote character in their username or password, then it will break.

The solution is to use PreparedStatement and its setString() method, instead of Statement.

Using PreparedStatement in general throughtout your code will also make it more secure and more readable/maintainable.

See:
http://fishbowl.pastiche.org/2003/11/10/ahov_2_sql_injection

[seby mathew]

ofcourse 'PreparedStatement' is a solution for tackling the single chords,
but remember 'PreparedStatement' is not meant for this purpose.

In best practice it is better to use 'PreparedStatement' only when your qry is executing frequently, or else better use 'Statement' , but you have to tackle the single chords in your string.

[stu derby]

Originally posted by seby mathew:
ofcourse 'PreparedStatement' is a solution for tackling the single chords,
but remember 'PreparedStatement' is not meant for this purpose.

In best practice it is better to use 'PreparedStatement' only when your qry is executing frequently, or else better use 'Statement' , but you have to tackle the single chords in your string.

That's wrong. You should use PreparedStatement for all INSERT/UPDATE/DELETE/SELECT statements that contain data values, most especially if the statement is going to contain user-supplied input, but even if not.

There are many reasons:
1) to protect against SQL-injection attacks
2) more efficient, even when the statement is re-executed with low frequency (possibly as low as once a week, depending on your database and your database load).
3) no problems with escaping single-quote characters
4) greatly reduces problems caused by implicit data-type conversions
5) easier to code and read the SQL; no chance of syntax errors caused by the mess of string concatenations, far far fewer logic errors from coding something other than what you meant.

At my company, when we interview a candidate for a job we give them a small programming problem. If they claim knowledge of JDBC and don't use PrepearedStatement at the appropriates places, we usually don't make them a job offer; we don't have time to train people out of bad and dangerous habits.

[seby mathew]

There's a popular belief that using a PreparedStatement object is faster than using a Statement object. After all, a prepared statement has to verify its metadata against the database only once, while a statement has to do it every time. The truth of the matter is that it takes about 65 iterations of a prepared statement before its total time for execution catches up with a statement.

- www.oreilly.com

Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency

- java.sun.com



'protection against SQL-injection attacks' is ofcourse an advantage but not the primary advantage.


this is not the right space to discuss the company policy

[stu derby]

Originally posted by seby mathew:
There's a popular belief that using a PreparedStatement object is faster than using a Statement object. After all, a prepared statement has to verify its metadata against the database only once, while a statement has to do it every time. The truth of the matter is that it takes about 65 iterations of a prepared statement before its total time for execution catches up with a statement.

- www.oreilly.com

The URL you wished to cite is incomplete, the full URL is:
www.oreilly.com Oracle/JDBC

That widely cited performance test (using Oracle) has a major bug in it and was totally discredited years ago; it was actually comparing the time to execute a set of Statements with the time to load the PreparedStatement class plus execute the same sized set of PreparedStatements. Class loading time is very significant, so it was an "apples to oranges" test that gives wrong and misleading results.

In fact, when the test is done correctly, executing a single PreparedStatement is about as fast as a single Statement, and after that, PreparedStatement is faster (on Oracle). A quick summary of results:

Rows to Insert Statement PrepareStatement
1 0.05 seconds 0.05 seconds
10 0.30 seconds 0.18 seconds
100 2.69 seconds 1.44 seconds
1000 28.25 seconds 15.25 seconds

See here, about the 2nd or 3rd section down:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:10128287191505
The code that produces this result is supplied, so you can try it for yourself.

Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency

- java.sun.com

When it comes to Oracle database performance, I would rather believe Tom Kyte, a VP at Oracle and the author of my above-cited link to anything Sun has to say.

'protection against SQL-injection attacks' is ofcourse an advantage but not the primary advantage.

I don't know how you rate something "primary", but I consider preventing unauthorized use or even unauthorized destruction of data by strangers on the Internet pretty important.

this is not the right space to discuss the company policy

Using PreparedStatement correctly is important enough that a company that has been using JDBC extensively for 7 years considers the inappropriate use of Statement as a warning sign when making hiring decisions.